How does the JPA @SequenceGenerator annotation work

I am learning JPA and have confusion in the @SequenceGenerator annotation.

To my understanding, it automatically assigns a value to the numeric identity fields/properties of an entity.

Q1. Does this sequence generator make use of the database's increasing numeric value generating capability or generates the number on its own?

Q2. If JPA uses a database auto-increment feature, then will it work with datastores that don't have an auto-increment feature?

Q3. If JPA generates numeric value on his own, then how does the JPA implementation know which value to generate next? Does it consult with the database first to see what value was stored last in order to generate the value (last + 1)?

---

Q4. Please also shed some light on sequenceName and allocationSize properties of @SequenceGenerator annotation.

sequenceName is the name of the sequence in the DB. This is how you specify a sequence that already exists in the DB. If you go this route, you have to specify the allocationSize which needs to be the same value that the DB sequence uses as its "auto increment".

Usage:

@GeneratedValue(generator="my_seq")
@SequenceGenerator(name="my_seq",sequenceName="MY_SEQ", allocationSize=1)

If you want, you can let it create a sequence for you. But to do this, you must use SchemaGeneration to have it created. To do this, use:

@GeneratedValue(strategy=GenerationType.SEQUENCE)

Also, you can use the auto-generation, which will use a table to generate the IDs. You must also use SchemaGeneration at some point when using this feature, so the generator table can be created. To do this, use:

@GeneratedValue(strategy=GenerationType.AUTO)

Even though this question is very old and I stumbled upon it for my own issues with JPA 2.0 and Oracle sequences.

Want to share my research on some of the things -

Relationship between @SequenceGenerator(allocationSize) of GenerationType.SEQUENCE and INCREMENT BY in database sequence definition

Make sure @SequenceGenerator(allocationSize) is set to same value as INCREMENT BY in Database sequence definition to avoid problems (the same applies to the initial value).

For example, if we define the sequence in database with a INCREMENT BY value of 20, set the allocationsize in SequenceGenerator also to 20. In this case the JPA will not make a call to database until it reaches the next 20 mark while it increments each value by 1 internally. This saves database calls to get the next sequence number each time. The side effect of this is - Whenever the application is redeployed or the server is restarted in between, it'll call database to get the next batch and you'll see jumps in the sequence values. Also we need to make sure the database definition and the application setting to be in-sync which may not be possible all the time as both of them are managed by different groups and you can quickly lose control of. If database value is less than the allocationsize, you'll see PrimaryKey constraint errors due to duplicate values of Id. If the database value is higher than the allocationsize, you'll see jumps in the values of Id.

If the database sequence INCREMENT BY is set to 1 (which is what DBAs generally do), set the allocationSize as also 1 so that they are in-sync but the JPA calls database to get next sequence number each time.

If you don't want the call to database each time, use GenerationType.IDENTITY strategy and have the @Id value set by database trigger. With GenerationType.IDENTITY as soon as we call em.persist the object is saved to DB and a value to id is assigned to the returned object so we don't have to do a em.merge or em.flush. (This may be JPA provider specific..Not sure)

Another important thing -

JPA 2.0 automatically runs ALTER SEQUENCE command to sync the allocationSize and INCREMENT BY in database sequence. As mostly we use a different Schema name(Application user name) rather than the actual Schema where the sequence exists and the application user name will not have ALTER SEQUENCE privileges, you might see the below warning in the logs -

> 000004c1 Runtime W CWWJP9991W: openjpa.Runtime: Warn: Unable > to cache sequence values for sequence "RECORD_ID_SEQ". Your > application does not have permission to run an ALTER SEQUENCE command. > Ensure that it has the appropriate permission to run an ALTER SEQUENCE > command.

As the JPA could not alter the sequence, JPA calls database everytime to get next sequence number irrespective of the value of @SequenceGenerator.allocationSize. This might be a unwanted consequence which we need to be aware of.

To let JPA not to run this command, set this value - in persistence.xml. This ensures that JPA will not try to run ALTER SEQUENCE command. It writes a different warning though -

> 00000094 Runtime W CWWJP9991W: openjpa.Runtime: Warn: The > property "openjpa.jdbc.DBDictionary=disableAlterSeqenceIncrementBy" is > set to true. This means that the 'ALTER SEQUENCE...INCREMENT BY' SQL > statement will not be executed for sequence "RECORD_ID_SEQ". OpenJPA > executes this command to ensure that the sequence's INCREMENT BY value > defined in the database matches the allocationSize which is defined in > the entity's sequence. With this SQL statement disabled, it is the > responsibility of the user to ensure that the entity's sequence > definition matches the sequence defined in the database.

As noted in the warning, important here is we need to make sure @SequenceGenerator.allocationSize and INCREMENT BY in database sequence definition are in sync including the default value of @SequenceGenerator(allocationSize) which is 50. Otherwise it'll cause errors.

I use this and it works right

@Id
@GeneratedValue(generator = "SEC_ODON", strategy = GenerationType.SEQUENCE)
@SequenceGenerator(name = "SEC_ODON", sequenceName = "SO.SEC_ODON",allocationSize=1)
@Column(name="ID_ODON", unique=true, nullable=false, precision=10, scale=0)
public Long getIdOdon() {
    return this.idOdon;
}

Now, back to your questions:

> Q1. Does this sequence generator make use of the database's increasing > numeric value generating capability or generates the number on its > own?

By using the GenerationType.SEQUENCE strategy on the @GeneratedValue annotation, the JPA provider will try to use a database sequence object of the underlying database that supports this feature (e.g., Oracle, SQL Server, PostgreSQL, MariaDB).

If you are using MySQL, which doesn't support database sequence objects, then Hibernate is going to fall back to using the GenerationType.TABLE instead, which is undesirable since the TABLE generation performs badly.

So, don't use the GenerationType.SEQUENCE strategy with MySQL.

> Q2. If JPA uses a database auto-increment feature, then will it work > with datastores that don't have auto-increment feature?

I assume you are talking about the GenerationType.IDENTITY when you say database auto-increment feature.

To use an AUTO_INCREMENT or IDENTITY column, you need to use the GenerationType.IDENTITYstrategy on the @GeneratedValue annotation.

> Q3. If JPA generates numeric value on its own, then how does the JPA > implementation know which value to generate next? Does it consult with > the database first to see what value was stored last in order to > generate the value (last + 1)?

The only time when the JPA provider generates values on its own is when you are using the sequence-based optimizers, like:

• hi/lo
• pooled or pooled-lo

These optimizers are meat to reduce the number of database sequence calls, so they multiply the number of identifier values that can be generated using a single database sequence call.

To avoid conflicts between Hibernate identifier optimizers and other 3rd-party clients, you should use pooled or pooled-lo instead of hi/lo. Even if you are using a legacy application that was designed to use hi/lo, you can migrate to the pooled or pooled-lo optimizers.

> Q4. Please also shed some light on sequenceName and allocationSize > properties of @SequenceGenerator annotation.

The sequenceName attribute defines the database sequence object to be used to generate the identifier values. IT's the object you created using the CREATE SEQUENCE DDL statement.

So, if you provide this mapping:

@Id
@GeneratedValue(
    strategy = GenerationType.SEQUENCE,
    generator = "seq_post"
)
@SequenceGenerator(
    name = "seq_post"
)
private Long id;

Hibernate is going to use the seq_post database object to generate the identifier values:

SELECT nextval('hibernate_sequence')

The allocationSize defines the identifier value multiplier, and if you provide a value that's greater than 1, then Hibernate is going to use the pooled optimizer, to reduce the number of database sequence calls.

So, if you provide this mapping:

@Id
@GeneratedValue(
    strategy = GenerationType.SEQUENCE,
    generator = "seq_post"
)
@SequenceGenerator(
    name = "seq_post",
    allocationSize = 5
)
private Long id;

Then, when you persist 5 entities:

for (int i = 1; i <= 5; i++) {
    entityManager.persist(
        new Post().setTitle(
            String.format(
                "High-Performance Java Persistence, Part %d",
                i
            )
        )
    );
}

Only 2 database sequence calls will be executed, instead of 5:

SELECT nextval('hibernate_sequence')
SELECT nextval('hibernate_sequence')
 
INSERT INTO post (title, id)
VALUES ('High-Performance Java Persistence, Part 1', 1)
 
INSERT INTO post (title, id)
VALUES ('High-Performance Java Persistence, Part 2', 2)
 
INSERT INTO post (title, id)
VALUES ('High-Performance Java Persistence, Part 3', 3)
 
INSERT INTO post (title, id)
VALUES ('High-Performance Java Persistence, Part 4', 4)
 
INSERT INTO post (title, id)
VALUES ('High-Performance Java Persistence, Part 5', 5)

I have MySQL schema with autogen values. I use strategy=GenerationType.IDENTITY tag and seems to work fine in MySQL I guess it should work most db engines as well.

CREATE TABLE user (
    id bigint NOT NULL auto_increment,
    name varchar(64) NOT NULL default '',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

User.java:

// mark this JavaBean to be JPA scoped class
@Entity
@Table(name="user")
public class User {
    @Id @GeneratedValue(strategy=GenerationType.IDENTITY)
    private long id;    // primary key (autogen surrogate)
    
    @Column(name="name")
    private String name;

    public long getId() { return id; }
    public void setId(long id) { this.id = id; }

    public String getName() { return name; }
    public void setName(String name) { this.name=name; }
}