mysql数据库事务操作

数据库中的事务就是一个事务中的操作要么一起成功,要么一起失败,事务本身具有原子性、一致性、隔离性和持久性。

  • SET AUTOCOMMIT = 0;  //取消自动提交,开始事务处理

  • SET AUTOCOMMIT = 1;  //开启自动提交,关闭事务处理

  • START TRANSACTION;  //启动事务

  • BEGIN 启动事务,相当于START TRANSACTION

  • COMMIT 提交事务

  • ROLLBACK 回滚全部操作

  • SAVEPOINT 设置事务保存点

  • POLLBACK TO SAVEPOINT 回滚操作到保存点

上面的命令都是基于session的,数据库把每一个连接到数据库的用户看作一个session。下面是数据库事务提交的一个简单例子:

package com.yeetrack.test;
   
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
   
/**
 * Created with IntelliJ IDEA.
 * User: youthflies
 * Date: 13-3-7
 * Time: 上午11:43
 * To change this template use File | Settings | File Templates.
 */
public class TranMysql
{
    public static final String DBDRIVER = "com.mysql.jdbc.Driver";
    public static final String DBURL = "jdbc:mysql://127.0.0.1:3306/test";
    public static final String DBUSER = "root";
    public static final String DBPASS = "toor";
   
    public static void main(String[] args)
    {
        Connection conn = null;
        Statement stmt = null;
        try
        {
            Class.forName(DBDRIVER);
            conn = DriverManager.getConnection(DBURL,DBUSER, DBPASS);
            conn.setAutoCommit(false); //取消自动提交
            stmt = conn.createStatement();
            //插入三条数据
            stmt.addBatch("insert into User (name, password) values ('youthflies','youthflies')");
            stmt.addBatch("insert into User (name, password) values ('gameace', 'gameace')");
            stmt.addBatch("insert into User (name, password) values ('victorace', 'victorace')");
        } catch (ClassNotFoundException e)
        {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } catch (SQLException e)
        {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
        try
        {
            int temp[] = stmt.executeBatch(); //批量执行
            for(int i=0;i<=temp.length-1;i++)
                System.out.println(temp[i]);
            System.out.println("执行了:"+temp.length);
            conn.commit(); //批量执行成功就提交事务
   
        } catch (SQLException e)
        {
            try
            {
                conn.rollback(); //批量执行失败,事务回滚
            } catch (SQLException e1)
            {
                e1.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
            }
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
   
        try
        {
            stmt.close();
            conn.close();
        } catch (SQLException e)
        {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
    }
}

发表评论

电子邮件地址不会被公开。 必填项已用*标注

(Spamcheck Enabled)