,

Using JdbcTemplate with Spring Boot and Thymeleaf

In this tutorial, we will be building a demo web application for a Dog Rescue organization that uses JdbcTemplate and Thymeleaf. For this example, we will be using a MySQL database. However, this example is not limited to MySQL and the database could be swapped out for another type with ease.

You can browse and download the code on Github as you follow this example.

1 – Project Structure

The project uses a typical Maven structure. You may notice I am using Spring Tool Suite, which JT is not a fan of!

Project structure of Spring Boot Thymeleaf JdbcTemplate tutorial

 

2 – Dependencies

Besides typical Spring Boot Starter dependencies, we include Thymeleaf and MySQL connector.

 

3 – Configuration

We configure all our datasource information here in the application.properties. Later we will autowire this for our JdbcTemplate use.

application.properties

4 – Database Initialization

When our application starts, these SQL file will be automatically detected and ran. In our case, we will drop the table “dog” every time the application starts, create a new table named “dog” and then insert the values shown in data.sql.

You may recall that “vaccinated” is a Boolean value in Java. In MySQL Boolean is a synonym for TINYINT(1), so we can use this data type for the column.

schema.sql

data.sql

5 – Model/Entity

Here we define the characteristics of a dog that we want to know for our Dog Rescue. The getters and setters were auto created and it is suggested to do this to save time.

6 – Repository

We extend the CrudRepository for our DogRepository. The only additional method we create is a derived query for finding a dog by name.

7 – Service

Using the SOLID principles that JT discusses on the site here :SOLID Principles , we build a service interface and then implement that interface.

DogService.java

DogServiceImpl.java

Here we implement the methods mentioned in DogService.java.

  • addADog – is an example of how to add a record using JdbcTemplate’s update method. It takes three parameters: String, Date and Boolean.
  • deleteADOG – is an example of how to delete a record using JdbcTemplate’s update method. It takes two parameters: Long (id) and String (name).
  • List atriskdogs – is an example of how to select records using JdbcTemplate’s query method. This uses a
    ResultSetExtractor. It takes one parameter: Date. The method returns records of dogs that were rescued before that date that have not been vaccinated (Boolean value of false).
  • long getGeneratedKey – is an example of how to insert records using JdbcTemplate’s query method with PreparedStatementCreator and retrieve a generated key. It takes the same parameters as the other insert example: String, Date and Boolean.

8 – Controller

DogController.java

    • @GetMapping(value = “/”) – there is an optional requirement to provide a search value of type Date in yyyy-MM-dd format. This variable is called q (for “query”) and if it is not null then we will create an ArrayList of all dogs rescued before that date who have not been vaccinated. This ArrayList is called dogModelList and added as an attribute known as “search”. This attribute will be used in our Thymeleaf template.
      Because of its ease of use,
      we use the built in findall method of the CrudRepository to create a list of all dogs in the repository and add it as an attribute, which will also be used by Thymeleaf.
    • @PostMapping(value = “/”) – we request all the parameters that will be passed in our HTML form. We use these values to add a dog to our database.
    • @PostMapping(value = “/delete”) – we request the parameters needed to delete a dog. After the dog is deleted, we redirect the user back to our homepage.
    • @PostMapping(value = “/genkey”) – this is the mapping for inserting a record that returns a generated key. The generated key is printed to standard out in our example.

9 – Thymeleaf template

As this is a basic example application to demonstrate approaches to JdbcTemplate, JPA, Thymeleaf, and other technologies, we have just this one page with a minimalist user interface.

      • Using th:each we are able to iterate through all the records in our dog table
      • Using th:text with the variable and field name, we can display the record. I.E. th:text=”${dogs.id}
      • Using th:if=”${not #lists.isEmpty(search), we prevent the web page from showing the table of search results for dogs at risk (not vaccinated) unless there are results to be shown.
      • With our forms, we map the request to a specific URI and specify names for the inputs of our form that match the parameters in our controller.

index.html

10 – @SpringBootApplication

Our class with the main method has nothing unique in it. The @SpringBootApplication annotation takes care of autodetecting beans that are registered with the various stereotype annotations, such as @Service, etc.

11 – Demo

Landing page

So, I have navigated to localhost:8080 as I did not change the default ports for this application. When I land on the page, you can see that it is displaying the current dogs in our database.

Dog Rescue homepage

Find Dogs That Need Vaccines

Imagine that instead of three dogs in this database we had a larger, less manageable number. Having a feature that allows employees of a dog rescue to find dogs that need vaccinations would be useful if there were more dogs.

The search functionality takes a date and shows dogs that were rescued before that date that have not been vaccinated.

Although we know right now that Buddy is the only dog without his vaccinations, let’s show how this works.

Search for dogs without vaccine

Thymeleaf conditional statement

Add A Dog

As we know, the ID is autogenerated. So we can add all the fields minus the ID and successfully still a Dog to the database.
Adding a dog in thymeleaf template

Table in thymeleaf

Delete A Dog

We remove a dog from the database by using the primary ID but also ask for the name of the dog to verify it is the correct one.

We redirect the user back to the index, so it displays the table of dogs minus the one deleted. Below you can see I have removed “Pooch”.
Delete JdbcTemplate Thymeleaf

Spring Boot Delete Dog

Add A Dog And Retrieve Generated Key

Sometimes we need to retrieve the generated key from our database for other uses. Here in this example, we insert a dog named “Lassie” and retrieve the generated key.

Insert Lassie in database

In the console, this is printed

Our table is once again updated

updated table

Download the code from Github

If you’d like, you can view and download the code from Github

About Michael

Michael Good is a software engineer located in the Washington DC area that is interested in Java, cyber security, and open source technologies. Follow his personal blog to read more from Michael.

Share

Leave a Reply