Why JPA persist() does not generated auto-increment primary ID?

Question

I'm using JPA toplink-essential and SQL Server 2008

My goal is to get auto-increment primary key value of the data that is going to be inserted into the table. I know in JDBC, there is getInsertedId() like method that give you the id of auto-increment primary id (but that's after the insert statement executed though)

In JPA, I found out @GenratedValue annotation can do the trick.

@Entity
@Table(name = "tableOne")
public class TableOne implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "tableId")
    private Integer tableId;

Now if I run the code below it should give me the auto incremented id but it returns NULL...

 EntityManager em = EmProvider.getInstance().getEntityManagerFactory().createEntityManager();
 EntityTransaction txn = em.getTransaction();
 txn.begin();

 TableOne parent = new TableOne();
 em.persist(parent); //here I assume that id is pre-generated for me.
 System.out.println(parent.getTableId()); //this returns NULL :(

I can get obtained id if I commit: em.getTransaction().commit() but there is no way to get id already with persist??? hmm — Meow, Feb 02 '11 at 05:39
Your code should work as you expect. I tested it and I get the id before committing or if I then rollback. Strange that it doesn't. — Joel, Feb 02 '11 at 11:02
Does it make any difference if tableId is an int rather than an Integer? — Joel, Feb 02 '11 at 11:05
I switched type from "Integer" to "int" and now parent.getTableId() is returning 0 (with @GeneratedValue.SEQUENCE) though it shouldn't be 0, not sure why. — Meow, Feb 02 '11 at 23:56
It's a default value of int type and nothing to do with database or JPA. — JSS, Feb 03 '11 at 12:34

score 11 · Answer 1 · answered Feb 02 '11 at 15:16

11

The problem is you are using IDENTITY id generation. IDENTITY id generation cannot do preallocation as they require the INSERT to generate the id. TABLE and SEQUENCE id generation support preallocation, and I would always recommend usage of these, and never using IDENTITY because of this issue and because of performance.

You can trigger the id to be generated when using IDENTITY id generation by calling flush().

answered Feb 02 '11 at 15:16

James

17,965
11
91
146

hmmm sequence not working for me. I changed my annotation in entity class to: `@Id @SequenceGenerator(name="seq", sequenceName="seq") @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="seq")` – Meow Feb 02 '11 at 23:43
1

Then after em.persist(myBean); int insertedId = myBean.getId(); This getId() is returning null... am i doing something wrong? – Meow Feb 02 '11 at 23:45
1

That is a timing problem. JPA did not insert the data when you try to get the id value. You must flush to make him start doing the modifications on the database. Afterwards you have the id. Better would be that JPA does the modifications on the db when you call getId(). – Hasan Tuncay Jan 25 '13 at 08:33

score 6 · Answer 2 · answered Jul 18 '12 at 06:05

6

just simply do this :

public void create(T entity) {
   getEntityManager().persist(entity);
   getEntityManager().flush();
   getEntityManager().refresh(entity);
}

After refreshing the entity you have the ID field with proper value.

answered Jul 18 '12 at 06:05

Mehdi

4,396
4
29
30

3

The reason for adding the `flush()` is that `persist()` is not clearly guaranteed to have generated the `@GeneratedValue`, but it must be generated at or before the `flush()`. See this question: http://stackoverflow.com/questions/9087848/when-does-the-jpa-set-a-generatedvalue-id – Raedwald Aug 26 '12 at 11:09

JSS · Accepted Answer · 2011-02-02T10:43:40.393

2

We are also using SQL Server 2008 and it never worked for me so I always execute separate query "SELECT @@IDENTY" to get the inserted id.

The reason I found on the net was that auto id (IDENTITY) is managed by database and never fetched in Entity until unless you commit the row or manually retrieve the info from database.

edited Feb 02 '11 at 10:43

answered Feb 02 '11 at 09:31

JSS

2,061
1
20
26

Do you mean because SQL Server 2008, we can't use @GeneratedValue.Sequence or Table or Identity ??? I figured I had to flush first (insert call to db to get generated id) but my goal is without insert call if I can get primary id. – Meow Feb 02 '11 at 23:47
You may be correct, the doc doesn't seems to list SQL Server to support SEQUENCE object.... "Sequence objects use special database objects to generate ids. Sequence objects are only supported in some databases, such as Oracle, DB2, and Postgres." http://en.wikibooks.org/wiki/Java_Persistence/Identity_and_Sequencing#Table_sequencing – Meow Feb 03 '11 at 00:05
marking as an answer, SEQUENCE is not being supported by SQL Server – Meow Feb 03 '11 at 23:34

Why JPA persist() does not generated auto-increment primary ID?

3 Answers3

Linked