Using Sequence in MySQL and Hibernate

Question

I am working on a project using Hibernate and MySQL. I intend to use a sequence to generate an identifier for all tables in the database. (It is hard to describe my question, so I will show you an example).

For example: I have 2 tables A and B. First, I insert 10 records into table A, and their identifiers will be from 1 to 10 . Then I insert 10 records into table B, and I want their identifiers to be 11-20 , not 1-10. This means that the generated identifier value will be counted by all records in all tables in the databases, and not in one table.

So how can I use this concept in MySQL? Declare and syntax? In Hibernate, how can I use a strategy and generator for a data model to apply this generated strategy to a database? Thank you very much!

Answer 1

You have no sequences in Mysql, but you can use the TABLE identifier 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 may need to add the @javax.persistence.TableGenerator [...] to all of your entities

Edit

Thank you very much. But do we have another way to use this fragment without adding it to each object? And when creating tables in the database, how can I declare the primary key ID column for the DBMS to automatically generate such a value? - napoleonit76

Unfortunately, I don’t know an elegant way to do this :( In my project, we use a sequence in several entities, and we need to declare a sequence in each class. One thing you could try (but I don’t really like it) is to create a superclass for of all your entities, and this superclass contains only the ID field and annotations.

Speaking about the fact that the database uses this strategy, I do not think that this is really possible. You can try to add a trigger and apply it to each table in the schema. The trigger fires when you insert a new row into the table, select a value from the generator table and add it to the row. I honestly don't know if this can work, and I'm 99% sure it won't work if you have 2 concurrent sessions creating strings.

Can you rethink your strategy and use regular auto-increment columns for identifiers and put the sequence number in another column of your tables?