从单个Spring JDBC Update中检索多个查询生成的键。

huangapple go评论78阅读模式
英文:

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;

huangapple
  • 本文由 发表于 2020年9月26日 05:04:42
  • 转载请务必保留本文链接:https://go.coder-hub.com/64071302.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定