How to use Postgres sequence with JPA?

Question

I have a 'customer' table in my postgresql database with a particular field: cus_number which has a sequence defined. This field is not the primary key, there's already an cus_id field.

The default value of the cus_number is

nextval('customer_cus_number_seq'::regclass) it's the sequence.

With pgadmin, when I insert a row in this customer table with a null value as the cus_number it works fine and the default value sequence is used. But in my webap, when I persist a new Customer(), the row inserted has nothing in the field cus_number.

Here is my custom entity definition :

public class Customer implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "pers_id")
private Integer id;

...

@Column(name = "cus_number")
private Integer number;

...

}

And the sequence script :

  CREATE SEQUENCE customer_cus_number_seq
  INCREMENT 1
  MINVALUE 100
  MAXVALUE 9223372036854775807
  START 114
  CACHE 1;

ALTER TABLE customer_cus_number_seq OWNER TO fizzconsulting;

Do you some tips for me please ?

Thanks.

"when I insert a row in this customer table with a null value as the cus_number it works" - no it doesn't. If you supply a NULL value, a NULL value will be stored. The default clause is only used when the column is not mentioned in the column list of the insert statement (that's something different than supplying a NULL value) — user330315
– user330315, Commented Apr 3, 2015 at 18:18
Yes you are right !, and this explain why the default value (sequence) is set when doing insert withouth cus_number. But with JPA how can I avoid to set a null value when persist a customer entity ? — Guney Mutlu
– Guney Mutlu, Commented Apr 3, 2015 at 19:13
You created a sequence. GenerationType.IDENTITY is for a different purpose (for an auto-increment column). You need a @SequenceGenerator something along the lines above the field private Integer id; @SequenceGenerator(name = "customerIdSequence", sequenceName = "customer_cus_number_seq", allocationSize=1, initialValue=1) and then refer to this sequence using @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "customerIdSequence"). Disclaimer to the statement : I do not use PostgreSQL but it should conceptually be the same as Oracle which I had been using in the past. — Tiny
– Tiny, Commented Apr 4, 2015 at 2:45
@a_horse_with_no_name Or an explicit DEFAULT keyword is provided, but few apps and tools seem to support that :-( — Craig Ringer
– Craig Ringer, Commented Apr 4, 2015 at 8:18

Guney Mutlu · Accepted Answer · 2015-04-04 21:37:30Z

3

Ok Here's the solution :

For allowing the database default value of this fields (which is the nextval of the sequence cus_number_seq) JPA has to not reference the cus_number value while persisting transaction. To do this just annotate @Column(name = "cus_number", insertable = false) This way the field is not mentionned while inserting a new customer, so the default value nextval sequence used DB side.

it works fine now. Thanks for your tips.

answered Apr 4, 2015 at 21:37

Guney Mutlu

611 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paolo Over a year ago

With this approach the insert works, but the value generated by the sequence is not returned by the insert query. Any suggestions?

Guney Mutlu · Accepted Answer · 2015-04-04 09:03:22Z

2

@Tiny, is next is the right definition ?

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@SequenceGenerator(name = "customerNumberSequence", sequenceName = "customer_cus_number_seq", allocationSize = 1, initialValue = 100)
@Basic(optional = false)
@Column(name = "pers_id")                                                                       
private Integer id;
...
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "customerNumberSequence")
@Column(name = "cus_number")
private Integer number;
...

JPA seems to not accept this declaration : Exception Description: Class [class tfe.entity.Customer] has two @GeneratedValues: for fields [customer.cus_number] and [customer.pers_id]. Only one is allowed.

Thanks.

answered Apr 4, 2015 at 9:03

Guney Mutlu

611 silver badge7 bronze badges

1 Comment

Neil Stockton Over a year ago

Why have you got IDENTITY in there still? you say you're using a sequence, so use SEQUENCE. Why are you putting SEQUENCE on "number" field now? it should go on "id"

Collectives™ on Stack Overflow

How to use Postgres sequence with JPA?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related