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

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

}

}

分享到：

2007-03-13 13:36

浏览 773

评论