2

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.

Community
  • 1
  • 1
slartidan
  • 20,403
  • 15
  • 83
  • 131

1 Answers1

1

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.

Community
  • 1
  • 1
Filip
  • 906
  • 3
  • 11
  • 33
  • according to http://stackoverflow.com/questions/12827496/will-scope-identity-work-in-this-case, the @@SCOPE_IDENTITY works if it is within the same transaction... but it'd test it first! – Filip May 27 '15 at 18:55
  • first, it's `Scope_Identity()` and not `@@Scope_identity`. Second, what reason do you have for not using a stored procedure? – Zohar Peled May 27 '15 at 19:28
  • 1
    any code located in stored proc, triggers, etc is hidden code. If you work in small dev team, it does not matter so much. In a larger team, if your junior team member doesn't know that DB does some tricks behing the scene it can lead to some major time waste. I not saying its bad, I'm saying its a personal pref. – Filip May 28 '15 at 16:47
  • It doesn't have to be hidden. IMO, using sql server without using it's built in tools like stored procedures etc' it's like driving a ferrary at 10 mph. – Zohar Peled May 28 '15 at 17:41