Mapping PostgreSQL serial type with Hibernate annotations

Question

I am using Hibernate 3.3 and PostgreSQL 8.x and would like to use Hibernate annotations to map an auto-incremented column which is NOT a primary key.

It doesn't matter if the column is mapped using SERIAL type or sequences in Postgres as long as it gets auto-incremented by the database and not by Hibernate. I tried the following mappings, but they always generated null orderId.

@Column(name = "orderId", insertable = false)
@Generated(GenerationTime.INSERT)
//@GeneratedValue(strategy = javax.persistence.GenerationType.AUTO)
private Integer orderId;

I will appreciate any help with this.

Thanks

I wrote an integration test and checked this field's value in the debugger. — alecswan, Feb 12 '11 at 18:47
@alecswan please revise the selected answer - i believe my answer is more correct and i personally have to keep coming back here to remember how to do it correctly! every time i attempt to use the incorrect answer and then look further and find my own correct answer! — pstanton, Jun 27 '14 at 01:58

score 36 · Accepted Answer · edited Oct 26 '15 at 22:19

36

The following mapping should work fine:

@Column(name = "orderId")
@Generated(GenerationTime.INSERT)
private Integer orderId;

Note, however, that generated value for freshly saved objects is not available until session is flushed.

EDIT: Note that this mapping doesn't affect doesn't make Hibernate to create a column of type serial during schema generation, since Hibernate doesn't know anything about the nature of value generation at the database side. Therefore, if you want Hibernate to create a column with a proper type, you need to specifiy it explicitly:

@Column(name = "orderId", columnDefinition = "serial")
@Generated(GenerationTime.INSERT)
private Integer orderId;

And on a recent Hibernate version (4.3), you can use this:

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private Long orderId;

edited Oct 26 '15 at 22:19

laurent

674
9
14

answered Feb 12 '11 at 19:04

axtavt

239,438
41
511
482

1

Flushing the session before loading the object did not work. I noticed that Hibernate just generates orderid column with type INTEGER in Postgres database. I don't see how this can work at all if the type is not SERIAL and Hibernate does not generate any triggers that would update this column. Any other thoughts? Thanks – alecswan Feb 12 '11 at 20:21
@alecswan: Updated. Also, flushing is related to the session where objects were save, not the the session where they are loaded. – axtavt Feb 12 '11 at 20:31
1

In my case objects were saved and loaded in the same session. Adding columnDefinition = "serial" fixed the problem, thanks! Is there a solution, e.g. using Identity generator, to make this solution work on other databases as well? – alecswan Feb 12 '11 at 21:18
@alecswan: As far as I know, Hibernate doesn't support generators for non-id properties. – axtavt Feb 13 '11 at 11:10
1

up-vote because this can be used for generating values on non-id columns. And that can be a big issue. This makes it easy with the downside of being hibernate specific (and not JPA) and Database specific (PostgreSQL). In my case that is not an issue. – beginner_ Jul 29 '13 at 09:29
The latter solution doesn't work. It inserts only one time then reports duplicate key on the second time. It seems that Hibernate doesn't understand SERIAL datatype of Postgres. – emeraldhieu Oct 03 '14 at 06:51
@axtavt Upvote. Thank u so much. Former solution works for me. But I'm confused in one point **when I add SequenceGenerator & GenerationType.SEQUENCE, Why Hibernate returns zero ID on first execution ?** *If there r some records present in DB (PostgreSQL), then Hibernate must return last ID value & on `save(obj)` it must generate next ID.* Right ? **Why Hibernate don't know about max ID with ID type `serial` in DB ?** Can you please give some clarification on this ? – OO7 Feb 09 '15 at 08:46
When referencing from other persistent classes, the referencing column should be specified as integer / bigint, otherwise serial will be used (creating new sequences, which makes no sense) – Nacho Coloma May 13 '15 at 17:50
If you have to use spring.jpa.properties.hibernate.temp.use_jdbc_metadata_defaults=false on spring boot, your solution does't work. You have to use the full option. Like @Id @Column(name = "id", columnDefinition = "serial not null") @SequenceGenerator(allocationSize = 1, sequenceName = "opcionais_id_seq", name = "opcionais_id_seq") @GeneratedValue(generator = "opcionais_id_seq", strategy = GenerationType.SEQUENCE) – Rod Lima May 15 '17 at 14:39

score 25 · Answer 2 · answered Jan 16 '13 at 01:08

25

the accepted answer doesn't work for me.

this did though:

@Id
@Column(name = "your_id", columnDefinition = "serial")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer yourId;

answered Jan 16 '13 at 01:08

pstanton

35,033
24
126
168

this is also JPA not compatible which is a bonus. – pstanton Jun 27 '14 at 01:30
3

How is **not** compatible a bonus? – hd1 Jun 06 '16 at 19:48
1

Works for me, without the `columnDefinition = "serial"`, I prefer to declare this only in the SQL generation file, using flyway (or liquibase) – pdem Feb 19 '18 at 13:04
1

Wait, didn't OP say his column is NOT a primary key? Does `@Id` annotation still apply in such case? – Klesun Jun 07 '21 at 08:42
@Klesun i must have missed that requirement - oops – pstanton Jun 08 '21 at 06:20

score 5 · Answer 3 · edited Sep 11 '12 at 15:59

5

I'm using this with postgresql9.1, should work with 8 too:

@SequenceGenerator(allocationSize=1, initialValue=1, sequenceName="account_id_seq", name="account_id_seq")
@GeneratedValue(generator="account_id_seq", strategy=GenerationType.SEQUENCE)
@Id
@Column(name="id")
private Integer id;

edited Sep 11 '12 at 15:59

Mike Aski

9,180
4
46
63

answered Aug 10 '12 at 13:15

bjonczy

51
1
4

1

You can also use `strategy=GenerationType.IDENTITY` – Craig Ringer Oct 04 '12 at 06:22
2

Does this work without @Id? (Because the question is explicit about the field not being an id field) – Angelo Fuchs Oct 05 '12 at 13:43
This ^ is a JPA annotation version, Thanks! – Steve Jones Oct 26 '12 at 01:53

Klesun · Answer 4 · 2021-06-08T12:32:44.160

1

I tried both GenerationTime.INSERT and GenerationType.IDENTITY, but neither prevented the null from being inserted on an updating saveAll() call.

What did help was marking the field as non-writable for hibernate:

@Column(insertable = false, updatable = false)
private Integer orderId;

(Kudos to this answer)

edited Jun 08 '21 at 12:32

answered Jun 07 '21 at 10:56

Klesun

12,280
5
59
52

score 0 · Answer 5 · answered Aug 08 '20 at 11:15

Database sequence is not sync with your private key. So you need to update sequence index.

Check your sequence id from db and execute this sql command. (Do not forget to get backup your db just in case)

SELECT setval('your_primary_key_sequence', (SELECT MAX(your_primary_key) FROM your_table)+1);

Mapping PostgreSQL serial type with Hibernate annotations

5 Answers5

Linked