SQL sequence: Hibernate and Liquibase

Question

I am quite new to development with databases, so maybe this question is not entirely spot on, but I'd appreciate if someone can make it a bit clearer to me... I've read all about sequences, and how they are preferred over identities. I have a hypothetical question. If I were to use a sequence to generate my PK along with Hibernate (data insertion) and Liquibase (schema creation), what would be the right spot to define sequence?

For example: Sequence generation on class level.

User.java

@Entity
@Table(name = "USER")
public class User {

    @Id
    @SequenceGenerator(name = "USER_SEQ", sequenceName = "USER_SEQ")
    @GeneratedValue(strategy = SEQUENCE, generator = "USER_SEQ")
    @Column(name = "ID")
    private Long id;

    // other fields
}

Or other example: Sequence generation on Schema level.

changelog.xml

<changeSet author="wesleyy">
    <createSequence catalogName="cat"
            cycle="true"
            incrementBy="1"
            maxValue="1000"
            minValue="10"
            ordered="true"
            schemaName="public"
            sequenceName="user_seq"
            startValue="1"/>
</changeSet>

Is it required to define a sequence in both Liquibase and Hibernate? What exactly is the difference between the two?

Faz · Answer 1 · 2018-02-02T01:12:59.600

If what you wish for is the id primary key to be auto generated then this can be simply done as follows using a JPA implementation such as Hibernate or others:

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private long id;

The Id primary key will be auto generated and managed for you.

Using liquibase, in your base changelog xml file, you can do the following as long as your database supports auto generated id's (many do this but check if unsure):

  <changeSet author="name" id="auto increment example">
    <createTable tableName="users">
        <column autoIncrement="true" name="id" type="SERIAL">
            <constraints primaryKey="true" primaryKeyName="users_pkey"/>
        </column>
        <column name="some_other_column" type="VARCHAR(255)"/>
        <column name="another_column" type="VARCHAR(255)"/>
    </createTable>
</changeSet>

Yes but i'm explicitly asking about sequence concept, not just sequential id generation. Sequence is internally another table in the db that allows for better performance — wesleyy, Feb 02 '18 at 05:51
Be careful with using `GenerationType.IDENTITY` as this disables batching. If you care about performance and need a huge amount of rows. E.g. see https://thorben-janssen.com/hibernate-tips-use-custom-sequence/ — Guillermo, Jul 23 '20 at 20:37

score 3 · Answer 2 · answered Feb 02 '18 at 07:28

3

By lines in class User

@SequenceGenerator(name = "USER_SEQ", sequenceName = "USER_SEQ")
@GeneratedValue(strategy = SEQUENCE, generator = "USER_SEQ")

you say to hibernate: for each object User inserted into database give new value for primary key from sequence named USER_SEQ.

By adding described changeSet to liquibase xml script you say to liquibase: next time create in database sequence named user_seq if this changeSet was not already applied to database.

In other words in liquibase script you create sequence and in code of class User you use it.

answered Feb 02 '18 at 07:28

Anton Tupy

951
5
16

Something I'm not sure about: is the sequence created automatically from Hibernate if I don't explicitly define it in Liquibase? Or if I want to use sequence i MUST define it in both places? – wesleyy Feb 02 '18 at 07:32
Sometimes such sequences created automatically. For instance for field of type autoincrement in Postgresql. It is database specific. – Anton Tupy Feb 02 '18 at 08:51

score 0 · Answer 3 · answered Jan 19 '19 at 15:17

Your question is a bit misleading because it is related to two different things:

generation of entity identifiers (behavior of your application) and
definition of the underlying database schema.

"If I were to use a sequence to generate my PK along with Hibernate (data insertion) and Liquibase (schema creation), what would be the right spot to define sequence?"

You could define a sequence in many different ways. It always ends with SQL statement, but sometimes you may prefer to use Liquibase, Flyway or Hibernate to DDL auto export (Hibernate hbm2ddl.auto possible values and what they do?).

My personal preference is to rely on Hibernate automatic schema export during initial development and later on use some of the mentioned version-based DB migration tools.

SQL sequence: Hibernate and Liquibase

3 Answers3