User's blog

How to use database sequences with JPA/Hibernate entities

—

by

user
in , ,

When creating a new database table, you can decide how the table’s identifier column value will be generated. One way of doing this is to use a database sequence. A database sequence can be used to generated the next value or next value range that will be reserved for usage as table row identifier values.

In this post, we will discuss how to configure a JPA entity in a Spring Boot environment to use a database sequence for generating ID values. We will also review very common pitfalls when using sequences from a Postgres database.

Creating the database sequence

In this example, we will be using Postgres as our database. Creating a sequence in Postgres is very simple. Here is an example sequence which we will be using later on.

CREATE SEQUENCE post_id_sequence
INCREMENT BY 50;

Sequences have two very important properties (aside from the sequence name) and those are the sequence datatype and the increment size.

  • Datatype: The datatype should match your ID column’s datatype. In Postgres, the sequence has a bigint datatype by default.
  • Increment size: The increment size, designated in the query above with INCREMENT BY is the size with which the sequence’s next value will be incremented. A size of 10 will produce values 10, 20, 30 and so on. The sequence above will produce the values 50, 100, 150.

We chose the value of 50 because it matches JPA’s default sequence value allocation size. The increment size in the database should match the configuration in the JPA entity. Otherwise, you will have inconsistencies in your ID column.

Creating the database table

To keep things simple, we will create the Posts table with just two columns. The ID and the content text. 

CREATE TABLE posts
(id bigint PRIMARY KEY, content text);

Notice that we did not define how the primary key will be generated. This will be defined in JPA.

Configuring a JPA entity to generated the ID using a sequence

In order to configure a JPA entity to generate the ID using a predefined database sequence, we will need to do two things. First, to mark the ID column with the @GeneratedValue annotation, to indicate that the value for this column will be autogenerated and not provided manually. The second is to configure the entity to use the correct sequence for ID generation.


import javax.persistence.*

@Entity
class PostEntity (

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "posts_jpa_sequence_generator")
    @SequenceGenerator(name = "posts_jpa_sequence_generator", sequenceName = "post_id_sequence")
    private val id: Long = 0,

    private val content: String? = null
)

Notice here a few things:

  • GenerationType.SEQUENCE: This indicates that we will be using sequences for the ID generation
  • posts_jpa_sequence_generator: This is the name of the generator that we will define in the next annotation. This is not the name of the sequence.
  • @SequenceGenerator: This configures JPA to use our sequence. Here we define the name of the generator, the name of the sequence (via sequenceName) and the allocation size / increment size of the sequence (default is 50).

Please note that since we configured our sequence in the database to use an increment of 50, we do not need to set the value in the @SequenceGenerator. Had we used a different value, for example the value of 1 which is the default from Postgres, then we would need to set the property here as well. Take a look at how this would have looked like.

import javax.persistence.*

@Entity
class PostEntity (

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "posts_jpa_sequence_generator")
    @SequenceGenerator(name = "posts_jpa_sequence_generator", sequenceName = "post_id_sequence", allocationSize = 1)
    private val id: Long = 0,

    private val content: String? = null
)

To save a new instance, you can simply instantiate the object without providing the id.

    fun autogeneratePost(): PostEntity {
        return postRepository.save(PostEntity(content = "${System.currentTimeMillis()}")
        )
    }

After triggering the function a few times, you will see in the database that the identifiers are generated correctly.

If entities will be generated exclusively from the Spring / JPA application, then I would recommend to use a larger allocation size. 50 would be a good start. You can use larger values if your table is very active. This reduces the amount of roundtrips used by the application to ask for the next sequence value from the database.

Note that every time you restart your application, JPA will try to increment the sequence with the next value, even if not all values in the previous increment range have been used up. Therefore, you might find gaps in the ID values in your table. This is normal and nothing to worry about. If you find too many gaps, then this is a sign that you need to use a smaller increment size.

Troubleshooting: Hibernate/JPA is producing negative IDs when using a Postgres database sequence

One common issue that developers who use a combination of Postgres, JPA and Spring encounter is that they find that their data is saved with identifiers that have negative values. Moreover, they might even encounter constrain violation exceptions. So what went wrong? Here is a checklist that you can go through in case you encounter such behavior:

  • Did you configure the entity to use the correct sequence? Make sure that the sequence name is correctly set in the @SequenceGenerator annotation with the “sequenceName” property. You need to configure both the @GeneratedValue and the @SequenceGenerator annotations. See the example in the previous sections for reference.
  • Did you make sure that the allocation size of the database sequence is the same as in the @SequenceGenerator annotation? This is the most common cause for negatively generated values. By default, Postgres sequences have an increment size of 1. However, JPA has a default allocationSize of 50. When JPA asks the database for the next value of the sequence, it gets 1. But since the allocationSize is set to 50, JPA thinks that it has allocated for itself the IDs range 1 to -49. So either update your entity, or use a sequence with a matching increment size.
  • You have exhausted all positive sequence values. Therefore, the sequence has rotated into its negative values range. Though it is extremely unlikely, it is possible if
    • you have a too large increment/allocation size
    • your sequence is shared among multiple tables
    • you used a small datatype for the sequence
    • your sequence datatype does not match the datatype of the ID column
    • or there is a misconfiguration in JPA where the next value of the sequence is triggered too frequently.

There is no significant performance benefit from sharing sequences among database tables. To avoid this scenario, I recommend using a unique sequence per table, specially if you have very active tables with many entries.

Summary

In this post, we discussed how to configure a JPA entity to use a specific database sequence for identifier generation. We also discussed some common pitfalls that developers go through when using sequences together with JPA / Hibernate. If you liked this post, then please share it with your friends, colleagues and family 🙂