Despite the current trend regarding &nbps;
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
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](/assets/resources/hibernate-query-by-example/search-form.png)
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,
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 |
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:
- Creating an instance of the entity
- For each field that had been filled, filling in the corresponding attribute
- Making an example out of the instance
- 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.