您当前的位置： 首页 > 学无止境 > 心得笔记 网站首页心得笔记

【第17章：Java数据库编程】_事务处理

发布时间：2021-01-12 17:45:34编辑：雪饮阅读（）

 如果进行多条sql操作，一般如插入，若某个sql出错了，则在没有使用事务的情况下，一般就会出现，没有错误的sql都插入成功了，而有错误的则插入失败了。而安全的情况应该是要么成功，要么失败。

没有使用事务的情况
import java.sql.Connection ;
import java.sql.DriverManager ;
import java.sql.Statement ;
public class Hello{
       public static final String DBDRIVER = "org.gjt.mm.mysql.Driver" ;
       public static final String DBURL = "jdbc:mysql://localhost:3306/xynes" ;
       public static final String DBUSER = "root" ;
       public static final String DBPASS = "" ;
       public static void main(String args[]) throws Exception{
              Class.forName(DBDRIVER) ;
            Connection conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS) ;
             
              Statement stmt = conn.createStatement() ;
             
              stmt.addBatch("INSERT INTO v9_admin(username,password)" +" VALUES ('kasumi-1','phase220807') ") ;
              stmt.addBatch("INSERT INTO v9_admin(username,password)" +" VALUES ('snowDrink-2','220807phase') ") ;
              //出现一条错误sql
              stmt.addBatch("INSERT INTO v9_admin(username,password)" +" VALUES ('momiji-'3','snowDrink-3') ") ;
              //下面都是正确sql      
              stmt.addBatch("INSERT INTO v9_admin(username,password)" +" VALUES ('ayane-4','kasumi-4') ") ;
              stmt.addBatch("INSERT INTO v9_admin(username,password)" +" VALUES ('momiji-5','ayane-5') ") ;
             
 
              int temp[] = stmt.executeBatch() ;
              System.out.println("新增了" + temp.length + "条数据") ;
             
              stmt.close() ;
              conn.close() ;
       }
};

带事务的多条插入sql语句操作
import java.sql.Connection ;
import java.sql.DriverManager ;
import java.sql.Statement ;
public class Hello{
       public static final String DBDRIVER = "org.gjt.mm.mysql.Driver" ;
       public static final String DBURL = "jdbc:mysql://localhost:3306/xynes" ;
       public static final String DBUSER = "root" ;
       public static final String DBPASS = "" ;
       public static void main(String args[]) throws Exception{
              Class.forName(DBDRIVER) ;
            Connection conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS) ;
              //关闭默认的自动提交
              conn.setAutoCommit(false) ;
              Statement stmt = conn.createStatement() ;
             
              stmt.addBatch("INSERT INTO v9_admin(username,password)" +" VALUES ('kasumi-1','phase220807') ") ;
              stmt.addBatch("INSERT INTO v9_admin(username,password)" +" VALUES ('snowDrink-2','220807phase') ") ;
              //出现一条错误sql
              stmt.addBatch("INSERT INTO v9_admin(username,password)" +" VALUES ('momiji-'3','snowDrink-3') ") ;
              //下面都是正确sql      
              stmt.addBatch("INSERT INTO v9_admin(username,password)" +" VALUES ('ayane-4','kasumi-4') ") ;
              stmt.addBatch("INSERT INTO v9_admin(username,password)" +" VALUES ('momiji-5','ayane-5') ") ;
             
              try{
                     int temp[] = stmt.executeBatch() ;
                     System.out.println("新增了" + temp.length + "条数据") ;
                     conn.commit();
              }
              catch(Exception e){
                     try{
                            //有任何错误就回滚
                            conn.rollback() ;
                     }catch(Exception e1){
                            e1.printStackTrace();
                     }
              }
             
             
              stmt.close() ;
              conn.close() ;
       }
};
D:\>javac Hello.java
D:\>java Hello
需要注意的是被插入的数据表的存储引擎必须是可以支持事务的存储引擎，比如InnoDB。
然后会发现没有任何一条sql插入成功，这样就满足了要失败都失败，要成功都成功。
保存点操作
如果在一系列操作sql，一般如插入中某个插入sql后面设定了保存点，则回滚操作还可以不用全部回滚，可以仅仅回滚到该保存点，则该保存点之前的数据会被提交到数据表中，该保存点之后的数据则不会被提交到数据表中。
import java.sql.Connection ;
import java.sql.DriverManager ;
import java.sql.Statement ;
import java.sql.Savepoint ;
public class Hello{
       public static final String DBDRIVER = "org.gjt.mm.mysql.Driver" ;
       public static final String DBURL = "jdbc:mysql://localhost:3306/xynes" ;
       public static final String DBUSER = "root" ;
       public static final String DBPASS = "" ;
       public static void main(String args[]) throws Exception{
              Class.forName(DBDRIVER) ;
            Connection conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS) ;
              //关闭默认的自动提交
              conn.setAutoCommit(false) ;
              Statement stmt = conn.createStatement() ;
             
              stmt.executeUpdate("INSERT INTO v9_admin(username,password)" +" VALUES ('kasumi-1','phase220807') ") ;
              stmt.executeUpdate("INSERT INTO v9_admin(username,password)" +" VALUES ('snowDrink-2','220807phase') ") ;
              //设定保存点
              Savepoint sp = conn.setSavepoint() ;
             
              stmt.executeUpdate("INSERT INTO v9_admin(username,password)" +" VALUES ('ayane-4','kasumi-4') ") ;
              stmt.executeUpdate("INSERT INTO v9_admin(username,password)" +" VALUES ('momiji-5','ayane-5') ") ;
             
              try{
                     conn.rollback(sp);
                     conn.commit();
              }
              catch(Exception e){
                     e.printStackTrace() ;
              }
             
              stmt.close() ;
              conn.close() ;
       }
};
D:\>javac Hello.java
D:\>java Hello