jpa自定义sql_SpringBoot 系列 JPA 错误姿势之 Entity 映射

本篇为 JPA 错误使用姿势第二篇，java 的 POJO 类与数据库表结构的映射关系，除了驼峰命名映射为下划线之外，还会有什么别的坑么？

I. 映射问题

1. 项目基本配置

首先搭建基本的 springboot + jpa 项目， 我们使用的 springboot 版本为2.2.1.RELEASE，mysql 版本 5+

org.springframework.boot    spring-boot-starter-data-jpamysql    mysql-connector-java

项目配置文件application.properties

## DataSourcespring.datasource.url=jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=falsespring.datasource.driver-class-name=com.mysql.cj.jdbc.Driverspring.datasource.username=rootspring.datasource.password=spring.jpa.database=MYSQLspring.jpa.hibernate.ddl-auto=nonespring.jpa.show-sql=truespring.jackson.serialization.indent_output=truespring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

表结构

CREATE TABLE `meta_group` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `group` varchar(32) NOT NULL DEFAULT '' COMMENT '分组',  `profile` varchar(32) NOT NULL DEFAULT '' COMMENT 'profile 目前用在应用环境 取值 dev/test/pro',  `desc` varchar(64) NOT NULL DEFAULT '' COMMENT '解释说明',  `deleted` int(4) NOT NULL DEFAULT '0' COMMENT '0表示有效 1表示无效',  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',  PRIMARY KEY (`id`),  KEY `group_profile` (`group`,`profile`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COMMENT='业务配置分组表';

2. 错误 case

java 变量命名推荐的是驼峰命名方式，因此与数据库中字段的下划线方式需要关联映射，通过 jpa 的相关知识学习，我们知道可以使用@Column注解来处理，所以有下面这种写法

@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;}

从命名上就可以看出上面这种 case 是错误的，那么到底是什么问题呢？

先写一个对应的 Repository 来实测一下

public interface ErrorGroupJPARepository extends JpaRepository {}

测试代码

@Componentpublic class GroupManager {    @Autowired    private ErrorGroupJPARepository errorGroupJPARepository;    public void test() {        String group = UUID.randomUUID().toString().substring(0, 4);        String profile = "dev";        String desc = "测试jpa异常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);        return res.getId();    }}

从输出结果来看，提示的是 sql 异常，why?

• group,desc 为关键字，拼 sql 的时候需要用反引号包裹起来

3. 正确姿势一

第一种正确使用姿势，直接在@column的 name 中，添加反引号包裹起来

@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;}

4. 正确姿势二

除了上面的 case 之外，还有另外一种通用的方式，实现自定义的PhysicalNamingStrategy，实现字段映射

比如我们自定义JpaNamingStrategyStandardImpl继承自默认的PhysicalNamingStrategyStandardImpl策略，然后在字段名中，对于没有引号的包裹的字段名主动添加一个反引号

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 {            return name;        }    }}

注意使用上面的映射策略，需要修改配置文件(application.properties)

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

测试 case

@SpringBootApplicationpublic class Application {    public Application(GroupManager groupManager) {        groupManager.test();    }    public static void main(String[] args) {        JpaNamingStrategyStandardImpl.setMode(1);        SpringApplication.run(Application.class, args);    }}@Componentpublic class GroupManager {    @Autowired    private ErrorGroupJPARepository errorGroupJPARepository;    @Autowired    private GroupJPARepository groupJPARepository;    public void test() {        String group = UUID.randomUUID().toString().substring(0, 4);        String profile = "dev";        String desc = "测试jpa异常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);        return res.getId();    }}

执行之后输出: