Solve the problem of attribute failure such as springboot jpa @ Column columnDefinition

  • 2021-12-04 10:04:09
  • OfStack

Directory jpa @ Column columnDefinition attribute failure reasons such as jpa column annotation knowledge points precision and scale doubtful points summary 1

jpa @ Column columnDefinition property invalid

Delete 1 attribute, default false


#spring.jpa.properties.hibernate.globally_quoted_identifiers=true

Cause

When opened, '`' will be added when the Create sql statement is executed, which will invalidate the columnDefinition attribute, author: dreamlu

For example

1. Set the property to true


alter table `xxx` add column `xxx` `varchar(50) default ''`
// sql  Syntax error 

2. The attribute is false


alter table xxx add column xx varchar(50) default ''
//  Successful execution 

It can be seen that the second type requires that the names of fields/tables cannot be the same as keywords in mysql or other databases

jpa column Notes

Knowledge point

@ Column annotation 1 has 10 attributes, all of which are optional, and the meanings of each attribute are as follows:

name The name attribute defines the name of the field that the annotated field corresponds to in the database table; unique The unique property indicates whether the field is a 1-only identity, and the default is false. If a table has 1 field that requires a 1-only identity, you can use either that tag or @ UniqueConstraint in the @ Table tag. nullable The nullable property indicates whether the field can be an null value, which defaults to true. insertable The insertable property indicates whether the value of this field needs to be inserted when inserting data using the "INSERT" script. updatable The updatable property indicates whether the value of this field needs to be updated when data is inserted using the "UPDATE" script. insertable and updatable properties 1 are used for read-only properties, such as primary and foreign keys. The values of these fields are usually generated automatically. columnDefinition The columnDefinition attribute represents the SQL statement created by this field when the table is created, and is generally used when generating the table definition from Entity. (That is, if the table in DB is already built, this property is not necessary.) table The table attribute defines the name of the table that contains the current field. length The length property represents the length of the field, which is valid when the field is of type varchar and defaults to 255 characters. precision And scale The precision and scale attributes denote precision, precision denotes the total length of the value when the field type is double, and scale denotes the number of decimal places.

Doubts about precision and scale


@Table(name = "CUSTOMERS")
@Entity
public class Customer {
    @Column(name = "ID")
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Id
    private Integer id; 
    @Column(name = "Name")
    private String name;
 
    @Column(name = "Email", nullable = true, length = 128)
    private String email;
 
    @Column(name = "Age")
    private int age;
 
    @Column(name = "Remark", columnDefinition = "text")
    private String remark;
 
    @Column(name = "Salary1", columnDefinition = "decimal(5,2)")
    private double salary1;
 
    @Column(name = "Salary2", precision = 5, scale = 2)
    private double salary2;
 
    @Column(name = "Salary3", columnDefinition = "decimal(5,2)")
    private BigDecimal salary3;
 
    @Column(name = "Salary4", precision = 5, scale = 2)
    private BigDecimal salary4;
    ......
}

Database DDL:


CREATE TABLE `customers` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Age` int(11) DEFAULT NULL,
  `Email` varchar(128) DEFAULT NULL,
  `Name` varchar(255) DEFAULT NULL,
  `Remark` text,
  `Salary1` decimal(5,2) DEFAULT NULL,
  `Salary2` double DEFAULT NULL,
  `Salary3` decimal(5,2) DEFAULT NULL,
  `Salary4` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Summary 1

1. double type decimal type specified in the columnDefinition attribute with precision is ultimately columnDefinition (except in the oracle database, which is specified as float type, because oracle database does not have double type, if precision is specified for oracle database, it is changed to


@Column(name = "Salary1", columnDefinition = "decimal(5,2)")  // Or columnDefinition = "number(5,2)"
    private Float salary1;

2. The double type will be mapped to the double type in the database, and the precision and scale attributes are invalid

3. The BigDecimal type is mapped to the decimal type in the database, and the precision and scale attributes are valid

4. precision and scale attributes are valid only in BigDecimal types


Related articles: