SpringBoot系列JPA錯誤姿勢之Entity映射

NO IMAGE

SpringBoot系列JPA錯誤姿勢之Entity映射

SpringBoot 系列 JPA 錯誤姿勢之 Entity 映射

本篇為 JPA 錯誤使用姿勢第二篇,java 的 POJO 類與數據庫表結構的映射關係,除了駝峰命名映射為下劃線之外,還會有什麼別的坑麼?

I. 映射問題

1. 項目基本配置

首先搭建基本的 springboot + jpa 項目, 我們使用的 springboot 版本為2.2.1.RELEASE,mysql 版本 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>

項目配置文件application.properties

## DataSource
spring.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.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<ErrorMetaGroupPo, Integer> {
}

測試代碼

@Component
public 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();
}
}

SpringBoot系列JPA錯誤姿勢之Entity映射

從輸出結果來看,提示的是 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

@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() {
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();
}
}

執行之後輸出:

SpringBoot系列JPA錯誤姿勢之Entity映射

II. 其他

0. 項目&關聯博文

推薦博文

源碼

1. 一灰灰 Blog

盡信書則不如,以上內容,純屬一家之言,因個人能力有限,難免有疏漏和錯誤之處,如發現 bug 或者有更好的建議,歡迎批評指正,不吝感激

下面一灰灰的個人博客,記錄所有學習和工作中的博文,歡迎大家前去逛逛

SpringBoot系列JPA錯誤姿勢之Entity映射

相關文章

Dubbo路由機制概述

《大廠面試》面試官看了直呼想要的簡歷

帶你再看Vue文檔,你就順便學會了React(慎入!)

開源|AabResGuard:AAB資源混淆工具