JPA primary key auto generate

Question

my primary key entity look like below

@GeneratedValue(strategy= GenerationType.TABLE)
private Long id;

when i run, i get error

could not get or update next value;nested exception is org.hibernate.exception.SQLGrammerException:could not get or update next value

but when i just change to

@GeneratedValue 
private Long id;

no error throw . I want to generate unique primary key per table on oracle db .

Solution 1

The @GeneratedValue(strategy=GenerationType.TABLE) tells the JPA provider to use a table to get IDs from when inserting newly created entities into the database.

When using Hibernate as provider, this will result in a table hibernate_sequences which has two columns: the entity name, and the max identity already assigned to this entity. Here, it seems Hibernate doesn't succeed to get the next ID from it for your entity but it's hard to say exactly why because you didn't provide enough informations for that.

So, could you please provide the full stacktrace? Also, please turn logging with hibernate.show_sql property set to true and set the proper log level log4j.logger.org.hibernate.SQL=DEBUG. Join the log to your question if possible.

Maybe just check that you did configure the correct hibernate.dialect for Oracle. Actually, join your hibernate configuration too if possible.

PS: The "traditional" way to generate PK with Oracle is to use sequences (you could let Hibernate guess the best strategy for your database type using GenerationType.AUTO or force it using SEQUENCE) but I'll assume you want the resultant data structure be database agnostic. If not, I'd suggest to go for sequences instead.

EDIT: Answering a comment from the OP about GenerationType.AUTO. Indeed, the default is a single global sequence called hibernate_sequence and this might be a problem. But, with the setup shown below, you can use GenerationType.AUTO and still control the name of the sequence for the cases where the database uses sequences:

@Id
@GeneratedValue(strategy=GenerationType.AUTO, generator="my_entity_seq_gen")
@SequenceGenerator(name="my_entity_seq_gen", sequenceName="MY_ENTITY_SEQ")
private long id;

In other words, you can use use a different sequence name for each table without losing portability.

Solution 2

There are 4 strategies for auto generation in JPA:

• Auto
• Identity
• Sequence
• Table

For Oracle auto generation primary key annotation, Sequence and Table are your choices. The basic logic is to define a generator first, use @SequenceGenerator or @TableGenerator respectively, then use the generator as attribute in @GeneratedValue.

This is a sample of how to use Sequence strategy:

  @Id
  @SequenceGenerator(name="SEQ_GEN", sequenceName="SEQ_JUST_FOR_TEST", allocationSize=1)
  @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SEQ_GEN")
  private long id;

Here is an example of how to use table strategy:

  @Id
  @TableGenerator(name="TABLE_GEN",table="T_GENERATOR", pkColumnName = "GEN_KEY", pkColumnValue = "MONITOR2012.T_JUST_FOR_TEST", valueColumnName = "GEN_VALUE", initialValue = 1, allocationSize = 1 )
  @GeneratedValue(strategy = GenerationType.TABLE, generator="TABLE_GEN")
  private long id;

If no generator specified in @GeneratedValue annotation, the choice will leave to the JPA implementation.

If you are working on database with existing tables, make sure you the sequence or the table defined in database before you run your application. The table generator will also need you to insert a line to the table before the @GeneratedValue annotation can work properly.

Here is a tutorial about how to configure primary key auto generation in JPA for Oracle database.

Comments

[cometta]

if i put GenerationType.Auto, it will use global incremental number, mean all my entities will increment the same number. The code run fine, but will it run out of incremental number?

[Nrj]

@Pascal : If we are providing a SequenceGenerator does it help specifying the startegy as AUTO?

[Devanshu Mevada]

@Nrj Actually, if you use AUTO and if the database uses sequences, @SequenceGenerator will help to control the name of the sequence. But it will just be "ignored" if the database doesn't (e.g. with HSQLDB). So, unlike SEQUENCE, this remains portable.

[Nrj]

@Pascal: Great. Thanks it clarifies.

[cometta]

thank you for the clarrify. may i know what is the max number for sequence ? will it run out of sequence ?

[Devanshu Mevada]

By default the sequence's maximum number is 1E+27, or 1,000,000,000,000,000,000,000,000,000. That should be enough :)