Tuesday, December 28, 2010

Building queries ... the easy way

On a past project, users needed to be able to create a custom query and execute it. To do this, the user was able to select a field, an operator and select or fill in a value. If, for example, we were searching for people living in Belgium, the user would select person.address.country for the field, like for the operator and fill in "BE" for the value. I'm sure you all know how the resulting SQL would look like.

There are different ways to create a query builder. If you're using Hibernate, the above example could easily be translated to HQL using the criteria API. In some cases, however, the resulting SQL is not exactly what you wanted (e.g. not performing well) or maybe Hibernate is just not capable generating a correct SQL. Unfortunately, at that time, we discovered we were suffering both aforementioned problems, so the only solution was to create our own query builders using string concatenation. Yes, this can get ugly really fast, when not being careful, but I managed to create something that works and looks nice ... more or less.

On a more recent project, users were also capable of creating custom queries. Instead of re-using parts of the query builders written in the past, I decided to ask Google for help. Surely there had to be someone or some project out there that came across the same issues I was having with Hibernates criteria API. During my search, I came across querydsl which seemed to do what I wanted to accomplish.

querydsl comes in different "flavours". You can use it to query your database using HQL or SQL. You can even use it to query objects in a simple collection (similar to lambdaj I guess). To do this, querydsl uses so called "Q" entities. So, if I'm querying a person and its addresses, I would have a QPerson and a QAddress. These entities can be generated from your JPA annotated domain model, or can be reverse engineered from your database. Of course, you can generate these "Q" entities by hand, since it's a very clean and easy to understand API. Once you have your "Q" entities in place, you can start querying the database in a very fluent API, like this: query.from(person).join(address).on(person.id.eq(address.person)).where(address.country.like("BE")). This API is also type-safe (HQL or criteria aren't), so you won't be querying a person's name using it's birth date :)

The only remaining difficulty was to translate the criteria sent by the user interface to something understood by querydsl. To do this, I created something that "observes" the creation of the "Q" entities. When a QPerson has a field "firstName", the observer knows this. The observer also knows there is a relation between the QPerson and the QAddress entities expressed by the relation person.address. In the end, the observer holds a complete mapping between the fields available in my "Q" entities and their actual names (e.g.: StringPath("FIRST_NAME") maps to person.firstName). Once you understand the insides of querydsl, this isn't very hard to create. Finally, you just need to merge this mapping with the criteria sent by the user interface and your query is ready to be executed against the database.

I wish I could publish the code written on this project, but the nature of the project doesn't allow me to do so. In this post, I just wanted to tell you something about querydsl and how easy it is to plug this into an existing project. It's also very extendible and has a very active community and core developer. Should you have any questions on this, please leave a comment.


Tim Schraepen said...

In any case this querydsl is much more readable than the JPA2 Criteria API.
And I love the chaining.
Something worth considering on my next project!
Thanks for the post, hope you're doing well :)

kennywest said...

I'm glad someone is still reading this blog :)

Timo Westkämper said...

Nice blog post! Great to hear that also the internals of Querydsl are fairly understandable and accessible.