JDBC 3种获得mysql插入数据的自增字段值的方法
来源:互联网 发布:google chrome翻墙mac 编辑:程序博客网 时间:2024/04/27 16:27
1. Retrieving AUTO_INCREMENT Column Values using Statement.getGeneratedKeys()
2. Retrieving AUTO_INCREMENT Column Values using SELECT LAST_INSERT_ID()
stmt.executeUpdate("INSERT INTO autoIncTutorial (dataField) "
http://blog.sina.com.cn/s/blog_4cc16fc50100c6sz.html
mysql 官方说明:
http://dev.mysql.com/doc/connector-j/en/connector-j-usagenotes-last-insert-id.html
6.4 RetrievingAUTO_INCREMENT
Column Values through JDBC
Before version 3.0 of the JDBC API, there was no standard way of retrieving key values from databases that supported auto increment or identity columns. With older JDBC drivers for MySQL, you could always use a MySQL-specific method on theStatement
interface, or issue the query SELECT LAST_INSERT_ID()
after issuing an INSERT
to a table that had an AUTO_INCREMENT
key. Using the MySQL-specific method call isn't portable, and issuing aSELECT
to get the AUTO_INCREMENT
key's value requires another round-trip to the database, which isn't as efficient as possible. The following code snippets demonstrate the three different ways to retrieve AUTO_INCREMENT
values. First, we demonstrate the use of the new JDBC 3.0 methodgetGeneratedKeys()
which is now the preferred method to use if you need to retrieveAUTO_INCREMENT
keys and have access to JDBC 3.0. The second example shows how you can retrieve the same value using a standardSELECT LAST_INSERT_ID()
query. The final example shows how updatable result sets can retrieve theAUTO_INCREMENT
value when using the insertRow()
method.
Example 6.8 Connector/J: Retrieving AUTO_INCREMENT
column values using Statement.getGeneratedKeys()
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(); // // 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 } 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 } }}
Example 6.9 Connector/J: Retrieving AUTO_INCREMENT
column values using SELECT LAST_INSERT_ID()
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 } 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 } }}
Example 6.10 Connector/J: Retrieving AUTO_INCREMENT
column values in Updatable ResultSets
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"); 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 } }}
Running the preceding example code should produce the following output:
Key returned from getGeneratedKeys(): 1Key returned from SELECT LAST_INSERT_ID(): 1Key returned for inserted row: 1
At times, it can be tricky to use the SELECT LAST_INSERT_ID()
query, as that function's value is scoped to a connection. So, if some other query happens on the same connection, the value is overwritten. On the other hand, thegetGeneratedKeys()
method is scoped by the Statement
instance, so it can be used even if other queries happen on the same connection, but not on the sameStatement
instance.
- JDBC 3种获得mysql插入数据的自增字段值的方法
- JDBC 3种获得mysql插入数据的自增字段值的方法
- JDBC 3种获得mysql插入数据的自增字段值的方法
- JDBC 3种获得mysql插入数据的自增字段值的方法
- 获取mysql表插入数据自增字段的值
- JDBC ORACLE: 取得SQL总列数; 插入数据后 取得指定自增字段(主键)的值
- JDBC应用程序连接数据库--插入数据并且获得相应自增长值的列
- mysql插入数据后返回自增ID的方法
- mysql插入数据后返回自增ID的方法
- mysql插入数据后返回自增ID的方法
- mysql插入数据后返回自增ID的方法
- mysql插入数据后返回自增ID的方法
- MySQL插入数据后返回自增id的方法
- 向含有自增字段的mysql数据库插入数据的问题
- mysql中含有自增字段数据表插入数据时需注意的问题
- 使用mysql插入数据时,对于自增字段的处理
- JDBC应用程序连接数据库–插入数据并且获得相应自增长值的列(即主键id)
- MySQL自增字段的设置方法
- 【Android 很进阶】android OOM理解以及相关方法分析
- xcode6 新建Pch文件
- iOS中UIWindow妙用(一) App 的密码保护功能
- mondrian 生成图表 XY轴数据过多
- 东莞已摘黄帽 东莞为什么会被叫做性都?
- JDBC 3种获得mysql插入数据的自增字段值的方法
- veste barbour homme the mother Chen Fengju two cronies to pick up one by one
- fib数列性质
- 2015红帽网上高峰论坛学习心得
- 串结构练习——字符串连接
- 1023. 组个最小数
- Android Activity 测试指南
- Android应用开发使用第三方字体
- 微信发送客服消息