Last Updated on May 23, 2019 by Simanta

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!

2 – Dependencies

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

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>guru.springframework</groupId>
  <artifactId>guru-springframework-dogrescue</artifactId>
  <version>0.0.1</version>
  <packaging>jar</packaging>

  <name>DogRescue</name>
  <description>Spring Boot JdbcTemplate and Thymeleaf Example</description>

  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.5.6.RELEASE</version>
    <relativePath/> <!-- lookup parent from repository -->
  </parent>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    <java.version>1.8</java.version>
  </properties>

  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter</artifactId>
    </dependency>
   <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-thymeleaf</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>
     <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
      </dependency>
      <dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
   </dependency>
        <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
    </dependency>
  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
      </plugin>
    </plugins>
  </build>


</project>

3 – Configuration

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

application.properties

#==== connect to mysql ======#
spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:mysql://localhost:3306/dogrescue?useSSL=false
spring.datasource.username=root
spring.datasource.password=
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

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

DROP TABLE IF EXISTS dog;
CREATE TABLE dog (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  rescued DATE NOT NULL,
  vaccinated TINYINT(1) NOT NULL,
  PRIMARY KEY (id));

data.sql

INSERT INTO dog(name,rescued,vaccinated)VALUES('Fluffy ','2017-08-11','1');
INSERT INTO dog(name,rescued,vaccinated)VALUES('Pooch','2017-07-21','1');
INSERT INTO dog(name,rescued,vaccinated)VALUES('Buddy','2017-08-25','0');

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.

package guru.springframework.model;

import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Dog {
	
        @Id
        @GeneratedValue(strategy = GenerationType.AUTO)
        private long id;
        private String name;
        private Date rescued;
        private Boolean vaccinated;
        public long getId() {
                return id;
        }
        public void setId(long id) {
                this.id = id;
        }
        public String getName() {
                return name;
        }
        public void setName(String name) {
                this.name = name;
        }
        public Date getRescued() {
                return rescued;
        }
        public void setRescued(Date rescued) {
                this.rescued = rescued;
        } 
        public Boolean getVaccinated() {
                return vaccinated;
        }
        public void setVaccinated(Boolean vaccinated) {
                this.vaccinated = vaccinated;
        }
	
}

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.

package guru.springframework.dao;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import guru.springframework.model.Dog;

@Repository
public interface DogRepository extends CrudRepository<Dog,Long> {
        
        Dog findByName(String 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

package guru.springframework.service;

import java.util.Date;
import java.util.List;

public interface DogService {
        
        void addADog(String name, Date rescued, Boolean vaccinated);
        
        void deleteADOG(String name, Long id);
        
        List atriskdogs(Date rescued);
        
        long getGeneratedKey(String name, Date rescued, Boolean vaccinated);
        
}

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.

package guru.springframework.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;

@Service
public class DogServiceIm implements DogService {
        @Autowired
        private DataSource dataSource;
        private JdbcTemplate jdbcTemplate;
        private long generatedKey;
        private String rescuedstring;

        public void setDataSource(DataSource dataSource) {
                this.dataSource = dataSource;
                this.jdbcTemplate = new JdbcTemplate(dataSource);
        }
        
        public void addADog(String name, Date rescued, Boolean vaccinated){
                
                JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
                jdbcTemplate.update("INSERT INTO dog(name,rescued,vaccinated)VALUES(?,?,?)",name,rescued,vaccinated );
                
        }
        
        public void deleteADOG(String name, Long id){
                JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
                jdbcTemplate.update("DELETE FROM dog WHERE name='"+name+"' AND id="+id);
        }
        
        public List atriskdogs(Date rescued){
                String sql = "SELECT * FROM dog WHERE rescued < '"+ rescued+"' AND vaccinated = '0'";
                List dogList = new ArrayList();
                JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
                jdbcTemplate.query(sql, new ResultSetExtractor() {
                        public List extractData(ResultSet rs) throws SQLException {
                                
                                while (rs.next()) {
                                        String name = rs.getString("name");
                                        dogList.add(name);
                                }
                                return dogList;
                        }
                }
                                
                
        );
        System.out.println("doglist");
        return dogList;        
        }

        public long getGeneratedKey(String name, Date rescued, Boolean vaccinated) {
                String sql ="INSERT INTO dog(name,rescued,vaccinated)VALUES(?,?,?)";
                JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
                KeyHolder holder = new GeneratedKeyHolder();
                Format formatter = new SimpleDateFormat("yyyy-MM-dd");
                rescuedstring = formatter.format(rescued);
                System.out.println(rescuedstring);
                java.sql.Date rescuedsql = java.sql.Date.valueOf(rescuedstring);
                System.out.println(rescuedsql);
                jdbcTemplate.update(new PreparedStatementCreator() {
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement statement = connection.prepareStatement(sql.toString(),
                                        Statement.RETURN_GENERATED_KEYS);
                        statement.setString(1, name);
                        statement.setDate(2, rescuedsql );
                        statement.setBoolean(3, vaccinated);
                        return statement;
                }
                }, holder);
                generatedKey = holder.getKey().longValue();
                System.out.println("generated key is " + generatedKey);
                return generatedKey;
        }

}

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.

package guru.springframework.controller;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.format.annotation.DateTimeFormat;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;

import guru.springframework.dao.DogRepository;
import guru.springframework.model.Dog;
import guru.springframework.service.DogService;

@Controller
public class DogController {

        @Autowired
        private DogRepository dogrepository;

        @Autowired
        private DogService dogservice;

        private ArrayList dogModelList;

        private List dogrisklist = null;

        @GetMapping(value = "/")
        public String doghome(
                        @RequestParam(value = "search", required = false) @DateTimeFormat(pattern = "yyyy-MM-dd") Date q,
                        Model model) {
                if (q != null) {
                        dogModelList = new ArrayList();
                        System.out.println("q is = " + q);
                        dogrisklist = dogservice.atriskdogs(q);
                        for (String name : dogrisklist) {
                                System.out.println("Dogs in repository are : " + dogrepository.findAll());
                                Dog doggy = dogrepository.findByName(name);
                                System.out.println(doggy.toString() + "doggy name : " + doggy.getName());
                                dogModelList.add(doggy);
                                System.out.println("This dog's name is : " + doggy.getName());
                        }
                }
                model.addAttribute("search", dogModelList);

                model.addAttribute("dogs", dogrepository.findAll());

                return "index";

        }

        @PostMapping(value = "/")
        public String adddog(@RequestParam("name") String name,
                        @RequestParam("rescued") @DateTimeFormat(pattern = "yyyy-MM-dd") Date rescued,
                        @RequestParam("vaccinated") Boolean vaccinated, Model model) {
                dogservice.addADog(name, rescued, vaccinated);
                System.out.println("name = " + name + ",rescued = " + rescued + ", vaccinated = " + vaccinated);
                return "redirect:/";
        }

        @PostMapping(value = "/delete")
        public String deleteDog(@RequestParam("name") String name,
                        @RequestParam("id") Long id) {
                dogservice.deleteADOG(name, id);
                System.out.println("Dog named = " + name + "was removed from our database. Hopefully he or she was adopted.");
                return "redirect:/";

        }
        
        @PostMapping(value = "/genkey")
        public String genkey(@RequestParam("name") String name,
                        @RequestParam("rescued") @DateTimeFormat(pattern = "yyyy-MM-dd") Date rescued,
                        @RequestParam("vaccinated") Boolean vaccinated, Model model) {
                dogservice.getGeneratedKey(name, rescued, vaccinated);
                System.out.println("name = " + name + ",rescued = " + rescued + ", vaccinated = " + vaccinated);
                return "redirect:/";
        }

}

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

<!DOCTYPE html>
<html lang="en">
<head>
<!-- META SECTION -->
<title>Spring Framework Guru JdbcTemplate, Thymeleaf, JPA Example</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<!-- END META SECTION -->
<!--  BEGIN STYLE -->
<style>
table, th, td {
    border: 1px solid black;
    padding: 1px;
}
</style>
<!--  END STYLE -->

</head>
<body>
    <div>
      <h2>Current Dogs In Rescue</h2>
      <table>
        <thead>
          <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Rescue Date</th>
            <th>Vaccinated</th>
          </tr>
        </thead>
        <tbody>
          <tr th:each="dogs : ${dogs}">
            <td th:text="${dogs.id}">Text ...</td>
            <td th:text="${dogs.name}">Text ...</td>
            <td th:text="${dogs.rescued}">Text ...</td>
            <td th:text="${dogs.vaccinated}">Text...</td>
          </tr>
        </tbody>
      </table>
    </div>
    <!--  begin form for finding at risk dogs -->
    <h2>Find Dogs That Need Vaccines</h2>
    <form action="#" th:action="@{/}" th:object="${search}">
    <label for="search_input">Search:</label> <input name="search"
      id="search"  >
    </input>
    <div th:if="${not #lists.isEmpty(search)}">
      <h3>Search Results Of At Risk Dogs</h3>
      <table>
        <thead>
          <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Vaccinated</th>
            <th>Rescued</th>
          </tr>
        </thead>
        <tbody>
          <tr th:each="search : ${search}">
            <td th:text="${search.id}">Text ...</td>
            <td th:text="${search.name}">Text ...</td>
            <td th:text="${search.vaccinated}">Text ...</td>
            <td th:text="${search.rescued}">Text...</td>
          </tr>
        </tbody>
      </table>
    </div>
  </form>
  <div>
  <h2>Add A Dog</h2>
  <form action="#" th:action="@{/}" method="post">
  <label>Name<input type="text" name="name" id="name"></input></label>
  <label>Vaccinated<input type="text" name="vaccinated" id="vaccinated"></input></label>
  <label>Rescued<input type="text" name="rescued" id="rescued"></input></label>
  <input type="submit" value="Submit"></input>
  </form>
  </div>
  <div>
  <h2>Delete A Dog</h2>
  <form action="/delete" th:action="@{/delete}" method="post">
  <label>Name<input type="text" name="name" id="name"></input></label>
  <label>ID<input type="text" name="id" id="id"></input></label>
  <input type="submit" value="Submit"></input>
  </form>
  </div>
  <div>
  <h2>Return Generated Key When Adding A Dog</h2>
    <form action="/genkey" th:action="@{/genkey}" method="post">
  <label>Name<input type="text" name="name" id="name"></input></label>
  <label>Vaccinated<input type="text" name="vaccinated" id="vaccinated"></input></label>
  <label>Rescued<input type="text" name="rescued" id="rescued"></input></label>
  <input type="submit" value="Submit"></input>
  </form>
  </div>
</body>
</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.

package guru.springframework;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class DogRescueApplication {

        public static void main(String[] args) {
                SpringApplication.run(DogRescueApplication.class, args);
        }
}

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.

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.

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.

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”.

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.

In the console, this is printed

Our table is once again updated

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.