1. Overview
In this tutorial, we will build a simple project with the Spring JDBC template that can access a MySql database. The project consists of a database, data source, entity, and a Spring service to access jdbcTemplate methods. I use maven as the build tool. The pom.xml is set up to use Java 17, Spring 5.3.20, and MySql connector 8.0.29.
2. What is the Spring jdbctemplate?
Spring JDBC module is an abstraction layer on top of java's JDBC implementation. Spring's JDBC implementation helps us to avoid the boilerplate code used in JDBC included in the standard JDK. Additionally, Spring JDBC provides its own ways to translate exceptions (eliminates checked exceptions) and simple ORM capabilities.
Accessing relational databases with jdbctemplate is one of the best ways to abstract the complications introduced by regular java JDBC. To utilize jdbctemplate in our applications, we have to introduce the spring framework's jdbctemplate library and related dependencies into our project. There are several ways to do that. This tutorial will show you how to integrate jdbctemplate in our project's pom.xml file.
3. Add spring capabilities to pom.xml
First, we have to add spring framework capabilities to our pom.xml file. Below is the additional code that we add to pom.xml to make use of the spring framework.
<properties>
<spring.version>5.3.20</spring.version>
<maven.compiler.source>17</maven.compiler.source>
<maven.compiler.target>17</maven.compiler.target>
</properties>
<dependencies>
<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>Add MySQL capabilities to pom.xml
In order for spring jdbctemplate to access databases, it needs a database connector. We will use mysql connector.
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.29</version>
</dependency>4. Complete pom.xml file
The below code illustrates the complete pom.xml file with jdbctemplate and mysql connector support.
<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.learnbestcoding</groupId>
<artifactId>jdbctemplate</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>jdbctemplate</name>
<url>http://maven.apache.org</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<spring.version>5.3.20</spring.version>
<maven.compiler.source>17</maven.compiler.source>
<maven.compiler.target>17</maven.compiler.target>
</properties>
<dependencies>
<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>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.29</version>
</dependency>
</dependencies>
</project>5. Setting up MySQL database to access
We will use the MySql database as our persistence layer. But these instructions are valid for any database such as Postgres, Oracle, MsSQL, etc. If that is the case, ensure to use proper SQL statements for DDL and DML statements that are valid for your database vendor. We try to follow ANSI standards throughout this tutorial to minimize such impacts. Also, we assume that the database is running on localhost port 3306.
5.1 Create a table to access
Let's create a simple database and a table named customers that holds information about customers. Our database name is springweb.
create database springweb;
use springweb;
CREATE TABLE customers (
customer_id BIGINT(20) NOT NULL AUTO_INCREMENT,
customer_name VARCHAR(100) NOT NULL,
address VARCHAR(100) DEFAULT NULL,
no_of_orders int DEFAULT '0'
PRIMARY KEY (customer_id)
)
insert into customers(customer_id,customer_name,address,no_of_orders) values
(1,'Homedepot','Some street, New York',1),
(2,'Lows','2nd street, Toronto',2),
(3,'Bunnings','1st street, Melbourne',3);
Now we have a customers table with 3 records inserted to work with.
6. Accessing database with jdbctemplate
Before writing SQL queries to access data from our database, it is a good practice to set up a data source that we can re-use from anywhere in the application.
6.1 Creating a datasource
Data sources help to reduce boilerplate code by centralizing the parameters we use to access the database. To achieve this, let's create a class and name it JdbcConfiguration.java.
package com.java.jdbctemplate;
import javax.sql.DataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
@Configuration
@ComponentScan("com.java")
public class JdbcConfiguration {
@Bean
public static DataSource mysqlDataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/springweb");
dataSource.setUsername("springweb");
dataSource.setPassword("springweb-password");
return dataSource;
}
}
Feel free to change your user name and password accordingly. What this code does is create and return a connection to your database with the provided credentials. We will call this code before performing any CRUD (Create, Read, Update, Delete) operations.
6.2 A POJO (Plain Old Java Object) to map results
The simple table we have created above has only a handful of columns. However, in the real world, this is not the case. Tables can have a number of columns, making it inconvenient to use a standard resultset to extract results. Therefore, our Customers.java class will represent each row in the table.
package com.java.jdbctemplate;
public class Customers {
Integer customerId;
String customerName;
String address;
public Integer getCustomerId() {
return customerId;
}
public void setCustomerId(Integer customerId) {
this.customerId = customerId;
}
public String getCustomerName() {
return customerName;
}
public void setCustomer_name(String customerName) {
this.customerName = customerName;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
/*This is for demo purposes only*/
public String toString()
{
return "\n" + customerId + " - " + customerName + " - " + address;
}
}
7. Testing jdbctemplate queryforobject with parameters
The JDBC templates queryForObject() accepts a query and a class type as arguments. The queryForObject type casts the query result to the class we provided as an argument. In other words, the class type represents the data type the queryForObject returns after executing the query. The class should be a non-primitive type. It can be a custom RowMapper to map returned fields to a Java Object. However, there are several overrides for queryForObject() with different arguments.
Let's test out our data access implementation in a regular java main method. This is entirely for testing purposes and not useful in a production environment. I have demonstrated a few CRUD operations you can perform with the jdbctemplate.
package com.java.jdbctemplate;
import java.util.List;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
public class CustomerFinder {
static JdbcTemplate jdbcTemplate;
public static void main(String args[])
{
jdbcTemplate = new JdbcTemplate(JdbcCofiguration.mysqlDataSource());
List customers = jdbcTemplate.query("SELECT * FROM customers", new BeanPropertyRowMapper(Customers.class));
int count = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM customers", Integer.class);
String name = jdbcTemplate.queryForObject("SELECT customer_name FROM customers where customer_id = ? ", String.class, new Object[]{3});
System.out.println("We have "+ count + " customers. They are : "+customers.toString());
System.out.println("Customer id 3 belongs to "+ name);
jdbcTemplate.update("INSERT INTO customers (customer_id, customer_name, address, no_of_orders) VALUES (?, ?, ?, ?)", 4, "Happy Hardware", "1st St, Vancouver", 4);
jdbcTemplate.update("Update customers set no_of_orders = ? where customer_id = ?", 10, 4);
customers = jdbcTemplate.query("SELECT * FROM customers", new BeanPropertyRowMapper(Customers.class));
count = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM customers", Integer.class);
System.out.println("Now we have "+ count + " customers. They are : "+customers.toString());
jdbcTemplate.update("Delete from customers where customer_id = ?",4);
}
}
In order for BeanPropertyRowMapper to work, your customers' table's field names should match with Customers' classes field names. i.e the customer_id field in the table should have a matching customerId variable in the customers class. In case if they don't match, you will need a RowMapper to map table fields to class fields manually.
8. Using jdbctemplate in a spring service
In the practical world, mostly we use data access services in the spring framework's service layer. By embedding our code in spring service, we can utilize spring annotations to auto-wire our variable references. It also handles specific data access layer-specific exceptions.
package com.java.jdbctemplate;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@Service("springDaoImpl")
public class SpringDaoImpl {
static JdbcTemplate jdbcTemplate;
@Autowired
public SpringDaoImpl(DataSource dataSource) {
jdbcTemplate = new JdbcTemplate(dataSource);
}
public List<Customers> findAllCustomers()
{
return jdbcTemplate.query("SELECT * FROM customers", new BeanPropertyRowMapper<Customers>(Customers.class));
}
public Integer findCustomerCount()
{
return jdbcTemplate.queryForObject("SELECT COUNT(*) FROM customers", Integer.class);
}
@Transactional
public void deleteCustomer(int customerId)
{
jdbcTemplate.update("DELETE FROM CUSTOMERS WHERE CUSTOMER_ID = ?", new Object[] {customerId});
}
@Transactional
public void insertCustomer(Customers customer)
{
jdbcTemplate.update("INSERT INTO CUSTOMERS (CUSTOMER_NAME, ADDRESS) VALUES (?,?)", new Object[] {customer.getCustomerName(), customer.getAddress()});
}
}
Download Spring jdbc template
