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

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

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.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?”,”root”, “admin”);

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

stmt = conn.createStatement();

stmt.executeUpdate(“DROP TABLE IF EXISTS autoIncTutorial”);

//创建数据库表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:3306/test”,”root”, “admin”);

// 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”,”root”, “admin”);

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’ toa MySQL database already available

Class.forName(“com.mysql.jdbc.Driver”);

conn = DriverManager

.getConnection(“jdbc:mysql://localhost/test”,”root”, “admin”);

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) {

}

}

}

}

public static void main(String[] args) throws Exception {

RetrievAutoIncrementTesttest = new RetrievAutoIncrementTest();

test.init();

test.test1();//测试第一种获取自增字段的值

test.test2();//测试第二种获取自增字段的值

test.test3();//测试第三种获取自增字段的值

}

}

http://www.codesky.net/article/200906/168971.html