Friday 21 January 2011

CriteriaBuilder and Dynamic Queries in JPA 2.0

A major new feature of Java EE 6 is JPA 2.0 and in particular the addition of the Criteria API which provides the ability to dynamically construct object-based queries.

This resolves some of the problems which arise when building dynamic native queries. The below example shows how to find customer entities with two search parameters:

public List<CustomerEntity> findCustomers(
    final String firstName, final String surname) {

    StringBuilder queryBuilder = new StringBuilder(
        "select c from Customer where ");
    List<String> paramList = new ArrayList<String>();
    paramList.add(" upper(c.firstName) like '%?%'"
        .replace("?", firstName.toUpperCase()));
    paramList.add(" upper(c.surname) like '%?%'"
        .replace("?", surname.toUpperCase()));

    Iterator itr = paramList.iterator();
    while(itr.hasNext()) {
        queryBuilder.append(itr.next());
        if (itr.hasNext()) {
            queryBuilder.append(" and ");
        }
    }

    final Query query = entityManager.createNativeQuery(
        queryBuilder.toString());

    List<Object> resultList = (List<Object>)query.getResultList();

    // iterate, cast, populate and return a list
}


The problem with the above is that it is not type safe and involves iterating over a List of Object where those Objects are themselves Object arrays. Also should Customer contain any child elements, these would have to be retrieved in a separate call.

Using the CriteriaBuilder, the same results can be achieved as shown below:

public List<CustomerEntity> findCustomers(final String firstName, final String surname) {

    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<CustomerEntity> query = builder.createQuery(CustomerEntity.class);
    Root<CustomerEntity> cust = query.from(CustomerEntity.class);
    query.select(cust);

    List<Predicate> predicateList = new ArrayList<Predicate>();

    Predicate firstNamePredicate, surnamePredicate;

    if ((firstName != null) && (!(firstName.isEmpty()))) {
        firstNamePredicate = builder.like(
            builder.upper(cust.<String>get("firstName")), "%"+firstName.toUpperCase()+"%");
        predicateList.add(firstNamePredicate);
    }

    if ((surname != null) && (!(surname.isEmpty()))) {
        surnamePredicate = builder.like(
            builder.upper(cust.<String>get("surname")), "%"+surname.toUpperCase()+"%");
        predicateList.add(surnamePredicate);
    }

    Predicate[] predicates = new Predicate[predicateList.size()];
    PredicateList.toArray(predicates);
    query.where(predicates);

    return entityManager.createQuery(query).getResultList();
} 


There is some type safety in the above but it can be furthered tied down by using the metamodel class for the entity, by using the metamodel class's public static members instead of text strings for the entity's attributes. The code would now look like this:

    firstNamePredicate = builder.like(
        builder.upper(cust.get(CustomerEntity_.firstName)),
            "%"+firstName.toUpperCase()+"%");

    surnamePredicate = builder.like(
        builder.upper(cust.get(CustomerEntity_.surname)), 
            "%"+surname.toUpperCase()+"%"); 


Having built metamodel classes using Maven, it's questionable whether it's a worthwhile exercise as any mistakes in the text based approach to finding attribute names should be flagged up by comprehensive unit testing.

8 comments:

  1. i'm just learning about JPA and criteriaBuilder. thanks for 'like' example. it's exactly what i was looking for. regards! :D

    ReplyDelete
  2. very good example the CriteriaBuilder..
    I needed to do these days using the Like operator, and stayed with the same appearance ..

    ReplyDelete
  3. Wouldn't this sql be run with literals, effectively making it impossible to cache any equally formed queries?
    Do you have an example where you use ParameterExpressions?

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. This is nice details on Criteria Builder. simplified example is very good which helps on clearing doubt on how we can use this criteria in dynamic queries.

    Here is current
    mostly asked questions in any java interview

    ReplyDelete
  6. Thanks for the post - this helped us getting on track with using CriteriaBuilder which has led to some significantly more streamlined code.

    ReplyDelete
  7. Isn't your code subject to sql injection?

    ReplyDelete

Note: only a member of this blog may post a comment.