HQL : Adding colon seperated strings for multiple parameters to search with

Question

I am working on an Spring-MVC project in which I am using Hibernate as the ORM tool. One of the features of the project is that it can search for other users, by giving various parameters like country, city, etc. Now, I would like to pass multiple countries for the search. For that reason I am sending a : separated names of countries to the method.

As initially the method was only for a single country, I have modified the query to work in this order. Is this the right way to pass multiple parameters as the query is complex. Thank you.

@Override
public List<Student> addHostSearchHistory(HostSearchHistory hostSearchHistory, Long hostId) {

    String queryString = giveMeFormattedHostSearchString("AND",hostSearchHistory);

    Query query = session.createQuery(queryString);

    if (!(hostSearchHistory.getCity() == null)) {
        if (!(hostSearchHistory.getCity().equals(""))) {
            query.setParameter("city", "%"+hostSearchHistory.getCity().toUpperCase()+"%");
        }
    }
    List<Student> studentList = query.list();

    Query query1 = session.createQuery(giveMeFormattedHostSearchString("OR",hostSearchHistory));
    if (!(hostSearchHistory.getCity() == null)) {
        if (!(hostSearchHistory.getCity().equals(""))) {
            query1.setParameter("city", "%"+hostSearchHistory.getCity().toUpperCase()+"%");
        }
    }

}

private String giveMeFormattedHostSearchString(String clause, HostSearchHistory hostSearchHistory){
    StringBuilder sb = new StringBuilder();
    sb.append("from Student as s where ");

    if (!(hostSearchHistory.getCountry() == null)) {
        if (!(hostSearchHistory.getCountry().isEmpty())) {
            String[] countries = hostSearchHistory.getCountry().split(":");
            sb.append("(");
            for(String s : countries){
                sb.append(" ").append("upper(s.studentCountry) like ").append(s);
            }
            sb.append(")");


        }
    }
    if (!(hostSearchHistory.getCity() == null)) {
        if (!(hostSearchHistory.getCity().isEmpty())) {
            sb.append(" ").append(clause).append(" ").append("upper(s.city) like :city");
        }
    }
}

Is there a better less error-prone way to search for multiple countries.. Please note, I have only included 3 parameters to avoid clutter. Thank you.

Answer 1

(Extracted based on comment in question)

If it is simply a foo.country in (:countryList) then Hibernate, or Spring Data can help. However for your case that you need to search list of countries using LIKE, you have to construct it manually. Your way of construction (embedding the value directly in resulting query) is not preferable as it is error-prone and make your code open for SQL injection.

One of the ways to do proper query construction is to make use of Criteria API.