Query DSL Overview

This post is the continuation of my previous post on spring data JPA. In this post I would be delving deeply into query DSL.Query DSL is an advanced feature provided by spring data JPA to query data from database. It has less code so less to maintain.By using query DSL one can check query at start up rather than at runtime. I am going to discuss key and concepts require to learn DSL and would learn through example. We will extending the same promo-api that we wrote in last post and would be writing Junit to verify query.
DSL stands for a domain specific language and is a customized extension of a software programming language that addresses a specific business or domain. In case of spring data JPA this means the framework is enhancing Java to be better suited for creating and working with JPA query. The spring data JPA query DSL is simply all about finding terms and syntax to work with JPA query more efficiently.
To demonstrate how query DSL works ,let us take an example to explain-
If I asked to write a query to fetch the list of promotion by name what do you think the query would look like, it's going to be a simple select query : select * from promotion where name like '%BuyX%'.Now how the same can be written in DSL, that's pretty simple you just have to write a custom finder method in your custom JpaRepository interface as below.Run your promo-api and open H2 console with below query-

Now create a finder method in Promotion JPA repository-

To test the above finder method is working or not let's write a unit test as below-

By query in H2 database with like there was two result ,we are testing the same here. You can see it's a green means our finder method is working fine.
Exactly that is what you need to write a custom finder methods.You can see that method is starting with 'findBy' denoting select query ,'Name' is nothing but an attribute in PromotionType model and 'like' is nothing but sql like statement. This one method gives a simple select query with like statement that we wrote above.Since we are expecting list of PromotionType with select query that is why finder methods is returning list.

Query Methods Syntax

Now that we have created one simple custom finder method but what is happening behind the scene.Internally spring data JPA implements the custom finder method and generates a relevant query that matches exactly the task that you want to perform by writing finder methods. The way you denote the methods say it all to spring data JPA to create a query on behalf of you. Now the question is how do we write custom methods. Well ,we have some set of rules to write a custom method in your custom Jpa Repository.

• Spring data JPA has query parser will look for the following : find..By ,query..By ,read..By ,count..By ,get..By
• Criteria uses JPA entity attribute names to filter out results
• Multiple criteria can be combined with ["And" ,"Or"]
• The return type of custom methods are determined by the entity that we pass in JpaRepository interface. It could be a single entity ,a list of entity ,a boolean to see if records exits or an integer to verify method like count.

There are multiple keywords which are used in query DSL for writing custom method in Jpa repository interface.I am going to discuss each of them separately with a example. So let's start discussing keywords -

And and Or

The And and Or keywords are exactly like And and Or keywords in JPQL and Sql. It is used to combine multiple criterion query filters together using a conditional And or Or. Let's go and create a custom method using these keywords in PromotionRepository interface. If you query Promotion table this is how it looks-

Create a custom finder method in PromotionRepository -

Modify PromotionRepositoryTest class to test the above finder method and run as Junit-

The green shows that all test cases passed that means our query is working fine. Behind the scene the spring data jpa query parser forms a JPQL query with where clause-
select * from promotion p where p.name =?1 and p.startdate =?2
And select * from promotion p where p.name =?1 or p.startdate =?2.

Equals ,Is and Not

Equals and Is keywords are equivalent to '=' and it is used when comparing the criteria with filter value. Not keyword is used to compare not equals. The equivalent JPQL query would look like select * from promotion p where p.name =?'. There are many ways to write a custom finder by using these keywords-

• findByName(String name) : Query to find by name in given method parameter
• findByNameIs(String name) : Query to find by name is in given method parameter
• findByNameEquals(String name) : Query to find name equals given method parameter
• findByNameNot(String name) : Query to find name not in given method parameter
We can also combine these keywords to form methods -
• findByNameIsOrStartdateEquals(String name ,String startdate) : Query to find name is given in method parameter or equals to startdate equals to in given method parameters.

Like or NotLike

This keywords are useful when trying to match or not match ,a portion of criteria filter value. It is empowered by SQL 'like' keyword when you are trying to match a portion of value.The custom finder method would look like following-

• findByNameLike("Buy%") : This will try to match all the records starting with name as 'Buy'.
• findByStateNotLike("Buy%") : This will give all the records not staring with keyword 'Buy'.

The equivalent JPQL query would look like- select * from promotion p where p.name like 'Buy%'.

StartingWith, EndingWith and Containing

It is similar to 'like' keyword except '%' is automatically added to the filter value.Finder methods can be formed as below-

• findByNameStartingWith("Buy") :It will match all the name having started with 'Buy'.
• findByNameEndingWith("GetY") :It will match all the name having ended with 'GetY'.
• findByStateContaining("X") :It will fetch all records with containing value 'X' in name attribute.

The JPQL equivalent query would be 'select * from promotion p where p.name like '%X%' for findByStateContaining("X") method.

LessThan or LessThanEqual and GreaterThan or GreaterThanEqual

The above keywords are used when you need to perform a < ,<= ,> or >= comparison with number data types.We can also combiner these keywords using And or Or keyword.Let us consider that you have OrderItem entity having price attribute defined in it, in that case the custom finder method would look like-

• findByPriceLessThan(20) :It will give all records having price less than 20.
• findByPriceLessThanEqual(20) :It will give all records having price less than equals to 20.
• findByPriceGreaterThan(30) :Resulting out all records having price greater than 30.
• findByPriceGreaterThanEqual(30) :Resulting out all the records having price greater than equals to 30.
• findByPriceGreaterThanEqualAndLessThanEqual(20 ,30) :This will give all records in which the price is greater than equals to 20 and less than equals to 30.

The JPQL equivalent for this would be 'select * from OrderItem o where o.price>=20 and o.price <=30' for findByPriceGreaterThanEqualAndLessThanEqual(20 ,30) method.

Before, After and Between

This is used when you need to perform a less than, greater than and range comparison with date/times data types. Consider the date attribute of Promotion entity ,the finder method would be formed as below-

• findByStartDateBefore(dateObject) : This is to find all records having date before than the dateObject given in parameters.
• findByStartDateAfter(dateObject) : It gives all records having date after than the dateObject given in parameters.
• findByStartDateBetween(startDate ,endDate) : It gives all records having date is in between startDate and endDate given in parameters.

The corresponding JPQL query for findByStartDateBetween(startDate ,endDate) is select * from promotion p where p.startdate between startDate ?1 and endDate ?2.

True and False

It is useful when comparing boolean value with true and false. Consider an Order entity having active as an attribute to define whether order is active or not ,the corresponding finder method will be-

• findByActiveTrue() : Returns all records by identify if the active column is true .
• findByActiveFalse() : Returns all records by identify if the active column is false.

The JPQL query would be select * from order o where o.active=true .

IsNull, IsNotNull and NotNull

These keywords are used to check whether a criteria value is null or not null. The finder method would look like -

• fineByNameIsNull() : It will return all records having name is null.
• fineByNameIsNotNull() : It will return all records having name is not null.
• fineByNameNotNull() : It will return all records having name is not null.

The JPQL query form would look like - select * from Promotion p where p.name is not null .

In and NotIn

It can be used when you need to test if a column value is part of a collection or set of values or not. To demonstrate finder method consider an Order table having status column. The finder method would look like -

• findByStatusIn(Collection status) : Gives list of result that satisfy given collection of status
• findByStatusNotIn(Collection status) : Gives list of result that not present in collection of status

The JPQL query would be 'select * from Order o where o.status in ?'

IgnoreCase

This can be used when you need to perform a case insensitive comparison. The finder method can be written as -

• findByNameIgnoreCase("buy") : Returns all records by ignoring the case given in the parameters.
• findByNameStartingWithIgnoreCase("buy") : Returns all records by ignoring the case and starting with string given in the parameters.

The JPQL : select * from Promotion p where UPPER(p.name) =UPPER(?1) .

OrderBy

This is used to set up order by clause on your query. The corresponding finder method would look like -

• findByNameOrderByDescriptionAsc() : For fetching all records by name in ascending order.
• findByNameOrderByDesc() : For fetching all records by name in descending order.

The JPQL : 'select * from Promotion p where p.name OrderBy Asc'

First, Top and Distinct

These keywords are used to limit the results returned by the query. The finder methods would look like -

• findFirstByNameLike("Bu") : To fetch first records satisfying name like 'Bu'.
• findTop5ByNameLike("Bu") : To fetch first 5 records satisfying name like 'Bu'.
• findDistinctByNameLike("B") : To fetch all distinct records satisfying name like 'B'.

JPQL query : select * from Promotion p where p.name like ?1 limit 5 .
select distinct name from Promotion p where p.name like ?1 . We have covered most of keywords used in query DSL and the way to write a custom finder method in custom repository interface. I have given few example as well. You can take that reference of given example and write same by using any IDE of your choice to see if it's working fine. If you have any doubt you can shoot me by commenting in comment section. I will cover some advance query in my next post as my complete focus on this post was to cover everything about query DSL. Thanks for reading. As spring data JPA is a very vast topic hence I have covered it by multiple post. In order to learn and understand spring data JPA completely you need go through each post. Below mentioned order you must follow so that you won't miss anything-

• Getting started with spring data JPA .
• Query DSL Overview
• Spring Data JPA and @Query Annotation
• Spring Data JPA Paging, Sorting, Custom Repository, Auditing and Locking