java在插入前获取记录的下一个序列号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18002377/
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
java getting SQL next sequence number of the record before insertion
提问by Z.V
I have TestingIDvariable and a sql string as follows in my java code. the sql string will later be used for prepareStatement.
我有TestingID变量和SQL字符串作为我的Java代码如下。sql 字符串稍后将用于prepareStatement.
int TestingID;
String sqlInsert = "INSERT INTO TESTING VALUES(TESTING_SEQ.NEXTVAL, ?, ?)";
...
MethodA(TestingID); //passing TestingID to MethodA
I need to get the next sequence value of the newly inserted record into the TestingIDso that I can use it in another method as shown above.
我需要将新插入记录的下一个序列值获取到 中，TestingID以便我可以在另一种方法中使用它，如上所示。
采纳答案by Cristian Meneses
By using that approach, you should query first for the new identity value (I see you're using sequences),. This can be done by issuing a select.
通过使用这种方法，您应该首先查询新的标识值（我看到您正在使用序列）。这可以通过发出选择来完成。
// This example is for Oracle
String sqlIdentifier = "select TESTING_SEQ.NEXTVAL from dual";
PreparedStatement pst = conn.prepareStatement(sqlIdentifier);
synchronized( this ) {
ResultSet rs = pst.executeQuery();
if(rs.next())
long myId = rs.getLong(1);
After that, pass it to the preparedStatement as an argument.
之后，将其作为参数传递给preparedStatement。
...
String sqlInsert = "INSERT INTO TESTING VALUES(?, ?, ?)";
PreparedStatement pst = conn.prepareStaetment(sqlInsert);
pst.setLong(1, myId);
...
From that point, you will always have your sequence number.
从那时起，您将始终拥有序列号。
These are not functional examples (no catch or finally, etc), but will give you an idea of how to do it ;)
这些不是功能示例（没有 catch 或 finally 等），但会让您了解如何去做；）
回答by Walter A
In Oracle you can use
在 Oracle 中，您可以使用
long myId = rs.getLong("NEXTVAL");
This will fail for HSQL. You can modify the sql statement, by adding "as NEXTVAL".
这对于 HSQL 将失败。您可以通过添加“as NEXTVAL”来修改 sql 语句。
String sqlIdentifier = "select TESTING_SEQ.NEXTVAL as NEXTVAL from dual";
回答by Naresh Salikanti
follow the below steps:
请按照以下步骤操作：
1) create sequence in database by using the following query.
CREATE SEQUENCE sequence_name
[START WITH start_num]
[INCREMENT BY increment_num]
[ { MAXVALUE maximum_num | NOMAXVALUE } ]
[ { MINVALUE minimum_num | NOMINVALUE } ]
[ { CYCLE | NOCYCLE } ]
[ { CACHE cache_num | NOCACHE } ]
[ { ORDER | NOORDER } ];
example:
例子：
CREATE SEQUENCE customers_seq
START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;
2) check whether the sequence is created successfully. by executing command:
2) 检查序列是否创建成功。通过执行命令：
select * from user_sequences;
check for the name "customers_seq"
检查名称“ customers_seq”
3) run the query:
3）运行查询：
sample program:
示例程序：
Statement stmt= connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT customers_seq.NEXTVAL FROM dual");
if ( rs!=null && rs.next() ) {
int cust_id = rs.getInt(1);
sysout(cust_id);
rs.close();
}
stmt.close();
con.close();
回答by Laszlo Lugosi
In spring and Oracle database, this should work.
在 spring 和 Oracle 数据库中，这应该可以工作。
public Long getSequence() {
org.springframework.jdbc.core.JdbcTemplate jdbcTemplateObject = new JdbcTemplate(dataSource);
Long seq;
String sql = "select SEQ_XY.NEXTVAL from dual";
seq = jdbcTemplateObject.queryForObject(sql, new Object[] {}, Long.class);
return seq;
}
