The best way to use Spring Data query methods
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
Introduction
In this article, we are going to see how Spring Data query methods are built, when you should use them, and especially when you should avoid them.
I decided to write this article after answering this StackOverflow question, which depicts an 87-character-long Spring Data query method.
TL;DR, Don’t write query methods that cannot even fit on the screen.
Spring Data query methods
Spring Data JPA can derive a JPA Query
from a JpaRepository
query method.
For instance, let’s assume we are using the following PostComment
entity:
For this entity, we have created a PostCommentRepository
that, so far, only extends the BaseJpaRepository
from the Hypersistence Utils project because, as I explained in this article, the default JpaRepository
is a lousy choice:
@Repository public interface PostCommentRepository extends BaseJpaRepository<PostComment, Long> { }
Now, let’s say we come up with several query requirements.
To find all the PostComment
entities that belong to a given parent Post
object reference, we can define the following query method in the PostCommentRepository
:
List<PostComment> findAllByPost(Post post);
We don’t even need to create an implementation for it since Spring Data JPA will derive the implementation from the method name.
When calling the findAllByPost
method, Hibernate generates the following SQL query:
SELECT p1_0.id, p1_0.created_on, p1_0.parent_id, p1_0.post_id, p1_0.review, p1_0.status, p1_0.votes FROM post_comment p1_0 WHERE p1_0.post_id = ?
The SQL query looks fine, so for this particular use case, it’s fine to use a query method.
We can even define a method that sorts the PostComments
by the createdOn
property, like this:
List<PostComment> findAllByPostOrderByCreatedOn(Post post);
When calling the findAllByPostOrderByCreatedOn
method, Hibernate executes the following SQL query:
SELECT p1_0.id, p1_0.created_on, p1_0.parent_id, p1_0.post_id, p1_0.review, p1_0.status, p1_0.votes FROM post_comment p1_0 WHERE p1_0.post_id = ? ORDER BY p1_0. created_on ASC
That’s also a fine SQL query that addresses our data access requirement.
We can also filter by the post
and status
and sort the result set by the createdOn
property:
List<PostComment> findAllByPostAndStatusOrderByCreatedOn( Post post, PostComment.Status status );
The findAllByPostAndStatusOrderByCreatedOn
query method is still readable and the associated SQL query looks like this:
SELECT p1_0.id, p1_0.created_on, p1_0.parent_id, p1_0.post_id, p1_0.review, p1_0.status, p1_0.votes FROM post_comment p1_0 WHERE p1_0.post_id = ? AND p1_0.status = ? ORDER BY p1_0. created_on ASC
So, for trivial query methods, it’s rather easy to anticipate the associated SQL query that will be executed.
But there’s no limit to how much we can filter with the query method, and we can easily end up with the following two query methods.
For instance, the more parameters we have to add, the longer the query method becomes:
List<PostComment> findAllByPostAndStatusAndReviewLikeOrderByCreatedOn( Post post, PostComment.Status status, String reviewPattern );
And for a query method that looks as follows:
List<PostComment> findAllByPostAndStatusAndReviewLikeAndVotesGreaterThanEqualOrderByCreatedOn( Post post, PostComment.Status status, String reviewPattern, int votes );
The associated SQL query starts looking simpler than the query method itself:
SELECT p1_0.id, p1_0.created_on, p1_0.parent_id, p1_0.post_id, p1_0.review, p1_0.status, p1_0.votes FROM post_comment p1_0 WHERE p1_0.post_id = ? AND p1_0.status = ? AND p1_0.review like ? ESCAPE ? AND p1_0.votes> = ? ORDER BY p1_0. created_on ASC
Just because you can, it doesn’t mean you should.
In fact, the Spring Data JPA manual provides a disclaimer, which, unfortunately, can be very easily overlooked:
Although getting a query derived from the method name is quite convenient, one might face the situation in which either the method name parser does not support the keyword one wants to use, or the method name would get unnecessarily ugly.
So you can either use JPA named queries through a naming convention (see Using JPA Named Queries for more information) or rather annotate your query method with @Query (see Using @Query for details).
@Query method alternative
Instead of writing the findAllByPostAndStatusAndReviewLikeAndVotesGreaterThanEqualOrderByCreatedOn
method, you can use the @Query
annotation:
@Query(""" select pc from PostComment pc where pc.post = :post and pc.status = :status and pc.review like :reviewPattern and pc.votes >= :votes order by createdOn """) List<PostComment> findAllByPostStatusReviewAndMinVotes( @Param("post") Post post, @Param("status") PostComment.Status status, @Param("reviewPattern") String reviewPattern, @Param("votes") int votes );
Thanks to Java Text Blocks, the @Query
method is readable and makes it easy to understand what the query method is supposed to do.
The SQL query associated with this @Query
method is semantically equivalent to the one generated by the findAllByPostAndStatusAndReviewLikeAndVotesGreaterThanEqualOrderByCreatedOn
method:
SELECT p1_0.id, p1_0.created_on, p1_0.parent_id, p1_0.post_id, p1_0.review, p1_0.status, p1_0.votes FROM post_comment p1_0 WHERE p1_0.post_id = ? AND p1_0.status = ? AND p1_0.review like ? ESCAPE '' p1_0.votes> = ? ORDER BY p1_0. created_on
And you’re not limited to query methods or the @Query
annotation.
Custom Repository query method alternative
For absolute flexibility, you can use a custom Repository
method that allows you to inject the EntityManager
and get access to any JPA or Hibernate API that you might need.
For an introduction to custom Spring Data JPA repositories, check out this article.
For instance, let’s make our PostCommentRepository
extend a CustomPostCommentRepository
interface:
public interface PostCommentRepository extends BaseJpaRepository<PostComment, Long>, CustomPostCommentRepository { }
The CustomPostCommentRepository
interface defines a single findCommentHierarchy
method that we will use to build a hierarchical PostCommentDTO
structure:
public interface CustomPostCommentRepository { List<PostCommentDTO> findCommentHierarchy(Post post); }
The CustomPostCommentRepository
has a CustomPostCommentRepositoryImpl
implementation that looks as follows:
public class CustomPostCommentRepositoryImpl implements CustomPostCommentRepository { @PersistenceContext private EntityManager entityManager; @Override public List<PostCommentDTO> findCommentHierarchy(Post post) { return entityManager.createQuery(""" select pc.id as id, pc.post.id as postId, pc.parent.id as parentId, pc.review as review, pc.createdOn as createdOn, pc.votes as votes from PostComment pc where pc.post = :post order by createdOn """) .setParameter("post", post) .unwrap(org.hibernate.query.Query.class) .setTupleTransformer(new PostCommentTupleTransformer()) .setResultListTransformer(DistinctListTransformer.INSTANCE) .getResultList(); } public static class PostCommentTupleTransformer implements TupleTransformer { private Map<Long, PostCommentDTO> commentDTOMap = new LinkedHashMap<>(); @Override public PostCommentDTO transformTuple(Object[] tuple, String[] aliases) { Map<String, Integer> aliasToIndexMap = aliasToIndexMap(aliases); PostCommentDTO commentDTO = new PostCommentDTO(tuple, aliasToIndexMap); commentDTOMap.put(commentDTO.getId(), commentDTO); PostCommentDTO parent = commentDTOMap.get(commentDTO.getParentId()); if (parent != null) { parent.addReply(commentDTO); } return commentDTO.root(); } private Map<String, Integer> aliasToIndexMap(String[] aliases) { Map<String, Integer> aliasToIndexMap = new LinkedHashMap<>(); for (int i = 0; i < aliases.length; i++) { aliasToIndexMap.put(aliases[i], i); } return aliasToIndexMap; } } }
And when fetching the PostCommentDTO
hierarchy:
List<PostCommentDTO> commentRoots = postCommentRepository.findCommentHierarchy(post);
We get the following PostCommentDTO
structure in the UI:
[ { "id":1, "postId":1, "parentId":null, "review":"Comment 1", "createdOn":1678874460000, "votes":1, "replies":[ ] }, { "id":2, "postId":1, "parentId":null, "review":"Comment 2", "createdOn":1678874460000, "votes":2, "replies":[ { "id":3, "postId":1, "parentId":2, "review":"Comment 3", "createdOn":1678874460000, "votes":3, "replies":[ ] }, { "id":4, "postId":1, "parentId":2, "review":"Comment 4", "createdOn":1678874460000, "votes":4, "replies":[ { "id":5, "postId":1, "parentId":4, "review":"Comment 5", "createdOn":1678874460000, "votes":5, "replies":[ ] }, { "id":6, "postId":1, "parentId":4, "review":"Comment 6", "createdOn":1678874460000, "votes":6, "replies":[ ] }, { "id":7, "postId":1, "parentId":4, "review":"Spam comment", "createdOn":1678874460000, "votes":0, "replies":[ ] }, { "id":8, "postId":1, "parentId":4, "review":"Comment 8", "createdOn":1678874460000, "votes":1, "replies":[ { "id":9, "postId":1, "parentId":8, "review":"Comment 9", "createdOn":1678874460000, "votes":2, "replies":[ ] }, { "id":10, "postId":1, "parentId":8, "review":"Comment 10", "createdOn":1678874460000, "votes":3, "replies":[ ] } ] } ] } ] } ]
There’s no way we could have got a hierarchical DTO structure using simple Spring Data query methods or the @Query
annotation.
Awesome, right?
I'm running an online workshop on the 11th of October about High-Performance SQL.If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
As mentioned by the Spring Data JPA manual, query methods can be very convenient, but you should not limit yourself to using them exclusively.
Unless the query is trivial, you are better off using either the @Query
annotation or a Spring Data JPA custom Repository.

Why not use Query By Example or Specification?
With Specification, you could write a very flexible select and only add the parameters that are being used by the user.
Because Query By Examples is extremely limited.
For example, just try to emulate the solution I gave in this article with Query By Examples and you will see what I meant.
Great article!! To use custom repository for build custom queries, looks very good and more organizate!
What you think about create native queries?
You’re welcome. Native SQL queries are great and unavoidable in any non-trivial project.