Spring - JDBC Template

Last Updated : 4 May, 2026

Spring JDBC Template is a part of the Spring Framework that simplifies database operations by removing boilerplate JDBC code. It provides a clean and efficient way to interact with relational databases using SQL queries. It handles connection management, exception handling, and resource cleanup automatically.

  • Reduces JDBC boilerplate code like opening/closing connections manually
  • Provides built-in exception handling using Spring’s DataAccessException hierarchy
  • Supports easy execution of SQL queries, updates, and result mapping

Types of JDBC Drivers

There are 4 types of JDBC Drivers.

1. JDBC-ODBC Bridge Driver: Connects to databases using an ODBC driver and is platform-dependent. It is not recommended for production use.

  • Uses ODBC driver to connect to the database
  • Platform-dependent and now obsolete

2. Native API Driver : Uses the database’s native API for communication and is partially written in Java. It is also platform-dependent.

  • Uses database-specific native libraries
  • Faster than Type 1 but not fully portable

3. Network Protocol Driver: Uses middleware to communicate with the database and is fully Java-based. It provides platform independence.

  • Uses a middleware server for communication
  • Supports multiple databases and is platform-independent

4. Thin Driver: Directly communicates with the database using its network protocol and is fully Java-based. It is the most commonly used JDBC driver.

  • Directly communicates with the database using Java
  • Fully portable and widely used in modern applications

Java Database Connectivity (JDBC) Architecture

This diagram shows how a Java application interacts with a database using JDBC, where the JDBC Driver Manager acts as a bridge between the Java API and the vendor-specific database system.

Java Database Connectivity

Advantages of JDBC API   

  • JDBC API helps retrieve and manipulate data from the database efficiently.
  • It supports query and stored procedures.
  • Almost any database for which ODBC driver is installed can be accessed.

Disadvantages of JDBC API  

  • Writing a lot of codes before and after executing the query, such as creating connection, creating a statement, closing result-set, closing connection, etc.
  • Writing exception handling code on the database logic.
  • Repetition of these codes from one to another database logic is time-consuming.

Data Access using Spring JDBC Template

There are a number of options for selecting an approach to form the basis for your JDBC database access. Spring framework provides the following approaches for JDBC database access:

  • JdbcTemplate
  • NamedParameterJdbcTemplate
  • SimpleJdbcTemplate
  • SimpleJdbcInsert and SimpleJdbcCall

1. JDBC Template

JdbcTemplate is a core class in the Spring JDBC module that simplifies database access by removing boilerplate JDBC code. It provides an easy and efficient way to execute SQL queries and handle results.

  • Executes SQL queries and updates while handling connection and resource management automatically
  • Converts JDBC exceptions into Spring’s consistent DataAccessException hierarchy
  • Simplifies ResultSet processing by reducing manual iteration and error handling

The common methods of spring JdbcTemplate class:

MethodsDescription
public int update(String query)Used to insert, update and delete records.
public int update(String query, Object... args)Used to insert, update and delete records using PreparedStatement using given arguments.
public T execute(String sql, PreparedStatementCallback action)Executes the query by using PreparedStatementCallback.
public void execute(String query)Used to execute DDL query.
public T query(String sql, ResultSetExtractor result)Used to fetch records using ResultSetExtractor.

JDBC Template Queries

1. Counting Records: Basic query to count students stored in the database using JdbcTemplate.

int count = jdbcTemplate.queryForObject(

"SELECT COUNT(*) FROM STUDENT", Integer.class);

2. Inserting a Record: Basic query to insert elements into the database.

public int addStudent(int id, String name, String country) {

return jdbcTemplate.update(

"INSERT INTO STUDENT (id, name, country) VALUES (?, ?, ?)",

id, name, country);

}

Note: The standard syntax of providing parameters is using the "?" character.

3. Fetching Records: Basic query to fetch records from the database.

public List<Student> getAllStudents() {

return jdbcTemplate.query(

"SELECT * FROM STUDENT",

new BeanPropertyRowMapper<>(Student.class));

}

Implementation: Spring JDBC Template

Given below are the steps to configure and use JdbcTemplate in a Spring application.

Step 1: Configure the Data Source

Start by configuring the DataSource and JdbcTemplate in a Spring configuration class.

SpringJdbcConfig.java:

Java 
@Configuration
@ComponentScan("com.exploit.jdbc")
public class SpringJdbcConfig {

    @Bean
    public DataSource mysqlDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        
        // Correct MySQL driver class for MySQL 8.x
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        
        // Correct URL with default MySQL port and timezone configuration
        dataSource.setUrl("jdbc:mysql://localhost:3306/springjdbc?serverTimezone=UTC");
        
        // Use environment variables or externalized configuration for credentials
        // Replace with actual username
        dataSource.setUsername("user");
        // Replace with actual password
        dataSource.setPassword("password"); 

        return dataSource;
    }
}

Step 2: Create the Model Class

Define a model class to represent the database table.

Student.java:

Java 
package com.exploit.org;

import lombok.Data;
import lombok.NoArgsConstructor;

// Lombok annotations to generate getters, setters, toString, equals, and hashCode
@Data
@NoArgsConstructor
public class Student {

    // Class data members
    private Integer id;
    private String name;
    private Integer age;

    // Parameterized Constructor
    public Student(Integer id, String name, Integer age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }
}

Step 3: Create the DAO Interface

Define a DAO interface for database operations.

StudentDAO.java:

Java 
package com.exploit.org;

// Importing required classes
import java.util.List;
import org.springframework.dao.DataAccessException;

// Interface
public interface StudentDAO {

    // Create: Insert a new student record
    void createStudent(Student student) throws DataAccessException;

    // Read: Retrieve a student by ID
    Student getStudentById(Integer id) throws DataAccessException;

    // Read: List all students
    List<Student> listStudents() throws DataAccessException;

    // Update: Update an existing student record
    void updateStudent(Student student) throws DataAccessException;

    // Delete: Delete a student by ID
    void deleteStudent(Integer id) throws DataAccessException;
}

Step 4: Implement the DAO Interface

Implement the DAO interface using JdbcTemplate.

Java 
package com.exploit.org;

// Importing required classes
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

@Repository
public class StudentDAOImpl implements StudentDAO {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    // RowMapper to map result set to Student object
    private final RowMapper<Student> rowMapper = (rs, rowNum) -> {
        Student student = new Student();
        student.setId(rs.getInt("id"));
        student.setName(rs.getString("name"));
        student.setAge(rs.getInt("age"));
        return student;
    };

    @Override
    public void createStudent(Student student) throws DataAccessException {
        String sql = "INSERT INTO Student (id, name, age) VALUES (?, ?, ?)";
        jdbcTemplate.update(sql, student.getId(), student.getName(), student.getAge());
    }

    @Override
    public Student getStudentById(Integer id) throws DataAccessException {
        String sql = "SELECT * FROM Student WHERE id = ?";
        return jdbcTemplate.queryForObject(sql, rowMapper, id);
    }

    @Override
    public List<Student> listStudents() throws DataAccessException {
        String sql = "SELECT * FROM Student";
        return jdbcTemplate.query(sql, rowMapper);
    }

    @Override
    public void updateStudent(Student student) throws DataAccessException {
        String sql = "UPDATE Student SET name = ?, age = ? WHERE id = ?";
        jdbcTemplate.update(sql, student.getName(), student.getAge(), student.getId());
    }

    @Override
    public void deleteStudent(Integer id) throws DataAccessException {
        String sql = "DELETE FROM Student WHERE id = ?";
        jdbcTemplate.update(sql, id);
    }
}

Step 5: Add Maven Dependencies

Add the required dependencies in the pom.xml file.

XML 
<dependencies>
    <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.33</version>
    </dependency>
</dependencies>

In the above pom.xml file we have used "spring-boot-starter-jdbc" dependency for implementing Java Database Connectivity in our application. Also we have used "mysql-connector-java" dependency to connect to the MySQL database and execute SQL queries.

Step 6: Implementing the StudentDAO Interface with jdbcTemplate

Below is the implementation class file StudentJDBCTemplate.java for the defined DAO interface StudentDAO.

StudentJDBCTemplate.java:

Java 
package com.exploit.org;

// Importing required classes
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.dao.DataAccessException;

@Repository
public class StudentJDBCTemplate implements StudentDAO {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    // RowMapper to map result set to Student object
    private final StudentMapper studentMapper = new StudentMapper();

    @Override
    public List<Student> listStudents() throws DataAccessException {
        String SQL = "SELECT * FROM Student";
        return jdbcTemplate.query(SQL, studentMapper);
    }

    @Override
    public void createStudent(Student student) throws DataAccessException {
        String SQL = "INSERT INTO Student (id, name, age) VALUES (?, ?, ?)";
        jdbcTemplate.update(SQL, student.getId(), student.getName(), student.getAge());
    }

    @Override
    public Student getStudentById(Integer id) throws DataAccessException {
        String SQL = "SELECT * FROM Student WHERE id = ?";
        return jdbcTemplate.queryForObject(SQL, studentMapper, id);
    }

    @Override
    public void updateStudent(Student student) throws DataAccessException {
        String SQL = "UPDATE Student SET name = ?, age = ? WHERE id = ?";
        jdbcTemplate.update(SQL, student.getName(), student.getAge(), student.getId());
    }

    @Override
    public void deleteStudent(Integer id) throws DataAccessException {
        String SQL = "DELETE FROM Student WHERE id = ?";
        jdbcTemplate.update(SQL, id);
    }
}

Note: StudentJDBCTemplate implements StudentDAO and provides database operations using JdbcTemplate. If you are using StudentJDBCTemplate, you do not need a separate StudentDAOImpl class.

Comment

Explore