42¢ glxn.net

Query By Example for JPA

A while back I implemented a little Query By Example API for JPA 2. The API is a quite simple to use and very flexible abstraction on top of the Hibernate Criteria API. The basic idea is that you build an example object and pass it in to the QBE API. The example could be a JPA Entity or a simple POJO. In case where example object is another type than the entity being queried the example object must have fields matching the same names as the fields you want to match on the entity class. There are some sensible defaults to the query that is built, but you can override many aspects like matching, junction and multiple ordering semantics. The javadoc on the source should be pretty extensive, but I have summarized the important stuff below.

Summarized API Doc

Start by using QBE.java

// instantiates the QBE API with the supplied entityManager
qbe = QBE.using(entityManager)

// sets the target JPA entity to query
query = qbe.query(Entity.class)

Then you have a QBEQuery

// set the example criteria, an JPA entity or a POJO with matching field names
example = query.by(pojoOrEntityExample)

From which you get a QBEExample

// Use exact match, i.e. equals
example = example.use(Matching.EXACT)

// Match the start of a string. i.e. XXXX%
example = example.use(Matching.START)

// Match the middle of a string. i.e. %XXXX%
example = example.use(Matching.MIDDLE)

// Match the end of a string. i.e. XXXX%
example = example.use(Matching.END)

// conjunction i.e. AND
example = example.use(Junction.UNION)

// disjunction i.e. OR
example = example.use(Junction.INTERSECTION)

// order ascending by field with name
// (you can use either supply a java object field name or name of @Column annotation)
// multiple orderings are supported
example = example.orderBy("fieldName", Order.ASCENDING)
example = example.orderBy("columnName", Order.ASCENDING)

// order descending
example = example.orderBy("fieldName", Order.DESCENDING)
example = example.orderBy("columnName", Order.DESCENDING)

// execute query and fetch the result list
List<Entity> results = example.list()

// execute query and fetch a unique result
Entity result = example.item()

// get the underlying JPA TypedQuery object (for pagination etc)
TypedQuery<Entity> typedQuery = getQuery()

Examples

Below are more example usages:

// get a list of entities using an arbitrary pojo as example input
List<Entity> resultList =
    QBE.using(entityManager)
        .query(Entity.class)
        .by(new Pojo("foo"))
        .list();

// get a single result using an arbitrary pojo as example input
Entity item =
    QBE.using(entityManager)
        .query(Entity.class)
        .by(new Pojo("foo"))
        .item();

// get and work with the underlying TypedQuery object (useful e.g. for paging)
TypedQuery<Entity> query =
    QBE.using(entityManager)
        .query(Entity.class)
        .by(new Pojo("foo"))
        .getQuery();
List<Entity> resultList =
    query
        .setFirstResult(0)
        .setMaxResults(10)
        .getResultList();

// override the Matching logic (defaults to EXACT)
QBE.using(entityManager)
    .query(Entity.class)
    .by(new Pojo("foo"))
    .use(Matching.EXACT);
QBE.using(entityManager)
    .query(Entity.class)
    .by(new Pojo("foo"))
    .use(Matching.START);
QBE.using(entityManager)
    .query(Entity.class)
    .by(new Pojo("foo"))
    .use(Matching.MIDDLE);
QBE.using(entityManager)
    .query(Entity.class)]
    .by(new Pojo("foo"))
    .use(Matching.END);

// override the Junction for multiple fields (defaults to UNION)
List<Entity> resultList =
    QBE.using(entityManager)
        .query(Entity.class)
        .by(example)
        .use(Junction.INTERSECTION)
        .list();

// define the ordering of the result
List<Entity> resultList =
    QBE.using(entityManager)
        .query(Entity.class)
        .by(new Pojo("foo"))
        .use(Matching.START)
        .orderBy(fieldToOrderBy, Order.ASCENDING)
        .list();

// define multiple fields for ordering semantics
List<Entity> resultList =
    QBE.using(entityManager)
        .query(Entity.class)
        .by(new Pojo("foo"))
        .use(Matching.START)
        .orderBy("firstName", Order.DESCENDING)
        .orderBy("lastName", Order.ASCENDING)
        .list();

Get it

To get started using QBE, just clone and build the project:

git clone git://github.com/kenglxn/QueryByExample.git
cd QueryByExample/
mvn clean install
and then add QBE as a dependency in your project
<dependency>
    <groupId>net.glxn</groupId>
    <artifactId>qbe</artifactId>
    <version>1.2</version>
</dependency>

If you don’t want to clone and build yourself, simply grab the jars from here

Then just run maven to install them into your local repo:

mvn install:install-file -Dfile=qbe-1.2.jar -DgroupId=net.glxn -DartifactId=qbe -Dversion=1.2 -Dpackaging=jar -DgeneratePom=true
comments powered by Disqus

Moar stuffs

12 Sep 2017 Building an executable WS client using maven and metro
07 Jun 2015 Deploy an Ember app to gh-pages using npm run-script
06 Jun 2015 JSON Contract testing using unit tests to assert full stack integration across REST services
03 May 2015 simple http serve a directory from terminal
07 Jan 2014 civu, a CLI for cloning git repositories from jenkins views
06 Jan 2014 PyramidSort, a Sublime Text plugin for for reformatting text
05 Jan 2014 Git commit-message hook for JIRA issue tags
31 May 2013 hacking kitchen tiles with coffeescript
30 May 2013 Nuke, ps grep kill something
24 May 2013 mvnr: recursive mvn command runner
23 May 2013 Query By Example for JPA
22 May 2013 gitr: recursive git command runner
21 May 2013 Keeping gh-pages branch in sync with master
19 May 2013 Migrated from wordpress to jekyll and github pages
14 Aug 2012 Using Sublime Text 2 as git commit message editor
10 Mar 2012 QRGen, a small wrapper on top of ZXING for generating QRCodes in java
04 Jan 2012 My Bash PS1 with git branch info
17 Aug 2010 Making a swing project using IntelliJ IDEA GUI builder with maven, Including executable jar
01 May 2010 Using Arquillian to test against a remote jboss container from within IDEA
06 Apr 2010 WELD/CDI lightningtalk from Know IT 2010 annual conference
03 Apr 2010 Solving Sudoku using java swing and junit
01 Mar 2010 Simple CDI/WELD login example
01 Mar 2010 Implementing @RequestParam in CDI/WELD using Qualifier and InjectionPoint as @HttpParam
01 Nov 2009 Seam Maven Refimpl