Spring Data JPA + QueryDSL. Taking best from both worlds

In this article you will find an example of combining the two most powerful frameworks for building 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.

You can find the full project on GitHub using this link. There is a simple SpringBoot application with configured MySQL datasource and initial database structure described as a Flyway migration.

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:

ER Diagram

Here we are using Lombok to reduce boilerplate code in the mapping.

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 Spring Data JPA interface JpaRepository:

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 BaseRepositoryImpl class:

Here is an extension of the Spring Data JPA implementation SimpleJpaRepository that gives us all CRUD operations + our own custom operations (like findByIdMandatory).

You can notice a few lines of code that do not belong to the Spring Data packages:

protected final QAuthor author = QAuthor.author;    
protected final QBook book = QBook.book;

and

this.queryFactory = new JPAQueryFactory(em);

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).

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 class generation. There is an example based on the Maven build tool:

Necessary Dependencies

Plugin for class generation

The 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:

select author0_.id as id1_0_, author0_.email as email2_0_, author0_.full_name as full_nam3_0_ from author author0_ where lower(author0_.email)='stephen.king@email.com' limit 1

QueryDSL will automatically convert the input parameter of the method to the lower case and use 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:

select author0_.full_name as col_0_0_, count(books1_.id) as col_1_0_ from author author0_ inner join book books1_ on author0_.id=books1_.author_id group by author0_.full_name

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 feature of QueryDSL — Projection.

We can map the query result to the custom DTO class directly:

No need to handle raw types like Tuple or Map. We can map the result directly to the DTO classes.

The last example will be dealing with ‘N+1 proble’. Let's assume that we need to retrieve all authors with their books. Using QueryDSL we can specify fetchJoin():

And here is the SQL result:

select distinct author0_.id as id1_0_0_, books1_.id as id1_1_1_, author0_.email as email2_0_0_, author0_.full_name as full_nam3_0_0_, books1_.author_id as author_i4_1_1_, books1_.iban as iban2_1_1_, books1_.name as name3_1_1_, books1_.author_id as author_i4_1_0__, books1_.id as id1_1_0__ from author author0_ inner join book books1_ on author0_.id=books1_.author_id

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.