Dynamic Queries with Querydsl-JPA
Pre-Intro
Pssstt, hey you, yes you! How do you make database queries from your java application? Lemmi guess, you either use native queries i.e. (“select * from books”)or you use Spring Data JPA’s methods such as findAll(),findByStatus() etc. So, here is the challenge of using these two methods
1. For native queries, you are prone to making errors/typos and writing dynamic queries could be a problem
2. For JPA methods, well, it has some form of type-safety in it but it might be difficult to write complex dynamic queries
What if I told you that you can mash-up the two and end up with a powerful tool. Enter QueryDsl!!!!
Introduction
Querydsl provides a typesafe querying layer on top of JPA, JDO, JDBC and other backends. It maps your domain models to generated typesafe classes called Q-Classes (since they are preceded with letter ‘Q’). The advantage of QueryDsl is, it provides syntax similar to SQL for querying therefore it’s not a steep learning curve for beginners especially with a background in SQL.
(For sure that intro was a bit of a mouthful, I agree. Let me explain in a very simple way)
Simplified Intro
Say you have the following entity (domain model), Cat
@Entity
@Table(name = "cat")
public class Cat { @Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name; private int age; // Getters and setters}
A plugin, as will be explained later, will generate some classes that we call ‘Q’-classes. So you will have a QCat class that is just the name of your entity preceded with a ‘Q’ in some folder generated-sources . These Q-classes will be used in the querying process. See the following example with it’s associated SQL query.
QCat qcat = QCat.cat; // Remember the QCat was generated by the
// querydsl pluginJPQLQuery<?> query = new JPAQuery<>(entityManager); // To be
// explained
// later
// Let's query
List<Cat> catList = query.select(qcat)
.from(qcat)
.where(qcat.name.like("%kitty%"))
.offset(0)
.limit(10)
.orderBy(qcat.id.desc())
.fetch();// The native SQL Query
select * from cat
where name like '%kitty%'
order by id desc
limit 0, 10;
See how easy that was!! Notice how similar-like the querying is to SQL? Let’s now setup and get to learn more on this awesome library.
Setting up
Let’s set up, shall we. We are going to use the popular framework — Spring Boot, MySql and Maven as our build tool.
In the pom.xml we’ve included a starter pack for JPA and web but of most importance is the QueryDsl dependencies querydsl-apt and querydsl-jpa. Also, still in the pom.xml we require a plugin, under build->plugin XML tags, for generating Q-classes for you domain models. What it does it scans for all classes annotated with @Entity and generates Q-classes related to your model and generates them to the specified folder outputDirectory, of which in our case is the project build directory in the generated-sources folder. These Q-classes are of great importance when it comes to querying as we will see later in this article.
Domain Models
Let’s create our domain models. We only have three for this example: Student, Subject and a many-to-many resolver StudentSubject (since a student can take many subjects and a subject can be taken by many students)
Here is our entity relationship
… and the models are as below:
There goes our domain models, simple and straight forward POJOs.
So let’s generate our Q-classes from these entities.
Generating Q-Classes with Different IDEs
Maven
(Yeah, yeah, I know Maven is not an IDE but lemmi just squeeze it in here)
To generate Q-classes with maven all you have to do is build as you’d do it normally mvn clean package. The Q-classes will be generated to the specified folder in the pom.xml .
Intellij
With Intellij you can go to the buildmenu and select Build Project as shown in the image below
or you can select Generate Sources and Update folders under the Maven side bar on the top right hand corner of your IDE
The generated sources will appear as below
If by any chance Intellij cannot find the Q-classes through code completion, go to File>Project Structure menu. A dialog appears and select Modules as below and ensure generated-sources is a Sources type. So you just click on the folder generated-sources and then click Sources at the top and you’ll make your generated-sources folder a Sources folder.
Netbeans
For Netbeans it’s really straight forward. Just build your project and voila, your Q-classes will be generated.
Business Logic
So, we have our domain models and our Q-classes. So, why don’t we go ahead and write our business logic, shall we…
In our first case lets write a simple query to fetch all Students whose firstname or lastname contains a specified string.
In order to do this we need to @Autowire an EntityManager in our @Service class (Business logic class). Then we create a method that does our query.
In our method, we need to instantiate a JPQLQuery, an interface, which we will use for the querying process and instantiate it to a JPAQuery which expects an EntityManager in it’s constructor.
JPQLQuery<?> query = new JPAQuery<>(entityManager);So, why create a JPQLQuery(interface) type whereas you can just create a JPAQuery(concrete class) type. Well, just peruse the internet and good design requires us to program to interface and not to concrete class. For example when instantiating a List, this is what we do List myList = new ArrayList(). Rarely do you find one doing this: ArrayList myList = new ArrayList(); (this code just smells).
Now that we have our querying object, we need to instantiate our Q-classes which we will use together with our querying object to write queries almost similar to SQL. Since these Q-classes are already generated for us, let’s go ahead and instantiate a QStudent object using it’s public field.
QStudent qstudent = QStudent.student;Now that we have all we need, let’s go ahead and construct the query. query.select(qstudent) — similar to select * from ...
.from(qstudent) — similar to ... * from Student
.where(qstudent.firstname.like("%" + str + "%").or(qstudent.lastname.like("%" + str + "%")))— similar to ... where(firstname like '%val%' or lastname like '%val%'.
.fetch() — terminates our query and requests for the results.
Pretty straight forward, right?
Let’s go ahead and test. I have added the following data in my student table:
I created a simple Controller to help me test using the popular tool Postman.
… and here are the results when I fetch using ‘d’ and ‘j’ as the search string:
Let’s go ahead and write another query to count the number of students taking a particular subject using the data below:
… and using Postman using parameters ‘Maths’ and ‘Chemistry’ returns the correct results 2 and 1 respectively.
Finally, let’s write a query that changes at runtime based on what the user prefers. We are going to write a query that fetches all students whose firstname or lastname is equal to the string passed. The user will determine which column to search from by passing a letter i.e. f for firstname and l for lastname .
And here are the tests done on Postman:
Remarks
We have learnt how to include QueryDsl into our project, generate Q-classes and use the Q-classes generated in querying.
We can do all sorts of querying similar to SQL such as avg, max, min, joins, order by, group by and so on and so fourth. The list is endless.
Cons
QueryDsl JPA is a great and simple tool for querying but has some drawbacks which QueryDsl SQL has provided solutions for. For example, QueryDsl JPA cannot be used in querying unrelated tables using joins since a path is required from one Q-class to another.
As much as QueryDsl is a great tool for querying, things can get a little bit messy when it comes to very complex queries (say, analytics queries) because it makes your code even more complex.
Conclusion
The choice of an ORM or querying tool lies entirely on the user there being numerous technologies out there such as: Hibernate, Criteria Api, Query Dsl/Sql, JOOQ to name just a few. But QueryDsl JPA has proven to be a great tool that has type safety and really works well with dynamic queries. As a user it’s your obligation to check them out and choose the one that best suites you.
