Extending query by example through annotation

December 4, 2007

Query by example (QBE) is a great feature for easily executing Criteria using Hibernate. By simply filling in an object and submitting it, Hibernate automatically generates the query based on the values set in the object. While this is an attractive option, in my experience it usefulness quickly becomes limited. In many cases, specifying Restrictions is required which quickly leads to ugly code. For example, lets take a Product class:

@Entity
@Table(name="PRODUCTS")
public class Product {
  @Id
  @Column(name="ID")
  private BigDecimal id;

  @Column(name="SKU")
  private String sku;

  @Column(name="CATEGORY")
  private Category category;

  @Column(name="NAME")
  private String name;

  @Column(name="DESCRIPTION")
  private String description;

  @Column(name="STARTED_OFFERING")
  private Date startedOffering;

  @Column(name="PRICE")
  private BigDecimal price;
  ...
}

This class has a number of attributes we could search on. The easiest of which are probably the category and name. With the id and name we can simply populate and object instance and use QBE:

...
Product product = new Product();

product.setCategory(Category.APPAREL);
product.setName("slacks");

Example productExample =
  Example.create(product).ignoreCase().enableLike();

List results = session.createCriteria(Product.class)
  .add(productExample)
  .list();
...

This works fine, but what if we want to search for products in a price range say gift ideas from $30.00 to $50.00, or products that we started offering between December 1, 2006 and March 31, 2007. Obviously we can’t accomplish this by simply setting values into our example product. We need to use the Criteria API in this case:

...
List products = session.createCriteria(Product.class)
  .add(Restrictions.between("price", minPrice, maxPrice))
  .add(Restrictions.
      between("startedOffering", startDate, endDate))
  .list();
...

This seems pretty simple but what if we need to add this to the UI. Well, first we need an object to bind our data to:

...
public class ProductCriteria {
  private BigDecimal minPrice;
  private BigDecimal maxPrice;
  private Date beginStartedOffering;
  private Date endStartedOffering;
  ...
  // getters and setters for attributes
  ...
}

Now we will need to perform checks to determine how to execute the search:

...
if(productCriteria.getMaxPrice() != null)
{
  criteria.add(Restrictions.less("price",
    productCriteria.getMaxPrice()))
}

if(productCriteria.getMinPrice() != null)
{
  criteria.add(Restrictions.greater("price",
    productCriteria.getMinPrice()))
}
...

Etc, etc… As you can imagine the problem is exacerbated the more Restrictions that become required to execute the search. So what about a hybrid approach? Lets define a few annotations:

@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface Criteria {
  Class entity;
}
...
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface Criterion {
  String property;
  Restriction operator default Restriction.EQUALS;
}
...

Restriction is simply a Java5 enum that will map to a Hibernate Restriction (this can be done through a polymorphic method in your Restriction implementation). Alright, now lets place these annotations on our Criteria object that we bound to the UI:

@Criteria(entity=Product.class)
public class ProductCriteria {
  @Criterion(property="price", operator=Restrictions.LESS)
  private BigDecimal minPrice;

  @Criterion(property="price", operator=Restrictions.GREATER)
  private BigDecimal maxPrice;

  @Criterion(property="startedOffering",
    operator=Restrictions.GREATER)
  private Date beginStartedOffering;

  @Criterion(property="startedOffering",
    operator=Restrictions.LESS)
  private Date endStartedOffering;

  ...
  // getters and setters for attributes
  ...
}

At run-time the annotations can be processed to create and execute the criteria provided the object that we bound to the UI. Assuming we have a ProductCriteria instance populated with data from our UI interaction, the call would look like:

...
List <Product> products =
  criteriaManager.execute(productCriteria, entityManager);
...

The CriteriaManager knows the entity type we’re searching for (we placed it in the @Criteria annotation), it knows the restrictions we’re applying and which @Entity property we are applying them to (that is in the @Criterion annotations), and it knows the values that will be used in executing (based on the attribute the @Criterion annotation is placed on). We could also directly inject the EntityManager instance into the CriteriaProcessor.

So you may say this just seems like we’re moving the problem around. Well, not really. There are some benefits to this approach:

• Vendor independence: The use of custom APIs is eliminated. Instead of calling on the Hibernate Criteria API, it becomes very simple to swap out our implementation. Simply alter the code that processes the annotations we defined.
• One-stop shop: The annotations place the meta-data about how a criteria should execute with the actual criteria values. If changes need to be made to your example query, you make the changes in a single location rather than having to alter both your object bound to the UI and some DAO that processes that object. Additionally, the logic associated with executing criteria is right where it belongs, on the criteria object.
• Remove unnecessary DAOs: Removes the need for a custom DAO to process the object bound to the UI with a bunch of if/thens. The EntityManager provided by JPA was a recognition that the constant DAOs implementing CRUD interfaces could be generalized. This approach recognizes that constant implementation of DAOs handling if/then scenarios can be generalized.
• JPA Consistency: The @Criteria and @Criterion annotations look very similar to @Entity and @Column. This is intentional to create consistency between our @Entity mappings and our @Criteria mappings.
• Testability: All logic for creation of criteria is in one place, the annotation processor. This one place can be heavily tested ensuring its functionality. A custom annotation processor could be developed to make testing your criteria classes very simple (development of this annotation processor is actually very simple).
• Code generation: Tools like grails use compile-time annotation processing through an APT to auto-generate things like JSPs, classes, configuration, etc. This same approach can be taken using @Criteria. It would be fairly simple to write an APT that generates search criteria JSPs based on the @Criteria instance and the provided annotations.

Essentially we have a very reusable criteria execution strategy. There are of course many extensions that could be included with this including:

• conjunctions – what about ANDs and ORs?
• associations – what happens if my mappings contain a @OneToOne, @OneToMany, or even a @ManyToMany?
• dynamic operators – what if my operators are not static (i.e. the user can choose an operator)?

These questions will be answered in an upcoming post so stay tuned.

Posted in Hibernate, JPA