Hibernate：设置多变量搜索和其他表中数据的参数

Question

我正在开发一个Spring-MVC应用程序，我想在其中使用一些提到的标准在数据库中进行搜索。不幸的是，他们大约是其中的10-12个。所以我之前问过一个问题，如何将它们分解成小块，检查其中哪些不是空的，并将它们用作参数。通过使用下面提到的StringBuilder，我取得了部分成功。

我遇到的问题是如何为非空值设置Query.SetParameter（“variableName”,variableName）。首先是示例：

 @Override
public List<Student> addHostSearchHistory(HostSearchHistory hostSearchHistory, Long hostId) {
    session = this.sessionFactory.getCurrentSession();
    Host host = (Host) session.get(Host.class,hostId);
    host.getHostSearchHistorySet().add(hostSearchHistory);
    hostSearchHistory.setHsHistory(host);
    session.save(hostSearchHistory);
    session.flush();

    StringBuilder sb = new StringBuilder();
    sb.append("from Student as s where ");

    if(!(hostSearchHistory.getCountry().equals(""))){
        sb.append("s.country=:").append(hostSearchHistory.getCountry());
    }
    if(!(hostSearchHistory.getCity().equals(""))){
        sb.append("OR s.city=:").append(hostSearchHistory.getCity());
    }
    if(!(hostSearchHistory.getDrivingLicense().equals(""))){
        sb.append("OR s.studentDetails.drivingLicense=").append(hostSearchHistory.getDrivingLicense());
    }
    if(!(hostSearchHistory.getGender().equals(""))){
        sb.append("OR s.gender=").append(hostSearchHistory.getGender());
    }
    if(!(hostSearchHistory.getMotherTongue().equals(""))){
        sb.append("OR s.studentDetails.motherTongue=:").append(hostSearchHistory.getMotherTongue());
    }
    if(!(hostSearchHistory.getSmoker().equals(""))){
        sb.append("OR s.studentDetails.smoker=").append(hostSearchHistory.getSmoker());
    }
    if(!(hostSearchHistory.getPreviousAuPair().equals(""))){
        sb.append("OR s.studentDetails.previouslyAuPair=").append(hostSearchHistory.getPreviousAuPair());
    }
    if(!(hostSearchHistory.getWillingToWork().equals(""))){
        sb.append("OR s.studentDetails.willingToWork=").append(hostSearchHistory.getWillingToWork());
    }
    if(!(hostSearchHistory.getWorkForSingleParent().equals(""))){
        sb.append("OR s.studentDetails.workForSingleParent=").append(hostSearchHistory.getWorkForSingleParent());
    }
    if(!(hostSearchHistory.getWorkingForDisabledChild().equals(""))){
        sb.append("OR s.studentDetails.workingForDisabledChild").append(hostSearchHistory.getWorkingForDisabledChild());
    }
    sb.append(" order by s.registrationDate desc");

    Query query = session.createQuery(sb.toString());
    if(!(hostSearchHistory.getCountry().equals(""))){
        query.setParameter("country",hostSearchHistory.getCountry());
    }
    if(!(hostSearchHistory.getCity().equals(""))){
        query.setParameter("city",hostSearchHistory.getCity());
    }
    if(!(hostSearchHistory.getDrivingLicense().equals(""))){
        query.setParameter("drivingLicense",hostSearchHistory.getDrivingLicense());
    }
    if(!(hostSearchHistory.getGender().equals(""))){
        query.setParameter("gender",hostSearchHistory.getGender());
    }
    if(!(hostSearchHistory.getMotherTongue().equals(""))){
        query.setParameter("motherTongue",hostSearchHistory.getMotherTongue());
    }
    if(!(hostSearchHistory.getSmoker().equals(""))){
        query.setParameter("smoker",hostSearchHistory.getSmoker());
    }
    if(!(hostSearchHistory.getPreviousAuPair().equals(""))){
        query.setParameter("previouslyAuPair",hostSearchHistory.getPreviousAuPair());
    }
    if(!(hostSearchHistory.getWillingToWork().equals(""))){
        query.setParameter("willingToWork",hostSearchHistory.getWillingToWork());
    }
    if(!(hostSearchHistory.getWorkForSingleParent().equals(""))){
        query.setParameter("workForSingleParent",hostSearchHistory.getWorkForSingleParent());
    }
    if(!(hostSearchHistory.getWorkingForDisabledChild().equals(""))){
        query.setParameter("workingForDisabledChild",hostSearchHistory.getWorkingForDisabledChild());
    }

    List<Student> studentList = query.list();
    for(Student student : studentList){
        System.out.println("Student name is "+student.getUsername());
    }
   return studentList;

}

现在，即使我可以这样构建查询，我也必须继续挖掘HostSearchHistory，然后设置query.setParameters（）。那还有别的办法吗？

另外，正如你们可能已经注意到的，最后一个sb.append请求映射中的信息，特别是与studentinfo的一对一映射。语法正确吗？我在下面贴出Student和StudentInfo的模型，以进一步清晰。请让我知道。多谢.

学生模型：

@Entity
@Table(name="student")
public class Student implements UserDetails{
@Id
    @Column(name="studentid")
    @GeneratedValue(strategy = GenerationType.SEQUENCE,generator = "student_seq_gen")
    @SequenceGenerator(name = "student_seq_gen",sequenceName = "student_seq")
    private Long studentid;


    @OneToOne(mappedBy = "studentDetails",fetch = FetchType.LAZY,cascade = CascadeType.REMOVE)
    private StudentInfo studentInfoDetails = new StudentInfo();

    public void setStudentInfoDetails(StudentInfo studentInfoDetails){
        this.studentInfoDetails = studentInfoDetails;
    }

    public StudentInfo getStudentInfoDetails(){
        return this.studentInfoDetails;
    }
}

@Entity
@Table(name = "studentinfo")
public class StudentInfo {
    @Id
    @Column(name="infoid")
    @GeneratedValue(strategy = GenerationType.SEQUENCE,generator = "student_info_gen")
    @SequenceGenerator(name = "student_info_gen",sequenceName = "student_info_seq")
    private Long studentInfoId;

 @OneToOne
    @JoinColumn(name = "studentid",nullable = false)
    private Student studentDetails;

    public Student getStudentDetails() {
        return studentDetails;
    }

    public void setStudentDetails(Student studentDetails) {
        this.studentDetails = studentDetails;
    }
}

谢谢你的帮助。

Answer 1

我认为您可以通过示例使用Hibernate查询来简化这一点。您只需为示例对象设置所有参数值(null值将被忽略，因此生成的SQL中不会包含null的任何内容）。它看起来像这样

Student s = new Student();
s.setCountry(hostSearchHistory.getCountry());
s.setCity(hostSearchHistory.getCity());
...
StudentDetails sd = new StudentDetails();
sd.setDrivingLicense(hostSearchHistory.getDrivingLicense());
sd.setSmoker(hostSearchHistory.getSmoker());
...

Criteria crit = session.createCriteria(Student.class);
crit.add(Example.create(s));
crit.createCriteria("studentDetails").add(Example.create(sd));
List<Student> studentList = crit.list();

有一些限制，比如id字段会被忽略，关联也会被忽略（否则，只需将sd设置为s就足够了）。