set parameter which is null in @NamedQuery (JPA QL 1.0)

Question

I am writing a JPA QL named query that would search for documents. This should be done in only one query and I cannot switch to native SQL (that's non functional requirement). The query I wrote looks as follows:

query = "select doc from Doc doc " +
" join doc.Csts cst " +
" where cst.cstFrstNm like :FrstNm " +
" and cst.cstLastNm like :LastNm " +
" and doc.resId = :id ";

This query is parametrized using following instructions:

query.setParameter("FrstNm ", firstName + '%');
query.setParameter("LastNm ", lastName + '%');            
query.setParameter("id", resId);

firstName, lastName, resId in the above code are my search criteria and they are all Strings and they can be null. When one of them is null, it shouldn't be taken into consideration when the query is invoked.

For instance:

Let's say that: firstName = "John", lastName = "Doe" and resId is null, then the query should return all the entries from Doc table that are for user John Doe, no matter what is their resId.

I was trying to put additional OR and AND conditions into query that'd check if resId is null but it didn't work. I'm testing it on HSQLDB.

Is it any way to modify this JPA query to handle null values?

Solution 1

Two solutions:

First one, don't use a named query and craft a new query for each request:

boolean first = true;
query = query = "select doc from Doc doc " +
" join doc.Csts cst " +
if (firstName != null) {
    query += (first ? " where " : " and ");
    query += " cst.cstFrstNm like :FrstNm ";
    first = false;
}
query += ";";
// ...

Second solution, you are using wildcard. If your parameter is empty, just put a '%' in your query, which will match all corresponding strings:

query.setParameter("FrstNm ", (firstName != null ? firstName : "") + '%');
query.setParameter("LastNm ", (lastName != null ? lastName : "") + '%');

If firstName is empty, your query will look like:

... cst.cstFrstNm like '%' ...

and will match all the values. It will result in the parameter not filtering any result, with a behaviour similar as if it wasn't present.

Solution 2

You may need to build the query dynamically to satisfy your parameters. For instance:

public List<Employee> findEmployees(String name, String deptName, String projectName, String city) {
  StringBuffer query = new StringBuffer();
  query.append("SELECT DISTINCT e ");
  query.append("FROM Employee e LEFT JOIN e.projects p ");
  query.append("WHERE ");
  List<String> criteria = new ArrayList<String>();
  if (name != null) { criteria.add("e.name = :name"); }
  if (deptName != null) { criteria.add("e.dept.name = :dept"); }
  if (projectName != null) { criteria.add("p.name = :project"); }
  if (city != null) { criteria.add("e.address.city = :city"); }
  if (criteria.size() == 0) {
     throw new RuntimeException("no criteria");
  }
  for (int i = 0; i < criteria.size(); i++) {
    if (i > 0) { query.append(" AND "); }
       query.append(criteria.get(i));
  }
  Query q = em.createQuery(query.toString());
  if (name != null) { q.setParameter("name", name); }
  if (deptName != null) { q.setParameter("dept", deptName); }
  if (projectName != null) { q.setParameter("project", projectName); }
  if (city != null) { q.setParameter("city", city); }
  return (List<Employee>)q.getResultList();
}

You migt also like to consider Criteria API if you are using JPA 2.0 for this kind of task.