JDBC 3種獲得mysql插入資料的自增欄位值的方法

獲得mysql auto increment欄位值的3種方法
居然在mysql內建的docs中找到了。下面測試程式可以運行

-----------都必須在先插入後，才可以得到 Statement.getGeneratedKeys()
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();
 }

}