How to make Hibernate to use SequenceHiLoGenerator for JPA GenerationType.AUTO on Oracle?

Question

I have a table with id like below, but hibernate uses org.hibernate.id.SequenceGenerator instead of SequenceHiLoGenerator for GenerationType.AUTO, how can I tell hibernate to use SequenceHiLoGenerator?

@Id
@SequenceGenerator(name="admin_seq", sequenceName="unique_id")
@GeneratedValue(strategy=GenerationType.AUTO, generator="admin_seq")
private Long id

If I use GenerationType.SEQUENCE, hibernate will use SequenceHiLoGenerator, but I need to use GenerationType.AUTO for compatibility with MySQL.

I have tried using @GenericGenerator, it works for Oracle but will be complained by MySQL: org.hibernate.dialect.MySQLDialect does not support sequences.

@GenericGenerator(name = "admin_seq", strategy = "org.hibernate.id.SequenceHiLoGenerator",
        parameters = {
                @Parameter(name = "sequence", value = "unique_id"),
                @Parameter(name = "max_lo", value = "50") })
@GeneratedValue(strategy=GenerationType.AUTO, generator="admin_seq")
private Long id

I also tried using SequenceStyleGenerator by setting hibernate.id.new_generator_mappings=true in hibernate properties. It doesn't work either.

LeOn - Han Li · Accepted Answer · 2016-05-25 03:43:48Z

If you set hibernate.id.new_generator_mappings=true which uses SequenceStyleGenerator, it would work with Oracle. We have similar configuration in our project which works fine.

If you want to achieve bulk id retrieval(like 50 each time) to speed up the persist/save, the quirk here is you need to set allocationSize to 50 and also set your sequence Increment By to 50. 50 here is just a random number I choose, if you have massive persist, you can assign larger number. The most important thing here is the allocationSize in code and the Increment By in Database should MATCH.

The Oracle sql is something like

ALTER SEQUENCE YOUR_SEQUENCE_NAE INCREMENT BY 50;

The JPA entity ID field is like:

        @SequenceGenerator(name = "YOUR_ID_GEN", sequenceName = "SEQ_YOUR_ID",  allocationSize=50)
        @Id
        @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "YOUR_ID_GEN")
        @Column(name = "YOUR_ID")
        public Long getYourId() {
            return this.yourId;
        }

MWiesner · Accepted Answer · 2016-01-23 15:00:32Z

0

strategy = GenerationType.AUTO should work against MySQL with an identity column even though you've configured a sequence too. The sequencegenerator will be used when running against Oracle, but ignored when running against MySQL.

In your persistence.xml you should also switch your hibernate.dialect property between the correct values for a Oracle dialect and a MySQL dialect.

edited Jan 23, 2016 at 15:00

MWiesner

9,07612 gold badges40 silver badges72 bronze badges

answered Jan 16, 2016 at 1:09

Kevin Hooke

2,6312 gold badges20 silver badges34 bronze badges

1 Comment

xihui Over a year ago

Thanks for your quick reply! Yes, GenerationType.AUTO works for both Oracle and MySQL as you said. But for Oracle, it doesn't use SequenceHiLoGenerator, instead it uses simple SequenceGenerator which will hit Oracle DB to query the next sequence value on every new id. I worry it could cause performance issue.

Collectives™ on Stack Overflow

How to make Hibernate to use SequenceHiLoGenerator for JPA GenerationType.AUTO on Oracle?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related