DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

The software you build is only as secure as the code that powers it. Learn how malicious code creeps into your software supply chain.

Apache Cassandra combines the benefits of major NoSQL databases to support data management needs not covered by traditional RDBMS vendors.

Generative AI has transformed nearly every industry. How can you leverage GenAI to improve your productivity and efficiency?

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Related

  • Spring Data JPA - Part 1
  • Manage Hierarchical Data in MongoDB With Spring
  • Spring Data: Data Auditing Using JaVers and MongoDB
  • CRUD Operations on Deeply Nested Comments: Scalable Spring Boot and Spring Data approach

Trending

  • The Perfection Trap: Rethinking Parkinson's Law for Modern Engineering Teams
  • Immutable Secrets Management: A Zero-Trust Approach to Sensitive Data in Containers
  • Simplifying Multi-LLM Integration With KubeMQ
  • How to Ensure Cross-Time Zone Data Integrity and Consistency in Global Data Pipelines
  1. DZone
  2. Data Engineering
  3. Databases
  4. Spring Data JPA + QueryDSL: Taking the Best From Both Worlds

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.

By 
Dmytro Stepanyshchenko user avatar
Dmytro Stepanyshchenko
·
Updated Apr. 18, 21 · Tutorial
Likes (5)
Comment
Save
Tweet
Share
28.7K Views

Join the DZone community and get the full member experience.

Join For Free

You can find the full project on GitHub using this link. There is a simple SpringBoot application with configured MySQL data source 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:

Author Book Example

Entity Relationship Diagram.

Java
 




x
45


 
1
package com.example.entity;
2

           
3
import lombok.Getter;
4
import lombok.Setter;
5
import lombok.ToString;
6
import lombok.experimental.Accessors;
7

           
8
import javax.persistence.*;
9
import java.util.HashSet;
10
import java.util.Objects;
11
import java.util.Set;
12

           
13
@Getter
14
@Setter
15
@Entity
16
@Accessors(chain = true)
17
@ToString(onlyExplicitlyIncluded = true)
18
public class Author {
19
    @Id
20
    @ToString.Include
21
    @GeneratedValue(strategy = GenerationType.IDENTITY)
22
    private Long id;
23

           
24
    @ToString.Include
25
    private String email;
26

           
27
    @ToString.Include
28
    private String fullName;
29

           
30
    @OneToMany(mappedBy = "author")
31
    private Set<Book> books = new HashSet<>();
32

           
33
    @Override
34
    public boolean equals(Object o) {
35
        if (this == o) return true;
36
        if (o == null || getClass() != o.getClass()) return false;
37
        Author author = (Author) o;
38
        return Objects.equals(email, author.email);
39
    }
40

           
41
    @Override
42
    public int hashCode() {
43
        return Objects.hash(email);
44
    }
45
}



Java
 




xxxxxxxxxx
1
46


 
1
package com.example.entity;
2

           
3
import lombok.Getter;
4
import lombok.Setter;
5
import lombok.ToString;
6
import lombok.experimental.Accessors;
7

           
8
import javax.persistence.*;
9
import java.util.Objects;
10

           
11
@Getter
12
@Setter
13
@Entity
14
@Accessors(chain = true)
15
@ToString(onlyExplicitlyIncluded = true)
16
public class Book {
17
    @Id
18
    @ToString.Include
19
    @GeneratedValue(strategy = GenerationType.IDENTITY)
20
    private Long id;
21

           
22
    @ToString.Include
23
    private String name;
24

           
25
    @ToString.Include
26
    private String iban;
27

           
28
    @ManyToOne(fetch = FetchType.LAZY)
29
    @JoinColumn(name = "author_id")
30
    private Author author;
31

           
32
    @Override
33
    public boolean equals(Object o) {
34
        if (this == o) return true;
35
        if (o == null || getClass() != o.getClass()) return false;
36

           
37
        Book book = (Book) o;
38

           
39
        return Objects.equals(iban, book.iban);
40
    }
41

           
42
    @Override
43
    public int hashCode() {
44
        return iban != null ? iban.hashCode() : 0;
45
    }
46
}



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


Starting Repositories

Now let's create our repositories for the entities:

Creating Repositories

First of all, we will introduce the BaseRepository interface and extend the Spring Data JPA interface JpaRepository:

Java
 




xxxxxxxxxx
1
14


 
1
package com.example.repository;
2

          
3
import com.example.exception.DbResultNotFoundException;
4
import org.springframework.data.jpa.repository.JpaRepository;
5
import org.springframework.data.repository.NoRepositoryBean;
6

          
7
@NoRepositoryBean
8
public interface BaseRepository<T, ID> extends JpaRepository<T, ID> {
9
    T findByIdMandatory(ID id) throws DbResultNotFoundException;
10

          
11
    void clear();
12

          
13
    void detach(T entity);
14
}


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:

Java
 




xxxxxxxxxx
1


 
1
package com.example.repository;
2

          
3
import com.example.entity.Book;
4

          
5
public interface BookRepository extends BaseRepository<Book, Long> {
6
}



Implementation

Now it is time to look at the implementation.

Let's start with the BaseRepositoryImpl class:

Java
 




xxxxxxxxxx
1
41


 
1
package com.example.repository.jpa;
2

          
3
import com.example.entity.QAuthor;
4
import com.example.entity.QBook;
5
import com.example.exception.DbResultNotFoundException;
6
import com.example.repository.BaseRepository;
7
import com.querydsl.jpa.impl.JPAQueryFactory;
8
import org.springframework.data.jpa.repository.support.SimpleJpaRepository;
9

          
10
import javax.persistence.EntityManager;
11

          
12
public abstract class BaseRepositoryImpl<T, ID> extends SimpleJpaRepository<T, ID> implements BaseRepository<T, ID> {
13
    protected final QAuthor author = QAuthor.author;
14
    protected final QBook book = QBook.book;
15

          
16
    private final EntityManager em;
17
    protected final JPAQueryFactory queryFactory;
18

          
19
    public BaseRepositoryImpl(Class<T> domainClass, EntityManager em) {
20
        super(domainClass, em);
21
        this.em = em;
22
        this.queryFactory = new JPAQueryFactory(em);
23
    }
24

          
25
    @Override
26
    public T findByIdMandatory(ID id) throws DbResultNotFoundException {
27
        return findById(id)
28
                .orElseThrow(() -> {
29
                    String errorMessage = String.format("Entity [%s] with id [%s] was not found in DB", getDomainClass().getSimpleName(), id);
30
                    return new DbResultNotFoundException(errorMessage);
31
                });
32
    }
33

          
34
    public void clear() {
35
        em.clear();
36
    }
37

          
38
    public void detach(T entity) {
39
        em.detach(entity);
40
    }
41
}


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:

Java
 




x


 
1
------------------------------------------------
2
protected final QAuthor author = QAuthor.author;    
3
protected final QBook book = QBook.book;
4
------------------------------------------------
5
this.queryFactory = new JPAQueryFactory(em);
6
------------------------------------------------



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.


Java
 




xxxxxxxxxx
1
14


 
1
package com.example.repository.jpa;
2

          
3
import com.example.entity.Book;
4
import com.example.repository.BookRepository;
5
import org.springframework.stereotype.Repository;
6

          
7
import javax.persistence.EntityManager;
8

          
9
@Repository
10
public class BookRepositoryImpl extends BaseRepositoryImpl<Book, Long> implements BookRepository {
11
    public BookRepositoryImpl(EntityManager em) {
12
        super(Book.class, em);
13
    }
14
}



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:

XML
 




xxxxxxxxxx
1


 
1
<dependency>
2
    <groupId>com.querydsl</groupId>
3
    <artifactId>querydsl-jpa</artifactId>
4
</dependency>
5
<dependency>
6
    <groupId>com.querydsl</groupId>
7
    <artifactId>querydsl-apt</artifactId>
8
</dependency>


XML
 




xxxxxxxxxx
1
16


 
1
<plugin>
2
    <groupId>com.mysema.maven</groupId>
3
    <artifactId>apt-maven-plugin</artifactId>
4
    <version>1.1.3</version>
5
    <executions>
6
        <execution>
7
            <goals>
8
                <goal>process</goal>
9
            </goals>
10
            <configuration>
11
                <outputDirectory>target/generated-sources/java</outputDirectory>
12
                <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
13
            </configuration>
14
        </execution>
15
    </executions>
16
</plugin>



The apt-maven-plugin is responsible for finding all JPA Entity classes and generating appropriate Qclasses in the generated-source folder:

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:

Java
 




xxxxxxxxxx
1
17


 
1
@Repository
2
public class AuthorRepositoryImpl extends BaseRepositoryImpl<Author, Long> implements AuthorRepository {
3
    public AuthorRepositoryImpl(EntityManager em) {
4
        super(Author.class, em);
5
    }
6

          
7
    @Override
8
    public Optional<Author> findByEmailIgnoreCase(String email) {
9
        return Optional.ofNullable(
10
                queryFactory
11
                        .select(author)
12
                        .from(author)
13
                        .where(author.email.equalsIgnoreCase(email))
14
                        .fetchFirst()
15
        );
16
    }
17
}



And there is the executed SQL query:

MySQL
 




xxxxxxxxxx
1


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

Java
 




xxxxxxxxxx
1
20


 
1
@Repository
2
public class AuthorRepositoryImpl extends BaseRepositoryImpl<Author, Long> implements AuthorRepository {
3
    public AuthorRepositoryImpl(EntityManager em) {
4
        super(Author.class, em);
5
    }
6

          
7
    @Override
8
    public List<AuthorStatistic> findAuthorStatistic() {
9
        return queryFactory
10
                .from(author)
11
                .innerJoin(author.books, book)
12
                .groupBy(author.fullName)
13
                .select(Projections.constructor(AuthorStatistic.class,
14
                        author.fullName,
15
                        book.count())
16
                )
17
                .fetch();
18
    }
19

          
20
}



And there is the built SQL query:

MySQL
 




xxxxxxxxxx
1


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

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

Java
 




xxxxxxxxxx
1
11


 
1
package com.example.dto;
2

          
3
import lombok.AllArgsConstructor;
4
import lombok.Data;
5

          
6
@Data
7
@AllArgsConstructor
8
public class AuthorStatistic {
9
    private String authorName;
10
    private Long bookSize;
11
}



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 fetchJoin():

Java
 




xxxxxxxxxx
1
15


 
1
@Repository
2
public class AuthorRepositoryImpl extends BaseRepositoryImpl<Author, Long> implements AuthorRepository {
3
    public AuthorRepositoryImpl(EntityManager em) {
4
        super(Author.class, em);
5
    }
6
    
7
    @Override
8
    public List<Author> findAllWithBooks() {
9
        return queryFactory
10
                .select(author).distinct()
11
                .from(author)
12
                .innerJoin(author.books, book).fetchJoin()
13
                .fetch();
14
    }
15
}



And here is the SQL result:

MySQL
 




xxxxxxxxxx
1


 
1
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
2

          



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.

Conclusion

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.

Spring Data Data (computing) Spring Framework Database Relational database Java (programming language) MySQL

Published at DZone with permission of Dmytro Stepanyshchenko. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Spring Data JPA - Part 1
  • Manage Hierarchical Data in MongoDB With Spring
  • Spring Data: Data Auditing Using JaVers and MongoDB
  • CRUD Operations on Deeply Nested Comments: Scalable Spring Boot and Spring Data approach

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!