java - JPA，使用 CriteriaBuilder 进行过滤查询

Question

我想在记录表(学生信息)上实现附加过滤器。这是问题的分解

• 可以使用三个参数进行过滤。
• 该方法返回计数、结果、pageSize 和 pageNumber。

表格随着过滤器参数的变化而变化。

我是 JPA 中的 criteriaBuilder 新手。已经设法用我在网上阅读的一些信息编写了一些代码。我希望有人能帮助我。

此网站上实现了这种搜索:www.hdpopcorns.co

这是我的代码；

public Page<Student> findStudentByParam(FindStudentDTO findStudentDTO, Pageable pageable) {


    String studentName = findStudentDTO.getStudentName();

    Long levelId = findStudentDTO.getLevel();

    Long sclassId = findStudentDTO.getSclass();

    CriteriaBuilder cb = emf.getCriteriaBuilder();
    CriteriaQuery<FindStudentDTO> studentQuery =  cb.createQuery(FindStudentDTO.class);
    Root<Student> studentRoot = studentQuery.from(Student.class);
    Root<SClass> classRoot = studentQuery.from(SClass.class);

    List<Predicate> filterPredicates = new ArrayList<>();

    if((studentName != null && !studentName.isEmpty()) && ((levelId == null) && (sclassId == null))){
        System.out.println("Ths is level 1");
        filterPredicates.add(

                      cb.or(
                            cb.like(cb.lower(studentRoot.get("firstName")),"%" + studentName + "%"),
                            cb.like(cb.lower(studentRoot.get("middleName")),"%" + studentName + "%"),
                            cb.like(cb.lower(studentRoot.get("lastName")),"%" + studentName + "%")
                      )
        );
     }else if(levelId != null && ((studentName == null && studentName.isEmpty()) && sclassId == null)) {
        filterPredicates.add(cb.equal(studentRoot.join("level").get("id"),findStudentDTO.getLevel()));
        log.info("I am here");
    }else if(sclassId != null && ((studentName == null && studentName.isEmpty()) && levelId == null)) {
        System.out.println("2");
        filterPredicates.add(cb.equal(studentRoot.join("sClass").get("id"),findStudentDTO.getSclass()));
    }else if((levelId != null && sclassId != null) && ((studentName == null && studentName.isEmpty()))){
        System.out.println("3");
        filterPredicates.add(cb.and(
           cb.equal(studentRoot.join("level").get("id"),levelId),
           cb.equal(studentRoot.join("sClass").get("id"),sclassId)
        ));
    }else if(((levelId != null) && (studentName != null)) && (sclassId == null)){
        System.out.println("This is level 4");
        filterPredicates.add(cb.and(
           cb.equal(studentRoot.join("level").get("id"),levelId),
                cb.or(
                        cb.like(cb.lower(studentRoot.get("firstName")),"%" + studentName + "%"),
                        cb.like(cb.lower(studentRoot.get("middleName")),"%" + studentName + "%"),
                        cb.like(cb.lower(studentRoot.get("lastName")),"%" + studentName + "%")
                )

        ));
    }else if(((sclassId != null) && (!studentName.isEmpty() && studentName != null)) && levelId == null){

        System.out.println("5");
        filterPredicates.add(cb.and(
                cb.equal(studentRoot.get("sClass").get("id"),sclassId),
                cb.or(
                        cb.like(cb.lower(studentRoot.get("firstName")),"%" + studentName + "%"),
                        cb.like(cb.lower(studentRoot.get("middleName")),"%" + studentName + "%"),
                        cb.like(cb.lower(studentRoot.get("lastName")),"%" + studentName + "%")
                )
        ));
    }else if((studentName != null && !studentName.isEmpty()) && (sclassId != null) && (levelId != null)){

        System.out.println("6");
        filterPredicates.add(cb.and(
                cb.or(
                        cb.like(cb.lower(studentRoot.get("firstName")),"%" + studentName + "%"),
                        cb.like(cb.lower(studentRoot.get("middleName")),"%" + studentName + "%"),
                        cb.like(cb.lower(studentRoot.get("lastName")),"%" + studentName + "%")
                ),
                cb.equal(studentRoot.join("sClass").get("id"),sclassId),
                cb.equal(studentRoot.join("level").get("id"),levelId)
              ));
    }

    List<Predicate> predicateList = filterPredicates.stream().distinct().collect(Collectors.toList());
    System.out.println("filtered records is: "+filterPredicates.size());

   studentQuery.select(cb.construct(FindStudentDTO.class,studentRoot.get("id"),
                                        studentRoot.get("firstName"),
                                        studentRoot.get("lastName"),
                                        studentRoot.get("gender"),
                                        studentRoot.join("level").get("name"),
                                        studentRoot.join("sClass").get("name"),
                                        studentRoot.join("level").get("id"),
                                        studentRoot.join("sClass").get("id")
                                                        ))
            .where(cb.and(predicateList.toArray(new Predicate[0])));

    System.out.println("This is the size of the predicate" + filterPredicates.size());

    TypedQuery q = emf.createQuery(studentQuery.distinct(true));
   q.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
    q.setMaxResults(pageable.getPageSize());

    List<Student> list = (List) q.getResultList().stream().distinct().collect(Collectors.toList());

    Collection result = (Collection) new LinkedHashSet(q.getResultList()).stream().distinct().collect(Collectors.toList());

  return new PageImpl<>((List<Student>) result,pageable,getAllCount(studentName,levelId,sclassId));

}

    private Long getAllCount(String studentName, Long levelId, Long 
 sClassId){
    CriteriaBuilder cb = emf.getCriteriaBuilder();
    CriteriaQuery query = cb.createQuery(Long.class);
    Root longRoot = query.from(Student.class);

    List<Predicate> filterPredicates = new ArrayList<>();


    if(studentName != null && !studentName.isEmpty()){
            filterPredicates.add(cb.or(
                cb.like(cb.lower(longRoot.get("firstName")),"%" + studentName + "%"),
                cb.like(cb.lower(longRoot.get("middleName")),"%" + studentName + "%"),
                cb.like(cb.lower(longRoot.get("lastName")),"%" + studentName + "%")

        ));
      }else if(levelId != null) {
        filterPredicates.add(cb.equal(longRoot.get("level").get("id"),levelId));
        }else if(sClassId != null) {

        filterPredicates.add(cb.equal(longRoot.get("sClass").get("id"),sClassId));
    }
    query.select(cb.count(longRoot)).where(cb.and
            (filterPredicates.toArray(new Predicate[0])));

    System.out.println("Count query" + emf.createQuery(query).getResultList());

    return (Long) emf.createQuery(query).getSingleResult();
}

}

这些是模型:

@Entity
@Table(name = "Class")
public class SClass extends AbstractSchoolEntity{

@NotBlank
private String name;

@NotEmpty(message = "description must not be empty")
private String description;


@JoinColumn(name = "level_id")
@ManyToOne(cascade = CascadeType.REFRESH, fetch = FetchType.EAGER)
private Level level;

@OneToOne
private Period period;

@JsonIgnore
@OneToMany(mappedBy = "sClass",
        cascade = CascadeType.ALL)
private List<Student> students;

@JsonIgnore
@OneToMany(mappedBy = "sClass",
        cascade = CascadeType.ALL)
private List<ClassTeacher> teachers;

public SClass() {
}

public SClass(String name, String description, Level level) {
    this.name = name;
    this.description = description;
    this.level = level;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public List<Student> getStudents() {
    return students;
}

public void setStudents(List<Student> students) {
    this.students = students;
}


public String getDescription() {
    return description;
}

public void setDescription(String description) {
    this.description = description;
}

public Level getLevel() {
    return level;
}

public void setLevel(Level level) {
    this.level = level;
}

public Period getPeriod() {
    return period;
}

public void setPeriod(Period period) {
    this.period = period;
}

public List<ClassTeacher> getTeachers() {
    return teachers;
}


public void setTeachers(List<ClassTeacher> teachers) {
    this.teachers = teachers;
}

public void addTeacher(ClassTeacher classTeacher) {
    if(teachers == null)
        teachers = new ArrayList<ClassTeacher>();
    teachers.add(classTeacher);
    classTeacher.setsClass(this);
}


@Override
public List<String> getDefaultSearchFields() {
    return Arrays.asList("period.name");
}

@Override
public String toString() {
    return "SClass{" +
            "name='" + name + '\'' +
            ", description='" + description + '\'' +
            ", level=" + level +
            ", period=" + period +
            ", students=" + students +
            ", teachers=" + teachers +
            '}';
}

}

@Entity
public class Level extends AbstractSchoolEntity{

@NotNull(message = "name must not be empty")
@Size(min=2, max=30)
private String name;


private int ordinal;

@JsonIgnore
@OneToMany(mappedBy = "level",
        cascade = CascadeType.ALL)
private List<Student> studentList;


public String getName() {
    return name;
}
public void setName(String name) {
    this.name = name;
}
public int getOrdinal() {
    return ordinal;
}
public void setOrdinal(int ordinal) {
    this.ordinal = ordinal;
}

public List<Student> getStudentList() {
    return studentList;
}

public void setStudentList(List<Student> studentList) {
    this.studentList = studentList;
}

@Override
public String toString() {
    return "Level{" +
            "name='" + name + '\'' +
            ", ordinal=" + ordinal +
            ", studentList=" + studentList +
            '}';
}
}

@SuppressWarnings("serial")
@Entity
public class Student extends AbstractSchoolEntity {

private String admissionNumber;


@NotNull
@Size(min=2, max=40)
private String firstName;

private String middleName;

@NotNull
@Size(min=2, max=40)
private String lastName;

private  String gender;

private  String religion;

@OneToOne
private Image passport;

private String email;


private String phoneNumber;



@Temporal(TemporalType.DATE)
@DateTimeFormat(pattern = "yyyy-mm-dd")

private Date dateOfBirth;

@OneToOne
private User user;

@JsonManagedReference
@ManyToMany
private List<Parent> parents;

@JsonManagedReference
@ManyToOne(fetch = FetchType.EAGER)
private Level level;

@JsonManagedReference
@ManyToMany
private List<Guardian> guardians;


@OneToOne(cascade = CascadeType.ALL)
private Address address;

@JsonManagedReference
@ManyToOne(fetch = FetchType.EAGER)
private SClass sClass;

public Student() {
}

public Level getLevel() {
    return level;
}

public void setLevel(Level level) {
    this.level = level;
}

public String getAdmissionNumber() {
    return admissionNumber;
}

public void setAdmissionNumber(String admissionNumber) {
    this.admissionNumber = admissionNumber;
}

public String getFirstName() {
    return firstName;
}

public void setFirstName(String firstName) {
    this.firstName = firstName;
}

public String getMiddleName() {
    return middleName;
}

public String getPhoneNumber() {
    return phoneNumber;
}

public void setPhoneNumber(String phoneNumber) {
    this.phoneNumber = phoneNumber;
}

public void setMiddleName(String middleName) {
    this.middleName = middleName;
}

public String getLastName() {
    return lastName;
}

public void setLastName(String lastName) {
    this.lastName = lastName;
}

public String getGender() {
    return gender;
}

public void setGender(String gender) {
    this.gender = gender;
}

public String getReligion() {
    return religion;
}

public void setReligion(String religion) {
    this.religion = religion;
}

public String getEmail() {
    return email;
}

public void setEmail(String email) {
    this.email = email;
}



public Date getDateOfBirth() {
    return dateOfBirth;
}

public void setDateOfBirth(Date dateOfBirth) {
    this.dateOfBirth = dateOfBirth;
}

public void addParent(Parent p) {
    if(parents == null)
        parents = new ArrayList<Parent>();

    parents.add(p);

    p.addStudent(this);

}

public User getUser() {
    return user;
}

public void setUser(User user) {
    this.user = user;
}

public List<Guardian> getGuardians() {
    return guardians;
}

public void setGuardians(List<Guardian> guardians) {
    this.guardians = guardians;
}

public List<Parent> getParents() {
    return parents;
}

public void setParents(List<Parent> parents) {
    this.parents = parents;
}

public Address getAddress() {
    return address;
}

public void setAddress(Address address) {
    this.address = address;
}



public Image getPassport() {
    return passport;
}

public void setPassport(Image passport) {
    this.passport = passport;
}

public SClass getsClass() {
    return sClass;
}

public void setsClass(SClass sClass) {
    this.sClass = sClass;
}

@Override
public List<String> getDefaultSearchFields() {
    return Arrays.asList("admissionNumber","firstName","lastName");
}

@Override
public String toString() {
    return "Student{" +
            "admissionNumber='" + admissionNumber + '\'' +
            ", firstName='" + firstName + '\'' +
            ", middleName='" + middleName + '\'' +
            ", lastName='" + lastName + '\'' +
            ", gender='" + gender + '\'' +
            '}';
   }

}

提前致谢

Answer 1

确保 Student , Level和SClass实体由右侧 @OneToMany 连接/@ManyToMany注释。

既然你正在使用JPA，我建议你看看QueryDSL生成动态查询谓词。您需要首先为您的实体生成谓词类。一旦生成，这些类就提供了一种在构建器模式中链接谓词的好方法。

您可以找到非常有用的快速入门文章here和 here .

呵呵!