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?
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.