Java 通过 jdbctemplate 从 sql 插入的身份

Question

提问by javamonkey79

Is it possible to get the @@identity from the SQL insert on a Spring jdbc template call? If so, how?

是否可以从 Spring jdbc 模板调用的 SQL 插入中获取 @@identity？如果是这样，如何？

Answer 1

采纳答案by Jason Gritman

The JDBCTemplate.updatemethod is overloaded to take an object called a GeneratedKeyHolder which you can use to retrieve the autogenerated key. For example (code taken from here):

该JDBCTemplate.update方法被重载以获取一个名为 GeneratedKeyHolder 的对象，您可以使用它来检索自动生成的密钥。例如（取自此处的代码）：

final String INSERT_SQL = "insert into my_test (name) values(?)";
final String name = "Rob";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
    new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps =
                connection.prepareStatement(INSERT_SQL, new String[] {"id"});
            ps.setString(1, name);
            return ps;
        }
    },
    keyHolder);
// keyHolder.getKey() now contains the generated key

Answer 2

回答by javamonkey79

I don't know if there is a "one-liner" but this seems to do the trick (for MSSQL at least):

我不知道是否有“单线”，但这似乎可以解决问题（至少对于 MSSQL）：

// -- call this after the insert query...
this._jdbcTemplate.queryForInt( "select @@identity" );

Decent article here.

体面的文章在这里。

Answer 3

回答by todd.pierzina

How about SimpleJdbcInsert.executeAndReturnKey? It takes two forms, depending on the input:

怎么样SimpleJdbcInsert.executeAndReturnKey？它有两种形式，取决于输入：

(1) The input is a `Map`

(1) 输入是一个 `Map`

public java.lang.Number executeAndReturnKey(java.util.Map<java.lang.String,?> args)
Description copied from interface: SimpleJdbcInsertOperations
Execute the insert using the values passed in and return the generated key. This requires that the name of the columns with auto generated keys have been specified. This method will always return a KeyHolderbut the caller must verify that it actually contains the generated keys.
Specified by:
executeAndReturnKeyin interface SimpleJdbcInsertOperations
Parameters:
args - Map containing column names and corresponding value
Returns:
the generated key value

public java.lang.Number executeAndReturnKey(java.util.Map<java.lang.String,?> args)
从界面复制的描述： SimpleJdbcInsertOperations
使用传入的值执行插入并返回生成的键。这要求已指定具有自动生成键的列的名称。此方法将始终返回 a，KeyHolder但调用者必须验证它是否确实包含生成的密钥。
指定者：
executeAndReturnKey在界面中 SimpleJdbcInsertOperations
参数：
args - Map containing column names and corresponding value
返回：
the generated key value

(2) The input is a `SqlParameterSource`

(2) 输入是一个 `SqlParameterSource`

public java.lang.Number executeAndReturnKey(SqlParameterSourceparameterSource)
Description copied from interface: SimpleJdbcInsertOperations
Execute the insert using the values passed in and return the generated key. This requires that the name of the columns with auto generated keys have been specified. This method will always return a KeyHolderbut the caller must verify that it actually contains the generated keys.
Specified by:
executeAndReturnKeyin interface SimpleJdbcInsertOperations
Parameters:
parameterSource - SqlParameterSource containing values to use for insert
Returns:
the generated key value.

public java.lang.Number executeAndReturnKey(SqlParameterSourceparameterSource)
从界面复制的描述： SimpleJdbcInsertOperations
使用传入的值执行插入并返回生成的键。这要求已指定具有自动生成键的列的名称。此方法将始终返回 a，KeyHolder但调用者必须验证它是否确实包含生成的密钥。
指定者：
executeAndReturnKey在界面中 SimpleJdbcInsertOperations
参数：
parameterSource - SqlParameterSource containing values to use for insert
返回：
生成的键值。

Answer 4

回答by Sheetal Mohan Sharma

Adding detailed notes/sample code to todd.pierzina answer

将详细注释/示例代码添加到 todd.pierzina 答案

jdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
        jdbcInsert.withTableName("TABLE_NAME").usingGeneratedKeyColumns(
                "Primary_key");
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("Column_NAME1", bean.getval1());
        parameters.put("Column_NAME2", bean.getval2());
        // execute insert
        Number key = jdbcInsert.executeAndReturnKey(new MapSqlParameterSource(
                parameters));
           // convert Number to Int using ((Number) key).intValue()
            return ((Number) key).intValue();