如何从本机JPA INSERT获取生成的标识值？ [英] How to get generated Identity value from a native JPA INSERT?

问题描述

在我的应用程序中，我正在生成一个SQL-INSERT。此插入将作为JPA本机查询（Hibernate）执行，并在SQL Server中自动生成新的标识值。

如何接收生成的标识值？ p>

我想避免再次选择 @@ IDENTITY ，因为第二个用户可能已经在同时，导致错误的结果。
（见为JPA本地插入查询获取生成的标识符

使用@@ IDENTITY的代码如下所示：

  Query statementInsert = entityManager.createNativeQuery（INSERT INTO x（colum1，column2）VALUES（：value1，：value2））; 
 //设置参数... 
 statementInsert.executeUpdate（）; 
 
 Query statmentSelectId = entityManager.createNativeQuery（SELECT @@ IDENTITY）; 
 int generatedId =（（BigDecimal）statmentSelectId.getSingleResult（））。intValueExact（）; 
  

我宁可放置 SELECT SCOPE_IDENTITY（）在我的 INSERT -Statement后面（如

我梦想着这样的事情：

$ b $ （）;} 查询statementInsertAndSelectId = entityManager.createNativeQuery（INSERT INTO x（colum1，column2）VALUES（：value1，：value2）; SELECT SCOPE_IDENTITY（）;）;
//设置参数...
// ?????????
int generatedId =（（BigDecimal）statementInsert.getSingleResult（））; //不工作，JPA抱怨无结​​果集


使用标准构建器或命名查询是不可能的在我的项目中。

解决方案

即使您将它重复写入，您可能会收到并发呼叫使@@ IDENTITY返回错误的值。

您需要使用@@ SCOPE_INDENTITY才能获取系统范围内的最新值，但不是您范围内的最新值。
根据 https://msdn.microsoft.com/en- us / library / ms190315.aspx 两个语句在同一个作用域中，如果它们在相同的存储过程，函数或批处理中。

这可能是棘手的是在JPA中批量获取2条语句。批量插入可能是一个挑战，需要特定环境或特定于Hibernate（通过JPA）代码。请参阅如何在JPA中执行BATCH插入？和其他关于批量插入的帖子。

商店过程可能是一个解决方案，但作为个人偏好，我远离存储过程。

In my application I'm generating an SQL-INSERT. This insert will be executed as JPA native query (Hibernate) and automatically generate a new identity value in our SQL Server.

How can I receive the generated identity value?

I want to avoid to do a second select to @@IDENTITY, because a second user could have inserted something in the meanwhile, leading to wrong results. (seen in Getting generated identifier for JPA native insert query )

The code using @@IDENTITY looks like this:

Query statementInsert = entityManager.createNativeQuery("INSERT INTO x(colum1, column2) VALUES (:value1, :value2)");
// setting parameters...
statementInsert.executeUpdate();

Query statmentSelectId = entityManager.createNativeQuery("SELECT @@IDENTITY");
int generatedId = ((BigDecimal) statmentSelectId.getSingleResult()).intValueExact();

I would prefer to put SELECT SCOPE_IDENTITY() right behind my INSERT-Statement (as seen in How to get Identity value from SQL server after insert record). This would require some kind of "batch execution" for JPA native queries.

I'm dreaming of something like this:

Query statementInsertAndSelectId = entityManager.createNativeQuery("INSERT INTO x(colum1, column2) VALUES (:value1, :value2); SELECT SCOPE_IDENTITY();");
// setting parameters...
// ?????????
int generatedId = ((BigDecimal) statementInsert.getSingleResult()); // not working, JPA complains about "No ResultSet"

Using "criteria builder" or "named query" is not possible in my project.

解决方案

Even if you are writing it back to back, you might get concurrent calls that will make the @@IDENTITY return the wrong value.

You need to use @@SCOPE_INDENTITY not to get the system wide latest value, but the latest value within your scope. According to https://msdn.microsoft.com/en-us/library/ms190315.aspx two statements are in the same scope if they are in the same stored procedure, function, or batch.

That might be tricky to get 2 statement in a batch in JPA. Batch insert might be a challenge that require environment specific or hibernate specific (over JPA) code. See How to do the BATCH insert in JPA? and other posts regarding batch insert.

A store procedure could be a solution, but as a personal preference, I stay away from stored proc.

这篇关于如何从本机JPA INSERT获取生成的标识值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！