转载自 孙义朗： https://www.cnblogs.com/arrrrrya/p/7865090.html

实体类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
package com.syl.demo.daomain;
import lombok.Data;
import javax.persistence.*;

@Entity
@Data
public class User {
    @Id
    private String userId;
    private String userName;
    private String password;
    private String gender;//性别
    private String age;
    @ManyToOne
    private School school;
    @ManyToOne
    private Grade grade;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
import lombok.Data;
import javax.jws.soap.SOAPBinding;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;

@Data
@Entity
public class School {
    @Id
    private String schoolId;
    private String schoolName;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
package com.syl.demo.daomain;
import lombok.Data;
import javax.persistence.*;

@Entity
@Data
public class Grade {
    @Id
    private String gradeId;
    private String gradeName;
    @ManyToOne
    private School school;
}

封装查询条件类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
package com.syl.demo.daomain;
import lombok.Data;
import javax.persistence.Id;

@Data
public class Params {
    private String userId;
    private String userName;
    private String gender;
    private String age;

    private String gradeName;

    private String schoolName;
}

Dao层

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
package com.syl.demo.dao;

import com.syl.demo.daomain.Grade;
import com.syl.demo.daomain.School;
import com.syl.demo.daomain.User;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;

import java.util.List;

public interface UserRepository extends JpaRepository<User,String> {
    List<User> findAll();

    List<User> findAll(Specification<User> spc, Pageable pageable);
}

Service及实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
package com.syl.demo.service;
import com.syl.demo.daomain.Grade;
import com.syl.demo.daomain.Params;
import com.syl.demo.daomain.School;
import com.syl.demo.daomain.User;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import java.util.List;

public interface UserService {
    List<User> findAll();

    List<User> findAll(int pageNum, int pageSize,User user);

    List<User> findAll(int pageNum, int pageSize, Params params);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
package com.syl.demo.service.impl;
import com.syl.demo.dao.UserRepository;
import com.syl.demo.daomain.Grade;
import com.syl.demo.daomain.Params;
import com.syl.demo.daomain.School;
import com.syl.demo.daomain.User;
import com.syl.demo.service.UserService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import javax.persistence.criteria.*;
import java.util.ArrayList;
import java.util.List;

@Service
public class UserServiceImpl implements UserService {
    @Autowired
    private UserRepository userRepository;

    @Override
    public List<User> findAll() {
        return userRepository.findAll();
    }

    //查询User，单表，多条件
    @Override
    public List<User> findAll(int pageNum, int pageSize, User user) {
        Pageable pageable = new PageRequest(pageNum, pageSize);
        List<User> uList = userRepository.findAll(new Specification<User>() {
            @Override
            public Predicate toPredicate(Root<User> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder cb) {
                List<Predicate> predicates = new ArrayList<>();
                if (user.getUserId() != null && !user.getUserId().equals("")) {
                    predicates.add(cb.like(root.get("userId").as(String.class), "%" + user.getUserId() + "%"));
                }
                if (user.getUserName() != null && !user.getUserName().equals("")) {
                    predicates.add(cb.like(root.get("userName").as(String.class), "%" + user.getUserName() + "%"));
                }
                if (user.getGender() != null && !user.getGender().equals("")) {
                    predicates.add(cb.like(root.get("gender").as(String.class), "%" + user.getGender() + "%"));
                }
                if (user.getAge() != null && !user.getAge().equals("")) {
                    predicates.add(cb.like(root.get("age").as(String.class), "%" + user.getAge() + "%"));
                }
                Predicate[] pre = new Predicate[predicates.size()];
                criteriaQuery.where(predicates.toArray(pre));
                return cb.and(predicates.toArray(pre));
            }
        }, pageable);

        return uList;
    }

    //查询User，多表，多条件
    @Override
    public List<User> findAll(int pageNum, int pageSize, Params params) {
        Pageable pageable = new PageRequest(pageNum, pageSize);
        List<User> uList = userRepository.findAll(new Specification<User>() {
            @Override
            public Predicate toPredicate(Root<User> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder cb) {
                List<Predicate> list = new ArrayList<>();
                //根据userId 查询user
                if (StringUtils.isNotBlank(params.getUserId())) {
                    list.add(cb.equal(root.get("userId").as(String.class), params.getUserId()));
                }
                //根据userName 模糊查询user
                if (StringUtils.isNotBlank(params.getUserName())) {
                    list.add(cb.like(root.get("userName").as(String.class), "%" + params.getUserName() + "%"));
                }
                //根据gender 查询user
                if (StringUtils.isNotBlank(params.getGender())) {
                    list.add(cb.equal(root.get("gender").as(String.class), params.getGender()));
                }
                //根据age>? 查询user
                if (StringUtils.isNotBlank(params.getAge())) {
                    list.add(cb.gt(root.get("age").as(Integer.class), Integer.valueOf(params.getAge())));
                }
                //根据gradeName 查询user
                if (StringUtils.isNotBlank(params.getGradeName())) {
                    Join<Grade, User> join = root.join("grade", JoinType.LEFT);
                    list.add(cb.equal(join.get("gradeName"), params.getGradeName()));
                }
                //根据schoolName 查询user
                if (StringUtils.isNotBlank(params.getSchoolName())) {
                    Join<School, User> join = root.join("grade", JoinType.LEFT);
                    list.add(cb.equal(join.get("school").get("schoolName"), params.getSchoolName()));
                }
                Predicate[] pre = new Predicate[list.size()];
                criteriaQuery.where(list.toArray(pre));
                return cb.and(list.toArray(pre));
            }
        }, pageable);

        return uList;
    }
}

控制层

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
package com.syl.demo.controller;
import com.syl.demo.daomain.Grade;
import com.syl.demo.daomain.Params;
import com.syl.demo.daomain.School;
import com.syl.demo.daomain.User;
import com.syl.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;

/**
 * Created by 孙义朗 on 2017/11/16 0016.
 */
@RestController
public class UserController {
    @Autowired
    UserService userService;

    @GetMapping(value = "/getUser")
    public List<User> getUser() {
        List<User> uList = userService.findAll();
        return uList;
    }

    //查询User，单表，多条件
    @PostMapping(value = "/getUser/{pageNum}/{pageSize}")
    public List<User> getUser(@PathVariable("pageNum") Integer pageNum,
                               @PathVariable("pageSize") Integer pageSize,
                               @RequestBody User user) {
        List<User> uList = userService.findAll(pageNum, pageSize, user);
        return uList;
    }

    //查询User，多表，多条件
    @PostMapping(value = "/getUser2/{pageNum}/{pageSize}")
    public List<User> getUser2(@PathVariable("pageNum") Integer pageNum,
                               @PathVariable("pageSize") Integer pageSize,
                               @RequestBody Params params) {
        List<User> uList = userService.findAll(pageNum, pageSize, params);
        return uList;
    }
}