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.

Insert details about how the information is going to be processed

DOWNLOAD NOW