Dynamic Where Clause in JPA

Every once in a while you'll come across a scenario where you want to dynamically add parameters to a query's where clause.  For dynamic filtering I created a query generator that will simply add a segment to the where clause and the parameter to the query, and it works pretty well.

However, there are times when the scenario is a bit more complex and you'll need some more advanced logic.  For example given this entity:


Let's say we're given a list of similar data objects that have an optional userId, the last name, and the last 4 digits of the ssn. We want to match these to our User object. The rules to match up to the User is thus: use the userId if there is one, but if not match with the lastName and the last 4 digits of the ssn.

The SQL Query would look something like this:
In order to generate a query we need some conditional logic to decide if we should add the userId to a list to query the :userIds parameter or if we should add an OR clause to find a match of lastname and ssn.

The Criteria API

I've generally tried to avoid using the criteria API for my dynamic queries because it is fairly verbose, but it is an effective tool for the complex logic we're looking at. Below is an example of using the criteria API to generate the query we need.

As you can see it is pretty verbose but the flexibility to basically do whatever we want is pretty powerful.