jdbc 事務(wù)保存點范例

jdbc 保存點的接口提供了額外的事務(wù)控制。設(shè)置一個保存點就是在事務(wù)中定義一個邏輯回滾點。如果發(fā)生錯誤，則可以使用 rollback 方法來撤消到上一個保存點。

復(fù)制并將以下示例代碼保存到：jdbcsavepoint.java 中，編譯并運行如下：

1. jdbc 事務(wù)保存點范例

//step 1. import required packages

import java.sql.*;

public class jdbcsavepoint {
   // 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 delete statment with
        // required arguments for rs example.
        system.out.println("creating statement...");
        stmt = conn.createstatement();

        //step 6: 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 7: delete rows having id grater than 104
        // but save point before doing so.
        savepoint savepoint1 = conn.setsavepoint("rows_deleted_1");
        system.out.println("deleting row....");
        string sql = "delete from employees " +
                    "where id = 106";
        stmt.executeupdate(sql);  
        // oops... we deleted too wrong employees!
        //step 8: rollback the changes afetr save point 2.
        conn.rollback(savepoint1);

        // step 9: delete rows having id grater than 104
        // but save point before doing so.
        savepoint savepoint2 = conn.setsavepoint("rows_deleted_2");
        system.out.println("deleting row....");
        sql = "delete from employees " +
                    "where id = 107";
        stmt.executeupdate(sql);  

        //step 10: now list all the available records.
        sql = "select id, first, last, age from employees";
        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. 編譯運行

編譯并運行結(jié)果如下 -

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

f:\worksp\jdbc>java -djava.ext.dirs=f:\worksp\jdbc\libs jdbcsavepoint
connecting to database...
thu jun 01 02:35:49 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...
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

deleting row....
deleting row....
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

goodbye!

f:\worksp\jdbc>