MySQL primary key auto-generate and generator tables and JPA primary key mappings

Source: Internet
Author: User
CREATE TABLE Stock (
id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    NO VARCHAR (255) NOT NULL,
    name VARCHAR (255) NOT NULL,
    price DECIMAL (6,2) NOT NULL,
UNIQUE KEY Stock_NO (NO),
    INDEX Stock_Name (name)
) ENGINE = InnoDB;
JPA primary key mapping
@Entity
@Table (name = "Stock", uniqueConstraints = {
        @UniqueConstraint (name = "Stock_NO", columnNames = {"NO"})
},
indexes = {
        @Index (name = "Stock_Name", columnList = "name")
})
public class Stock {
private long id;
    private String no;
    private String name;
    private double price;
    
    @Id
    @GeneratedValue (strategy = GenerationType.IDENTITY)
public long getId () {
return id;
}
public void setId (long id) {
this.id = id;
}
 @GeneratedValue (strategy = GenerationType.IDENTITY): The entity primary key generation strategy is automatically generated, compatible with MySQL primary key automatic generation strategy, the keyword is AUTO_INCREMENT. If the MySQL primary key does not specify AUTO_INCREMENT, the following exception is reported.
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute statement

Caused by: org.hibernate.exception.GenericJDBCException: could not execute statement

Caused by: java.sql.SQLException: Field 'id' doesn't have a default value
uniqueConstraints = {
        @UniqueConstraint (name = "Stock_NO", columnNames = {"NO"})
},
indexes = {
        @Index (name = "Stock_Name", columnList = "name")
}: Create a unique index, the index name is Stock_NO, for the column is NO, create an index, the index name is Stock_Name, for the column is name. The configuration of index generation can take effect only if pattern generation is started. To enable pattern generation, do the following configuration in the configuration file persistence.xml:
<properties>
            <property name = "javax.persistence.schema-generation.database.action"
                      value = "drop-and-create" />
        </ properties>
Although schema generation is very convenient and can automatically generate the table structure, the table structure generated by it is not always optimal, and it is not guaranteed to be correct. Therefore, as a best practice, it is not recommended to enable mode generation in the production environment and manually maintain the table mechanism. Disable mode generation, do the following configuration in the configuration file persistence.xml:
 <properties>
            <property name = "javax.persistence.schema-generation.database.action"
                      value = "none" />
        </ properties>
Generator table The primary key generation strategy is the generator table. This strategy is not common, and is generally used for legacy databases using JPA. Otherwise, the primary key generation strategy will generally choose automatic generation (GenerationType.IDENTITY) or sequence generation (GenerationType.SEQUENCE). To insert a piece of data into the target table, the JPA implementer selects a primary key record on the target table from the generator table, and this record holds the primary key of the target table. The JPA implementer increases the primary key value, and then inserts the value before the primary key increase into the target table. MySQL generator table
CREATE TABLE CreatorKey (
  TableName VARCHAR (64) NOT NULL PRIMARY KEY,
  KeyValue BIGINT UNSIGNED NOT NULL,
  INDEX CreatorKey_Table_Values (TableName, KeyValue)
) ENGINE = InnoDB;
MySQL target table
CREATE TABLE Student (
  id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  name VARCHAR (100) NOT NULL,
  INDEX Student_name (name)
) ENGINE = InnoDB;
JPA primary key mapping
@Entity
@Table
public class Student {
private long id;
private String name;

@Id
    @GeneratedValue (strategy = GenerationType.TABLE,
            generator = "studentGenerator")
    @TableGenerator (name = "studentGenerator", table = "creatorkey",
            pkColumnName = "TableName", pkColumnValue = "Publishers",
            valueColumnName = "KeyValue", initialValue = 1,
            allocationSize = 1)
    @Column (name = "studentId")
public long getId () {
return id;
}
name: the name defined by the primary key generation strategy; table: the name of the generator table in the database; pkColumnName: the name of the primary key column of the generator table; pkColumnValue: the value of the primary key column of the generator table; valueColumnName: the name of the value column of the generator table ; InitialValue: the initial value of the generator table; allocationSize: the increment or decrement of the value of the generator table. generator: the name defined by the primary key generation strategy, which is consistent with the name attribute; generator = "studentGenerator": use the primary key generation strategy of the generator table. The attribute initialValue, allocationSize of the primary key @TableGenerator According to the source code, the attributes initialValue and allocationSize of @TableGenerator are optional, and the default values are 0 and 50, respectively.
  / **
     * (Optional) The initial value to be used to initialize the column
     * that stores the last value generated.
     * /
    int initialValue () default 0;

    / **
     * (Optional) The amount to increment by when allocating id
     * numbers from the generator.
     * /
    int allocationSize () default 50;
However, according to the actual test results, the situation is not as indicated by the source code. Clear the generator table creatorkey and target table student, remove the attributes initialValue and allocationSize, and perform the persistence operation.
 Student student = new Student ();
            student.setName ("张三");
            manager.persist (student);
The results obtained are like this. It can be seen that the initial value of the generator table is not 0, and the increment or decrement is not 50. And each time after restarting the web server, the initial value and increment are uncertain. More importantly, the generation of the primary key has lost contact with the generator table, and KeyValue stays at a certain value and will not change. Therefore, it is recommended that when the annotation @TableGenerator is written, although the attributes initialValue and allocationSize are optional, it is necessary to specify values for these two attributes. Surprisingly, even if you explicitly specify values for these two attributes, in many cases, the above problems will occur. After testing, when initialValue and allocationSize are both set to 1, the operation is normal. The scope of the primary key @TableGenerator According to the source code, in the same persistence unit, the definition of the primary key generation strategy of @TableGenerator is global and can be referenced by other entities.
/ **
 * Defines a primary key generator that may be
 * referenced by name when a generator element is specified for
 * the {@link GeneratedValue} annotation. A table generator
 * may be specified on the entity class or on the primary key
 * field or property. The scope of the generator name is global
 * to the persistence unit (across all generator types).

However, according to the actual test results, the situation is not as indicated by the source code. Even within the same persistence unit, @ TableGenerator's primary key generation strategy definition only takes effect for the entity that defines it.
@Entity
@Table
public class Book implements Serializable
{
    private long id;
    @Id
    @GeneratedValue (strategy = GenerationType.TABLE,
            generator = "studentGenerator")
    public long getId ()
    {
        return this.id;
    }

    public void setId (long id)
    {
        this.id = id;
    }
Start the web server and report the following exception.

javax.persistence.PersistenceException:

[PersistenceUnit: EntityMappings] Unable to build Hibernate SessionFactory
Caused by: org.hibernate.AnnotationException: Unknown Id.generator: studentGenerator
org.hibernate.AnnotationException: Unknown Id.generator: studentGenerator
If you add the definition of the primary key generation strategy to the entity Book, it will run normally.
@Entity
@Table
public class Book implements Serializable
{
     private long id;

     @Id
     @GeneratedValue (strategy = GenerationType.TABLE,
             generator = "studentGenerator")
     @TableGenerator (name = "studentGenerator", table = "creatorkey",
             pkColumnName = "TableName", pkColumnValue = "Publishers",
             valueColumnName = "KeyValue", initialValue = 1, allocationSize = 1)
     public long getId ()
     {
         return this.id;
     }

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





MySQL primary key automatic generation and generator table and JPA primary key mapping

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.