Entity mapping for SpringBoot series JPA error pose

Entity mapping for SpringBoot series JPA error pose

Java POJO class mapping to database table structure, besides the hump name mapping to underline, what other pit?

I. Mapping problem

1. Perform basic project configurations

First, build the basic Springboot + JPA project, we use springboot version 2.2.1.RELEASE, mysql version 5+

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
Copy the code

Project configuration file application.properties

# # the DataSource spring. The DataSource. Url = JDBC: mysql: / / 127.0.0.1:3306 / story? useUnicode=true&characterEncoding=UTF-8&useSSL=false spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver spring.datasource.username=root spring.datasource.password= spring.jpa.database=MYSQL spring.jpa.hibernate.ddl-auto=none  spring.jpa.show-sql=true spring.jackson.serialization.indent_output=true spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImplCopy the code

Table structure

CREATE TABLE `meta_group` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group` varchar(32) NOT NULL DEFAULT ' ' COMMENT 'group'.`profile` varchar(32) NOT NULL DEFAULT ' ' COMMENT 'profile is currently used in the application environment with the value dev/test/pro'.`desc` varchar(64) NOT NULL DEFAULT ' ' COMMENT 'Explanation'.`deleted` int(4) NOT NULL DEFAULT '0' COMMENT '0 is valid and 1 is invalid '.`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time'.`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modification time',
  PRIMARY KEY (`id`),
  KEY `group_profile` (`group`.`profile`))ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COMMENT='Service Configuration Group Table';
Copy the code

2. The wrong case

Java variable naming is recommended to be hump naming, so it needs to be associated with the underscore of the field in the database. Through jPA knowledge, we know that we can use the @column annotation to handle this, so we have the following notation

@Data
@Entity
@Table(name = "meta_group")
public class ErrorMetaGroupPo {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "group")
    private String group;

    @Column(name = "profile")
    private String profile;

    @Column(name = "desc")
    private String desc;

    @Column(name = "deleted")
    private Integer deleted;

    @Column(name = "create_time")
    private Timestamp createTime;

    @Column(name = "update_time")
    private Timestamp updateTime;
}
Copy the code

From the naming we can see that the above case is wrong, so what is the problem?

Write a corresponding Repository to test this

public interface ErrorGroupJPARepository extends JpaRepository<ErrorMetaGroupPo.Integer> {}Copy the code

The test code

@Component
public class GroupManager {
    @Autowired
    private ErrorGroupJPARepository errorGroupJPARepository;

    public void test(a) {
        String group = UUID.randomUUID().toString().substring(0.4);
        String profile = "dev";
        String desc = "Test JPA exception case!";
        try {
            int id = addGroup1(group, profile, desc);
            System.out.println("add1: " + id);
        } catch(Exception e) { e.printStackTrace(); }}public Integer addGroup1(String group, String profile, String desc) {
        ErrorMetaGroupPo jpa = new ErrorMetaGroupPo();
        jpa.setGroup("add1: " + group);
        jpa.setDesc(desc);
        jpa.setProfile(profile);
        jpa.setDeleted(0);
        Timestamp timestamp = Timestamp.from(Instant.now());
        jpa.setCreateTime(timestamp);
        jpa.setUpdateTime(timestamp);
        ErrorMetaGroupPo res = errorGroupJPARepository.save(jpa);
        returnres.getId(); }}Copy the code

SQL exception: SQL exception: why?

• Group,desc is the keyword. When you spell SQL, you need to enclose the keyword with back quotation marks

3. Correct pose 1

The first correct gesture is to enclose the name of the @column in backquotes

@Data
@Entity
@Table(name = "meta_group")
public class MetaGroupPO {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "`group`")
    private String group;

    @Column(name = "`profile`")
    private String profile;

    @Column(name = "`desc`")
    private String desc;

    @Column(name = "`deleted`")
    private Integer deleted;

    @Column(name = "`create_time`")
    private Timestamp createTime;

    @Column(name = "`update_time`")
    private Timestamp updateTime;
}
Copy the code

4. Correct pose two

In addition to the above case, there is another common way to implement a custom PhysicalNamingStrategy that implements field mapping

Such as our custom JpaNamingStrategyStandardImpl inherited from the default PhysicalNamingStrategyStandardImpl strategy, and then, in the field name for no quotation package field name to take the initiative to add a negative quotes

public class JpaNamingStrategyStandardImpl extends PhysicalNamingStrategyStandardImpl {
    @Setter
    private static int mode = 0;

    @Override
    public Identifier toPhysicalColumnName(Identifier name, JdbcEnvironment context) {
        if (mode == 1) {
            if (name.isQuoted()) {
                return name;
            } else {
                return Identifier.toIdentifier("`" + name.getText() + "`".true); }}else {
            returnname; }}}Copy the code

Note that to use the above mapping strategy, you need to modify the configuration file (application.properties)

spring.jpa.hibernate.naming.physical-strategy=com.git.hui.boot.jpacase.strategy.JpaNamingStrategyStandardImpl
Copy the code

The test case

@SpringBootApplication
public class Application {
    public Application(GroupManager groupManager) {
        groupManager.test();
    }

    public static void main(String[] args) {
        JpaNamingStrategyStandardImpl.setMode(1); SpringApplication.run(Application.class, args); }}@Component
public class GroupManager {
    @Autowired
    private ErrorGroupJPARepository errorGroupJPARepository;

    @Autowired
    private GroupJPARepository groupJPARepository;


    public void test(a) {
        String group = UUID.randomUUID().toString().substring(0.4);
        String profile = "dev";
        String desc = "Test JPA exception case!";
        try {
            int id = addGroup1(group, profile, desc);
            System.out.println("add1: " + errorGroupJPARepository.findById(id));
        } catch (Exception e) {
            e.printStackTrace();
        }

        try {
            int id2 = addGroup2(group, profile, desc);
            System.out.println("add2: " + groupJPARepository.findById(id2));
        } catch(Exception e) { e.printStackTrace(); }}public Integer addGroup1(String group, String profile, String desc) {
        ErrorMetaGroupPo jpa = new ErrorMetaGroupPo();
        jpa.setGroup("add1: " + group);
        jpa.setDesc(desc);
        jpa.setProfile(profile);
        jpa.setDeleted(0);
        Timestamp timestamp = Timestamp.from(Instant.now());
        jpa.setCreateTime(timestamp);
        jpa.setUpdateTime(timestamp);
        ErrorMetaGroupPo res = errorGroupJPARepository.save(jpa);
        return res.getId();
    }

    public Integer addGroup2(String group, String profile, String desc) {
        MetaGroupPO jpa = new MetaGroupPO();
        jpa.setGroup("add2: " + group);
        jpa.setDesc(desc);
        jpa.setProfile(profile);
        jpa.setDeleted(0);
        Timestamp timestamp = Timestamp.from(Instant.now());
        jpa.setCreateTime(timestamp);
        jpa.setUpdateTime(timestamp);
        MetaGroupPO res = groupJPARepository.save(jpa);
        returnres.getId(); }}Copy the code

Output after execution:

II. The other

0. Projects & Associated blog posts

Recommend the blog

• 190612-SpringBoot series tutorial JPA base environment setup
• 190614-SpringBoot series of tutorials added to JPA recording using posture
• 190623-SpringBoot series JPA update using postures
• 190702-SpringBoot series tutorial JPA delete using posture details
• 190717-SpringBoot series JPA query: The basics of postures
• 191218-SpringBoot series tutorial JPA wrong pose environment configuration issues

The source code

• Project: github.com/liuyueyi/sp…
• Project: github.com/liuyueyi/sp…

1. An ashy Blog

As far as the letter is not as good, the above content is purely one’s opinion, due to the limited personal ability, it is inevitable that there are omissions and mistakes, if you find bugs or have better suggestions, welcome criticism and correction, don’t hesitate to appreciate

Below a gray personal blog, record all the study and work of the blog, welcome everyone to go to stroll

• A grey Blog Personal Blog blog.hhui.top
• A Grey Blog-Spring feature Blog Spring.hhui.top