JDBC Transaction

两条需求sql需要同时执行，如银行转账等。

1.将AutoCommit()属性设置为false不让自动提交

2.执行自己的操作

3.自己进行提交commit(),提交完之后将AutoCommit()属性置为true。catch到任何SQLException，要rollback。

import java.sql.*;

public class JDBCTransaction {
    //JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://127.0.0.1:3306/firstDB?useSSL=false&useUnicode=true&characterEncoding=UTF-8";
    static final String USER = "root";
    static final String PASS = "123";

    public static void main(String[] args)
    {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
            conn.setAutoCommit(false);
            System.out.println("Creating statement...");
            stmt = conn.createStatement();
            stmt.addBatch("insert into book values(11,'巴黎圣母院',51,600)");
            stmt.addBatch("insert into book values(12,'阿Q正传',52,550)");
            stmt.addBatch("insert into book values(13,'孔乙己',53,580)");
            stmt.executeBatch();
            conn.commit();
            conn.setAutoCommit(true);
            stmt.close();
            conn.close();
        }catch (ClassNotFoundException e)
        {
            e.printStackTrace();
        }
        catch (SQLException se)
        {
            se.printStackTrace();
            try {
                if (conn != null)
                    conn.rollback();
                    conn.setAutoCommit(true);
            }catch (SQLException e1)
            {
                e1.printStackTrace();
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }finally {
            try {
                if (stmt!=null)
                {
                    stmt.close();
                    stmt = null;
                }
            }catch (SQLException se2)
            {
                se2.printStackTrace();
            }
            try
            {
                if (conn!=null)
                {
                    conn.close();
                    conn = null;
                }
            }
            catch (SQLException se)
            {
                se.printStackTrace();
            }
            try{
                if (rs!=null)
                {
                    rs.close();
                    rs = null;
                }
            }catch (SQLException se1)
            {
                se1.printStackTrace();
            }
        }
        System.out.println("try finish");
    }
}
//输出
Connecting to database...
Creating statement...
try finish

Snip20191110_4.png