QBE pattern

When writing an enterprise system, there is a common pattern of a user who fill in a search form and get a corresponding result based on the given search parameters.

Handling this request manually in the backend is a bit tedious since for every search parameter, the developer need to check if the user insert a value and if so, concatenate the corresponding string into the WHERE clause in the SQL.

The Query By Example (QBE) pattern is a pattern that helps deal with the mentioned task.

It is implemented by ORM frameworks (such as Hibernate) and also avlaiable as part of possible to implement utilizing the the spec in JPA 2.0 (as seen in OpenJPA project)

The QBE implementation expect the model instance (the object which is annotated with @Entity).
That instance should have the form search values in it.
Than the implementation  framework does all the SQL wiring magic and save us the tedious work.
So at the end we just return the query result.

Lets discuss a case where a user want to query the Order object.

The Order class need to be annotated with @Entity

@Entity
public class Order{
..
}

Using a Hibernate implementation :

public List findByOrderElements(Order order) {

 Session session = getSession();

//Hibernate assembles the query with the given entity to query
 Example example = Example.create(order) //create Example object given the instance to query
     .enableLike(MatchMode.ANYWHERE) //optional parameters
     .excludeZeroes()
     .ignoreCase();

//Get the query result
 List result = session.createCriteria(Order.class)
     .add(example)
     .list();
        
 return result;
}

That's it...

Important note:

• QBE will only work on one object (won't help in case the form input has properties of other related fields, i.e.  requires join)
• Version properties, identifiers and associations are ignored (if QBE would consider the id than it would match only one instance...)

For even more advanced stuff regarding QBE pattern, please follow my next post.