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!

Solution

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?