Hibernate : Setting the parameters for multi-variable search and data from other table

Question

I am working on a Spring-MVC application in which I would like to search in the database with some mentioned criteria. Unfortunately, they are around 10-12 of them. So I had asked a question before on SO as to how I can break them up into small chunks, check which of them are not null, and use them as a parameter. I was partially successful by using a StringBuilder as mentioned below.

The problem I am having is how can I set the query.setParameter("variablename",variableName) for values which are not null. First the example :

 @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;

}

Now even when I can build the query like this, I again have to keep digging up the HostSearchHistory and then set the query.setParameters(). Is there any alternative to that?

Also, as you guys may have noticed, the last sb.append requests information from a mapping, specifically one-to-one mapping with StudentInfo. Is that syntax correct? I am posting the model of Student and StudentInfo below for further clarity. Kindly let me know. Thanks a lot.

Student model :

@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;
    }
}

StudentInfo model :

@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;
    }
}

Thank you for your help.

You can use the Criteria API. You can find an example here: http://stackoverflow.com/questions/12199433/jpa-criteria-api-with-multiple-parameters — Faton, May 12 '15 at 13:12
@Faton : I have edited my main post to include the complete method, is that correct. What is also more important for me to know is if I can refer to one-to-one entities the way I have. Thanks a lot. — We are Borg, May 12 '15 at 13:15
The declaration @OneToOne seems correct. If you build your query this way, don't forget to handle the case when all the fields are null/empty. — Faton, May 12 '15 at 13:24
@Faton : I am doing that by checking the strings... Isn't that right? — We are Borg, May 12 '15 at 13:26
If all the strings are empty, you will get the following "from Student as s where order by s.registrationDate desc". — Faton, May 12 '15 at 13:27
@Faton Yes. I guess I have to set a Flag variable somewhere so I dont reveal all the students. Thanks. I will do it. Meanwhile can you construct an answer so I can mark it, as I am testing my code in a short moment so I can ask you if there is something troublesome. — We are Borg, May 12 '15 at 13:30
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/77620/discussion-between-faton-and-we-are-borg). — Faton, May 12 '15 at 13:39

erhun · Answer 1 · 2015-05-12T13:38:19.263

This is a sample from my project i will not suggest a better way but show some missing points at your query. I have longer queries too but write this one to keep it simple. I use CreateQuery approach :

@Override
    public List<CampaignCode> queryCampaignCode(Merchant merchant, PaymentSystemType paymentSystemType, Date now) {
        if (merchant == null) {
            new IllegalArgumentException("Merchant parameter can not be null!");
        }
        StringBuilder sb = new StringBuilder();
        sb.append("FROM ").append(CampaignCode.class.getName()).append(" WHERE merchant=:merchant");
        if (paymentSystemType != null) {
            sb.append("AND paymentSystemType=:paymentSystemType ");
        }
        if (now != null) {
            sb.append(" AND :now BETWEEN startDate AND endDate ");
        }
        //Return current session
        Query query = getSession().createQuery(sb.toString());
        if (paymentSystemType != null) {
            query.setParameter("paymentSystemType", paymentSystemType);
        }
        if (now != null) {
            query.setParameter("now", now);
        }
        query.setParameter("merchant", merchant);
        return query.list();
    }

So in here i have a merchant object which is set all time so i have no syntax error in every possiblity to created sql. in your example if user only set country you get sql syntax exception at run time, so carefull to AND usage.
In your example you check just with String#equals method i always trust Apache StringUtils#isBlank if all my parameters are string(it is not the case for my sample but in your case i prefer StringUtils#isBlank)
It will be good to get table name from class name like Student.class.getName() so this way your query will aware if your class name is changed.

Finally

i am happy with this usage and also i can use this method for a lor business logics at upper layers(Service or business layer) So i reuse same methods again and again with this way.

EDIT

After your edit my first scenario is still happened if Country not set and city or other parameter is SET.

score 1 · Accepted Answer · answered May 12 '15 at 14:00

I think you can simplify this a lot by using Hibernate Query By Example. You just set all the parameter values to your example object(s) (null values are ignored, so anything that is null will not be included in generated SQL). It would look something like this

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();

There are some limitations, like id fields are ignored, as are associations (otherwise just setting sd to s should be enough).

Hibernate : Setting the parameters for multi-variable search and data from other table

2 Answers2