jdbc getgeneratedkeys mysql_mysql基本的JDBC概念

26.3.1.4. 检索AUTO_INCREMENT列的值

在JDBC API 3.0版之前，没有从支持“自动增量”或ID列的数据库中检索关键值的标准方法。对于针对MySQL的早期JDBC驱动，总是不得不在语句接口上使用特定的MySQL方法，或在向拥有AUTO_INCREMENT关键字的表发出“INSERT后”发出“SELECT LAST_INSERT_ID()”。特殊的MySQL方法调用是不可移植的，而且对于发出“SELECT”以获取AUTO_INCREMENT关键字的值来说，需要对数据库执行另一往返操作，其效率不高。在下面的代码片段中，介绍了检索AUTO_INCREMENT值的三种不同方式。首先，介绍了JDBC-3.0新方法“getGeneratedKeys()”的使用方式，当你需要检索AUTO_INCREMENT关键字并访问JDBC-3.0，它是目前的首选方法。在第2个示例中，介绍了使用标准“SELECT LAST_INSERT_ID()”查询检索相同值的方法。在最后一个示例中，介绍了使用“insertRow()”方法时，用可更新结果集检索AUTO_INCREMENT值的方式。

示例26.8. 使用Statement.getGeneratedKeys()检索AUTO_INCREMENT列的值Statement stmt = null;

ResultSet rs = null;

try {

//

// 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);

//

// Issue the DDL queries for the table for this example

//

stmt.executeUpdate("DROP TABLE IF EXISTS autoIncTutorial");

stmt.executeUpdate(

"CREATE TABLE autoIncTutorial ("

+ "priKey INT NOT NULL AUTO_INCREMENT, "

+ "dataField VARCHAR(64), PRIMARY KEY (priKey))");

//

// Insert one row that will generate an AUTO INCREMENT

// key in the 'priKey' field

//

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 (SQLException ex) {

// ignore

}

}

if (stmt != null) {

try {

stmt.close();

} catch (SQLException ex) {

// ignore

}

}

}

示例26.9. 使用SELECT LAST_INSERT_ID()检索AUTO_INCREMENT列的值Statement stmt = null;

ResultSet rs = null;

try {

//

// Create a Statement instance that we can use for

// 'normal' result sets.

stmt = conn.createStatement();

//

// Issue the DDL queries for the table for this example

//

stmt.executeUpdate("DROP TABLE IF EXISTS autoIncTutorial");

stmt.executeUpdate(

"CREATE TABLE autoIncTutorial ("

+ "priKey INT NOT NULL AUTO_INCREMENT, "

+ "dataField VARCHAR(64), PRIMARY KEY (priKey))");

//

// Insert one row that will generate an AUTO INCREMENT

// key in the 'priKey' field

//

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 (SQLException ex) {

// ignore

}

}

if (stmt != null) {

try {

stmt.close();

} catch (SQLException ex) {

// ignore

}

}

}

示例26.10. 在可更新的ResultSets中检索AUTO_INCREMENT列的值Statement stmt = null;

ResultSet rs = 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

//

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,

java.sql.ResultSet.CONCUR_UPDATABLE);

//

// Issue the DDL queries for the table for this example

//

stmt.executeUpdate("DROP TABLE IF EXISTS autoIncTutorial");

stmt.executeUpdate(

"CREATE TABLE autoIncTutorial ("

+ "priKey INT NOT NULL AUTO_INCREMENT, "

+ "dataField VARCHAR(64), PRIMARY KEY (priKey))");

//

// 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 (SQLException ex) {

// ignore

}

}

if (stmt != null) {

try {

stmt.close();

} catch (SQLException ex) {

// ignore

}

}

}