Spring Data JPA + QueryDSL: Taking the Best From Both Worlds
An example of combining the two most powerful frameworks for building a maintainable and type-safe persistence layer; from Spring Data JPA we will take only CRUD operations and for all complex queries we will use QueryDSL.
Join the DZone community and get the full member experience.Join For Free
Here we will concentrate only on the building persistence layer. As we deal with the relational database we will rely on the JPA specification. Let’s take a simple entity model like Author/Book as an example:
Here we are using Lombok to reduce boilerplate code in the mapping.
Now let's create our repositories for the entities:
First of all, we will introduce the
BaseRepository interface and extend the Spring Data JPA interface
Here we can add any methods from the JPA specification that are absent in the Spring Data
JpaRepository class. Also, I find method
findByIdMandatory super-useful as I expect (in most cases) the entity to be present in the DB in case of searching by ID.
Take a note that we marked the interface as @NoRepositoryBean for disabling the automatic method implementation feature of the Spring Data (this will also work for all child interfaces as we did not enable @EnableJpaRepositories annotation explicitly in the application).
Then the entity repository interfaces look as follows:
Now it is time to look at the implementation.
Let's start with the
Here is an extension of the Spring Data JPA implementation
SimpleJpaRepository that gives us all CRUD operations + our own custom operations (like
You can notice a few lines of code that do not belong to the Spring Data packages:
The above lines are part of the QueryDSL library.
We will talk about the generated class first, but before that let's see
BookRepositoryImpl that does not have any additional methods.
That’s it. Simple as that. Just provide the entity type + id type to the base class. (Spring framework will inject Entity Manager automatically so no need to specify @Autowired annotation — starting from Spring 4.3).
QueryDSL Generated Classes
Now let's come back to the QueryDSL generated classes. It is a part of the framework that allows you to write type-safe queries in a SQL-similar way.
First, we need to bring the necessary dependencies and enable the plugin for a class generation. There is an example based on the Maven build tool:
apt-maven-plugin is responsible for finding all JPA Entity classes and generating appropriate
Qclasses in the generated-source folder:
Now let's see what we can do with QueryDSL.
Let's assume we need to find the author by email ignoring the case sensitivity:
And there is the executed SQL query:
QueryDSL will automatically convert the input parameter of the method to the lower case and use the
lower() SQL function on the query.
If we need to execute a more complex query and count books size by each author we can do it like this:
And there is the built SQL query:
As you can see, we retrieve only the information which is needed: author name and book count.
It is done by one of the best features of QueryDSL — Projection.
We can map the query result to the custom DTO class directly:
The last example will be dealing with ‘N+1 problem’. Let's assume that we need to retrieve all authors with their books. Using QueryDSL we can specify
And here is the SQL result:
As you can see only one query executed. All books for the authors will be loaded and there will be no additional subselects or LazyLoadingExceptions.
distinct() operator was used in the original query for removing author duplicates from the result.
If you enjoy the article and would like to see the practices and frameworks for testing the persistence layer you can check my next article here.
Published at DZone with permission of Dmytro Stepanyshchenko. See the original article here.
Opinions expressed by DZone contributors are their own.