实际开发中,查询这一操作是必不可少的。根据多个条件动态查询的需求是普通的不能再普通的需求了。所以这篇介绍一种根据条件动态查询的方法。顺便介绍下分页。
在上一篇中,StudentRepository接口继承了JpaRepository。这种适用于简单的增删改查。
现在让StudentRepository再继承一个JpaSpecificationExecutor,这样子就方便我们多种条件的查询了。
先看下JpaSpecificationExecutor接口的方法
public interface JpaSpecificationExecutor<T> { T findOne(Specification<T> var1); List<T> findAll(Specification<T> var1); Page<T> findAll(Specification<T> var1, Pageable var2); List<T> findAll(Specification<T> var1, Sort var2); long count(Specification<T> var1); }
所有的条件都拼装成了Specification条件类。
现在StudentRepository的接口是这样的
public interface StudentRepository extends JpaRepository<Student, Long>, JpaSpecificationExecutor<Student> { List<Student> findByNameLike(String name); List<Student> findByAgeGreaterThanAndNameEquals(Integer age, String name); }
较原来多继承了一个JpaSpecificationExecutor,下面我们模仿有name值时就根据name模糊查询,有age值时就查询等于这个age的值,此处增加两个类StudentService和StudentServiceImpl
package com.vincent.service; import com.vincent.model.Student; import java.util.List; /** * @author * @date 2018/8/21 下午4:27 */ public interface StudentService { List<Student> getStudents(String name, Integer age); }
package com.vincent.service.impl;
import com.vincent.demo.dao.StudentRepository;
import com.vincent.demo.entity.Student;
import com.vincent.demo.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.util.ArrayList;
import java.util.List;
/**
* @author rw
* @date 2019/1/14 下午10:10
*/
@Service
public class StudentServiceImpl implements StudentService {
@Autowired
StudentRepository studentRepository;
@Override
public List<Student> getStudents(String name, Integer age) {
return studentRepository.findAll((Root<Student> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) -> {
List<Predicate> list = new ArrayList<>();
if (name != null) {
list.add(criteriaBuilder.equal(root.get("name"), name));
}
if (age != null) {
list.add(criteriaBuilder.equal(root.get("age"), age));
}
Predicate[] p = new Predicate[list.size()];
return criteriaBuilder.and(list.toArray(p));
});
}
}
测试
@Test public void testSelect2() { System.out.println(studentService.getStudents(null, 12)); System.out.println(studentService.getStudents("a", 12)); }
分页
接口
Page<Student> getStudents(String name, Integer age, Pageable pageable);
实现
@Override public Page<Student> getStudents(String name, Integer age, Pageable pageable) { return studentRepository.findAll((Root<Student> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) -> { Predicate predicate = null; if (name != null) { predicate = criteriaBuilder.equal(root.get("name"), name); } if (age != null) { predicate = criteriaBuilder.equal(root.get("age"), age); } return predicate; }, pageable); }
测试
@Test public void testSelect3() { Page<Student> students = studentService.getStudents(null, null, new PageRequest(0, 2)); System.out.println(students.getContent()); System.out.println(students.getTotalPages()); }
结果
2018-09-05 13:50:15.308 INFO 3953 --- [ main] o.h.h.i.QueryTranslatorFactoryInitiator : HHH000397: Using ASTQueryTranslatorFactory
[Student{id=111, name='cba', age=121, schoolId=1}, Student{id=1111, name='vincent', age=22, schoolId=1}]
7
这里有个小注意点,new PageRequest(0, 2)这边的分页pageNum是从0开始的,同时pageNum不可以为负数!