SpringDataJPA进阶查询—JPQL原⽣SQL查询、分页处理、部分字段映射查询
上⼀篇介绍了⼊门基础篇。本篇介绍SpringDataJPA进⼀步的定制化查询，使⽤JPQL或者SQL进⾏查询、部分字段映射、分页等。本⽂尽量以简单的建模与代码进⾏展⽰操作，⽂章⽐较长，包含查询的⽅⽅⾯⾯。如果能耐⼼看完这篇⽂章，你应该能使⽤SpringDataJPA应对⼤部分的持久层开发需求。如果你需要使⽤到动态条件查询，请查看下⼀篇博客，专题介绍SpringDataJPA的动态查询。
⼀、⼊门引导与准备
JPQL（JavaPersistence Query Language）是⼀种⾯向对象的查询语⾔，它在框架中最终会翻译成为sql进⾏查询，如果不知JPQL请⼤家⾃⾏⾕歌了解⼀下，如果你会SQL，了解这个应该不废吹灰之⼒。
1.核⼼注解@Query介绍
使⽤SpringDataJPA进⾏JPQL/SQL⼀般查询的核⼼是@Query注解，我们先来看看该注解
1. @Retention(RetentionPolicy.RUNTIME)
2. @Target({ ElementType.METHOD, ElementType.ANNOTATION_TYPE })
3. @QueryAnnotation
4. @Documented
5. public @interface Query {
6. String value() default "";
7. String countQuery() default "";
8. String countProjection() default "";
9. boolean nativeQuery() default false;
10. String name() default "";
11. String countName() default "";
12. }
该注解使⽤的注解位置为⽅法、注解类型，⼀般我们⽤于注解⽅法即可。@QueryAnnotation标识这是⼀个查询注解；
@Query注解中有6个参数，value参数是我们需要填⼊的JPQL/SQL查询语句；nativeQuery参数是标识该查询是否为原⽣SQL查询，默认为false；countQuery参数为当你需要使⽤到分页查询时，可以⾃⼰定义（count查询）计数查询的语句，如果该项为空但是如果要⽤到分页，那么就使⽤默认的主sql条件来进⾏计数查询；name参数为命名查询需要使⽤到的参数，⼀般配配合@NamedQuery⼀起使⽤，这个在后⾯会说到；countName参数作⽤与countQuery 相似，但是使⽤的是命名查询的（count查询）计数查询语句；countProjection为涉及到投影部分字段查询时的计数查询（count查询）；关于投影查询，待会会说到。
有了@Query基础后，我们就可以⼩试⽜⼑⼀把了，对于jar包依赖，我们⽤的依旧是上⼀节的依赖，代码如下：
2.准备实验环境
1. <parent>
2. <groupId>org.springframework.boot</groupId>
3. <artifactId>spring-boot-starter-parent</artifactId>
4. <version>1.4.1.RELEASE</version>
5. </parent>
6.
7. <properties>
8. <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
9. <java.version>1.8</java.version>
10. &upId>org.springframework.boot</upId>
11. </properties>
12.
13. <dependencies>
14. <!-- SpringBoot Start -->
15. <dependency>
16. <groupId>${upId}</groupId>
17. <artifactId>spring-boot-starter-web</artifactId>
18. </dependency>
19. <!-- jpa -->
20. <dependency>
21. <groupId>${upId}</groupId>
22. <artifactId>spring-boot-starter-data-jpa</artifactId>
23. </dependency>
24. <dependency>
25. <groupId>${upId}</groupId>
26. <artifactId>spring-boot-starter-test</artifactId>
27. </dependency>
28. <!-- mysql -->
29. <dependency>
30. <groupId>mysql</groupId>
31. <artifactId>mysql-connector-java</artifactId>
32. </dependency>
33. <dependency>
34. <groupId>junit</groupId>
35. <artifactId>junit</artifactId>
36. <version>4.12</version>
37. </dependency>
38. </dependencies>
项⽬结构如下：
JpaConfiguration配置类与上篇的相同：
1. @Order(Ordered.HIGHEST_PRECEDENCE)
2. @Configuration
3. @EnableTransactionManagement(proxyTargetClass=true)
4. @EnableJpaRepositories(basePackages={"org.fage.**.repository"})
5. @EntityScan(basePackages={"org.fage.**.entity"})
6. public class JpaConfiguration {
7. @Bean
8. PersistenceExceptionTranslationPostProcessor persistenceExceptionTranslationPostProcessor(){
9. return new PersistenceExceptionTranslationPostProcessor();
10. }
11. }
App类：
1. @SpringBootApplication
2. @ComponentScan("org.fage.**")
3. public class App {
4. public static void main(String[] args) throws Exception {
5. SpringApplication.run(App.class, args);
7. }
对于实体建模依旧⽤到上⼀篇所⽤的模型Department、User、Role，Department与User为⼀对多，User与Role为多对多，为了⽅便后⾯介绍投影，user多增加⼏个字段，代码如下：
1. @Entity
2. @Table(name = "user")
3. public class User implements Serializable {
4.
5. private static final long serialVersionUID = -7237729978037472653L;
6. @Id
7. @GeneratedValue(strategy = GenerationType.IDENTITY)
8. private Long id;
9. private String name;
10. private String password;
11. @Column(name = "create_date")
12. @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
13. @Temporal(TemporalType.TIMESTAMP)
14. private Date createDate;
15. private String email;
16. // ⼀对多映射
17. @ManyToOne
18. @JoinColumn(name = "department_id")
19. private Department department;
20. // 多对多映射
21. @ManyToMany @JsonBackReference
22. @JoinTable(name = "user_role", joinColumns = { @JoinColumn(name = "user_id") }, inverseJoinColumns = {
23. @JoinColumn(name = "role_id") })
24. private List<Role> roles;
25. //getter and setter .....
26. }
1. @Entity
2. @Table(name = "department")
3. public class Department implements Serializable {
4.
5. /**
6. *
7. */
8. private static final long serialVersionUID = 3743774627141615707L;
9. @Id
10. @GeneratedValue(strategy=GenerationType.IDENTITY)
11. private Long id;
12. private String name;
13. @OneToMany(mappedBy = "department")@JsonBackReference
14. @JsonBackReferenceprivate List<User> users;
15. //getter and setter
16. }
1. @Entity
2. @Table(name="role")
3. public class Role implements Serializable{
4.
5. /**
6. *
7. */
8. private static final long serialVersionUID = 1366815546093762449L;
9. @Id
10. @GeneratedValue(strategy=GenerationType.IDENTITY)
11. private Long id;
12. private String name;
13.
14. //getter and setter
15. }
建模成功时，⽣成的表结构如下：
对于Repository：
1. @Repository
2. public interface DepartmentRepository extends JpaRepository<Department, Long>{}
1. @Repository
2. public interface RoleRepository extends JpaRepository<Role, Long>{}
1. @Repository
2. public interface UserRepository extends JpaRepository<User, Long>{
3. }
如果以上代码有看不懂的地⽅，请移步到上⼀篇⼀览基础篇。⾄此，我们已经将环境整理好了，⾄于表中的数据插⼊，希望各位参考上⼀篇⽂章进⾏基础的crud操作将表中数据进⾏填充，接下来介绍@Query查询
⼆、使⽤JPQL查询
1 .核⼼查询与测试样例
在UserRepository中增加以下⽅法：
1. //--------------JPQL查询展⽰-------------//
2.
3. //展⽰位置参数绑定
4. @Query(value = "from User u where u.name=?1 and u.password=?2")
5. User findByNameAndPassword(String name, String password);
6.
7. //展⽰名字参数绑定
8. @Query(value = "from User u where u.name=:name ail=:email")
9. User findByNameAndEmail(@Param("name")String name, @Param("email")String email);
10.
11. //展⽰like模糊查询
12. @Query(value = "from User u where u.name like %:nameLike%")
13. List<User> findByNameLike(@Param("nameLike")String nameLike);
15. //展⽰时间间隔查询
16. @Query(value = "from User u ateDate between :start and :end")
17. List<User> findByCreateDateBetween(@Param("start")Date start, @Param("end")Date end);
18.
19. //展⽰传⼊集合参数查询
20. @Query(value = "from User u where u.name in :nameList")
21. List<User> findByNameIn(@Param("nameList")Collection<String> nameList);
22.
23. //展⽰传⼊Bean进⾏查询（SPEL表达式查询）
24. @Query(value = "from User u where u.name=:#{#usr.name} and u.password=:#{#usr.password}")
25. User findByNameAndPassword(@Param("usr")User usr);
26.
27. //展⽰使⽤Spring⾃带分页查询
28. @Query("from User u")
29. Page<User> findAllPage(Pageable pageable);
30.
31. //展⽰带有条件的分页查询
32. @Query(value = "from User u ail like %:emailLike%")
33. Page<User> findByEmailLike(Pageable pageable, @Param("emailLike")String emailLike);
TestClass的代码如下：
1. @RunWith(SpringRunner.class)
2. @SpringBootTest
3. public class TestClass {
4. final Logger logger = Logger(TestClass.class);
5. @Autowired
6. UserRepository userRepository;
7.
8. @Test
9. public void testfindByNameAndPassword(){
10. userRepository.findByNameAndPassword("王⼤帅", "123");
11. }
12.
13. @Test
14. public void testFindByNameAndEmail(){
15. userRepository.findByNameAndEmail("张⼤仙", "2@qq");
16. }
17.
18. @Test
19. public void testFindByNameLike(){
20. List<User> users = userRepository.findByNameLike("马");
21. logger.info(users.size() + "----");
sql包含哪几个部分22. }
23.
24. @Test
25. public void testFindByCreateDateBetween() throws ParseException{
26. List<User> users = userRepository.findByCreateDateBetween(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse("2018-01-01 00:00:00"), new Date(System.currentTimeMillis()));
27. logger.info(users.size() + "----");
28. }
29.
30. @Test
31. public void testFindByNameIn(){
32. List<String> list = new ArrayList<String>();
33. list.add("王⼤帅");
34. list.add("李⼩三");
35. userRepository.findByNameIn(list);
36. }
37.
38. @Test
39. public void testfindByNameAndPasswordEntity(){
40. User u = new User();
41. u.setName("李⼩三");
42. u.setPassword("444");
43. userRepository.findByNameAndPassword(u);
44. }
45.
46. @Test
47. public void testFindAllPage(){
48. Pageable pageable = new PageRequest(0,5);
49. Page<User> page = userRepository.findAllPage(pageable);
50. ObjectMapper mapper = new ObjectMapper();
51. String json = mapper.writeValueAsString(page);
52. logger.info(json);
53. }
54. @Test
55. public void findByEmailLike(){
56. Pageable pageable = new PageRequest(0,5,new Sort(Direction.ASC,"id"));
57. userRepository.findByEmailLike(pageable, "@qq");
58. }
59. }
⾄此，显⽰了使⽤JPQL进⾏单表查询的绝⼤多数操作，当你在实体设置了fetch=FetchType.LAZY 或者EAGER时，会有不同的⾃动连接查询，⿎励⼤家⾃⾏尝试。以上查询语句有必要对其中⼏个进⾏解释⼀下；
对于UserRepository中的第⼀与第⼆个⽅法，⽬的是为了⽐较与展⽰位置绑定与名字绑定的区别，相信根据名称⼤家就能判别是什么意思与区别了，位置绑定即是⽅法参数从左到右第所在位置的参数与查询语句中的第进⾏对应。名字绑定即是查询语句中的参数名称与⽅法参数名称⼀⼀对应；对于第三个与第四个查询例⼦就不多说了；第五条查询语句展⽰的是传⼊集合进⾏in查询；第六条查询例⼦展⽰的是传⼊bean进⾏查询，该查询使⽤的表达式是Spring的SPEL表达式；
2. 分页与排序
最后两条查询语句展⽰的是进⾏分页查询、分页并排序查询，使⽤的计数查询默认使⽤主查询语句中的条件进⾏count，当Repository接⼝的⽅法中含有Pageable参数时，那么SpringData认为该查询是需要分页的；org.springframework.data.domain.Pageable是⼀个接⼝，接⼝中定义了分页逻辑操作，它具有⼀个间接实现类为PageRequest，我们最需要关注的是PageRequest这个实现类的三个构造⽅法：
1. public class PageRequest extends AbstractPageRequest {
2. ....
3. ....
4. public PageRequest(int page, int size) {
5. this(page, size, null);
6. }
7. public PageRequest(int page, int size, Direction direction, properties) {
8. this(page, size, new Sort(direction, properties));
9. }
10. public PageRequest(int page, int size, Sort sort) {
11. super(page, size);
12. this.sort = sort;
13. }
14. ....
15. ....
16. }
page参数为页码（查第⼏页，从0页开始），size为每页显⽰多少条记录数；
Direction则是⼀个枚举，如果该参数被传⼊则进⾏排序，常⽤的有Direction.ASC/Direction.DESC，即正序排与逆序排，如果排序，需要根据哪个字段排序呢？properties是⼀个可变长参数，传⼊相应字段名称即可根据该字段排序。还有最后⼀个参数Sort，Sort这个类中有⼀个构造⽅法：public Sort(Direction direction, properties)，没错，我不⽤说相信⼤家都已经懂了是⼲什么⽤的了。
Pageable与PageRequest的关系解释完了，那么就该介绍⼀下最后两条查询语句的返回值Page<T>是⼲什么⽤的了，让我们看看倒数第⼆个测试⽅法返回的json串结果：
1. { "content": [
2. { "id": 1,"name": "王⼤帅","password": "123", "createDate": 1515312688000, "email": "1@qq","department": { "id": 1, "name": "开发部"}},
3. { "id": 2, "name": "张⼤仙", "password": "456", "createDate": 1515139947000, "email": "2@qq", "department": {"id": 1, "name": "开发部" }},
4. {"id": 3, "name": "李⼩三","password": "789","createDate": 1514794375000, "email": "3@qq","department": {"id": 1, "name": "开发部" }},
5. {"id": 4, "name": "马上来","password": "444", "createDate": 1512116003000, "email": "4@qq", "department": { "id": 1,"name": "开发部" } },
6. { "id": 5, "name": "马德华", "password": "555","createDate": 1515312825000,"email": "5@qq","department": { "id": 1, "name": "开发部"} }],
7. "last": true,
8. "totalPages": 1,
9. "totalElements": 5,
10. "size": 5,
11. "number": 0,
12. "sort": null,
13. "first": true,
14. "numberOfElements": 5
15. }
跟踪源码得到结论，Page<T>是⼀个接⼝，它的基类接⼝Slice<T>也是⼀个接⼝，⽽实现类Chunk实现了Slice，实现类PageImpl继承了Chunk并且实现了Page接⼝。所以实际上Json输出的字符串是PageImpl的拥有的所有属性(包括其⽗类Chunk)。content属性是分页得出的实体集合，类型为List，也就是上⾯json串中的content。last属性表⽰是否为最后⼀页，totalPages表⽰总页数，totalElements表⽰总记录数，size为每页记录数⼤⼩，number表⽰当前为第⼏页，numberOfElements表⽰当前页所拥有的记录数，first表⽰当前是否第⼀页，sort为排序信息。
到这⾥，Page与Pageable都了解了。
3. 关联查询与部分字段映射投影
接下来介绍使⽤JPQL进⾏关联查询与部分字段映射。现在的查询需求是，查出所有⽤户的名字、⽤户所属部门、⽤户的email、统计⽤户所拥有的⾓⾊有多少个，然后将列表结果进⾏给前端显⽰。有的朋友说，那我把关联到的对象都拿出来不就完了。可是，实际开发中⼀个表下有⼏⼗个字段会很常见，如果
全部都拿出来是没有必要的，所以我们可以把需要的字段拿出来就可以了，下⾯介绍两种⽅法实现这种需求。
3.1 使⽤VO（view object）做映射与投影
我们在src/main/java中增加⼀个org.fage.vo包，该包下存放VO对象，我们在该包下创建⼀个UserOutputVO：
1. public class UserOutputVO {
2. private String name; //⽤户的名字
3. private String email; //⽤户的email
4. private String departmentName; //⽤户所属的部门
5. private Long roleNum; //该⽤户拥有的⾓⾊数量
6.
7. public UserOutputVO(String name, String email, String departmentName, Long roleNum) {
8. super();
9. this.name = name;
10. ail = email;
11. this.departmentName = departmentName;
12. leNum = roleNum;
13. }
14. public UserOutputVO() {
15. super();
16. }
17. //getter and setter and toString
18. ...
19. }
在UserRepository中创建查询⽅法：
1. @Query(value = "select new org.fage.vo.UserOutputVO(u.name, u.email, d.name as departmentName, count(r.id) as roleNum) from User u "
2. + "left join u.department d left les r group by u.id")
3. Page<UserOutputVO> findUserOutputVOAllPage(Pageable pageable);
这⾥注意⼀下，VO中的构造⽅法参数⼀定要与查询语句中的查询字段类型相匹配（包括数量），如果不匹配就会报错。以下是测试代码：
1. @Test
2. public void testFindUserOutputVOAllPage(){
3. Pageable pageable = new PageRequest(0,5);
4. Page<UserOutputVO> page = userRepository.findUserOutputVOAllPage(pageable);
5. List<UserOutputVO> list = Content();
6. for(UserOutputVO vo : list)
7. logger.String());
8. }
输出结果：
对于连接查询，有join、left join 、right join，与sql的类似，但是唯⼀需要注意的地⽅就是建模的关系要能连接起来，因为只有这样才能使⽤“.”进⾏连接；就像你想的那样，它是类似对象导航的，与sql的表连接有些使⽤上的不同，但是最终的连接结果是相同的。
3.2 使⽤projection接⼝做映射与投影
依然使⽤的是上⾯查询VO的需求进⾏查询，换成projection的⽅式，在org.fage.vo中创建⼀个接⼝：
1. public interface UserProjection {
2. String getName();
3.
4. @Value("#{ailColumn}")//当别名与该getXXX名称不⼀致时，可以使⽤该注解调整
5. String getEmail();
6.
7. String getDepartmentName();
8.
9. Integer getRoleNum();
10. }
在UserRepository中创建查询语句：
1. //故意将email别名为emailColumn，以便讲解@Value的⽤法
2. @Query(value = "select u.name as name, u.email as emailColumn, d.name as departmentName, count(r.id) as roleNum from User u "
3. + "left join u.department d left les r group by u.id")
4. Page<UserProjection> findUserProjectionAllPage(Pageable pageable);
在TestClass中添加测试⽅法：
1. @Test
2. public void testFindUserProjectionAllPage(){
3. Page<UserProjection> page = userRepository.findUserProjectionAllPage(new PageRequest(0,5));
4. Collection<UserProjection> list = Content();
5. for(UserProjection up : list){
6. logger.Name());
7. logger.Email());
8. logger.DepartmentName());
9. logger.RoleNum()+"");
10. }
11. }
测试结果是成功的。在这⾥需要注意⼏点约束，Projection接⼝中必须以“getXXX”来命名⽅法，关于“XXX”则是要与查询语句中的别名相对应，注意观察上⾯的Projection接⼝与查询语句就发现了。不难发现，有⼀个别名为emailColumn，与Projection接⼝中的getEmail⽅法并不对应，这种时候可以使⽤
@Value{"${}"}注解来调整，注意其中的target不能省略，可以把target看成⽤别名查出来的临时对象，这样就好理解了。
两种⽅式都可以，对于到底哪种⽅式好，这取决于你的需求。
4.命名查询
如果以上查询实例都弄懂了，那么命名查询也是类似的，换汤不换药；这⾥简单的只举两个例⼦，需求变更时请⼤家⾃⾏尝试。
命名查询的核⼼注解是@NamedQueries 与 @NamedQuery；@NamedQueries中只有⼀个value参数，该参数是@NamedQuery的数组。@NamedQuery注解我们需要关注两个参数，query参数也就是需要写⼊查询语句的地⽅；name参数则是给该查询命名，命名⽅式⼀般约定为 “实体类名.实体对应的repository 的查询⽅法名”，如果看不懂没关系，请看下⾯的例⼦。
在Role实体中增加以下代码：
1. @Entity
2. @Table(name="role")
3. @NamedQueries({
4. @NamedQuery(name = "Role.findById", query = "from Role r where r.id=?1"),
5. @NamedQuery(name = "Role.findAllPage", query = "from Role r")
6. //...更多的@NamedQuery
7. })
8. public class Role implements Serializable{
9.
10. private static final long serialVersionUID = 1366815546093762449L;
11. @Id
12. @GeneratedValue(strategy=GenerationType.IDENTITY)
13. private Long id;
14. private String name;
15.
16. public Role(){
17. super();
18. }
19.
20. public Role(String name){
21. this.name = name;
22. }
23. //getter and setter
24.
25. }
对应的RoleRepository代码：
1. @Repository
2. public interface RoleRepository extends JpaRepository<Role, Long>{
3.
4. Role findById(Long id);
5.
6. Page<Role> findAllPage(Pageable pageable);
7. }
相应的测试代码：
1. @Test
2. public void testFindRoleById(){
3. roleRepository.findById(1l);
4. }
5.
6. @Test
7. public void testFindRoleAllPage(){
8. roleRepository.findAll(new PageRequest(0,5));
9. }
以上就是命名查询的常⽤⽅式。
5. JPQL⽅式总结
还是⽐较建议使⽤JPQL⽅式，因为SpringDataJPA各⽅⾯（⽐如分页排序）、动态查询等等都⽀持得⽐较好，Spring的SPEL表达式还可以扩展到SpringSecurity与SpringDataJPA⾼级的session⽤户查询⽅式，后续博客会有对SpringSecurity的介绍，等到那时候在⼀起讲解。
三、使⽤原⽣SQL查询
有些时候，JPQL使⽤不当会导致转化成的sql并不如理想的简洁与优化，所以在特定场合还是得⽤到原⽣SQL查询的，⽐如当你想优化sql时等等。
1 .⼀般查询
使⽤原⽣查询时⽤的也是@Query注解，此时nativeQuery参数应该设置为true。我们先来看⼀些简单的查询
1. @Query(value = "select * from user u where u.id=:id", nativeQuery = true)
2. User findByIdNative(@Param("id")Long id);
3.
4. @Query(value = "select * from user", nativeQuery = true)
5. List<User> findAllNative();
看看测试代码：
1. @Test
2. @Transactional
3. public void testFindByIdNative(){
4. User u = userRepository.findByIdNative(1l);
5. logger.String());
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论