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

Question

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

score 1 · Accepted Answer · answered Mar 21 '22 at 17:01

1

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));

answered Mar 21 '22 at 17:01

Chaosfire

4,818
4
8
23

Thank you, when using jpql, does it get the categoryID from the URL? So if I were to change the URL to another ID, it would perform a different query? – JohnSA Mar 21 '22 at 18:11
@JohnSA You get the id using `@PathVariable` from url - check [here](https://stackoverflow.com/questions/19803731/spring-mvc-pathvariable) how. Then pass the value to the repository method of your choice. – Chaosfire Mar 21 '22 at 21:30

score 1 · Answer 2 · answered Mar 22 '22 at 07:26

You need to make a distinction between your controller and your repository. The controller is what is responsible for everything related to HTTP and HTML, so parsing the URL, generating the HTML, etc... The repository is the class that is responsible for querying the database.

A controller would typically look like this:

@Controller
@RequestMapping("/comments")
public class CommentsController {

  @GetMapping
  public String listAllComments(Model model) {
    // Normally, you don't go directly to the repository, 
    // but use a service in between, but I show it like this 
    // to make a bit easier to follow
    List<Comment> comments = repository.findAll();
    model.addAttribute("comments", comments);

    return "index"; // This references the Thymeleaf template. By default in a Spring Boot appliation, this would be `src/main/resources/templates/index.html`
  }

 @GetMapping("/{id}"
 public String singleComment(@PathVariable("id") Long id, Model model) {
   // Spring will automatically populate `id` with the value of the URL
   // Now you can use the id to query the database
   Comment comment = repository.findById(id).orElseThrow();
   model.addAttribute("comment", comment);

   return "single-comment";
 }
}

That second method would handle a URL of the form /comments/123.

In your example, if comments have a category, then most likely, you would use a query parameter, and not a path variable. In that case, your controller method would be:

@GetMapping
public String commentsByCategory(@QueryParameter("categoryId")Long categoryId, Model model) {
  List<Comments> comments = repository.findAllByCategoryId(categoryId);
  model.addAttribute("comments", comments);

  return "comments-by-category";
}

Then the URL would be /comments?categoryId=123

For the repository itself, be sure to read up on query methods in the documentation: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods

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

2 Answers2