英文:
Retrieve generated keys from multiple queries in single Spring JDBC Update
问题
我使用了单个Spring JDBC更新语句来更新我Postgres数据库中的两个表。我的SQL查询如下：
UPDATE accounts SET last_transaction_amount = :transaction_amount WHERE acct_num = :acct_num; INSERT INTO transactions (transaction_amout) VALUES (:transaction_amount);
使用`NamedParameterJdbcTemplate#update`，我在执行这个查询并获得了预期结果方面没有问题。
`transactions`表生成一个连续的交易标识符，我希望将其返回给我的应用程序。
我尝试在`update`调用中传递了一个`GeneratedKeyHolder`。这返回了错误消息“在不期望返回结果时返回了结果”。[文档链接](https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/core/namedparam/NamedParameterJdbcTemplate.html#update-java.lang.String-org.springframework.jdbc.core.namedparam.SqlParameterSource-org.springframework.jdbc.support.KeyHolder-)。
我尝试了传递一个`GeneratedKeyHolder`和一个列名数组（`new String[] {"transaction_id"}`）。这返回了错误，说列不存在。请注意，当我只传递`INSERT`查询而没有前面的`UPDATE`查询时，此方法调用确实可以返回事务ID。[文档链接](https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/core/namedparam/NamedParameterJdbcTemplate.html#update-java.lang.String-org.springframework.jdbc.core.namedparam.SqlParameterSource-org.springframework.jdbc.support.KeyHolder-java.lang.String:A-)。
我如何检索生成的键？谢谢！
英文:
I am using a single Spring JDBC update to make an update to two tables in my Postgres database. My SQL query is as follows:
UPDATE accounts SET last_transaction_amount = :transaction_amount WHERE acct_num = :acct_num; INSERT INTO transactions (transaction_amout) VALUES (:transaction_amount);
Using NamedParameterJdbcTemplate#update, I have no issue executing this query and achieving the expected results.
The transactions table generates a sequential transaction identifier, and I want to return this to my application.
I've tried passing a GeneratedKeyHolder in the update call. This is returning the error "A result was returned when none was expected". Docs link.
I've tried passing a GeneratedKeyHolder and array of column names (new String[] {"transaction_id"}). This is returning the error that the column doesn't exist. Note this method call does work to return the transaction id when I only pass the INSERT query without the preceding UPDATE query. Docs link.
How can I retrieve the generated key? Thank you!
答案1
得分: 2
你似乎在寻找 RETURNING 子句。假设序列号称为 transaction_id：
INSERT INTO transactions (transaction_amout)
VALUES (:transaction_amount)
RETURNING transaction_id;
英文:
You seem to be looking for the RETURNING clause. Assuming that the serial number is called transaction_id:
INSERT INTO transactions (transaction_amout)
VALUES (:transaction_amount)
RETURNING transaction_id;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论