java 有没有办法使用 NamedParameterJdbcTemplate 和 GeneratedKeyHolder 提取主键（或 ROWID）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11450996/
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 extract primary key(or ROWID) using NamedParameterJdbcTemplate and GeneratedKeyHolder?
提问by darwinbaisa
I am trying to extract ROWID or the primary key using Spring's NamedParameterJdbcTemplate and GeneratedKeyHolder.
我正在尝试使用 Spring 的 NamedParameterJdbcTemplate 和 GeneratedKeyHolder 提取 ROWID 或主键。
I am trying to do something like this.
我正在尝试做这样的事情。
MapSqlParameterSource parameters = new MapSqlParameterSource()
.addValue("param1", value1)
.addValue("param2", value2);
KeyHolder keyHolder = new GeneratedKeyHolder();
namedParameterJdbcTemplate.update("INSERT INTO TABLE(ID, col1, col2)"
+ "VALUES(TABLE.TABLE_SEQ.NEXTVAL, :param1, :param2)",
parameters, keyHolder);
After executing above query when I try to do keyHolder.getKey().longValue()it is throwing below exception.
执行上述查询后，当我尝试执行keyHolder.getKey().longValue()此操作时会引发以下异常。
HTTP Status 500 - Request processing failed; nested exception is org.springframework.dao.DataRetrievalFailureException: The generated key is not of a supported numeric type. Unable to cast [oracle.sql.ROWID] to [java.lang.Number]
When I went through this http://docs.oracle.com/cd/B28359_01/java.111/b31224/datacc.htmI understand (i hope i did) that ojdbc is not mapping oracle RowId to java RowId.
当我浏览这个http://docs.oracle.com/cd/B28359_01/java.111/b31224/datacc.htm 时，我明白（我希望我做到了）ojdbc 没有将 oracle RowId 映射到 java RowId。
Can any one suggest is there any way to extract the key? (Yes it can be done using PreparedStatement but it is making my code bit ugly to read and manipulate on some conditions). Your suggestions are much appreciated.
任何人都可以建议有没有办法提取密钥？（是的，它可以使用 PreparedStatement 来完成，但它使我的代码在某些条件下难以阅读和操作）。非常感谢您的建议。
回答by Wins
You have to use this
你必须使用这个
namedParameterJdbcTemplate.update("INSERT INTO TABLE(ID, col1, col2)"
+ "VALUES(TABLE.TABLE_SEQ.NEXTVAL, :param1, :param2)",
parameters, keyHolder, new String[]{"ID"});
回答by supernova
Here is a fully working example: Assuming Database is Oracle and column name which store generated Id is "GENERATED_ID" ( Can be any name)
这是一个完整的示例：假设数据库是 Oracle 并且存储生成的 Id 的列名是“GENERATED_ID”（可以是任何名称）
public Integer insertRecordReturnGeneratedId(final MyObject obj)
{
final String INSERT_QUERY = "INSERT INTO MY_TABLE VALUES(GENERATED_ID_SEQ.NEXTVAL, :param1, :param2)";
try
{
MapSqlParameterSource parameters = new MapSqlParameterSource().addValue( "param1", obj.getField1() ).addValue( "param2", obj.getField1() ) ;
final KeyHolder holder = new GeneratedKeyHolder();
this.namedParameterJdbcTemplate.update( INSERT_QUERY, parameters, holder, new String[] {"GENERATED_ID" } );
Number generatedId = holder.getKey();
// Note: USING holder.getKey("GENERATED_ID") IS ok TOO.
return generatedId.intValue();
}
catch( DataAccessException dataAccessException )
{
}
}
