Skip to content

Springboot/Thymeleaf – How to execute a different SQL query based on the URL?

I would like to be able to display different data based on the previous category that was selected. For example, I currently have categories, that once clicked should redirect a user to a new page that displays all the relevant information for that category. The url should look something like localhost:8080/category/321 where the ID of that category is last. I need to find a way to execute a different sql query depending on the URL/category that was selected. For example, if category 1 is selected, I would like all comments that have a category ID of 1 to be displayed using a statement like

SELECT * FROM Comments WHERE CategoryID='the category id of the category that was selected';

I have used the findAll() method elsewhere in my application to display all data, but I am unsure how to perform specific queries based on the URL. I have also looked briefly into findByID() Thanks

Answer

You can add additional methods in your repositories. For your case something like:

List<Comment> findByCategoryID(Long categoryId);

Spring will resolve the query using method name.

Or with jpql:

@Query("SELECT c FROM Comment AS c WHERE c.CategoryID = :categoryId")
List<Request> findByCategoryID(Long categoryId);

Or use findAll overload which works with Example. Java doc – here.

Example:

Comment comment = new Comment;
comment.setCategoryId(1);
List<Comment> comments = repository.findAll(Example.of(comment));