springboot jpa 多条件查询（多表）

前几天写的，贴上来。

实体类。

 1 package com.syl.demo.daomain;
 2 
 3 import lombok.Data;
 4 
 5 import javax.persistence.*;
 6 
 7 /**
 8  * Created by 孙义朗 on 2017/10/31.
 9  */
10 @Entity
11 @Data
12 public class User {
13     @Id
14     private String userId;
15     private String userName;
16     private String password;
17     private String gender;//性别
18     private String age;
19     @ManyToOne
20     private School school;
21     @ManyToOne
22     private Grade grade;
23 }

User

 1 package com.syl.demo.daomain;
 2 
 3 import lombok.Data;
 4 
 5 import javax.jws.soap.SOAPBinding;
 6 import javax.persistence.Entity;
 7 import javax.persistence.Id;
 8 import javax.persistence.ManyToOne;
 9 import javax.persistence.OneToMany;
10 
11 /**
12  * Created by 孙义朗 on 2017/10/31.
13  */
14 @Data
15 @Entity
16 public class School {
17     @Id
18     private String schoolId;
19     private String schoolName;
20 
21 
22 }

School

 1 package com.syl.demo.daomain;
 2 
 3 import lombok.Data;
 4 
 5 import javax.persistence.*;
 6 
 7 /**
 8  * Created by 孙义朗 on 2017/11/16.
 9  */
10 @Entity
11 @Data
12 public class Grade {
13     @Id
14     private String gradeId;
15     private String gradeName;
16     @ManyToOne
17     private School school;
18 
19 
20 }

Grade

还有一个封装查询条件的类

 1 package com.syl.demo.daomain;
 2 
 3 import lombok.Data;
 4 
 5 import javax.persistence.Id;
 6 
 7 /**
 8  * Created by 孙义朗 on 2017/11/16 0016.
 9  */
10 @Data
11 public class Params {
12     private String userId;
13     private String userName;
14     private String gender;
15     private String age;
16 
17     private String gradeName;
18 
19     private String schoolName;
20 
21 }

Params

Dao层

 1 package com.syl.demo.dao;
 2 
 3 import com.syl.demo.daomain.Grade;
 4 import com.syl.demo.daomain.School;
 5 import com.syl.demo.daomain.User;
 6 import org.springframework.data.domain.Pageable;
 7 import org.springframework.data.jpa.domain.Specification;
 8 import org.springframework.data.jpa.repository.JpaRepository;
 9 
10 import java.util.List;
11 
12 /**
13  * Created by 孙义朗 on 2017/11/16 0016.
14  */
15 public interface UserRepository extends JpaRepository<User,String> {
16     List<User> findAll();
17 
18     List<User> findAll(Specification<User> spc, Pageable pageable);
19 
20 
21 }

UserRepository

Service及实现

 1 package com.syl.demo.service;
 2 
 3 import com.syl.demo.daomain.Grade;
 4 import com.syl.demo.daomain.Params;
 5 import com.syl.demo.daomain.School;
 6 import com.syl.demo.daomain.User;
 7 import org.springframework.data.domain.Pageable;
 8 import org.springframework.data.jpa.domain.Specification;
 9 
10 import java.util.List;
11 
12 /**
13  * Created by 孙义朗 on 2017/11/16 0016.
14  */
15 public interface UserService {
16     List<User> findAll();
17 
18     List<User> findAll(int pageNum, int pageSize,User user);
19 
20     List<User> findAll(int pageNum, int pageSize, Params params);
21 
22 }

UserService

  1 package com.syl.demo.service.impl;
  2 
  3 import com.syl.demo.dao.UserRepository;
  4 import com.syl.demo.daomain.Grade;
  5 import com.syl.demo.daomain.Params;
  6 import com.syl.demo.daomain.School;
  7 import com.syl.demo.daomain.User;
  8 import com.syl.demo.service.UserService;
  9 import org.apache.commons.lang3.StringUtils;
 10 import org.springframework.beans.factory.annotation.Autowired;
 11 import org.springframework.data.domain.PageRequest;
 12 import org.springframework.data.domain.Pageable;
 13 import org.springframework.data.jpa.domain.Specification;
 14 import org.springframework.stereotype.Service;
 15 
 16 import javax.persistence.criteria.*;
 17 import java.util.ArrayList;
 18 import java.util.List;
 19 
 20 /**
 21  * Created by 孙义朗 on 2017/11/16 0016.
 22  */
 23 @Service
 24 public class UserServiceImpl implements UserService {
 25     @Autowired
 26     private UserRepository userRepository;
 27 
 28     @Override
 29     public List<User> findAll() {
 30         return userRepository.findAll();
 31     }
 32 
 33     //查询User，单表，多条件
 34     @Override
 35     public List<User> findAll(int pageNum, int pageSize, User user) {
 36         Pageable pageable = new PageRequest(pageNum, pageSize);
 37         List<User> uList = userRepository.findAll(new Specification<User>() {
 38             @Override
 39             public Predicate toPredicate(Root<User> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder cb) {
 40                 List<Predicate> predicates = new ArrayList<>();
 41                 if (user.getUserId() != null && !user.getUserId().equals("")) {
 42                     predicates.add(cb.like(root.get("userId").as(String.class), "%" + user.getUserId() + "%"));
 43                 }
 44                 if (user.getUserName() != null && !user.getUserName().equals("")) {
 45                     predicates.add(cb.like(root.get("userName").as(String.class), "%" + user.getUserName() + "%"));
 46                 }
 47                 if (user.getGender() != null && !user.getGender().equals("")) {
 48                     predicates.add(cb.like(root.get("gender").as(String.class), "%" + user.getGender() + "%"));
 49                 }
 50                 if (user.getAge() != null && !user.getAge().equals("")) {
 51                     predicates.add(cb.like(root.get("age").as(String.class), "%" + user.getAge() + "%"));
 52                 }
 53                 Predicate[] pre = new Predicate[predicates.size()];
 54                 criteriaQuery.where(predicates.toArray(pre));
 55                 return cb.and(predicates.toArray(pre));
 56             }
 57         }, pageable);
 58 
 59         return uList;
 60     }
 61 
 62     //查询User，多表，多条件
 63     @Override
 64     public List<User> findAll(int pageNum, int pageSize, Params params) {
 65         Pageable pageable = new PageRequest(pageNum, pageSize);
 66         List<User> uList = userRepository.findAll(new Specification<User>() {
 67             @Override
 68             public Predicate toPredicate(Root<User> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder cb) {
 69                 List<Predicate> list = new ArrayList<>();
 70                 //根据userId 查询user
 71                 if (StringUtils.isNotBlank(params.getUserId())) {
 72                     list.add(cb.equal(root.get("userId").as(String.class), params.getUserId()));
 73                 }
 74                 //根据userName 模糊查询user
 75                 if (StringUtils.isNotBlank(params.getUserName())) {
 76                     list.add(cb.like(root.get("userName").as(String.class), "%" + params.getUserName() + "%"));
 77                 }
 78                 //根据gender 查询user
 79                 if (StringUtils.isNotBlank(params.getGender())) {
 80                     list.add(cb.equal(root.get("gender").as(String.class), params.getGender()));
 81                 }
 82                 //根据age>? 查询user
 83                 if (StringUtils.isNotBlank(params.getAge())) {
 84                     list.add(cb.gt(root.get("age").as(Integer.class), Integer.valueOf(params.getAge())));
 85                 }
 86                 //根据gradeName 查询user
 87                 if (StringUtils.isNotBlank(params.getGradeName())) {
 88                     Join<Grade, User> join = root.join("grade", JoinType.LEFT);
 89                     list.add(cb.equal(join.get("gradeName"), params.getGradeName()));
 90                 }
 91                 //根据schoolName 查询user
 92                 if (StringUtils.isNotBlank(params.getSchoolName())) {
 93                     Join<School, User> join = root.join("grade", JoinType.LEFT);
 94                     list.add(cb.equal(join.get("school").get("schoolName"), params.getSchoolName()));
 95                 }
 96                 Predicate[] pre = new Predicate[list.size()];
 97                 criteriaQuery.where(list.toArray(pre));
 98                 return cb.and(list.toArray(pre));
 99             }
100         }, pageable);
101 
102         return uList;
103     }
104 
105 }

UserServiceImpl

控制层

 1 package com.syl.demo.controller;
 2 
 3 import com.syl.demo.daomain.Grade;
 4 import com.syl.demo.daomain.Params;
 5 import com.syl.demo.daomain.School;
 6 import com.syl.demo.daomain.User;
 7 import com.syl.demo.service.UserService;
 8 import org.springframework.beans.factory.annotation.Autowired;
 9 import org.springframework.web.bind.annotation.*;
10 
11 import java.util.List;
12 
13 /**
14  * Created by 孙义朗 on 2017/11/16 0016.
15  */
16 @RestController
17 public class UserController {
18     @Autowired
19     UserService userService;
20 
21     @GetMapping(value = "/getUser")
22     public List<User> getUser() {
23         List<User> uList = userService.findAll();
24         return uList;
25     }
26 
27     //查询User，单表，多条件
28     @PostMapping(value = "/getUser/{pageNum}/{pageSize}")
29     public List<User> getUser(@PathVariable("pageNum") Integer pageNum,
30                                @PathVariable("pageSize") Integer pageSize,
31                                @RequestBody User user) {
32         List<User> uList = userService.findAll(pageNum, pageSize, user);
33         return uList;
34     }
35 
36     //查询User，多表，多条件
37     @PostMapping(value = "/getUser2/{pageNum}/{pageSize}")
38     public List<User> getUser2(@PathVariable("pageNum") Integer pageNum,
39                                @PathVariable("pageSize") Integer pageSize,
40                                @RequestBody Params params) {
41         List<User> uList = userService.findAll(pageNum, pageSize, params);
42         return uList;
43     }
44 
45 }

UserController

完成。

postman测试效果如下

（单表查询http://www.cnblogs.com/arrrrrya/p/7831248.html）

posted on 2017-11-20 10:14 秀川阅读(28153) 评论(4) 编辑收藏举报

刷新页面返回顶部

秀川

springboot jpa 多条件查询（多表）

导航

公告