Using JdbcTemplate in a Spring Boot Web Application

• Download source - 8.2 KB

Introduction

This will be my last tutorial for the year 2018. In this tutorial, I am going to discuss something fun. Data access is one of my favorite topics. I used to hate work with databases because I was not very good with them. As I gain more experience, my dislike of databases is getting lesser and lesser. Over the years, I have worked with ADO.NET, the Entity Framework, Hibernate, SSRS reporting, and some other database related work. What I learned is that even though I hate the stuff, there is no escape from it. Might as well face it and learn it.

One of the Spring technologies that I didn't have the fortune to work with in the past is the JdbcTemplate. It is Spring's way to JDBC. In today's world, we have Entity Framework, and Hibernate for ORM, why does JDBC matter? There are two major reasons:

• With JDBC, you can work directly with the query itself and optimize it any way you want. With ORM, you don't get this luxury.
• Another reason is supporting legacy code. Sometimes, you inherit things that were done in the past. And an upgrade is needed, and you are the one appointed to do this. So a lot of the code is done with JDBC, maybe using Spring JDBC can help? Who knows.

To me, JDBC is just a very simple and straight forward way of getting data in and out of database. And I know it can be a little faster than the ORM. And sometimes, the ORM framework can make coding incredibly awkward. However, JDBC has its awkwardness as well, once I get the data out, I have to map them to an entity object.

This tutorial will discuss how to setup the Spring DAO for JdbcTemplate, and the basics on inserting data and retrieving them.

Setting Up Database

In order to have this sample application working, we have to set up a MySQL database with a database user, and a table. I have prepared two SQL scripts:

• One script that will create the database user and the database
• The other script will create the table in the newly created database

To create the database and user, here is the script:

-- user.sql
CREATE DATABASE cardb;
CREATE USER 'cardbuser'@'localhost' IDENTIFIED BY '123test321';
GRANT ALL PRIVILEGES ON cardb.* TO 'cardbuser'@'localhost';
FLUSH PRIVILEGES;

To create the table for the sample application, here is the script:

-- tables.sql

use cardb;

DROP TABLE IF EXISTS carinfo;

CREATE TABLE carinfo (
   id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
   yearofmanufacture INT NOT NULL,
   model VARCHAR(64) NOT NULL,
   make VARCHAR(64) NOT NULL,
   suggestedretailprice FLOAT NOT NULL,
   fullprice FLOAT NOT NULL,
   rebateamount FLOAT NOT NULL,
   createdate DATETIME NOT NULL,
   updatedate DATETIME NOT NULL
);

Now, let's take a look at the pom.xml file.

The Maven POM File

The Maven POM XML is very simple, it looks like this:

<?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>org.springframework</groupId>
    <artifactId>hanbo-boot-rest</artifactId>
    <version>1.0.1</version>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.5.RELEASE</version>
    </parent>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
    
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

Just to summarize what is in this POM file, the project inherits from spring-boot-starter-parent. And the dependencies needed are:

• spring-boot-starter-web: This is needed to create a web based application. It supports both MVC and RESTFul applications.
• spring-boot-starter-jdbc: This is needed to use Spring DAO JDBC functionality.
• mysql-connector-java: This is needed to provide jdbc driver specific to MySQL database.

When using Maven to do the build, it will create a jar file which can be run with just a Java command. I will show you how in the end.

The Main Entry

As I have described in the very first Spring Boot tutorial, a Spring Boot based web application does not need an application container to host it. So packaged as a jar file, it can be run as a regular Java program. This is why there is a main entry for this sample application. The code for this main entry looks like this:

package org.hanbo.boot.rest;

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

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

The class is marked as a Spring boot application using annotation @SpringBootApplication. And the main entry method will pass the class to the SpringApplication.run(). Spring Boot's application runner will create the IoC container, and scan all the packages and sub packages for any classes that can be added to the IoC container. Because we have the web starter dependency added, the application will start as a web application. Spring Boot takes care of the configuration automatically.

The REST API Controller

Next, I will show you the controller class for the REST APIs that are used for demonstrating the use of JdbcTemplate. Here is the full source code:

package org.hanbo.boot.rest.controllers;

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

import org.hanbo.boot.rest.models.CarModel;
import org.hanbo.boot.rest.models.GenericResponse;
import org.hanbo.boot.rest.repository.CarRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class SampleController
{
   @Autowired
   private CarRepository carRepo;
   
   @RequestMapping(value="/public/addCar", method = RequestMethod.POST)
   public ResponseEntity<GenericResponse> addCar(
      @RequestBody
      CarModel carToAdd)
   {
      GenericResponse retMsg = new GenericResponse();
      if (carToAdd != null)
      {
         try
         {
            carRepo.addCar(carToAdd);
            
            retMsg.setSuccess(true);
            retMsg.setStatusMsg("Operation is successful.");
         }
         catch (Exception ex)
         {
            ex.printStackTrace();
            retMsg.setSuccess(false);
            retMsg.setStatusMsg("Exception occurred.");
         }
      }
      else
      {
         retMsg.setSuccess(false);
         retMsg.setStatusMsg("No valid car model object to be added");
      }
      
      ResponseEntity<GenericResponse> retVal;
      retVal = ResponseEntity.ok(retMsg);
      return retVal;
   }
   
   @RequestMapping(value="/public/getCars", method = RequestMethod.GET)
   public ResponseEntity<List<CarModel>> getCars(
      @RequestParam("make")
      String make,
      @RequestParam("startYear")
      int startYear,
      @RequestParam("endYear")
      int endYear)
   {
      List<CarModel> foundCars
         = carRepo.findCar(make, startYear, endYear);
      
      if (foundCars == null) {
         foundCars = new ArrayList<CarModel>();
      }
      
      ResponseEntity<List<CarModel>> retVal;
      retVal = ResponseEntity.ok(foundCars);
      return retVal;
   }
}

I purposely made this as a REST API controller because it is relatively simple to demonstrate the functionality I want to show. The class is annotated as @RestController so that Spring Boot will recognize this class would act as a RESTFul API controller.

Inside the class, there are two methods. And both are used to handle user's HTTP requests. They are basically action methods. The first one is used to add a car model to the database. The other one is to query a list of car models that match three different search criteria. Both methods use the same data access repository, which uses the JdbcTemplate object.

Simply put, to add a new car model to database table, here is how:

@Autowired
private CarRepository carRepo;

...

carRepo.addCar(carToAdd);

To query the car models, the method uses the maker (such as Honda, Mazda, Ford, Chevrolet, etc.), the year range (a beginning year and end year), and returns a list of car models that matches the criteria. So the code for this is:

@RequestMapping(value="/public/getCars", method = RequestMethod.GET)
public ResponseEntity<List<CarModel>> getCars(
   @RequestParam("make")
   String make,
   @RequestParam("startYear")
   int startYear,
   @RequestParam("endYear")
   int endYear)
{
   ...
   List<CarModel> foundCars
      = carRepo.findCar(make, startYear, endYear);
   ...
}

In order to design such a data access repository, configuration has to be put in place. What configurations? Things like:

• The database connection string
• The user and password used for connecting to the database
• The transaction manager
• The database jdbc driver class

That is covered in the next section.

JDBC Connection Configuration

In order to use JdbcTemplate, configuration must be setup first. Here is the class that does it:

package org.hanbo.boot.rest.config;

import javax.sql.DataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

@Configuration
public class JdbcConfiguration
{
   @Bean
   public DataSource dataSource()
   {
      DriverManagerDataSource dataSource = new DriverManagerDataSource();

      dataSource.setDriverClassName("com.mysql.jdbc.Driver");
      dataSource.setUrl("jdbc:mysql://localhost:3306/cardb");
      dataSource.setUsername("cardbuser");
      dataSource.setPassword("123test321");

      return dataSource;
   }

   @Bean
   public NamedParameterJdbcTemplate namedParameterJdbcTemplate()
   {
      NamedParameterJdbcTemplate retBean
          = new NamedParameterJdbcTemplate(dataSource());
       return retBean;
   }

   @Bean
   public DataSourceTransactionManager txnManager()
   {
      DataSourceTransactionManager txnManager
         = new DataSourceTransactionManager(dataSource());
      return txnManager;
   }
}

Let's check out this class one part at a time. First is to annotate the class with @Configuration. This tells Spring Boot to treat the class as a configuration, and have it bootstrapped during start up.

@Configuration
public class JdbcConfiguration
{
...
}

Next, I need a data source. It is a Java object and by convention, it is called dataSource. The method dataSouce() is also used to configure the MySQL connection:

@Bean
public DataSource dataSource()
{
   DriverManagerDataSource dataSource = new DriverManagerDataSource();

   dataSource.setDriverClassName("com.mysql.jdbc.Driver");
   dataSource.setUrl("jdbc:mysql://localhost:3306/cardb");
   dataSource.setUsername("cardbuser");
   dataSource.setPassword("123test321");

   return dataSource;
}

This method dataSource() returns an object of class DriverManagerDatSource. The rest of the method will set up the data source object with configurations like MySQL jdbc driver, the connection string, user name and password. The code is self-explanatory.

Now, it is time to unveil the core object of this tutorial, the creation of the JdbcTemplate object. What I really like to use are queries with named parameters. In order to do this, I have to use a specific kind of JdbcTemplate object, of class NamedParameterJdbcTemplate. So here it is:

@Bean
public NamedParameterJdbcTemplate namedParameterJdbcTemplate()
{
   NamedParameterJdbcTemplate retBean
       = new NamedParameterJdbcTemplate(dataSource());
    return retBean;
}

All this method does is create an object of NamedParameterJdbcTemplate. And it needs a data source object.

The last thing you need is a transaction manager. A Spring transaction manager can be used to add transaction annotation to the repository methods os that the CRUD operations can be wrapped into SQL transactions. Here is the transaction manager object:

@Bean
public DataSourceTransactionManager txnManager()
{
   DataSourceTransactionManager txnManager
      = new DataSourceTransactionManager(dataSource());
   return txnManager;
}

With this transaction manager available, it is possible to commit and roll back transactions within the repository methods. These are all the configurations we need for JdbcTemplate object to work. The last thing we need is the repository class with the CRUD operations.

The Repository Class

A repository object is used for performing the CRUD operations with SQL database. For the sake of simplicity, I used my repository object in the controller class directly. The reality is that it is best to have DTO objects and data entity objects separated. And a service layer on top of the repository layer.

My repository class looks like this:

package org.hanbo.boot.rest.repository;

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

import org.hanbo.boot.rest.models.CarModel;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
public class CarRepository
{
   
   @Autowired
   private NamedParameterJdbcTemplate sqlDao;
   
   private final String addCar_sql = "INSERT INTO carinfo (yearofmanufacture, model, make, 
           suggestedretailprice, fullprice, rebateamount, createdate, updatedate)"
           + " VALUES (:yearOfManufacture, :model, :make, :suggestedRetailPrice, :fullPrice, 
           :rebateAmount, :createdDate, :updatedDate)";
   
   private final String getCars_sql = "SELECT id,"
      + " yearofmanufacture,"
      + " model,"
      + " make,"
      + " suggestedretailprice,"
      + " fullprice,"
      + " rebateamount,"
      + " createdate,"
      + " updatedate FROM carinfo WHERE make = :make AND 
      yearofmanufacture >= :startYear AND yearofmanufacture <= :endYear";


   @Transactional
   public void addCar(CarModel carToAdd)
   {
      if (carToAdd != null)
      {
         Map<String, Object> parameters = new HashMap<String, Object>();

         Date dateNow = new Date();
         
         parameters.put("yearOfManufacture", carToAdd.getYearOfManufacturing());
         parameters.put("model", carToAdd.getModel());
         parameters.put("make", carToAdd.getMaker());
         parameters.put("suggestedRetailPrice", carToAdd.getSuggestedRetailPrice());
         parameters.put("fullPrice", carToAdd.getFullPrice());
         parameters.put("rebateAmount", carToAdd.getRebateAmount());
         parameters.put("createdDate", dateNow);
         parameters.put("updatedDate", dateNow);
         
         int retVal = sqlDao.update(addCar_sql, parameters);
         System.out.println("Rows updated: " + retVal); 
         throw new RuntimeException("dummy Bad");
      }
      else
      {
         System.out.println("Car to add is invalid. Null Object.");          
      }
   }
   
   @Transactional
   public List<CarModel> findCar(String make, int startYear, int endYear)
   {
      List<CarModel> foundObjs = sqlDao.query(getCars_sql,
         (new MapSqlParameterSource("make", make))
            .addValue("startYear", startYear)
            .addValue("endYear", endYear),
         (rs) -> {
            List<CarModel> retVal = new ArrayList<CarModel>();
            if (rs != null)
            {
               while(rs.next())
               {  
                  CarModel cm = new CarModel();  
                  cm.setYearOfManufacturing(rs.getInt("yearOfManufacture"));  
                  cm.setMaker(rs.getString("make"));  
                  cm.setModel(rs.getString("model"));
                  cm.setSuggestedRetailPrice(rs.getFloat("suggestedretailprice"));
                  cm.setFullPrice(rs.getFloat("fullprice"));
                  cm.setRebateAmount(rs.getFloat("rebateamount"));
                  retVal.add(cm);  
               }  
            }
            
            return  retVal;
         });
      
      return foundObjs;
   }
}

The first thing about this class is that it is annotated with @Repository. Next, it is the autowired injection of the JdbcTemplate object, which will be used for the data access CRUD operations.

@Autowired
private NamedParameterJdbcTemplate sqlDao;

The injection of the JdbcTemplate object is from the configuration class. Next, I have to define two query strings. One is for inserting a data row to database table. The other is a query returning a list of car models.

private final String addCar_sql = "INSERT INTO carinfo (yearofmanufacture, model, make, 
                 suggestedretailprice, fullprice, rebateamount, createdate, updatedate)"
   + " VALUES (:yearOfManufacture, :model, :make, :suggestedRetailPrice, :fullPrice, 
                  :rebateAmount, :createdDate, :updatedDate)";
   
private final String getCars_sql = "SELECT id,"
   + " yearofmanufacture,"
   + " model,"
   + " make,"
   + " suggestedretailprice,"
   + " fullprice,"
   + " rebateamount,"
   + " createdate,"
   + " updatedate FROM carinfo WHERE make = :make AND 
      yearofmanufacture >= :startYear AND yearofmanufacture <= :endYear";

Remember what I said about named parameters? In the above queries, I used something like this: :suggestedRetailPrice. This is a named parameter. A named parameter is something that starts with a colon ":", followed by the actual parameter name. Anyways, here is the method that adds a car model:

@Transactional
public void addCar(CarModel carToAdd)
{
   if (carToAdd != null)
   {
      Map<String, Object> parameters = new HashMap<String, Object>();

      Date dateNow = new Date();
      
      parameters.put("yearOfManufacture", carToAdd.getYearOfManufacturing());
      parameters.put("model", carToAdd.getModel());
      parameters.put("make", carToAdd.getMaker());
      parameters.put("suggestedRetailPrice", carToAdd.getSuggestedRetailPrice());
      parameters.put("fullPrice", carToAdd.getFullPrice());
      parameters.put("rebateAmount", carToAdd.getRebateAmount());
      parameters.put("createdDate", dateNow);
      parameters.put("updatedDate", dateNow);
      
      int retVal = sqlDao.update(addCar_sql, parameters);
      System.out.println("Rows updated: " + retVal);
   }
   else
   {
      System.out.println("Car to add is invalid. Null Object.");          
   }
}

The way it works:

• Define a Map object, where the key matches the named parameters, and the values are values of the named parameters that will be added to database.
• Use the NamedParameterJdbcTemplate object which is called sqlDao to call update(), pass in addCar_sql (which has the query for inserting a car model).
• The call to update will return an integer that indicates how many rows have been affected.
• Finally, I print out the integer to see if it succeeded or not.

The query to find a list of cars, is done by the other repository method called findCar(), here is the source code:

@Transactional
public List<CarModel> findCar(String make, int startYear, int endYear)
{
   List<CarModel> foundObjs = sqlDao.query(getCars_sql,
      (new MapSqlParameterSource("make", make))
         .addValue("startYear", startYear)
         .addValue("endYear", endYear),
      (rs) -> {
         List<CarModel> retVal = new ArrayList<CarModel>();
         if (rs != null)
         {
            while(rs.next())
            {  
               CarModel cm = new CarModel();  
               cm.setYearOfManufacturing(rs.getInt("yearOfManufacture"));  
               cm.setMaker(rs.getString("make"));  
               cm.setModel(rs.getString("model"));
               cm.setSuggestedRetailPrice(rs.getFloat("suggestedretailprice"));
               cm.setFullPrice(rs.getFloat("fullprice"));
               cm.setRebateAmount(rs.getFloat("rebateamount"));
               retVal.add(cm);  
            }  
         }
         
         return  retVal;
      });
   
   return foundObjs;
}

This method is a little complicated. Here is how it works:

The method calls the sqlDao's query() method, passes in three parameters:

• The first parameter is the getCars_sql, which is the query string.
• The second parameter is an object of MapSqlParameterSource, which probably is a wrapped Map object. The keys match the named parameters, and the values are values of the named parameters.
• The third one is an implementation of an interface written in functional programming language, it basically maps an ResultSet object to a list of entity objects.

When the method invocation is done, a list of objects of type CarModel will be returned. The functional programming part of the method is this:

(rs) -> {
   List<CarModel> retVal = new ArrayList<CarModel>();
   if (rs != null)
   {
      while(rs.next())
      {  
         CarModel cm = new CarModel();  
         cm.setYearOfManufacturing(rs.getInt("yearOfManufacture"));  
         cm.setMaker(rs.getString("make"));  
         cm.setModel(rs.getString("model"));
         cm.setSuggestedRetailPrice(rs.getFloat("suggestedretailprice"));
         cm.setFullPrice(rs.getFloat("fullprice"));
         cm.setRebateAmount(rs.getFloat("rebateamount"));
         retVal.add(cm);  
      }  
   }
   
   return  retVal;
}

The input parameter called "rs" is of type ResultSet. The arrow "->" basically states that the ResultSet object will be operated by the function defined in the enclosure {...}. In this enclosure, first it creates a list of CarModel. Then it checks to see if the ResultSet object is not null. It will iterate through the result set one row at a time and convert the row into a CarModel object. And the object will be added into the list. In the end, the list will be returned back. This list object is the list that will be returned by the findCars() method.

That is all there is about how to set up the sample project to use JdbcTemplate. It is time to test it out.

Test the Application

Now that we have everything for the application, it is time to test it and see if it works. And it works. Before we can test, we need to build it. Then, we need to start it up as a Java application.

To build the project, "cd" into the base directory of the sample project, and use the following command:

mvn clean install

To start up the application, use the following command in the base directory of the sample project:

java -jar target\hanbo-boot-rest-1.0.1.jar

The command line console will output a lot of text. In the end, it will succeed and output something like this:

...
2018-12-01 22:56:10.870  INFO 10832 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : 
Tomcat started on port(s): 8080 (http) with context path ''

2018-12-01 22:56:10.877  INFO 10832 --- [           main] org.hanbo.boot.rest.App                  : 
Started App in 15.656 seconds (JVM running for 18.044)

Now, it is time to test it. I use a program called "Postman" which can send HTTP requests to this RESTFul sample application. The first thing to be tried is adding a CarModel object into the database. The URL for this is:

http://localhost:8080/public/addCar

This URL only accepts HTTP Post request. The request body will be a JSON string that represents the CarModel object, which looks like this:

{
   "yearOfManufacturing": 2007,
   "maker": "Subaru",
   "model": "Outback",
   "suggestedRetailPrice": 17892.95,
   "fullPrice": 18900.95,
   "rebateAmount": 300.65
}

To use "Postman" to send the request, do this:

• Set the URL to http://localhost:8080/public/addCar
• To the left of the URL text box, select the HTTP method to be "POST".
• The request body should contain the JSON string.
• Set the content type to "application/json".

Here is a screenshot of the "Postman":

Click button "Send". And the server will return a success response. Here is a screenshot:

Now try a search of the cars by the maker "Subaru", the start year (value: 2006) and end year (value: 2008). The request is done via HTTP GET with request parameters. Here is the request URL:

http://localhost:8080/public/getCars?make=Subaru&startYear=2006&endYear=2008

The screenshot of "Postman" for this request looks like this:

Click the button "Send", and the request will be handled successfully with a JSON string representing the list of cars found. Depending on what cars you have added in the database, the list would have no elements, one or more elements. Here is a screenshot where I added the same car twice:

That is all. If you want to test transaction rollback, you can add an exception thrown in addCar() on the line after the call of sqlDao.update(). Then test the add car scenario. The exception will be thrown and prevent the row to be added into the database.

Summary

This is the last tutorial I am putting out for the year 2018. On December 31, 2017, I set out to write 10 tutorials in the year 2018, and I accomplished this by December 1^st, 2018. Over the whole year, I have put out tutorials on a wide varieties of topics. This is the latest of my effort.

In this tutorial, I have discussed the following topics:

• The configuration of the JDBC based data access.
• Repository object that can add entity to database and retrieve based on some simple criteria.
• The use of JdbcTemplate, specifically NamedParameterJdbcTemplate object for data insertion and retrieval.
• And some simple testing of the data access via the RESTFul interface of the sample application.

As always, it is a blast to put out a tutorial like this. I hope you enjoy it.

History

• 12-01-2018 - Initial draft