Dynamic search/filter query based on user inputs with Hibernate, MySQL, Spring Boot

Before programming, I didn’t understand coding memes on the internet …

… until I had a chance to work with a guy who loves optimizing codes.

One feature of our project is to do a complex filter based on multiple inputs, and he assigned this task to me.

So basically, users can filter by tags, time of creation, also sort by latest/oldest posts/comments. The posts that have the latest comments always display first.

I completed it in just one day using Collections and Stream. After testing all the scenarios, and it worked fine, I confidently made a pull request and thought I would have a good sleep.

However, he sent me a message at midnight, saying that he refused to accept my pull request because my codes aren’t scalable and bad at performing.

“Try to use JPA and Hibernate instead of core Java for better performance. You can read more at Vlad Mihalcea and Thorben Janssen’s blog posts,” — he said.

I was uncomfortable and skeptical at first because I didn’t learn it from school. And more importantly, I need to delete my whole lines of code if I switch to his solution.

My big lesson: Always discuss the solution approach before jumping into coding

I couldn’t have a good sleep that night. After reading those two Hibernate gurus’ posts, I thought maybe my teammate was right, and I should follow his direction.

There are two main steps for this task:

1. Write complex SQL queries to generate results in sorted order.
2. Make the query dynamically based on the user’s input (no matter what inputs or types of sorts are, I just need one function to perform this task).

Write complex SQL queries to generate results in sorted order.

The trickiest thing in this step is to make posts having comments appear first and display their latest commenter. Posts that don’t have any comments, even it is created latest, will be less prioritized.

I utilize my knowledge of JOIN, alias table, conditional statement to generate a query like this:

SELECT 
  * 
FROM 
  posts p 
  LEFT JOIN (
    SELECT 
      c.post_id AS belongPost, 
      MAX(created_on) AS COMMENT 
    FROM 
      comments c 
    GROUP BY 
      belongPost
  ) AS TEMP ON TEMP.belongPost = p.post_id 
WHERE 
  p.topic_id = 'Assignment_Help' 
  AND p.status = 'ACCEPTED' 
  AND p.post_tags LIKE "%%" 
  AND p.created_on >= 2020 - 12 - 31 
  AND p.created_on <= 2021 - 08 - 30 
ORDER BY 
  IF(TEMP.COMMENT IS NULL, 1, 0), 
  TEMP.COMMENT desc, 
  p.created_on desc;

Such a huge query!!!

Make a dynamic query based on the user’s input

Low-performance approach: If I don’t use dynamic query, I need to create 04 other querries to do this task (sortByPost ascendingly, sortByPosts descendingly, sortByComments ascendingly and sortByComments descendingly).

A better solution: Use the dynamic query

I created another CustomPostRepository and my PostRepository extends CustomPostRepository.

@Repository
public interface PostRepository extends JpaRepository<Post,Integer>,CustomPostRepository{     
 // Other functions
}

In the CustomPostRepository, I put the signature of the function here:

public interface CustomPostRepository {
    List<Post> filterPostsBasedOnKeywords(
            String topicID, 
            String tags, 
            String start, 
            String end, 
            String sortBy, 
            String order, 
            Pageable pageable);
}

I designed CustomPostRepository to be an interface that contains all of the functions/queries that are dynamic; that’s why I separated it with PostRepository. And because PostRepository extends CustomPostRepository, I can access CustomPostRepository’s methods via the injected PostRepository instance. This design follows Open/ Closed principle in SOLID that I learned from javarevisted.

Finally, I created CustomPostRepositoryImpl class that implements CustomPostRepository interface. And here is where the magic happens:

The idea is to create a dynamic string using StringBuilder depending on the user’s input (sort by ascending/descending, posts/comments). Then,

Query q = entityManager.createNativeQuery(sb.toString(),Post.class);

will convert that string to a native query, and q.getResultList(); helps to get the result list.

Finally, it works and is a little bit faster than my oldJava core solution (I guess if I have thousands of records in my database, the differences will be more obvious).

Btw, thank you my teammate for being hard on codes and being hard on me.

Source code: https://github.com/trangntt-016/seneca-forum-backend

If you enjoy this article, please clap it up 👏 and share it so that others can find it! 😄.