Spring JdbcTemplate tutorial

In Spring JdbcTemplate tutorial, we show how to work with data using Spring's JdbcTemplate. We use Derby and MySQL databases. We create a Spring Boot application which uses JdbcTemplate. ZetCode has a complete e-book for MySQL Java, which contains an extended JdbcTemplate chapter: MySQL Java programming e-book.

Table of contents

Spring is a popular Java application framework. JdbcTemplate is a tool for simplifying programming with the JDBC. It takes care of tedious and error-prone low-level details such as handling transactions, cleaning up resources, and correctly handling exceptions. JdbcTemplate is included in Spring's spring-jdbc module. Spring Boot is a Spring's solution to create stand-alone, production-grade Spring based applications.

Apache Derby is an open source relational database implemented entirely in Java. It has small footprint and is easy to deploy and install. It supports both embedded and client/server modes.

MySQL is an open-source relational database management system. It is one of the most popular databases. It is often used in web applications.

Creating a database in Derby

We create a new testdb database in Derby. It will have a simple Cars table.

Database creation
Figure: Database creation

In the NetBeans' Services tab, we right-click on the Java DB node and select the Create Database option. We give it a testdb name. Note that the database is located in the .netbeans_derby directory of the user's home directory.

cars_derby.sql
CREATE TABLE CARS(ID INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY 
    (START WITH 1, INCREMENT BY 1), NAME VARCHAR(30), PRICE INT);

INSERT INTO CARS(Name, Price) VALUES('Audi', 52642);
INSERT INTO CARS(Name, Price) VALUES('Mercedes', 57127);
INSERT INTO CARS(Name, Price) VALUES('Skoda', 9000);
INSERT INTO CARS(Name, Price) VALUES('Volvo', 29000);
INSERT INTO CARS(Name, Price) VALUES('Bentley', 350000);
INSERT INTO CARS(Name, Price) VALUES('Citroen', 21000);
INSERT INTO CARS(Name, Price) VALUES('Hummer', 41400);
INSERT INTO CARS(Name, Price) VALUES('Volkswagen', 21600);

This is the SQL to create the Cars table; the ID of the car object is auto-incremented. We can use the NetBeans tools to create the Cars table. We right-click on the Databases node and select a New connection option.

Connections
Figure: Connections

A new connection object is created; it is represented by an orange icon. Its context menu provides options to connect to the specified database and execute a command. The Execute command option shows a tool to execute SQL commands. In this window, we can use the above SQL to create the Cars table.

Creating a database in MySQL

In this section, we create a new testdb database in MySQL. We use the mysql monitor to do the job, but we could easily use the NetBeans database tool as well.

cars_mysql.sql
DROP TABLE IF EXISTS Cars;
CREATE TABLE Cars(Id INT PRIMARY KEY AUTO_INCREMENT, 
                  Name VARCHAR(50), Price INTEGER) ENGINE=InnoDB;
                  
INSERT INTO Cars(Name, Price) VALUES('Audi', 52642);
INSERT INTO Cars(Name, Price) VALUES('Mercedes', 57127);
INSERT INTO Cars(Name, Price) VALUES('Skoda', 9000);
INSERT INTO Cars(Name, Price) VALUES('Volvo', 29000);
INSERT INTO Cars(Name, Price) VALUES('Bentley', 350000);
INSERT INTO Cars(Name, Price) VALUES('Citroen', 21000);
INSERT INTO Cars(Name, Price) VALUES('Hummer', 41400);
INSERT INTO Cars(Name, Price) VALUES('Volkswagen', 21600);

This is the SQL to create the Cars table in MySQL.

To create the database and the table, we use the mysql monitor tool.

$ sudo service mysql start

MySQL is started with sudo service mysql start command.

$ mysql -u user7 -p 

We connect to the database with the mysql monitor.

mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.02 sec)

With the CREATE DATABASE statement, a new database is created.

mysql> USE testdb;
mysql> SOURCE cars_mysql.sql

With the source command, we load and execute the cars_mysql.sql file.

mysql> SELECT * FROM Cars;
+----+------------+--------+
| Id | Name       | Price  |
+----+------------+--------+
|  1 | Audi       |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda      |   9000 |
|  4 | Volvo      |  29000 |
|  5 | Bentley    | 350000 |
|  6 | Citroen    |  21000 |
|  7 | Hummer     |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+
8 rows in set (0.00 sec)

We verify the data.

Maven dependencies

For our applications, we need to download the database drivers and the Spring modules. We do it with Maven.

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>4.3.5.RELEASE</version>
</dependency>

This will download the spring-jdbc module.

<dependency>
    <groupId>org.apache.derby</groupId>
    <artifactId>derbyclient</artifactId>
    <version>10.13.1.1</version>
</dependency>

This is the Maven dependency for the Derby driver.

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.40</version>
</dependency>

This is the Maven dependency for the MySQL driver.

The queryForObject() method

The queryForObject() method executes an SQL query and returns a result object. The result type is specified in the arguments.

SpringDBQueryObject.java
package com.zetcode;

import java.sql.SQLException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.SimpleDriverDataSource;

public class SpringDBQueryObject {
    
    public static void main(String[] args) throws SQLException {
        
        SimpleDriverDataSource ds = new SimpleDriverDataSource();
        ds.setDriver(new com.mysql.jdbc.Driver());
        ds.setUrl("jdbc:mysql://localhost:3306/testdb");
        ds.setUsername("user7");
        ds.setPassword("s$cret");
        
        String sql = "SELECT Count(*) FROM Cars";

        JdbcTemplate jtm = new JdbcTemplate(ds);
        int numOfCars = jtm.queryForObject(sql, Integer.class);
        
        System.out.format("There are %d cars in the table", numOfCars);
    }    
}

In the example, we use the queryForObject() method to get the number of cars in the Cars table.

String sql = "SELECT Count(*) FROM Cars";

This SQL returns the number of rows in the \codeinline{Cars} table.

int numOfCars = jtm.queryForObject(sql, Integer.class);

The second parameter of the queryForObject() method specifies the type of the result; an Integer in our case.

RowMapper

RowMapper maps rows of a result set on a per-row basis. Implementations of this interface perform the actual work of mapping each row to a result object.

Car.java
package com.zetcode;

public class Car {

    private Long Id;
    private String name;
    private int price;

    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 int getPrice() {
        return price;
    }

    public void setPrice(int price) {
        this.price = price;
    }
}

We have a Car class.

SpringDBRowMapper.java
package com.zetcode;

import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.SimpleDriverDataSource;

public class SpringDBRowMapper {

    public static void main(String[] args) throws SQLException {

        SimpleDriverDataSource ds = new SimpleDriverDataSource();
        ds.setDriver(new com.mysql.jdbc.Driver());
        ds.setUrl("jdbc:mysql://localhost:3306/testdb");
        ds.setUsername("user7");
        ds.setPassword("s$cret");
        
        RowMapper rm = (RowMapper<Car>) (ResultSet result, 
                int rowNum) -> {
                
            Car car = new Car();
            car.setId(result.getLong("Id"));
            car.setName(result.getString("Name"));
            car.setPrice(result.getInt("Price"));
            
            return car;
        };

        String sql = "SELECT * FROM Cars WHERE Id=?";
        Long id = 1L;
                
        JdbcTemplate jtm = new JdbcTemplate(ds);
        Car car = (Car) jtm.queryForObject(sql, new Object[] {id}, rm);
      
        System.out.printf("%d ", car.getId());
        System.out.printf("%s ", car.getName());
        System.out.printf("%d ", car.getPrice());
    }
}

In the example we use the RowMapper to map rows of the result set to the Car object.

RowMapper rm = (RowMapper<Car>) (ResultSet result, 
        int rowNum) -> {
        
    Car car = new Car();
    car.setId(result.getLong("Id"));
    car.setName(result.getString("Name"));
    car.setPrice(result.getInt("Price"));
    
    return car;
};

This is the mapping of the result set rows to the Car object.

Car car = (Car) jtm.queryForObject(sql, new Object[] {id}, rm);

The instance of the RowMapper is passed to the queryForObject() as the third parameter.

BeanPropertyRowMapper

The following example connects to the testdb database in Derby and gets a single car. It uses the BeanPropertyRowMapper to do the task.

BeanPropertyRowMapper is a RowMapper implementation that converts a row into a new instance of the specified mapped target class. The mapped target class must be a top-level class and it must have a default or no-arg constructor. Column names from the result set metadata are matched to the public setters for the corresponding properties.

pom.xml
<?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>com.zetcode</groupId>
    <artifactId>SpringDBClient</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>
    
    <dependencies>      
        <dependency>
            <groupId>org.apache.derby</groupId>
            <artifactId>derbyclient</artifactId>
            <version>10.12.1.1</version>
        </dependency>      
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>4.2.6.RELEASE</version>
        </dependency>
    </dependencies>        
</project>

This is the pom.xml file. Our dependencies include derbyclient and spring-jdbc. Spring JDBC module can be used outside of a Spring application. The spring-jdbc module provides a JDBC-abstraction layer that removes the need to do tedious JDBC coding and parsing of database-vendor specific error codes.

Car.java
package com.zetcode;

public class Car {

    private Long Id;
    private String name;
    private int price;

    // getters and setters
}

This is the Car bean to which we map the Cars table columns.

SpringDBClient.java
package com.zetcode;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.SimpleDriverDataSource;

public class SpringDBClient {

    public static void main(String[] args) {

        SimpleDriverDataSource dataSource = new SimpleDriverDataSource();
        dataSource.setDriver(new org.apache.derby.jdbc.ClientDriver());
        dataSource.setUrl("jdbc:derby://localhost:1527/testdb");
        dataSource.setUsername("app");
        dataSource.setPassword("app");

        String sql = "SELECT * FROM Cars WHERE Id=?";
        Long id = 1L;
                
        JdbcTemplate jtm = new JdbcTemplate(dataSource);
        
        Car car = (Car) jtm.queryForObject(sql, new Object[] {id}, 
                new BeanPropertyRowMapper(Car.class));        

        System.out.printf("%d ", car.getId());
        System.out.printf("%s ", car.getName());
        System.out.printf("%d ", car.getPrice());
    }
}

The example connects to the testdb database and retrieves one car from the Cars table.

SimpleDriverDataSource dataSource = new SimpleDriverDataSource();
dataSource.setDriver(new org.apache.derby.jdbc.ClientDriver());
dataSource.setUrl("jdbc:derby://localhost:1527/testdb");
dataSource.setUsername("app");
dataSource.setPassword("app");

We set up the data source for the Derby database. The SimpleDriverDataSource is a simple implementation of the standard JDBC DataSource interface, configuring a plain old JDBC Driver via properties.

String sql = "SELECT * FROM Cars WHERE Id=?";

This SQL statement selects a car object from the database.

JdbcTemplate jtm = new JdbcTemplate(dataSource);

A JdbcTemplate is created; it takes a data source as a parameter.

Car car = (Car) jtm.queryForObject(sql, new Object[] {id}, 
        new BeanPropertyRowMapper(Car.class));

With the queryForObject() method, we query for an object. We provide the SQL statement, the parameter, and the row mapper. The BeanPropertyRowMapper converts a row into a new instance of the Car target class.

System.out.printf("%d ", car.getId());
System.out.printf("%s ", car.getName());
System.out.printf("%d ", car.getPrice());

We print the data of the retrieved car to the terminal.

1 Audi 52642 

The application prints the first row from the Cars table.

The queryForList() method

The queryForList() method executes a query for a result list. In the following example, we retrieve all cars from the Cars table. This time we connect to the MySQL database.

pom.xml
<?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>com.zetcode</groupId>
    <artifactId>SpringDBClient2</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>
    
    <dependencies>
      
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.40</version>
        </dependency> 
        
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>4.3.5.RELEASE</version>
        </dependency>
    </dependencies>    
</project>

This is the pom.xml file for the project with the MySQL driver. Note that currently some driver versions may have issues with time zone or SSL. Choose version 5.1.39 if you encounter them.

Database properties
Figure: Database properties

We place the datasource attributes into the db.properties file. It is better to separate resources from the Java files.

db.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/testdb
jdbc.username=user7
jdbc.password=s$cret

These are the properties for the MySQL database.

SpringDBQueryForList.java
package com.zetcode;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Driver;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.SimpleDriverDataSource;

public class SpringDBQueryForList {

    public static void main(String[] args) throws IOException, 
            ClassNotFoundException {

        Properties prop = new Properties();
        prop.load(new FileInputStream("src/main/resources/db.properties"));

        SimpleDriverDataSource ds = new SimpleDriverDataSource();
        ds.setDriverClass(((Class<Driver>) Class.forName(prop.getProperty("jdbc.driver"))));
        ds.setUrl(prop.getProperty("jdbc.url"));
        ds.setUsername(prop.getProperty("jdbc.username"));
        ds.setPassword(prop.getProperty("jdbc.password"));
        
        String sql = "SELECT * FROM Cars";

        JdbcTemplate jtm = new JdbcTemplate(ds);
        List<Map<String, Object>> rows = jtm.queryForList(sql);

        rows.stream().forEach((row) -> {
            System.out.printf("%d ", row.get("Id"));
            System.out.printf("%s ", row.get("Name"));
            System.out.println(row.get("Price"));
        });
    }
}

The example connects to the MySQL testdb database and retrieves all rows from the Cars table.

Properties prop = new Properties();
prop.load(new FileInputStream("src/main/resources/db.properties"));

The data source properties are loaded from the db.properties file.

SimpleDriverDataSource ds = new SimpleDriverDataSource();
ds.setDriverClass(((Class<Driver>) Class.forName(prop.getProperty("jdbc.driver"))));
ds.setUrl(prop.getProperty("jdbc.url"));
ds.setUsername(prop.getProperty("jdbc.username"));
ds.setPassword(prop.getProperty("jdbc.password"));

We fill the SimpleDriverDataSource's attributes with the properties.

JdbcTemplate jtm = new JdbcTemplate(ds);
List<Map<String, Object>> rows = jtm.queryForList(sql);

The JdbcTemplate's queryForList() method returns a list of rows from the table.

rows.stream().forEach((row) -> {
    System.out.printf("%d ", row.get("Id"));
    System.out.printf("%s ", row.get("Name"));
    System.out.println(row.get("Price"));
});

We go through the list and print the data to the terminal.

1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
6 Citroen 21000
7 Hummer 41400
8 Volkswagen 21600

This is the output of the example.

Using named parameters

NamedParameterJdbcTemplate is a template class with a basic set of JDBC operations, allowing the use of named parameters rather than traditional '?' placeholders.

db.properties
jdbc.driver=org.apache.derby.jdbc.ClientDriver
jdbc.url=jdbc:derby://localhost:1527/testdb
jdbc.username=app
jdbc.password=app

These are the properties for the Derby database.

Car.java
package com.zetcode;

public class Car {

    private Long Id;
    private String name;
    private int price;

    // getters and setters
}

This is the Car bean.

SpringDBClient3.java
package com.zetcode;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Driver;
import java.util.Properties;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.datasource.SimpleDriverDataSource;

public class SpringDBClient3 {
    
    public static void main(String[] args) throws IOException, 
            ClassNotFoundException {

        Properties prop = new Properties();
        prop.load(new FileInputStream("src/main/resources/db.properties"));

        SimpleDriverDataSource ds = new SimpleDriverDataSource();
        ds.setDriverClass(((Class<Driver>) Class.forName(prop.getProperty("jdbc.driver"))));
        ds.setUrl(prop.getProperty("jdbc.url"));
        ds.setUsername(prop.getProperty("jdbc.username"));
        ds.setPassword(prop.getProperty("jdbc.password"));
        
        String sql = "SELECT * FROM Cars WHERE Name LIKE :name";
        String carName = "Volvo";

        NamedParameterJdbcTemplate jtm = new NamedParameterJdbcTemplate(ds);
        
        SqlParameterSource namedParams = new MapSqlParameterSource("name", carName); 
        Car car = (Car) jtm.queryForObject(sql, namedParams, 
                new BeanPropertyRowMapper(Car.class));
        
        System.out.printf("%d ", car.getId());
        System.out.printf("%s ", car.getName());
        System.out.printf("%d ", car.getPrice());
    }
}

The example looks for a car name; its SQL code uses a named parameter.

String sql = "SELECT * FROM Cars WHERE Name LIKE :name";

The SQL has the :name token, which is a named parameter.

NamedParameterJdbcTemplate jtm = new NamedParameterJdbcTemplate(ds);

The NamedParameterJdbcTemplate is used for named parameters.

SqlParameterSource namedParams = new MapSqlParameterSource("name", carName); 

The MapSqlParameterSource is used to pass in a simple Map of parameter values to the methods of the NamedParameterJdbcTemplate class.

Car car = (Car) jtm.queryForObject(sql, namedParams, 
        new BeanPropertyRowMapper(Car.class));

The named parameter is passed as the second argument to the queryForObject() method.

4 Volvo 29000 

This is the output of the example.

Classic Spring with JdbcTemplate

In this example, we create a command line classic Spring application that will use JdbcTemplate to connect to the database and issue SQL statements.

Classic Spring project
Figure: Classic Spring project

This is the project structure in NetBeans.

pom.xml
<?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>com.zetcode</groupId>
    <artifactId>SpringJdbcTemplateEx</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    
    <properties>
        
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
        <spring-version>4.3.6.RELEASE</spring-version>
        
    </properties>
    
    <dependencies>
        
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.40</version>
        </dependency>        
        
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>${spring-version}</version>
        </dependency>
        
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>${spring-version}</version>
        </dependency>
        
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring-version}</version>
        </dependency>
        
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring-version}</version>
        </dependency>
    
    </dependencies>    

</project>

The Maven pom.xml file contains dependencies for the MySQL driver, core Spring libraries, and JdbcTemplate.

my-beans.xml
<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans.xsd">

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/testdb?useSSL=false"/>
        <property name="username" value="testuser"/>
        <property name="password" value="test623"/>
    </bean>
    
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>    
    
</beans>

The my-beans.xml file contains definitions of beans managed by Spring framework. We have two beans: dataSource and jdbcTemplate. The jdbcTemplate bean refers to the bean via the ref attribute.

SpringJdbcTemplateEx.java
package com.zetcode;

import com.zetcode.bean.Friend;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

public class SpringJdbcTemplateEx {

    public static void main(String[] args) {

        ApplicationContext ctx
                = new ClassPathXmlApplicationContext("my-beans.xml");

        JdbcTemplate jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");

        jdbcTemplate.execute("DROP TABLE IF EXISTS Friends");
        jdbcTemplate.execute("CREATE TABLE Friends(Id INT, Name VARCHAR(30), "
                + "Age INT)");
        jdbcTemplate.update("INSERT INTO Friends VALUES(1, 'Paul', 27)");
        jdbcTemplate.update("INSERT INTO Friends VALUES(2, 'Monika', 34)");
        jdbcTemplate.update("INSERT INTO Friends VALUES(3, 'Peter', 20)");
        jdbcTemplate.update("INSERT INTO Friends VALUES(4, 'Lucy', 45)");
        jdbcTemplate.update("INSERT INTO Friends VALUES(5, 'Roman', 57)");

        int id = 1;
        String sql = "SELECT * FROM Friends WHERE Id=?";

        Friend f = (Friend) jdbcTemplate.queryForObject(sql, new Object[]{id},
                new BeanPropertyRowMapper(Friend.class));

        System.out.println(f);

        List<Friend> allFriends = jdbcTemplate.query("SELECT * FROM Friends",
                new BeanPropertyRowMapper(Friend.class));
        
        allFriends.stream().forEach(System.out::println);
    }
}

The example creates a new table and fills it with data. Later, we retrieve a specific row and all rows from the table with JdbcTemplate.

ApplicationContext ctx
        = new ClassPathXmlApplicationContext("my-beans.xml");

The ApplicationContext is created from the my-beans.xml file. It instatiates the beans from the XML file.

JdbcTemplate jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");

The getBean() method returns the instance of the jdbcTemplate bean.

jdbcTemplate.execute("DROP TABLE IF EXISTS Friends");
jdbcTemplate.execute("CREATE TABLE Friends(Id INT, Name VARCHAR(30), "
        + "Age INT)");

We use the JdbcTemplate's execute() method to issue DDL statements.

jdbcTemplate.update("INSERT INTO Friends VALUES(1, 'Paul', 27)");

The JdbcTemplate's update() method runs INSERT statements.

Friend f = (Friend) jdbcTemplate.queryForObject(sql, new Object[]{id},
        new BeanPropertyRowMapper(Friend.class));

With the JdbcTemplate's queryForObject() method we fetch a specifc Friend object. The BeanPropertyRowMapper converts a table row into a new instance of the specified mapped target class.

List<Friend> allFriends = jdbcTemplate.query("SELECT * FROM Friends",
        new BeanPropertyRowMapper(Friend.class));

With the JdbcTemplate's query() method, we select all friends from the table.

Spring Boot with JdbcTemplate

In this example, we create a command line Spring Boot application that will use JdbcTemplate to connect to the database. We will have two datasources: one for Derby and one for MySQL. The project is available at the author's Github page.

Spring Boot project
Figure: Spring Boot project

This is how our Spring Boot project looks like in NetBeans.

pom.xml
<?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>com.zetcode</groupId>
    <artifactId>SpringBootDBClient</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>
    
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.4.3.RELEASE</version>
        <relativePath />
    </parent>       
    
    <dependencies>
        <dependency>
            <groupId>org.apache.derby</groupId>
            <artifactId>derbyclient</artifactId>
            <version>10.13.1.1</version>
        </dependency>      
        
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>            
        </dependency> 
        
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <scope>provided</scope>
        </dependency>            

    </dependencies>
    
    <name>SpringBootDBClient</name>
</project>

The pom.xml file contains dependencies for the Spring Boot and Derby and MySQL drivers.

application.properties
# Derby
spring.derbydatasource.driverClassName=org.apache.derby.jdbc.ClientDriver
spring.derbydatasource.url=jdbc:derby://localhost:1527/testdb
spring.derbydatasource.username=app
spring.derbydatasource.password=app

# MySQL
spring.mysqldatasource.driverClassName = com.mysql.jdbc.Driver
spring.mysqldatasource.url = jdbc:mysql://localhost:3306/testdb
spring.mysqldatasource.username = testuser
spring.mysqldatasource.password = test623

In the application.properties file, we define Derby and MySQL datasources.

AppConfig.java
package com.zetcode.conf;

import javax.sql.DataSource;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

@Configuration
public class AppConfig {

    @Bean
    @Primary
    @ConfigurationProperties(prefix = "spring.derbydatasource")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.mysqldatasource")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().build();
    }
}

Spring creates automatically bean datasources for both databases. The @Primary annotation specifies the default datasource.

Car.java
package com.zetcode.bean;

public class Car {

    private Long Id;
    private String name;
    private int price;

    // getters and setters
}

This is our Car bean.

CarsDAO.java
package com.zetcode.dao;

import com.zetcode.bean.Car;
import java.util.List;

public interface CarsDAO {

  public void saveCar(Car car);
  public Car findCarByName(String name);
  public List<Car> findAll();
}

In the CarsDAO interface, we provide the contract for the access to the database.

DBCarsDAO.java
package com.zetcode.dao;

import com.zetcode.bean.Car;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class DBCarsDAO implements CarsDAO {
   
    @Autowired
    protected JdbcTemplate jtm;    

    @Override
    public void saveCar(Car car) {

        String sql = "INSERT INTO Cars(Name, Price) VALUES (?, ?)";
        Object[] params = new Object[] {car.getName(), car.getPrice()};

        jtm.update(sql, params);
    }

    @Override
    public Car findCarByName(String name) {

        String sql = "SELECT * FROM Cars WHERE Name = ?";
        Object[] param = new Object[] {name};

        Car car = (Car) jtm.queryForObject(sql, param,
                new BeanPropertyRowMapper(Car.class));

        return car;
    }

    @Override
    public List<Car> findAll() {

        String sql = "SELECT * FROM Cars";

        List<Car> cars = jtm.query(sql, new BeanPropertyRowMapper(Car.class));

        return cars;
    }
}

The DBCarsDAO class creates an implementation for the CarsDAO contract. We have three methods to save a car, find a car by its name, and retrieve all cars. We use the JdbcTemplate to work with the database.

@Autowired
protected JdbcTemplate jtm;

Spring automatically injects the JdbcTemplate bean. The bean takes the datasource that is specified in the AppConfig.

SpringDBClient.java
package com.zetcode.client;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.context.annotation.ComponentScan;

@EnableAutoConfiguration
@ComponentScan(basePackages="com.zetcode")
public class SpringDBClient {

    public static void main(String[] args) {

        SpringApplication.run(SpringDBClient.class, args);
    }
}

This is the entry point to our command line Spring application. The @EnableAutoConfiguration annotation enables auto-configuration of the Spring Application Context, attempting to guess and configure beans we would likely need. Auto-configuration classes are usually applied based on our classpath and what beans we have defined. With the @ComponentScan annotation we tell Spring where to look for Spring components.

MyRunner.java
package com.zetcode.client;

import com.zetcode.dao.CarsDAO;
import com.zetcode.bean.Car;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.stereotype.Component;

@Component
public class MyRunner implements CommandLineRunner {

    @Autowired
    private CarsDAO cdao;

    @Override
    public void run(String... args) throws Exception {

        try {
            Car car = cdao.findCarByName("Citroen");
            System.out.printf("ID: %d%n", car.getId());
            System.out.printf("Name: %s%n", car.getName());
            System.out.printf("Price: %d%n", car.getPrice());
            
        } catch (EmptyResultDataAccessException e) {
            System.out.println("Car was not found");
        }

        List<Car> cars = cdao.findAll();
        
        for (Car car: cars) {
            System.out.printf("%d ", car.getId());
            System.out.printf("%s ", car.getName());
            System.out.println(car.getPrice());
        }
    }
}

The Spring Boot command line application must implement the CommandLineRunner interface. We put the code to be executed into the run() method.

@Autowired
private CarsDAO cdao;

Spring injects the dao implementation into this class.

try {
    Car car = cdao.findCarByName("Citroen");
    System.out.printf("ID: %d%n", car.getId());
    System.out.printf("Name: %s%n", car.getName());
    System.out.printf("Price: %d%n", car.getPrice());
    
} catch (EmptyResultDataAccessException e) {
    System.out.println("Car was not found");
}

We try to find a car with the name Citroen. If there is no such a car, Spring throws an EmptyResultDataAccessException exception.

List<Car> cars = cdao.findAll();

for (Car car: cars) {
    System.out.printf("%d ", car.getId());
    System.out.printf("%s ", car.getName());
    System.out.println(car.getPrice());
}

We retrieve all cars from the database with the findAll() method. The data is printed to the console.

In this tutorial, we have presented the Spring's JdbcTemplate module. We have created a Spring Boot application that utilizes JdbcTemplate. ZetCode has the following related tutorials: Java tutorial, Spring JdbcTemplate tutorial, EclipseLink tutorial, Hibernate Derby tutorial, Servlet FreeMarker JdbcTemplate tutorial, MySQL Java tutorial, PostgreSQL Java tutorial, and Apache Derby tutorial.