/ HIBERNATE, QUERY, JPA, PERSISTENCE

Hibernate's Query-By-Example

Despite the current trend regarding &nbps;ORM frameworks, I never had any issue using JPA nor Hibernate. I must admit that my use-cases were pretty simple. Also, since that was already some years ago, we hosted those applications on our own on-site infrastructure so that the search for the ultimate performance - and its associated savings - was not a goal; empowering junior developers who were not familiar with a lot of technologies was.

Interestingly enough, Hibernate actually predates JPA, so that the implementation boasts more features than the specification: it was very obvious in JPA 1.0, much less so in 2.0. Yet, regardless how small the gap is, it exists. That’s a pity because Hibernate offers really good APIs in some areas that JPA doesn’t match. One of such area is querying, and its associated feature is Query-By-Example. This post is dedicated to describe the context for such a use-case, and how QBE helps to solve it easily.

A simple search form

Imagine a Person entity, with several attributes e.g. first name, last name, birthdate, etc. Now, imagine a form that allows to search for Person entities, and a search field for each "public" attribute.

A simple Entity search form

This is a pretty widespread requirement for business apps.

Let’s see how this can be implemented using:

Querying with JPQL

Back in JPA 1.0, JPQL was the only option to execute queries. It is still available up to the current version (2.1), and borrows heavily from SQL syntax.

For example, selecting all fields from all entities in the PERSON table translates into the following JPQL query:

SELECT p FROM Person p

Adding a search criterion is likewise very similar to SQL:

SELECT p FROM Person p WHERE firstName = 'Doe'

Implementing the previous search form using JPQL requires:

  • to check for every search field if it’s been filled
  • if yes, to create the where part
  • and eventually to concatenate all where predicates

This evaluates to something like the following:

@WebServlet("/jpql")
class JpqlServlet : HttpServlet() {
  override fun doPost(req: HttpServletRequest, resp: HttpServletResponse) {
    doAndDispatch(req, resp) { firstName, lastName, birthdate, em ->        (1)
      val select = "SELECT p FROM Person p"
      val jpql = if (firstName.isBlank()                                    (2)
                 && lastName.isBlank()
                 && birthdate == null) select
      else {
        val where = "$select WHERE"                                         (3)
        var expression = where
        if (firstName.isNotBlank())
          expression += " firstName = '$firstName'"                         (4)
        if (lastName.isNotBlank()) {
          if (expression != where)                                          (5)
            expression += " AND"                                            (6)
          expression += " lastName = '$lastName'"
        }
        if (birthdate != null) {
          if (expression != where)
            expression += " AND"
          expression += " birthdate = '$birthdate'"
        }
        expression
      }
      val cq = em.createQuery(jpql)
      cq.resultList
    }
  }
}
1 Common logic getting the request parameters, exposing the JPA entity manager and setting the persons list as a request attribute for the page to display
2 If no criteria has been set, the query string is just a simple SELECT with no WHERE clause
3 On the contrary, if at least one criterion has been set, add a WHERE clause
4 Add the attribute’s name and value to the WHERE clause
5 Check if a previous criterion has been set
6 If a previous criterion has been set, append an AND clause

For curious readers, here’s the code for the doAndDispatch() function. It will be used in alternative implementations as well.

private fun doAndDispatch(req: HttpServletRequest,
                  resp: HttpServletResponse,
                  f: (String, String, LocalDate?, EntityManager) -> List<*>) {

  fun String.toLocaleDate() : LocalDate? = if (this.isBlank()) null
  else LocalDate.parse(this)

  val firstName = req.getParameter("firstName")
  val lastName = req.getParameter("lastName")
  val birthdate = req.getParameter("birthdate")?.toLocaleDate()
  val em = Persistence.emf.createEntityManager()
  val persons = f(firstName, lastName, birthdate, em)
  req.setAttribute("persons", persons)
  req.setAttribute("firstName", firstName)
  req.setAttribute("lastName", lastName)
  req.setAttribute("birthdate", birthdate)
  req.getRequestDispatcher("/WEB-INF/persons.jsp").forward(req, resp)
}

The Criteria API

Obviously, using JPQL for this use-case is complex and error-prone: this is the problem with non-typesafe API e.g. String concatenation. To cope with that, JPA 2.0 introduced the Criteria API, which is offers a strongly-typed API.

The above SELECT query can be replaced with the following:

val cq = em.criteriaBuilder.createQuery(Person::class.java)
cq.from(Person::class.java)
val typedQuery = em.createQuery(cq)
typedQuery.resultList

While it actually requires more code for queries with no WHERE clause, the Criteria API is much better when there are criteria:

val cq = em.criteriaBuilder.createQuery(Person::class.java)
val person = cq.from(Person::class.java)
cq.where(em.criteriaBuilder.equal(person.get<String>("lastName"), "Doe"))
val typedQuery = em.createQuery(cq)
typedQuery.resultList

Regarding the search form use-case, one can see the benefit of using the Criteria API. The logic of evaluating whether a field has been filled stay the same, but adding a criteria becomes much easier:

doAndDispatch(req, resp) { firstName, lastName, birthdate, em ->
  val cq = em.criteriaBuilder.createQuery(Person::class.java)
  val person = cq.from(Person::class.java)
  var predicates = listOf<Predicate>()
  if (firstName.isNotBlank())
    predicates = predicates +
      em.criteriaBuilder.equal(person.get<String>("firstName"), firstName)
  if (lastName.isNotBlank())
    predicates  = predicates +
      em.criteriaBuilder.equal(person.get<String>("lastName"), lastName)
  if (birthdate != null)
    predicates = predicates +
      em.criteriaBuilder.equal(person.get<LocalDate>("birthdate"), birthdate)
  cq.where(*predicates.toTypedArray())
  val query = em.createQuery(cq)
  query.resultList
}
To enforce even stronger typing, it’s possible to generate JPA meta-model. Since the generation of the meta-model is not covered by the specification, please check the documentation of your ORM implementation.

Beyond JPA, the Query-By-Example feature

In Hibernate 4.x, a nifty feature named Query-By-Example was a perfect match for the search use-case. To use it was just a matter of:

  1. Creating an instance of the entity
  2. For each field that had been filled, filling in the corresponding attribute
  3. Making an example out of the instance
  4. And of course executing the query

The corresponding code looks like:

doAndDispatch(req, resp) { firstName, lastName, birthdate, em ->
  val session = em.delegate as Session                                            (1)
  val person = Person(firstName = if (firstName.isBlank()) null else firstName,   (2)
    lastName = if (lastName.isBlank()) null else lastName,
    birthdate = birthdate)
  val example = Example.create(person)                                            (3)
  val criteria = session.createCriteria(Person::class.java).add(example)
  criteria.list()
}
1 The EntityManager needs to be cast to a proprietary Hibernate’s Session, because only the later provides the QBE feature
2 null means the attribute won’t be part of the WHERE clause. For this reason, empty strings need to be set to null
3 Create the example from the entity

Note that while QBE is extremely powerful, it comes with some limitations. As seen above, one needs to set empty fields to null. Therefore, since the sample is in Kotlin, the entity class attributes had to be updated from non-nullable type to nullable type.

@Entity
class Person(@Id @GeneratedValue var id: Long? = null,
             val firstName: String?,
             val lastName: String?,
             val birthdate: LocalDate?)

Alternatively, one could create a dedicated class for solely the purpose of QBE.

If one has more requirements, then it’s always possible to fallback to the Criteria API seen above. For all I know, QBE does one thing, but it does it well.

Conclusion

As seen in this post, QBE is a very useful feature when implementing simple queries on entities. I was very surprised when I recently noticed that Hibernate 5.x had deprecated this feature! From what I’ve read, the rationale is that since the team is not large enough, it’s better to implement the JPA specifications than to provide proprietary features, regardless of their value.

It remains to be seen whether QBE makes it to the next JPA version. IMHO, it would be a shame not to add it - or to remove it from Hibernate entirely.

The complete source code for this post can be found on Github in Maven format.
Nicolas Fränkel

Nicolas Fränkel

Developer Advocate with 15+ years experience consulting for many different customers, in a wide range of contexts (such as telecoms, banking, insurances, large retail and public sector). Usually working on Java/Java EE and Spring technologies, but with focused interests like Rich Internet Applications, Testing, CI/CD and DevOps. Also double as a trainer and triples as a book author.

Read More
Hibernate's Query-By-Example
Share this