Java 有没有办法从准备好的语句中检索自动增量 ID
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1376218/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a way to retrieve the autoincrement ID from a prepared statement
提问by jW.
Is there a way to retrieve the auto generated key from a DB query when using a java query with prepared statements.
在使用带有准备语句的 java 查询时，有没有办法从数据库查询中检索自动生成的密钥。
For example, I know AutoGeneratedKeys can work as follows.
例如，我知道 AutoGeneratedKeys 可以按如下方式工作。
stmt = conn.createStatement();
stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
if(returnLastInsertId) {
ResultSet rs = stmt.getGeneratedKeys();
rs.next();
auto_id = rs.getInt(1);
}
However. What if I want to do an insert with a prepared Statement.
然而。如果我想用准备好的语句插入怎么办。
String sql = "INSERT INTO table (column1, column2) values(?, ?)";
stmt = conn.prepareStatement(sql);
//this is an error
stmt.executeUpdate(Statement.RETURN_GENERATED_KEYS);
if(returnLastInsertId) {
//this is an error since the above is an error
ResultSet rs = stmt.getGeneratedKeys();
rs.next();
auto_id = rs.getInt(1);
}
Is there a way to do this that I don't know about. It seems from the javadoc that PreparedStatements can't return the Auto Generated ID.
有没有办法做到这一点，我不知道。从 javadoc 看来， PreparedStatements 无法返回自动生成的 ID。
采纳答案by Yishai
Yes. See here. Section 7.1.9. Change your code to:
是的。见这里。第 7.1.9 节。将您的代码更改为：
String sql = "INSERT INTO table (column1, column2) values(?, ?)";
stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
stmt.executeUpdate();
if(returnLastInsertId) {
ResultSet rs = stmt.getGeneratedKeys();
rs.next();
auto_id = rs.getInt(1);
}
回答by jW.
Yes, There is a way. I just found this hiding in the java doc.
是的，有办法。我刚刚发现它隐藏在 java 文档中。
They way is to pass the AutoGeneratedKeys id as follows
他们的方法是按如下方式传递 AutoGeneratedKeys id
String sql = "INSERT INTO table (column1, column2) values(?, ?)";
stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
回答by nos
There's a couple of ways, and it seems different jdbc drivers handles things a bit different, or not at all in some cases(some will only give you autogenerated primary keys, not other columns) but the basic forms are
有几种方法，似乎不同的 jdbc 驱动程序处理的事情有点不同，或者在某些情况下根本没有（有些只会给你自动生成的主键，而不是其他列）但基本形式是
stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
Or use this form:
或者使用这个表格：
String autogenColumns[] = {"column1","column2"};
stmt = conn.prepareStatement(sql, autogenColumns)
回答by peterong
I'm one of those that surfed through a few threads looking for solution of this issue ... and finally get it to work. FOR THOSE USING jdbc:oracle:thin: with ojdbc6.jar PLEASE TAKE NOTE: You can use either methods: (Method 1)
我是通过几个线程寻找这个问题的解决方案的人之一......最终让它工作。对于那些使用 jdbc:oracle:thin: with ojdbc6.jar 请注意：您可以使用任一方法：（方法 1）
Try{
String yourSQL="insert into Table1(Id,Col2,Col3) values(SEQ.nextval,?,?)";
myPrepStatement = <Connection>.prepareStatement(yourSQL, Statement.RETURN_GENERATED_KEYS);
myPrepStatement.setInt(1, 123);
myPrepStatement.setInt(2, 123);
myPrepStatement.executeUpdate();
ResultSet rs = getGeneratedKeys;
if(rs.next()) {
java.sql.RowId rid=rs.getRowId(1);
//what you get is only a RowId ref, try make use of it anyway U could think of
System.out.println(rid);
}
} catch (SQLException e) {
//
}
(Method 2)
（方法二）
Try{
String yourSQL="insert into Table1(Id,Col2,Col3) values(SEQ.nextval,?,?)";
//IMPORTANT: here's where other threads don tell U, you need to list ALL cols
//mentioned in your query in the array
myPrepStatement = <Connection>.prepareStatement(yourSQL, new String[]{"Id","Col2","Col3"});
myPrepStatement.setInt(1, 123);
myPrepStatement.setInt(2, 123);
myPrepStatement.executeUpdate();
ResultSet rs = getGeneratedKeys;
if(rs.next()) {
//In this exp, the autoKey val is in 1st col
int id=rs.getLong(1);
//now this's a real value of col Id
System.out.println(id);
}
} catch (SQLException e) {
//
}
Basically, try not used Method1 if you just want the value of SEQ.Nextval, b'cse it just return the RowID ref that you may cracked your head finding way to make use of it, which also don fit all data type you tried casting it to! This may works fine (return actual val) in MySQL, DB2 but not in Oracle.
基本上，如果您只想要 SEQ.Nextval 的值，请尽量不要使用 Method1，因为它只返回 RowID ref，您可能会破解您的头脑，寻找使用它的方法，这也不适合您尝试转换的所有数据类型它！这可能在 MySQL、DB2 中工作正常（返回实际值），但在 Oracle 中无效。
AND, turn off your SQL Developer, Toad or any client which use the same login session to do INSERT when you're debugging. It MAY not affect you every time (debugging call) ... until you find your apps freeze without exception for some time. Yes ... halt without exception!
并且，在调试时关闭 SQL Developer、Toad 或任何使用相同登录会话执行 INSERT 的客户端。它可能不会每次都影响你（调试调用）......直到你发现你的应用程序无一例外地冻结了一段时间。是的……无一例外地停下来！
回答by JDGuide
Connection connection=null;
int generatedkey=0;
PreparedStatement pstmt=connection.prepareStatement("Your insert query");
ResultSet rs=pstmt.getGeneratedKeys();
if (rs.next()) {
generatedkey=rs.getInt(1);
System.out.println("Auto Generated Primary Key " + generatedkey);
}
