Spring Boot JdbcTemplate Example

In this tutorial, we will learn how to use JdbcTemplate in Spring boot application. We will be using the H2 in-memory database for storing the data.

Tools and Technologies Used

- Java 8

- Spring Boot 2.4.2

- Spring framework 5+

- H2 Database

- Eclipse STS

Development Process

1. Create Spring boot Project

2. Create User Class

3. Configure Database

4. Create UserRepository - JdbcTemplate

5. Run Spring boot project

6. Demo

1. Create Spring boot Project

Use the below guide to create a Spring boot project in Eclipse STS IDE:
-> Create Spring Boot Project in Spring Tool Suite [STS]

2. Maven Dependencies

After creating spring boot project, verify your pom.xml with below pom.xml file:

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.4.2</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>net.sourcecodeexamples</groupId>
	<artifactId>springboot-jdbc-example</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>springboot-jdbc-example</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>11</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<scope>runtime</scope>
		</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>

Notice that we are using a spring-boot-starter-jdbc module with Spring boot autoconfiguration for this module:

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jdbc</artifactId>
		</dependency>

3. Create User Class

Let's create a User class inside package "net.sourcecodeexamples.springboot" and add the following content to it:

package net.sourcecodeexamples.springboot;

/**
 * @author https://www.sourcecodeexamples.net/
 *
 */
public class User
{
	private Integer id;
	private String name;
	private String email;
	
	public User()
	{
	}

	public User(Integer id, String name, String email)
	{
		this.id = id;
		this.name = name;
		this.email = email;
	}

	public Integer getId()
	{
		return id;
	}

	public void setId(Integer id)
	{
		this.id = id;
	}

	public String getName()
	{
		return name;
	}

	public void setName(String name)
	{
		this.name = name;
	}

	public String getEmail()
	{
		return email;
	}

	public void setEmail(String email)
	{
		this.email = email;
	}

	@Override
	public String toString() {
		return "User [id=" + id + ", name=" + name + ", email=" + email + "]";
	}
}

4. Configure Database

Spring boot automatically configure database details for H2 in-memory database so we no need to explicitly add the database configuration in the application.properties file.

Let's add the following properties to the application.properties file:

logging.level.org.springframework=INFO

################### Hibernate Configuration ##########################
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

Let's create a schema.sql file under the resources folder and add the following content to it:

CREATE TABLE users (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(100) NOT NULL,
  email varchar(100) DEFAULT NULL,
  PRIMARY KEY (id)
);

Let's create a data.sql file under the resources folder and add the following content to it:

insert into users(id, name, email) values(1,'Admin','admin@gmail.com');
insert into users(id, name, email) values(2,'Laxmi','Laxmi@gmail.com');
insert into users(id, name, email) values(3,'Krishna','Krishna@gmail.com');

5. Create UserRepository - JdbcTemplate

Let's create a UserRepository class which talk with database. Add the following content to it:

package net.sourcecodeexamples.springboot;

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

/**
 * @author https://www.sourcecodeexamples.net/
 *
 */
@Repository
public class UserRepository
{
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Transactional(readOnly=true)
	public List findAll() {
		return jdbcTemplate.query("select * from users", new UserRowMapper());		
	}

	@Transactional(readOnly=true)
	public User findUserById(int id) {
		return jdbcTemplate.queryForObject("select * from users where id=?", new Object[]{id}, new UserRowMapper());
	}

	public User create(final User user) {
		final String sql = "insert into users(name,email) values(?,?)";
		
		KeyHolder holder = new GeneratedKeyHolder();

		jdbcTemplate.update(new PreparedStatementCreator() {           

                @Override
                public PreparedStatement createPreparedStatement(Connection connection)
                        throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, user.getName());
                    ps.setString(2, user.getEmail());
                    return ps;
                }
            }, holder);

		int newUserId = holder.getKey().intValue();
		user.setId(newUserId);		
		return user;
	}
}

class UserRowMapper implements RowMapper
{

	@Override
	public User mapRow(ResultSet rs, int rowNum) throws SQLException {
		User user = new User();
		user.setId(rs.getInt("id"));
		user.setName(rs.getString("name"));
		user.setEmail(rs.getString("email"));
		
		return user;
	}
}

6. Run and Test Spring boot project

Let's write the code to test different methods that would talk with the database:

package net.sourcecodeexamples.springboot;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SpringbootJdbcExampleApplication implements CommandLineRunner {

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

	@Autowired
	private UserRepository userRepository;

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

		User user1 = new User(0, "Admin", "admin@gmail.com");
		User savedUser = userRepository.create(user1);
		
		System.out.println("Saved user => " + savedUser);
		
		List users = userRepository.findAll();

		System.out.println("All users => " + users);
		
		User user = userRepository.findUserById(1);
		
		System.out.println("get user by id 1 => " + user);
	}
}

7. Demo

Run above project using maven command:

> G:\source-code-examples\springboot-jdbc-example>mvn test

Here the output:

Reference

https://www.javaguides.net/