How to get Identity generated value from embedded JPA INSERT?

Question

In my application, I am creating SQL-INSERT. This insertion will be executed as a native JPA (Hibernate) query and will automatically generate a new authentication value on our SQL Server.

How to get the obtained identification value?

I want to avoid the second choice @@IDENTITY , because the second user could insert something at this time, which led to incorrect results. (see Getting the generated identifier for an embedded JPA request )

Code using @@ IDENTITY is as follows:

 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 rather put SELECT SCOPE_IDENTITY() right behind my INSERT -Statement (as seen from How to get the Identity value from the SQL server after the insert record ). This will require some kind of "batch execution" for native JPA requests.

I dream 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 the "builder criteria" or "named query" is not possible in my project.

Answer 1

Even if you write it back to back, you can receive simultaneous calls that will force @@ IDENTITY to return the wrong value.

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

It can be difficult to get 2 statements in a JPA package. Batch insertion can be a problem requiring a specific environment or hibernation (more than JPA). See How to insert a BATCH tab in JPA? and other messages regarding batch insertion.

The storage procedure may be the solution, but as a personal preference, I stay away from the stored procedure.