关于JPA中动态查询的做法

原文出处:

【一目了然】Spring Data JPA使用Specification动态构建多表查询、复杂查询及排序示例

最近的一个需求是带条件查询,可是有的条件不是必填的选项,所以后端接收的时候有的参数可能为null,该怎么解决呢,网上查询了相关做法,我是这么做的:

实体类就用Student为例子,大家可以自行替换成自己的

• 首先给出实体类Student的定义

@Entity
@Table(name = "tblStudent")
@Data
public class TblStudent {


    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @Column(name = "name")
    private String name;

    @Column(name = "age")
    private Integer age;

    @Column(name = "sex")
    private String  sex;

    
}

• 这里是后端Controller接口定义

@RestController
@Slf4j
@RequestMapping(value = "/student")
public class StudentController {
@GetMapping("/list")
public JsonResult<List<Student>> getStudentList(@RequestParam() Integer _page,
                                                    @RequestParam() Integer _limit,
                                                    @RequestParam(required = false) String name,
                                                    @RequestParam(required = false) Integer age,
                                                    @RequestParam(required = false) String sex){
}
}

因为是条件查询,他们可能会从name/age/sex里面选择一两个或者全部查询所以 我用上了@RequestParam(required=false)
代表该参数(字段?)不是必须的,如果前台提交的请求没有这个参数,默认是null.

• 在注入需要的service后就是调用对应的Service里面的方法

@RestController
@Slf4j
@RequestMapping(value = "/student")
public class StudentController {
    
    @Autowired
    private StudentService studentService;

    @GetMapping("/list")
    public JsonResult<List<Student>> getLogList(@RequestParam() Integer _page,
                                                @RequestParam() Integer _limit,
                                                @RequestParam(required = false) String name,
                                                @RequestParam(required = false) Integer age,
                                                @RequestParam(required = false) String sex){


    //这里因为要写分页,通过前台传来参数进行操作,如果不用分页可以不写
     if (0 == _page.intValue() || 1 == _page.intValue()) {
            _page = 0;
     } else {
            _page = page - 1;
     }

    //这里pageable因为我分页用到了,当然你们不用的话可以不要
     Pageable pageable = PageRequest.of(_page, _limit);
     Page<TblStudent> page = studentService.getStudentService(name,age,sex,pageable);
                  

            //这样你就取到了你需要的数据了,后面需要做些什么操作就看具体业务需要了

}
}

• 下面是StudentService的内容

@Service
@Slf4j
public class StudentService {

    @Autowired
    private StudentRepository studentRepository;//注入StudentRepository

    public Page<TblStudent> getStudentList(String name, Integer  age, String sex,Pageable pageable) {
            Specification<TblStudent> specification = (Specification<TblStudent>) (root, criteriaQuery, cb) -> {
            //存放查询条件
            List<Predicate> predicatesList = new ArrayList<>();
            //like模糊查询
            if (!StringUtils.isEmpty(name)) {
                Predicate namePredicate = cb.like(root.get("name"), '%' + name + '%');
                predicatesList.add(namePredicate);
            }
            //equals精确查询 Integer类型
            if (!StringUtils.isEmpty(age)) {
                Predicate moduleNmPredicate = cb.gt(root.get("age"), age);
                predicatesList.add(agePredicate);
            }
            //精确查询String类型
            if (!StringUtils.isEmpty(sex)) {
                Predicate sexPredicate = cb.equal(root.get("sex"), sex);
                predicatesList.add(sexPredicate);
            }
            //between相当于SQL的between  ... and ...这里是where age between 10 and 20
            if (age != null) {
                Predicate crtTimePredicate = cb.between(root.get("age"), 10, 20);
                predicatesList.add(crtTimePredicate);
            }
            //排序
            criteriaQuery.orderBy(cb.asc(root.get("age")));
            //最终将查询条件拼好然后return
            Predicate[] predicates = new Predicate[predicatesList.size()];
            return cb.and(predicatesList.toArray(predicates));

        }
        Page<TblStudent> page = studentRepository.findAll(specification, pageable);
            return page;
        }


}

• 最后在DAO层的Repository中继承JpaSpecificationExecutor和JpaRepository

public interface StudentRepository extends JpaRepository<TblStudent, Integer>, JpaSpecificationExecutor<TblStudent> {}