Question 20390 in Mysql Question posted in
The official MySQL documentation can be found here.

JPA autogenerate id from a starting point without creating that separate id table – Mysql

In JPA I want to autogenerate an integer primary key where the generation should start from, say, 1000. I’m new to JPA and I have used IDENTITY type earlier to avoid those extra id tables that JPA otherwise generates. I’ve tried using SEQUENCE but it generates a seperate id table as well.

Earlier code:

@Id
@Column(length = 4)
@GeneratedValue (strategy = GenerationType.IDENTITY)
private Integer reservationId;

For what I can see, you can’t get IDENTITY to increment from a specified starting point.

Is there any way I can use JPA to increment from a starting point and not get extra id tables?

Tags:

2

Answers


  1. 0

    You can try to use:

    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq_gen_name")
@SequenceGenerator(name = "seq_gen_name", sequenceName = "my_sequence", initialValue = 1000, allocationSize = 1)

    In this example name is the name of the sequence generator,
    sequenceName is the name of the database sequence to use,
    initialValue is the initial value of the sequence and
    allocationSize is the increment size of the sequence.

    Note: The SEQUENCE strategy requires a database sequence to be created, so you’ll need to ensure that your database supports sequences and that the sequence is created before running your (JPA) application.

    Login or Signup to reply.
  2. 0

    I’m not an expert in MySQL, but searching the web it seems that MySQL doesn’t support sequences – at least, stricto sensu. So either you use GenerationType.IDENTITY or GenerationType.TABLE.

    In order to prove the concept, let’s code a little. In the following examples, I will show the entity class, its metadata, and the SQL the persistence provider generated based on them.

    GenerationType.IDENTITY

    The entity class:

    @Entity
public class EntityOne {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
}

    The generated SQL:

    create table EntityOne (id integer not null auto_increment, primary key (id)) engine=InnoDB;

    If you need to start the AUTO_INCREMENT column from a specific value, as per the documentation, a possible solution is to insert a row with that value and then delete it.

    GenerationType.SEQUENCE

    The entity class:

    @Entity
public class EntityTwo {

    @Id
    @SequenceGenerator(name = "EntityTwoSequence", initialValue = 1000)
    @GeneratedValue(generator = "EntityTwoSequence")
    private Integer id;
}

    The generated SQL:

    create table EntityTwo (id integer not null, primary key (id)) engine=InnoDB;
create table EntityTwoSequence (next_val bigint) engine=InnoDB;
insert into EntityTwoSequence values ( 1000 );

    Again, this is because MySQL does not support sequences. If the database used were one that supports sequences, a sequence creation would be seen above.

    GenerationType.TABLE

    The entity class:

    @Entity
@TableGenerator(name = "MySequenceGenerator")
public class EntityThree {

    @Id
    @GeneratedValue(generator = "MySequenceGenerator")
    private Integer id;
}

    The generated SQL:

    create table EntityThree (id integer not null, primary key (id)) engine=InnoDB;
create table MySequenceGenerator (sequence_name varchar(255) not null, next_val bigint, primary key (sequence_name)) engine=InnoDB;
insert into MySequenceGenerator(sequence_name, next_val) values ('EntityThree',0);

    It looks like the difference between @SequenceGenerator and @TableGenerator in MySQL is that @TableGenerator creates only one table with multiple rows to manage all the ids that reference it (the expected behavior, by the way), while @SequenceGenerator creates a table for each annotation.

    GenerationType.UUID

    I decided – why not?, to see what happens when using the UUID generation type.

    The entity class:

    @Entity
public class EntityFour {
    
    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private UUID id;
}

    The generated SQL:

    create table EntityFour (id binary(16) not null, primary key (id)) engine=InnoDB;

    Only the table is created. This suggests that the id generation, in this case, is managed by the persistence provider – which is actually what the specification says.

    IMPORTANT

    1. I suggest you to set the dialect property of your persistence provider. Normally, this is done in the persistence.xml file. If you are using Hibernate, you should add the following line:
    <persistence ...>
    ...
    <persistence-unit ...>
        ...
        <properties>
            ...
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" />
        </properties>
    </persistence-unit>
</persistence>

    1. To enable the SQL script schema generation, you have to set in persistence.xml file the properties jakarta.persistence.schema-generation.scripts.action and jakarta.persistence.schema-generation.scripts.create-target.

    2. Note that the next_val columns are defined as bigint. So, if you are going to use a sequence or table generator, I suggest you to set the entity id field as a Long.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search