SoFunction
Updated on 2025-03-08

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

jpa @Column columnDefinition property invalid

Delete a property, default false

#.globally_quoted_identifiers=true

reason

After opening, '`' will be added when creating SQL statements are executed, which will cause the columnDefinition attribute to be invalid. Author: dreamlu

For example

1. The property is set 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 ''
// Execution successfully

It can be seen that: There is a favor and gain. The second requirement is that the naming of fields/tables cannot be duplicated with keywords in mysql or other databases.

jpa column annotation

Knowledge points

There are 10 attributes in the @Column annotation. These 10 attributes are optional attributes. The meanings of each attribute are as follows:

  • name: The name attribute defines the name of the corresponding field to the marked field in the database table;
  • unique:The unique attribute indicates whether the field is a unique identifier, and the default is false. If there is a field in the table that requires a unique identification, you can either use that tag or use @UniqueConstraint in the @Table tag.
  • nullable : The nullable property indicates whether the field can be a null value, and the default is true.
  • insertable : The insertable property indicates whether the value of the field needs to be inserted when inserting data using the "INSERT" script.
  • updatable: The updatable attribute indicates whether the value of the field needs to be updated when inserting data using the "UPDATE" script. The insertable and updatable attributes are generally used for read-only attributes, such as primary keys and foreign keys. The values ​​of these fields are usually generated automatically.
  • columnDefinition :columnDefinition attribute indicates that the SQL statement created by this field is generally used when creating a table definition through Entity. (That is, if the table in DB has been built, this property is not necessary to be used.)
  • table : The table attribute defines the table name that contains the current field.
  • length :The length attribute represents the length of the field. This attribute is only valid when the field type is varchar, and the default is 255 characters.
  • precisionandscale : The precision attribute and scale attribute represent precision. When the field type is double, precision indicates the total length of the value, and scale indicates the number of digits occupied by the decimal point.

Precision and scale doubts

@Table(name = "CUSTOMERS")
@Entity
public class Customer {
    @Column(name = "ID")
    @GeneratedValue(strategy = )
    @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;

Let's summarize

If the type specifies the numeric type as decimal and specifies the precision in the columnDefinition property, the columnDefinition will be the final result (except in the oracle database, which is specified as float type, because the oracle database does not have a double type. If the oracle database is accurate, it will be changed to

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

Types will be mapped to double type in the database, precision and scale properties are invalid

Types are mapped to decimal types in the database, precision and scale properties are valid

The scale attributes are only valid in BigDecimal type

The above is personal experience. I hope you can give you a reference and I hope you can support me more.