Springs SimpleJdbcInsert 不会按预期生成自动生成的键

Question

我正在使用 springs SimpleJdbcInsert 执行 JDBC 插入并返回 2 个自动生成的键。

我使用的命令是：

KeyHolder keys = insert.withTableName("TRANSACTION").usingGeneratedKeyColumns("TRANSACTIONID", "ROWID").executeAndReturnKeyHolder(params);

但是 keys 仅包含一个名为 SCOPE_IDENTITY() 的密钥

日志似乎表明一切进展顺利，除了 TRANSACTIONID 自动生成的密钥AND ROWID 不会被填充，这里是一些相关日志

DEBUG o.s.jdbc.core.simple.SimpleJdbcInsert  - JdbcInsert not compiled before execution - invoking compile
DEBUG o.s.jdbc.core.metadata.TableMetaDataProviderFactory  - Using GenericTableMetaDataProvider
DEBUG o.s.jdbc.core.metadata.TableMetaDataProvider  - GetGeneratedKeys is supported
DEBUG o.s.jdbc.core.metadata.TableMetaDataProvider  - GeneratedKeysColumnNameArray is supported for H2
DEBUG o.s.jdbc.core.metadata.TableMetaDataProvider  - Retrieving metadata for PRIMARY.DB/PUBLIC/TRANSACTION
DEBUG o.s.jdbc.core.metadata.TableMetaDataProvider  - Retrieved metadata: TRANSACTIONID 4 false
DEBUG o.s.jdbc.core.metadata.TableMetaDataProvider  - Retrieved metadata: CREDITS 3 true
DEBUG o.s.jdbc.core.metadata.TableMetaDataProvider  - Retrieved metadata: TXNTYPE -6 true
DEBUG o.s.jdbc.core.metadata.TableMetaDataProvider  - Retrieved metadata: CARDTXNID 12 true
DEBUG o.s.jdbc.core.metadata.TableMetaDataProvider  - Retrieved metadata: DATE 93 true
DEBUG o.s.jdbc.core.metadata.TableMetaDataProvider  - Retrieved metadata: ROWID 4 false
DEBUG o.s.jdbc.core.metadata.TableMetaDataProvider  - Retrieved metadata: CARDINFOID 4 true
DEBUG o.s.jdbc.core.metadata.TableMetaDataProvider  - Retrieved metadata: PAYMENTMETHOD -6 true
DEBUG o.s.jdbc.core.metadata.TableMetaDataProvider  - Retrieved metadata: USERID 4 true
DEBUG o.s.jdbc.core.simple.SimpleJdbcInsert  - Compiled JdbcInsert. Insert string is [INSERT INTO TRANSACTION (CREDITS, TXNTYPE, CARDTXNID, DATE, CARDINFOID, PAYMENTMETHOD, USERID) VALUES(?, ?, ?, ?, ?, ?, ?)]
DEBUG o.s.jdbc.core.simple.SimpleJdbcInsert  - JdbcInsert for table [TRANSACTION] compiled
DEBUG o.s.jdbc.core.simple.SimpleJdbcInsert  - The following parameters are used for call INSERT INTO TRANSACTION (CREDITS, TXNTYPE, CARDTXNID, DATE, CARDINFOID, PAYMENTMETHOD, USERID) VALUES(?, ?, ?, ?, ?, ?, ?) with: [10, 2, 64H80073VY322412Y, 2011-03-30 14:05:12.526, null, 2, null]
DEBUG o.s.jdbc.core.JdbcTemplate  - Executing SQL update and returning generated keys
DEBUG o.s.jdbc.core.JdbcTemplate  - Executing prepared SQL statement
DEBUG o.s.jdbc.core.simple.SimpleJdbcInsert  - Using generated keys support with array of column names.
DEBUG o.s.jdbc.core.JdbcTemplate  - SQL update affected 1 rows and returned 1 keys

Answer 1

这是我正在使用的 H2 数据库的问题。它不支持返回多个自动生成的密钥。

Answer 2

试试这个。

这是一个完整的保存方法，它保存带有两个属性集的companyCarrier对象。这些属性的数据类型为整数和字符串。

然后将生成的密钥设置在 companyCarrier 对象的 id 属性上。

Object[] args = { companyCarrier.getCompanyId(),
            companyCarrier.getCarrierId() };
    Class<?>[] parameterTypes = { CompanyCarrier.class };
    int[] types = { Types.INTEGER, Types.VARCHAR };

    SqlUpdate su = new SqlUpdate();

    su.setJdbcTemplate(getJdbcTemplate());

    su.setSql(getSqlQuery(getClass(), "save", parameterTypes));

    setSqlTypes(su, types);

    su.setReturnGeneratedKeys(true);
    su.compile();

    KeyHolder keyHolder = new GeneratedKeyHolder();
    su.update(args, keyHolder);
    int id = keyHolder.getKey().intValue();

    if (su.isReturnGeneratedKeys()) {
        companyCarrier.setId(id);
    } else {
        throw new RuntimeException("No key generated for insert statement");
    }