jpa 跨表_springboot jpa 多条件查询（多表）

1 packagecom.syl.demo.service.impl;2

3 importcom.syl.demo.dao.UserRepository;4 importcom.syl.demo.daomain.Grade;5 importcom.syl.demo.daomain.Params;6 importcom.syl.demo.daomain.School;7 importcom.syl.demo.daomain.User;8 importcom.syl.demo.service.UserService;9 importorg.apache.commons.lang3.StringUtils;10 importorg.springframework.beans.factory.annotation.Autowired;11 importorg.springframework.data.domain.PageRequest;12 importorg.springframework.data.domain.Pageable;13 importorg.springframework.data.jpa.domain.Specification;14 importorg.springframework.stereotype.Service;15

16 import javax.persistence.criteria.*;17 importjava.util.ArrayList;18 importjava.util.List;19

20 /**

21 * Created by 孙义朗 on 2017/11/16 0016.22 */

23 @Service24 public class UserServiceImpl implementsUserService {25 @Autowired26 privateUserRepository userRepository;27

28 @Override29 public ListfindAll() {30 returnuserRepository.findAll();31 }32

33 //查询User，单表，多条件

34 @Override35 public List findAll(int pageNum, intpageSize, User user) {36 Pageable pageable = newPageRequest(pageNum, pageSize);37 List uList = userRepository.findAll(new Specification() {38 @Override39 public Predicate toPredicate(Root root, CriteriaQuery>criteriaQuery, CriteriaBuilder cb) {40 List 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 = newPredicate[predicates.size()];54 criteriaQuery.where(predicates.toArray(pre));55 returncb.and(predicates.toArray(pre));56 }57 }, pageable);58

59 returnuList;60 }61

62 //查询User，多表，多条件

63 @Override64 public List findAll(int pageNum, intpageSize, Params params) {65 Pageable pageable = newPageRequest(pageNum, pageSize);66 List uList = userRepository.findAll(new Specification() {67 @Override68 public Predicate toPredicate(Root root, CriteriaQuery>criteriaQuery, CriteriaBuilder cb) {69 List 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 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 join = root.join("grade", JoinType.LEFT);94 list.add(cb.equal(join.get("school").get("schoolName"), params.getSchoolName()));95 }96 Predicate[] pre = newPredicate[list.size()];97 criteriaQuery.where(list.toArray(pre));98 returncb.and(list.toArray(pre));99 }100 }, pageable);101

102 returnuList;103 }104

105 }