Search like you mean it

In the last post we added database support to our First Contact application, taking advantage of both JPA and Spring Data. We cheated a little however; to keep things simple we limited our contact search query to just a single keyword. Thanks to the rise of Google Search, powerful keyword searching across multiple attributes is now the most familiar way for people to navigate their data these days. Our users are going to expect more than just a simple, single keyword search.

The SQL query for this however is a little tricky. Unlike a standard query, the number of predicates in the query depends on the number of keywords. For example, if we wanted to search our contacts by first name or last name, for the keywords ‘one’, ‘two’ and ‘three’ our SQL would look something like the following:

select * from contact
  where (firstName like '%one%' or lastName like '%one%')
    and (firstName like '%two%' or lastName like '%two%')
    and (firstName like '%three%' or lastName like '%three%')

We need to check that each keyword exists in at least one of the columns we are searching (i.e. first name and last name). The more keywords we have, the more ‘and’ clauses we need. Our simple naming convention for Spring Data isn’t going to cut it for this sort of thing, we need to dynamically build our query at runtime! Luckily JPA includes a way to build dynamic queries using the classes found in javax.persistence.criteria.

The recommended way to build dynamic query predicates using Spring Data is to create a ‘Specifications‘ class that has a static method to generate each of the Specifications (i.e. dynamic query providers) that we will use. In our case we can create a ContactSpecifications class and add a single searchContacts method to it.

ContactSpecifications.java

package com.zenjava.firstcontact.service.repository;

import com.zenjava.firstcontact.service.Contact;
import org.springframework.data.jpa.domain.Specification;

import javax.persistence.criteria.*;

public class ContactSpecifications
{
    public static Specification searchContacts(final String[] keywords)
    {
        return new Specification()
        {
            public Predicate toPredicate(Root contact, CriteriaQuery query, CriteriaBuilder builder)
            {
                Predicate predicate = builder.conjunction();

                if (keywords != null)
                {
                    for (String keyword : keywords)
                    {
                        if (keyword != null && keyword.length() > 0)
                        {
                            String matchTerm = "%" + keyword.toLowerCase() + "%";
                            predicate.getExpressions().add(builder.or(
                                    builder.like(builder.lower(contact.get("firstName")), matchTerm),
                                    builder.like(builder.lower(contact.get("lastName")), matchTerm)
                            ));
                        }
                    }
                }

                return predicate;
            }
        };
    }
}

As you can see, the Specification class provides a ‘toPredicate’ method that generates a dynamic predicate (i.e. query) for the Contact table. In our implementation of this we iterate through the keywords provided, adding restrictions to the predicate as needed. This is a relatively simple predicate, searching only one table but you can create pretty much any query, including table joins, using this predicate builder approach. See the Spring Data Specification documentation for more detail on this.

Next we need a way to execute this dynamic query. With Spring Data we simply make our Repository interface extend JpaSpecificationExecutor. This additional interface provides several methods, such as  ’findOne(Specification)’ and ’findAll(Specification)’, that take Specification objects and execute the resulting queries for us. Our updated ContactRepository class now looks like this (we can remove the old ‘findByFirstNameLikeOrLastNameLike’ method as we won’t be needing this now we can do proper keyword searching):

ContactRepository.java

public interface ContactRepository
        extends CrudRepository, JpaSpecificationExecutor
{
}

As well as providing a simple ‘findAll’ method, the JpaSpecificationExecutor also provides methods for retrieving paged data – i.e. retrieving results in batches (such as 25 at a time) and paging forward and backwards through those results. This is extremely useful when you have a large set of data that you can’t show all at once. We won’t use this here, but check out the PageRequest class for more details on how to use this.

Finally, we can now update our ContactServiceImpl to use the new ContactSpecification to perform a true keyword search. Our new searchContacts method looks like this:

public List searchContacts(String[] keywords)
{
    return contactRepository.findAll(ContactSpecifications.searchContacts(keywords));
}

That’s it! Give it a run and check it out. This same basic query can be easily extended to cover more complex scenarios, allowing your users to search across multiple tables and multiple fields via a simple text field search – this is guaranteed to make you popular at parties.

The full source code for this post can be found at: http://code.google.com/p/jfxee/source/browse/trunk/first-contact/first-contact-client-db-search/