jdbc 事務提交回滾范例

所謂事務就是把單個 sql 語句或一組 sql 語句作為一個邏輯單元，如果任何語句失敗，整個事務失敗。以下是使用事務教程中描述的提交和回滾的代碼示例。

1. jdbc 事務提交回滾范例

復制并將以下示例代碼保存到：commitandrollback.java 中，編譯并運行如下 -

//step 1. import required packages

import java.sql.*;

public class commitandrollback {
   // jdbc driver name and database url
   static final string jdbc_driver = "com.mysql.jdbc.driver";  
   static final string db_url = "jdbc:mysql://localhost/emp";

   //  database credentials
   static final string user = "root";
   static final string pass = "123456";

 public static void main(string[] args) {
   connection conn = null;
   statement stmt = null;
   try{
      //step 2: register jdbc driver
      class.forname("com.mysql.jdbc.driver");

      //step 3: open a connection
      system.out.println("connecting to database...");
      conn = drivermanager.getconnection(db_url,user,pass);

      //step 4: set auto commit as false.
      conn.setautocommit(false);

      //step 5: execute a query to create statment with
      // required arguments for rs example.
      system.out.println("creating statement...");
      stmt = conn.createstatement(
                           resultset.type_scroll_insensitive,
                           resultset.concur_updatable);

      //step 6: insert a row into employees table
      system.out.println("inserting one row....");
      string sql = "insert into employees " +
                    "values (106, 28, 'curry', 'stephen')";
      stmt.executeupdate(sql);  

      //step 7: insert one more row into employees table
      sql = "insert into employees " +
                    "values (107, 32, 'kobe', 'bryant')";
      stmt.executeupdate(sql);

      //step 8: commit data here.
      system.out.println("commiting data here....");
      conn.commit();

      //step 9: now list all the available records.
      string sql = "select id, first, last, age from employees";
      resultset rs = stmt.executequery(sql);
      system.out.println("list result set for reference....");
      printrs(rs);

      //step 10: clean-up environment
      rs.close();
      stmt.close();
      conn.close();
   }catch(sqlexception se){
      //handle errors for jdbc
      se.printstacktrace();
      // if there is an error then rollback the changes.
      system.out.println("rolling back data here....");
      try{
         if(conn!=null)
            conn.rollback();
      }catch(sqlexception se2){
         se2.printstacktrace();
      }//end try

   }catch(exception e){
      //handle errors for class.forname
      e.printstacktrace();
   }finally{
      //finally block used to close resources
      try{
         if(stmt!=null)
            stmt.close();
      }catch(sqlexception se2){
      }// nothing we can do
      try{
         if(conn!=null)
            conn.close();
      }catch(sqlexception se){
         se.printstacktrace();
      }//end finally try
   }//end try
   system.out.println("goodbye!");
}//end main

   public static void printrs(resultset rs) throws sqlexception{
      //ensure we start with first row
      rs.beforefirst();
      while(rs.next()){
         //retrieve by column name
         int id  = rs.getint("id");
         int age = rs.getint("age");
         string first = rs.getstring("first");
         string last = rs.getstring("last");

         //display values
         system.out.print("id: " + id);
         system.out.print(", age: " + age);
         system.out.print(", first: " + first);
         system.out.println(", last: " + last);
     }
     system.out.println();
   }//end printrs()
}//end jdbcexample

2. 運行提交回滾范例

編譯并運行結果如下 -

f:\worksp\jdbc>javac -djava.ext.dirs=f:\worksp\jdbc\libs commitandrollback.java

f:\worksp\jdbc>java -djava.ext.dirs=f:\worksp\jdbc\libs commitandrollback
connecting to database...
thu jun 01 02:30:04 cst 2017 warn: establishing ssl connection without server's identity verification is not recommended. according to mysql 5.5.45+, 5.6.26+ and 5.7.6+ requirements ssl connection must be established by default if explicit option isn't set. for compliance with existing applications not using ssl the verifyservercertificate property is set to 'false'. you need either to explicitly disable ssl by setting usessl=false, or set usessl=true and provide truststore for server certificate verification.
creating statement...
inserting one row....
commiting data here....
list result set for reference....
id: 100, age: 28, first: max, last: su
id: 101, age: 25, first: wei, last: wang
id: 102, age: 35, first: xueyou, last: zhang
id: 103, age: 30, first: jack, last: ma
id: 106, age: 28, first: curry, last: stephen
id: 107, age: 32, first: kobe, last: bryant

goodbye!

f:\worksp\jdbc>