JPA Query by Example framework

Query By Example for JPA is originally inspired from Hibernate Example criterion. But since Hibernate's Example is not part of JPA 2, we have created our own API, using JPA 2 only.

How To Use QBE

We do not cover here QBE implementation details, instead we explain how to use the Query By Example API.

JPA Query by Example is available on Maven central repository:

<dependency>
	<groupId>com.jaxio</groupId>
	<artifactId>jpa-querybyexample</artifactId>
	<version>1.0.1</version>
</dependency>

Resources

• Take a look directly at the QBE junit tests, they are almost self-explanatory.
• Use Celerio to generate an advanced CRUD application that leverages this QBE API. See Celerio
• Watch a demo of an application generated by Celerio

Simple Query By Example

In its simplest form, Query By Example allows you to construct a query from a given entity instance.

Let's assume we have an Account entity having a lastName property and that we want to query all accounts whose last name matches 'Jagger'.

Using QBE, constructing the query is as simple as setting the lastName...:

Account example = new Account();
example.setLastName("Jagger");
List<Account> result = accountRepository.find(example);

At the SQL level, the resulting query looks like this:

select
    -- skip other fields for clarity
    account0_.LAST_NAME as LAST9_3_,
from
    Account account0_ 
where
    account0_.LAST_NAME=?

The AccountRepository extends a GenericRepository

Case sensitivity, order by

The first query above involves a String. Let's change it to make it case insensitive.

Our Account entity does not carry case sensitivity meta information. For this reason, we require some extra parameters for case sensitivity, but also ordering, etc. The number of parameters can grow quickly, so we have grouped them in the SearchParameters class which can be passed as a parameter to the accountRepository's methods.

Let's make the first query above case insensitive and let's add an ORDER BY.

Account example = new Account();
example.setLastName("Jagger"); 
SearchParameters sp = new SearchParameters().caseSensitive().orderBy(OrderByDirection.ASC, Account_.lastName);
List<Account> result = accountRepository.find(example, sp);

Note the usage of the Account_* static metamodel, which helps you to keep your query related Java code strongly typed.

At the SQL level, the resulting FROM clause now looks like this:

from
    ACCOUNT account0_ 
where
    lower(account0_.LAST_NAME)=? 
order by
    account0_.LAST_NAME asc

Pagination

In most web application we need to paginate the query results in order to save resources. In the query below, we retrieve only the 3rd page (we assume a page lists 25 rows). The first result is the 50th element and we retrieve at most 25 elements.

Account example = new Account();
example.setLastName("Jagger");
SearchParameters sp = new SearchParameters().orderBy(OrderByDirection.ASC, Account_.lastName) //
	.first(50).maxResults(25);
List<Account> result = accountRepository.find(example, sp);

At the SQL level, the resulting FROM clause now looks like this (we use H2 database):

from
    ACCOUNT account0_ 
where
    account0_.LAST_NAME=? 
order by
    account0_.LAST_NAME asc limit ? offset ?

LIKE and String

For strings, you can globally control whether a LIKE should be used and where the % wildcard should be placed. For example, adding :

example.setLastName("Jag");
SearchParameters sp = new SearchParameters().startingLike();

to our example above would result in

account0_.LAST_NAME LIKE 'Jag%'

Multiple criteria

Until now, we have worked only with one property, lastName, but we can set other properties, for example:

Account example = new Account();
example.setLastName("Jag");
example.setBirthDate(new Date());
SearchParameters sp = new SearchParameters().orderBy(OrderByDirection.ASC, Account_.lastName).startingLike();
List<Account> result = accountRepository.find(example, sp);

By default, the FROM clause uses a AND predicate.

from
    ACCOUNT account0_ 
where
    account0_.BIRTH_DATE=? 
    and (
        account0_.LAST_NAME like ?
    ) 
order by
    account0_.LAST_NAME asc

To use instead OR, use the .orMode(), as follow:

SearchParameters sp = new SearchParameters().orMode().orderBy(OrderByDirection.ASC, Account_.lastName).startingLike();

And this time we get:

where
    account0_.LAST_NAME like ? 
    or account0_.BIRTH_DATE=? 
order by
    account0_.LAST_NAME asc

Is that all ?

Not really, we have just scratched the surface. For the moment, we have covered only rather simple queries. While simplicity is key, it is often not sufficient. What about date or number range queries ? What about associated entities ? etc.

Beyond Query By Example

Mixing Query by Example and Range Query.

Now, let's imagine that you also want to restrict the query above to all accounts having their date of birth between 1940 and 1945 included. Of course, the entity does not have the appropriate property (from & to). For this reason, we introduce an additional Range parameter.

Here is an example:

Account example = new Account();
example.setLastName("Jagger");

Calendar from = Calendar.getInstance();
from.set(1940, 0, 1);

Calendar to = Calendar.getInstance();
to.set(1945, 11, 31);

Range<Account, Date> birthDateRange = Range.newRange(Account_.birthDate);
birthDateRange.from(from.getTime()).to(to.getTime());

SearchParameters sp = new SearchParameters().range(birthDateRange);
List<Account> result = accountRepository.find(example, sp);

Note that you can add ranges of any type: Integer, Long, LocalDate (joda time), BigDecimal, etc...

This codes leads in fine to following FROM clause:

from
    ACCOUNT account0_ 
where
    (
        account0_.BIRTH_DATE between ? and ?
    ) 
    and account0_.LAST_NAME=?

Here is a variation of the same example (depends on need, taste and color :-):

DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
Date from = dateFormat.parse("1920-12-01");
Date to = dateFormat.parse("1974-12-01");

SearchParameters sp = new SearchParameters().range(from, to, Account_.birthDate);
List<Account> accountList = accountRepository.find(sp);

Query all string properties in a OR clause

To find all entities having at least one of their String property matching a given value, use the searchPattern method.

Here is an example:

SearchParameters sp = new SearchParameters().searchMode(SearchMode.STARTING_LIKE).searchPattern("Jag");
List<Account> result = accountRepository.find(sp);

The FROM clause now includes all string columns:

from
    ACCOUNT account0_ 
where
    or account0_.LAST_NAME like ? 
    or account0_.USERNAME like ? 

Property Selector

In order to construct a OR clause for a given property we use the PropertySelector class.

Here is an example:

PropertySelector<Account, String> lastNameSelector = PropertySelector.newPropertySelector(Account_.lastName);
lastNameSelector.setSelected(Arrays.asList("Jagger", "Richards", "Jones", "Watts", "taylor", "Wyman", "Wood"));

SearchParameters sp = new SearchParameters().property(lastNameSelector);

List<Account> result = accountRepository.find(sp);

Here is the corresponding FROM clause:

	from
	    ACCOUNT account0_ 
	where
	    account0_.LAST_NAME='Jagger'
	    or account0_.LAST_NAME='Richards'
	    or account0_.LAST_NAME='Jones'
	    or account0_.LAST_NAME='Watts'
	    or account0_.LAST_NAME='Taylor'
	    or account0_.LAST_NAME='Wyman'
	    or account0_.LAST_NAME='Wood'

Note that if you use JSF2 with PrimeFaces, you can directly pass a PropertySelector to a multiple autoComplete component's value property. This way, the autoComplete component fills the PropertySelector. Here is how:

<p:autoComplete ... multiple="true" value="#{accountSearchForm.lastNameSelector.selected}" ... />

Here is a snapshot:

PrimeFaces uses the setSelected(List<Account> selection) method to fill the lastNameSelector.

Mix it all

Remember, you can mix all the example we have seen so far. You can have in a single query having multiple ranges, multiple property selector, multiple properties set on the example entity, etc.

This gives you great power ;-)

Query By Example on association

The Account entity has a @ManyToOne association with the Address entity.

Here is how we can retrieve all accounts pointing to an Address having its city property set to "Paris":

Account example = new Account();
example.setHomeAddress(new Address());
example.getHomeAddress().setCity("Paris");
List<Account> result = accountRepository.find(example);
Assert.assertThat(result.size(), is(2));

The FROM clause uses a JOIN:

from
    ACCOUNT account0_ cross 
join
    ADDRESS address1_ 
where
    account0_.ADDRESS_ID=address1_.ID 
    and address1_.CITY='Paris'

Enjoy!

License

The JPA Query By Example Framework is released under version 2.0 of the Apache License.