Hibernate Query by Example (QBE)

What is It

Query by example is an alternative querying technique supported by the main JPA vendors but not by the JPA specification itself.

QBE returns a result set depending on the properties that were set on an instance of the queried class.

So if I create an Address entity and fill in the city field then the query will select all the Address entities having the same city field as the given Address entity.

The typical use case of QBE is evaluating a search form where the user can fill in any search fields and gets the results based on the given search fields. In this case QBE can reduce code size significantly.

When to Use

·  Using many fields of an entity in a query

·  User selects which fields of an Entity to use in a query

·  We are refactoring the entities frequently and don’t want to worry about breaking the queries that rely on them

Limitations

·  QBE is not available in JPA 1.0 or 2.0

·  Version properties, identifiers and associations are ignored

·  The query object should be annotated with @Entity

Test Data

I used the following entities to test the QBE feature of Hibernate:

·  Address (long id, String city, String street, String countryISO2Code, AddressType addressType)

·  AddressType (Integer type, String description)

Imports

The examples will refer to the following classes:

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.Example;
import org.hibernate.criterion.Restrictions;
import org.junit.Test;
import java.util.List;

Utility Methods

I also made two utility methods to present a list of the two entity types:

private void listAddresses(List<Address> addresses) {
  for (Address address : addresses) {
    System.out.println(address.getId() + ", " +
      address.getCountryISO2Code() + ", " +
      address.getCity() + ", " + address.getStreet() + ", " +
      address.getAddressType().getType() + ", " +
      address.getAddressType().getDescription());
  }
}

private void listAddressTypes(List<AddressType> addressTypes) {
  for (AddressType addressType : addressTypes) {
    System.out.println(addressType.getType() + ", " +
      addressType.getDescription());
  }
}

Example 1: Equals

This example code returns the Address entities matching the given CountryISO2Code and City.

Method:

@Test
public void testEquals() throws Exception {
  Session session = (Session) entityManager.getDelegate();
  Address address = new Address();
  address.setCountryISO2Code("US");
  address.setCity("CHICAGO");
  Example addressExample = Example.create(address);
  Criteria criteria = session.createCriteria(Address.class).add(addressExample);
  listAddresses(criteria.list());
}

Result:

  75, US, CHICAGO, Los Angeles Way2, 6, Customer

  170, US, CHICAGO, Jackson Blvd 33a, 4, Delivery

  63, US, CHICAGO, Main Avenue 1, 5, Bill to

  37, US, CHICAGO, Jackson Blvd 33a, 4, Delivery

  36, US, CHICAGO, Jackson Blvd 33a, 4, Delivery

Example 2: Id Limitation

This example presents that id fields in the query object are ignored.

Method:

@Test
public void testIdLimitation() throws Exception {
  Session session = (Session) entityManager.getDelegate();
  Address address = new Address();
  address.setCountryISO2Code("US");
  address.setCity("CHICAGO");
  address.setId(100);  // setting id is ignored
  Example addressExample = Example.create(address);
  Criteria criteria = session.createCriteria(Address.class).add(addressExample);
  listAddresses(criteria.list());
} 

Result:

  75, US, CHICAGO, Los Angeles Way2, 6, Customer

  170, US, CHICAGO, Jackson Blvd 33a, 4, Delivery

  63, US, CHICAGO, Main Avenue 1, 5, Bill to

  37, US, CHICAGO, Jackson Blvd 33a, 4, Delivery

  36, US, CHICAGO, Jackson Blvd 33a, 4, Delivery

Example 3: Association Limitation

Associations of the query object are ignored, too.

Method:

@Test
public void testAssociationLimitation() throws Exception {
  Session session = (Session) entityManager.getDelegate();
  Address address = new Address();
  address.setCountryISO2Code("US");
  address.setCity("CHICAGO");
  AddressType addressType = new AddressType();
  addressType.setType(5);
  address.setAddressType(addressType);  // setting an association is ignored
  Example addressExample = Example.create(address);
  Criteria criteria = session.createCriteria(Address.class).add(addressExample);
  listAddresses(criteria.list());
}

Result:

  75, US, CHICAGO, Los Angeles Way2, 6, Customer

  170, US, CHICAGO, Jackson Blvd 33a, 4, Delivery

  63, US, CHICAGO, Main Avenue 1, 5, Bill to

  37, US, CHICAGO, Jackson Blvd 33a, 4, Delivery

  36, US, CHICAGO, Jackson Blvd 33a, 4, Delivery

Example 4: Like

QBE supports like in the query object if we enable it with Example.enableLike().

Method:

@Test
public void testLike() throws Exception {
  Session session = (Session) entityManager.getDelegate();
  Address address = new Address();
  address.setCountryISO2Code("US");
  address.setCity("AT%");
  Example addressExample = Example.create(address).enableLike();
  Criteria criteria = session.createCriteria(Address.class).add(addressExample);
  listAddresses(criteria.list());
}

 Result:

  83, US, ATLANTA, null, 6, Customer

  184, US, ATLANTA, null, 1, Shipper

  25, US, ATLANTA, null, 1, Shipper

Example 5: ExcludeProperty

We can exclude a property with Example.excludeProperty(String propertyName).

Method:

@Test
public void testExcludeProperty() throws Exception {
  Session session = (Session) entityManager.getDelegate();
  Address address = new Address();
  address.setCountryISO2Code("US");
  address.setCity("AT%");
  Example addressExample = Example.create(address).enableLike()
  .excludeProperty("countryISO2Code");
  // countryISO2Code is a property of Address
  Criteria criteria = session.createCriteria(Address.class).add(addressExample);
  listAddresses(criteria.list());
}

Result:

  154, GR, ATHENS, BETA ALPHA Street 5, 2, Consignee

  83, US, ATLANTA, null, 6, Customer

  25, US, ATLANTA, null, 1, Shipper

  184, US, ATLANTA, null, 1, Shipper

Example 6: IgnoreCase

Case-insensitive search is supported by Example.ignoreCase().

Method:

@Test
public void testIgnoreCase() throws Exception {
  Session session = (Session) entityManager.getDelegate();
  AddressType addressType = new AddressType();
  addressType.setDescription("customer");
  Example addressTypeExample = Example.create(addressType).ignoreCase();
  Criteria criteria = session.createCriteria(AddressType.class)
    .add(addressTypeExample);
  listAddressTypes(criteria.list());
}

Result:

  6, Customer

Example 7: ExcludeZeroes

We can ignore 0 values of the query object by Example.excludeZeroes().

Method:

@Test
public void testExcludeZeroes() throws Exception {
  Session session = (Session) entityManager.getDelegate();
  AddressType addressType = new AddressType();
  addressType.setType(0);
  addressType.setDescription("Customer");
  Example addressTypeExample = Example.create(addressType)
    .excludeZeroes();
  Criteria criteria = session.createCriteria(AddressType.class)
    .add(addressTypeExample);
  listAddressTypes(criteria.list());
}

Result:

  6, Customer

Example 8: Combining with Criteria

QBE can be combined with criteria query. In this example we add further restriction to the query object using criteria query.

Method:

@Test
public void testCombiningWithCriteria() throws Exception {
  Session session = (Session) entityManager.getDelegate();
  AddressType addressType = new AddressType();
  addressType.setDescription("Customer");
  Example addressTypeExample = Example.create(addressType);
  Criteria criteria = session
    .createCriteria(AddressType.class).add(addressTypeExample)
    .add(Restrictions.eq("type", 6));
  listAddressTypes(criteria.list());
}

Result:

  6, Customer

Example 9: Association

With criteria query we can filter both sides of an association, using two query objects.

Method:

@Test
public void testAssociation() throws Exception {
  Session session = (Session) entityManager.getDelegate();
  Address address = new Address();
  address.setCountryISO2Code("US");
  AddressType addressType = new AddressType();
  addressType.setType(6);
  Example addressExample = Example.create(address);
  Example addressTypeExample = Example.create(addressType);
  Criteria criteria = session.createCriteria(Address.class).add(addressExample)
    .createCriteria("addressType").add(addressTypeExample);
  // addressType is a property of Address
  listAddresses(criteria.list());
}

Result:

  84, US, BOSTON, null, 6, Customer

  83, US, ATLANTA, null, 6, Customer

  82, US, SAN FRANCISCO, null, 6, Customer

  75, US, CHICAGO, Los Angeles Way2, 6, Customer

EclipseLink

EclipseLink QBE uses QueryByExamplePolicy, ReadObjectQuery and JpaHelper:

QueryByExamplePolicy qbePolicy =newQueryByExamplePolicy();
qbePolicy.excludeDefaultPrimitiveValues();
Address address =newAddress();
address.setCity("CHICAGO");
ReadObjectQuery roq =newReadObjectQuery(address, qbePolicy);
Query query =JpaHelper.createQuery(roq, entityManager);

OpenJPA

OpenJPA uses OpenJPAQueryBuilder:

CriteriaQuery<Address> cq = openJPAQueryBuilder.createQuery(Address.class);
Address address =newAddress();
address.setCity("CHICAGO");
cq.where(openJPAQueryBuilder.qbe(cq.from(Address.class), address);

References

Hibernate:

·  Srinivas Guruzu and Gary Mak: Hibernate Recipes: A Problem-Solution Approach (Apress)

·  http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querycriteria.html#querycriteria-examples

·  http://www.java2s.com/Code/Java/Hibernate/CriteriaQBEQueryByExampleCriteria.htm

·  http://www.dzone.com/snippets/hibernate-query-example

·  http://gal-levinsky.blogspot.de/2012/01/qbe-pattern.html

Hibernate associations:

·  http://stackoverflow.com/questions/9309884/query-by-example-on-associations

·  http://stackoverflow.com/questions/8236596/hibernate-query-by-example-equivalent-of-association-criteria-query

JPA:

·  http://stackoverflow.com/questions/2880209/jpa-findbyexample

EclipseLink:

·  http://www.coderanch.com/t/486528/ORM/databases/findByExample-JPA-book

OpenJPA:

·  http://www.ibm.com/developerworks/java/library/j-typesafejpa/#N10C18