JDBC 3种获得mysql插入数据的自增字段值的方法

获得mysql auto increment字段值的3种方法
居然在mysql自带的docs中找到了。下面测试程序可以运行
1。Retrieving AUTO_INCREMENT Column Values using Statement.getGeneratedKeys()
2。Retrieving AUTO_INCREMENT Column Values using SELECT LAST_INSERT_ID()
3。Retrieving AUTO_INCREMENT Column Values in Updatable ResultSets

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class RetrievAutoIncrementTest {

 public void init()  throws Exception {
   Statement stmt = null;
   ResultSet rs = null;
   Connection conn = null;
   try {
    Class.forName("com.mysql.jdbc.Driver");
    conn = DriverManager.getConnection("jdbc:mysql://localhost/test?autoReconnect=true&useUnicode=true&characterEncoding=utf-8&mysqlEncoding=utf8", "root", "******");
       //
       // Issue the DDL queries for the table for this example
       //    
    stmt = conn.createStatement();
       stmt.executeUpdate("DROP TABLE IF EXISTS autoIncTutorial");
       stmt.executeUpdate(
               "CREATE TABLE autoIncTutorial ("
               + "priKey INT NOT NULL AUTO_INCREMENT, "
               + "dataField VARCHAR(64), PRIMARY KEY (priKey))");
   } finally {     
      if( rs != null ) {try{rs.close();}catch(Exception e){}}
      if( stmt != null ) {try{stmt.close();}catch(Exception e){}}
      if( conn != null ) {try{conn.close();}catch(Exception e){}}
  }
 }
 
 public void test1() throws Exception {
     Statement stmt = null;
     ResultSet rs = null;    
     Connection conn = null;
     try {
   Class.forName("com.mysql.jdbc.Driver");
   conn = DriverManager.getConnection("jdbc:mysql://localhost/test?autoReconnect=true&useUnicode=true&characterEncoding=utf-8&mysqlEncoding=utf8", "root", "******");
   //
      // Create a Statement instance that we can use for
      // 'normal' result sets assuming you have a
      // Connection 'conn' to a MySQL database already
      // available
      stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
                                  java.sql.ResultSet.CONCUR_UPDATABLE);

      //
      // Insert one row that will generate an AUTO INCREMENT
      // key in the 'priKey' field
      //
      for(int i=0; i<10; i++) {
       stmt.executeUpdate(
               "INSERT INTO autoIncTutorial (dataField) "
               + "values ('Can I Get the Auto Increment Field?')",
               Statement.RETURN_GENERATED_KEYS);
       //
       // Example of using Statement.getGeneratedKeys()
       // to retrieve the value of an auto-increment
       // value
       // 
       int autoIncKeyFromApi = -1; 
       rs = stmt.getGeneratedKeys(); 
       if (rs.next()) {
           autoIncKeyFromApi = rs.getInt(1);
       } else { 
           // throw an exception from here
       }
       rs.close();
       rs = null;
       System.out.println("Key returned from getGeneratedKeys():"
           + autoIncKeyFromApi);
      }     
  } finally {
      if( rs != null ) {try{rs.close();}catch(Exception e){}}
      if( stmt != null ) {try{stmt.close();}catch(Exception e){}}
      if( conn != null ) {try{conn.close();}catch(Exception e){}}     
  }
 }
 
 public void test2() throws Exception {
     Statement stmt = null;
     ResultSet rs = null;
     Connection conn = null;
     try {

      //
      // Create a Statement instance that we can use for
      // 'normal' result sets.
   Class.forName("com.mysql.jdbc.Driver");
   conn = DriverManager.getConnection("jdbc:mysql://localhost/test?autoReconnect=true&useUnicode=true&characterEncoding=utf-8&mysqlEncoding=utf8", "root", "******");

      stmt = conn.createStatement();
      //
      // Insert one row that will generate an AUTO INCREMENT
      // key in the 'priKey' field
      //
      for(int i=0; i<10; i++) {
       stmt.executeUpdate(
               "INSERT INTO autoIncTutorial (dataField) "
               + "values ('Can I Get the Auto Increment Field?')");
 
       //
       // Use the MySQL LAST_INSERT_ID()
       // function to do the same thing as getGeneratedKeys()
       // 
       int autoIncKeyFromFunc = -1;
       rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");
 
       if (rs.next()) {
           autoIncKeyFromFunc = rs.getInt(1);
       } else {
           // throw an exception from here
       } 
       rs.close(); 
       System.out.println("Key returned from " + "'SELECT LAST_INSERT_ID()': "
           + autoIncKeyFromFunc);
      }
  } finally {
      if( rs != null ) {try{rs.close();}catch(Exception e){}}
      if( stmt != null ) {try{stmt.close();}catch(Exception e){}}
      if( conn != null ) {try{conn.close();}catch(Exception e){}} 
  }
 }
 
 public void test3() throws Exception {
     Statement stmt = null;
     ResultSet rs = null;
     Connection conn = null;
     try {
      //
      // Create a Statement instance that we can use for
      // 'normal' result sets as well as an 'updatable'
      // one, assuming you have a Connection 'conn' to
      // a MySQL database already available
      //

         Class.forName("com.mysql.jdbc.Driver");
   conn = DriverManager.getConnection("jdbc:mysql://localhost/test?autoReconnect=true&useUnicode=true&characterEncoding=utf-8&mysqlEncoding=utf8", "root", "******");     
      stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
                                  java.sql.ResultSet.CONCUR_UPDATABLE);
      for(int i=0; i<10; i++) {
       //
       // Example of retrieving an AUTO INCREMENT key
       // from an updatable result set
       //
       rs = stmt.executeQuery("SELECT priKey, dataField "
          + "FROM autoIncTutorial");
 
       rs.moveToInsertRow();
       rs.updateString("dataField", "AUTO INCREMENT here?");
       rs.insertRow();
 
       //
       // the driver adds rows at the end
       //
       rs.last();
       //
       // We should now be on the row we just inserted
       //
       int autoIncKeyFromRS = rs.getInt("priKey");
       rs.close();
       rs = null;
       System.out.println("Key returned for inserted row: "
           + autoIncKeyFromRS);
      }
  } finally {
      if( rs != null ) {try{rs.close();}catch(Exception e){}}
      if( stmt != null ) {try{stmt.close();}catch(Exception e){}}
      if( conn != null ) {try{conn.close();}catch(Exception e){}} 
  }
 }
 
 /**
  * @param args
  */
 public static void main(String[] args) throws Exception {
  RetrievAutoIncrementTest test = new RetrievAutoIncrementTest();
  test.init();
  test.test1();
  test.test2();
  test.test3();
 }

}