Using sequence in MySQL and Hibernate

Question

I'm working on a project that uses Hibernate and MySQL. I intend to use sequence to generate ID for all tables in database. (It's hard to describe my question, so I will show you as an example).

For example: I have 2 tables A & B. Firstly, I insert 10 records to table A, and their IDs will be 1 to 10. Then, I insert 10 records to table B, and I want their IDs will be 11-20, not 1-10. It means the generated ID value will be counted by all records in all tables in databases, not in a single table.

So how can I use that concept in MySQL? Declare and syntax? In Hibernate, how can I use the strategy and generator on data model to apply that generated strategy in database? Thank you so much!

Augusto · Accepted Answer · 2011-02-17 09:09:45Z

6

You don't have sequences in Mysql, but you can use the 'TABLE' id generation

@javax.persistence.TableGenerator(
    name="EMP_GEN",
    table="GENERATOR_TABLE",
    pkColumnName = "key",
    valueColumnName = "hi"
    pkColumnValue="EMP",
    allocationSize=20
)

@Entity
public class Klass {

    @Id
    @GeneratedValue(strategy = GenerationType.TABLE, generator=EMP_GEN)
    @Column(name = "ID")
    private Long id;
}

You might need to add the snippet @javax.persistence.TableGenerator [...] in all your entities

Edit

Thank you so much. But do we have another way to use that snippet without adding it to each entity? And when creating tables on database, how can I declare the ID primary key column for DBMS to auto-generate the value like that? – napoleonit76

Unfortunately, I don't know an elegant way to do this :(. In my project, we use the a sequence in several entities, and we need to declare the sequence in each class. One thing you could try (but I don't really like) is to create a super class for all of your entities, and that superclass only contains the ID field and the annotations. I have the feeling that I've seen this in a project, but I'm not 100% sure.

About telling the DB to use that strategy, I don't think it's really possible. You can try to add a trigger and apply it to each table in the schema. The trigger will fire when you insert a new row in the table, pick a value from the generator table, and add it to the row. I honestly don't know if this might work, and I'm 99% sure that this won't work if you have 2 concurrent sessions creating rows.

Can you rethink your strategy and use normal auto-increment columns for the ids and put the sequential number in another column of your tables?

edited Feb 17, 2011 at 9:09

answered Feb 16, 2011 at 11:25

Augusto

30.2k6 gold badges66 silver badges98 bronze badges

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

3 Comments

Đinh Hồng Châu Over a year ago

Thank you so much. But do we have another way to use that snippet without adding it to each entity? And when creating tables on database, how can I declare the ID primary key column for DBMS to auto-generate the value like that?

CodeMed Over a year ago

@Augusto I have a similar problem. Are you willing to help me with it also? here is the link: stackoverflow.com/questions/25252541/…

kapishreshth Over a year ago

Avoid using GenerationType.TABLE because It entails pessimistic locks to generate unique values. However, If you still want to use it then also use some optimization techniques (e.g. pooled) to reduce the additional database queries. Use GenerationType.IDENTITY instead.

Collectives™ on Stack Overflow

Using sequence in MySQL and Hibernate

1 Answer 1

Edit

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Edit

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related