如何在新增資料後取得自動遞增的主鍵值? How to retrieve the auto-generated database key

自動遞增(ex: mysql 的 auto increment)的主鍵(Primary key)值, 如何在新增(insert)一筆資料後, 取得剛新增的資料鍵值呢?

Spring 的 NamedParameterJdbcTemplate 的 int update(String sql, SqlParameterSource paramSource, KeyHolder generatedKeyHolder) , 可於新增後取得鍵值.

這裡有範例.





這裡要說一下 Spring in Action 3rd 書中的小錯誤, 作者說因為 Spring 3 的 NamedParameterJdbcTemplate 已整合至 SimpleJdbcTemplate 中了, 故書中就只介紹SimpleJdbcTemplate.

很不巧地, 上面剛好是個反證.

也許未來會有改變, 現SPR-3492有在討論, NamedParameterJdbcTemplate 行但 SimpleJdbcTemplate 卻不行, 太沒道理了.


在未來實現之前, SPR-3492給了二個建議, 一是用jdbcTemplate.getJdbcOperations.update(PreparedStatementCreator psc, KeyHolder generatedKeyHolder), 或是用 SimpleJdbcInsert 來處理這種情況.


稍微研究了一下 SimpleJdbcInsert, 用法滿簡單的, 程式範例如下:

@Repository
public class MyDao extends SimpleJdbcDaoSupport {
    private SimpleJdbcInsert simpleInsert ;


    @Autowired
    public MyDao(DataSource dataSource) {
        setDataSource(dataSource) ;
        this.simpleInsert = new SimpleJdbcInsert(dataSource)
                                    .withTableName("myTable") // specific table name
                                    .usingGeneratedKeyColumns("id") ; // specific auto-generated column name
    }


    public int add() {
        HashMap param = new HashMap() ;
         param.put("colunm1", "1") ;
         param.put("colunm2", 2) ;
         param.put("colunm3", "3") ;

        // Specify the column names that the insert statement should be limited to use. 
        // same as simpleInsert.usingColumns("colunm1", "colunm2", "colunm3") ;
        String[] columns = param.keySet().toArray(new String[param.size()]) ;
        simpleInsert.usingColumns(columns) ;
        int key = simpleInsert.executeAndReturnKey(param).intValue() ; 
        return key ; 
     }
}

請注意範例中的 simpleInsert.usingColumns(), SimpleJdbcInsert有個 bug (對, 我認為的確是 bug, 雖然 SPR-6384標示為 won't fix), 在 insert 時, 如果 non-null欄位已設定 default value屬性, 理論上不需指定欄位. 但SimpleJdbcInsert在 insert 時, 卻會將所有欄位抓出來 insert, 造成 Exception.

以 mysql 為例, exception 如下:

com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Column 'column4' cannot be null

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1036)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3603)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3535)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1989)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2150)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626)

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2415)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2333)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2318)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)

at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)

at $java.sql.PreparedStatement$$EnhancerByProxool$$93c6179a.executeUpdate()
at org.springframework.jdbc.core.JdbcTemplate$3.doInPreparedStatement(JdbcTemplate.java:844)
at org.springframework.jdbc.core.JdbcTemplate$3.doInPreparedStatement(JdbcTemplate.java:1)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:842)
at org.springframework.jdbc.core.simple.AbstractJdbcInsert.executeInsertAndReturnKeyHolderInternal(AbstractJdbcInsert.java:445)
at org.springframework.jdbc.core.simple.AbstractJdbcInsert.executeInsertAndReturnKeyInternal(AbstractJdbcInsert.java:426)
at org.springframework.jdbc.core.simple.AbstractJdbcInsert.doExecuteAndReturnKey(AbstractJdbcInsert.java:380)
at org.springframework.jdbc.core.simple.SimpleJdbcInsert.executeAndReturnKey(SimpleJdbcInsert.java:122)



故需使用 usingColumns()限制要處理的欄位


最後, 以上僅於 mysql 驗證過, 其它資料庫未知, 我看 SimpleJdbcInsert 有一些函式專門給 Oracle 用的.......