学习笔记-Spring Data JPA
Java Persistence API
应用背景
SpringDataJPA是更大的SpringDataFamily的一部分，它使基于JPA的存储库易于实现。本模块处理对基于JPA的数据访问层的增强支持。它使构建使用数据访问技术的Spring驱动的应用程序变得更加容易。
应用场景
JPA：全自动化、面向对象，简单查询无需写SQL，复杂业务下HQL也不一定能满足，不够灵活。
结论：当项目业务逻辑不复杂，所需要查询关联的表不是很多，且项目稳定时适用JPA。
Mybatis: 半自动化、代码层面可以使用QBC（简单查询），SQL灵活高效。
结论：当项目业务需要迭代速度快，业务复杂性高（涉及到复杂报表，统计）。
简单案例
1 | |
支持的关键字、示例及JPQL片段如下表所示：
| Keyword | Sample | JPQL snippet |
|---|---|---|
| And | findByLastnameAndFirstname | … where x.lastname = ?1 and x.firstname = ?2 |
| Or | findByLastnameOrFirstname | … where x.lastname = ?1 or x.firstname = ?2 |
| Is,Equals | findByFirstname,findByFirstnameIs,findByFirstnameEquals | … where x.firstname = ?1 |
| Between | findByStartDateBetween | … where x.startDate between ?1 and ?2 |
| LessThan | findByAgeLessThan | … where x.age < ?1 |
| LessThanEqual | findByAgeLessThanEqual | … where x.age <= ?1 |
| GreaterThan | findByAgeGreaterThan | … where x.age > ?1 |
| GreaterThanEqual | findByAgeGreaterThanEqual | … where x.age >= ?1 |
| After | findByStartDateAfter | … where x.startDate > ?1 |
| Before | findByStartDateBefore | … where x.startDate < ?1 |
| IsNull | findByAgeIsNull | … where x.age is null |
| IsNotNull,NotNull | findByAge(Is)NotNull | … where x.age not null |
| Like | findByFirstnameLike | … where x.firstname like ?1 |
| NotLike | findByFirstnameNotLike | … findByFirstnameNotLike |
| StartingWith | findByFirstnameStartingWith | … where x.firstname like ?1 (parameter bound with appended %) |
| EndingWith | findByFirstnameEndingWith | … where x.firstname like ?1 (parameter bound with prepended %) |
| Containing | findByFirstnameContaining | … where x.firstname like ?1 (parameter bound wrapped in %) |
| OrderBy | findByAgeOrderByLastnameDesc | … where x.age = ?1 order by x.lastname desc |
| Not | findByLastnameNot | … where x.lastname <> ?1 |
| In | findByAgeIn(Collection |
… where x.age in ?1 |
| NotIn | findByAgeNotIn(Collection |
… where x.age not in ?1 |
| True | findByActiveTrue() | … where x.active = true |
| False | findByActiveFalse() | … where x.active = false |
| IgnoreCase | findByFirstnameIgnoreCase | … where UPPER(x.firstame) = UPPER(?1) |
具体Spring Data Jpa对方法名的解析规则可参看官方文档4.4.3. Property Expressions
原生查询
在复杂场景下如果需要数据进行统计，需要自定义原生SQL查询，但是在使用@Query查询的时候如何使用自定义DTO来接受查询回来的数据呢？这时候就需要自定义转换器了。
注册转换器：ConverterConfig.java
需要自定义转换的标识：CustomConvertible.java
转换器：CustomEntityConverter.java
DTO:BaseInfoDO.java
Responty: StatisticsRepository.java
相关资料
https://spring.io/guides/gs/accessing-data-jpa/
https://github.com/spring-projects/spring-data-examples/tree/main/jpa
官方文档：https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.query-methods.query-property-expressions
