JdbcTemplate

By / 5 October 2026 / 5 minutes of reading

JdbcTemplate is Spring’s helper class for running SQL through JDBC. It opens and closes connections, prepares statements, loops through result sets, and handles exceptions for you. Traditional JDBC needs about 15 lines (connection, statement, result set, try/catch/finally) for one query. With JdbcTemplate it is one line: jdbcTemplate.query(sql, rowMapper, args). It also turns checked SQLExceptions into Spring’s unchecked DataAccessException family, such as DuplicateKeyException, so you catch meaningful errors. It is used when you want plain SQL with much less boilerplate than raw JDBC, and without the weight of an ORM like JPA.

Types

  • JdbcTemplate: query, queryForObject, update, batchUpdate, execute. This is the main one used here.
  • NamedParameterJdbcTemplate: named parameters like :name instead of ?.
  • SimpleJdbcInsert: builds INSERTs from a table name and returns the generated key (used here).
  • SimpleJdbcCall: calls stored procedures and functions.
  • Row mapping: RowMapper (hand-written, as in 5.2), BeanPropertyRowMapper (automatic, used here), ResultSetExtractor.

Hands-on Experiment: Creating a spring boot application with JdbcTemplate which simplifies database operations compared with traditional JDBC programming.

The following steps are involved in developing this hands-on experiment.

Step 1: Generate the Project

Step 2: Project Structure

Step 3: pom.xml

Step 4: application.yml

Step 5: Create Entity Class

Step 6: Create Repository Interface

Step 7: Create Service Class

Step 8: Create Controller Class

Step 9: Main Application Class

Step 10: Run the Main Application Class

Step 1: Configure the Project on Spring Initializr

Go to start.spring.io and set:

  • Project: Maven
  • Language: Java
  • Spring Boot version: 3.2.x
  • Group: com.example
  • Artifact: jdbctemplate
  • Name: jdbctemplate
  • Package name: com.example.jdbctemplate
  • Packaging: Jar
  • Java: 17
  • Dependencies: Spring Web, JDBC API, PostgreSQL Driver

Click Generate to download the ZIP, then extract and import it into your IDE as a Maven project.

Step 2: Generated Project Structure

Step 3: Explore 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 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>3.3.5</version>
        <relativePath/>
    </parent>

    <groupId>com.example</groupId>
    <artifactId>student-jdbctemplate-demo</artifactId>
    <version>1.0.0</version>

    <properties>
        <java.version>17</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- JDBC + HikariCP + JdbcTemplate (no JPA / Hibernate) -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

Step 4: application.yml

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/student_jdbc_db
    username: postgres
    password: password
    driver-class-name: org.postgresql.Driver
  sql:
    init:
      mode: always        # runs schema.sql (creates the table) and data.sql (sample rows)

server:
  port: 8082

logging:
  level:
    org.springframework.jdbc.core.JdbcTemplate: DEBUG   # prints every SQL statement

Step 5: Create Entity Class

package com.example.jdbctemplate.model;

// Property names match the column names, so BeanPropertyRowMapper can map rows automatically.
public class Student {

    private Long id;
    private String name;
    private String email;
    private String department;

    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 String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }
    public String getDepartment() { return department; }
    public void setDepartment(String department) { this.department = department; }
}

Step 6: Create below Repository Interface

package com.example.jdbctemplate.repository;

import com.example.jdbctemplate.model.Student;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

@Repository
public class StudentRepository {

    private final JdbcTemplate jdbcTemplate;
    private final SimpleJdbcInsert insert;

    // BeanPropertyRowMapper: maps each column to the property with the same name. No manual rs.getString(...) code.
    private final RowMapper<Student> rowMapper = new BeanPropertyRowMapper<>(Student.class);

    public StudentRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
        // SimpleJdbcInsert: builds the INSERT for us and returns the generated id
        this.insert = new SimpleJdbcInsert(jdbcTemplate)
                .withTableName("students")
                .usingGeneratedKeyColumns("id");
    }

    // ---------- CREATE ----------
    public Student save(Student student) {
        Number id = insert.executeAndReturnKey(new BeanPropertySqlParameterSource(student));
        student.setId(id.longValue());
        return student;
    }

    // ---------- READ ----------
    public List<Student> findAll() {
        return jdbcTemplate.query(
                "SELECT id, name, email, department FROM students ORDER BY id", rowMapper);
    }

    public Optional<Student> findById(Long id) {
        List<Student> result = jdbcTemplate.query(
                "SELECT id, name, email, department FROM students WHERE id = ?", rowMapper, id);
        return result.stream().findFirst();
    }

    public List<Student> findByDepartment(String department) {
        return jdbcTemplate.query(
                "SELECT id, name, email, department FROM students WHERE department = ? ORDER BY id",
                rowMapper, department);
    }

    public long count() {
        Long total = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM students", Long.class);
        return total == null ? 0 : total;
    }

    // ---------- UPDATE ---------- (returns number of rows changed)
    public int update(Long id, Student student) {
        return jdbcTemplate.update(
                "UPDATE students SET name = ?, email = ?, department = ? WHERE id = ?",
                student.getName(), student.getEmail(), student.getDepartment(), id);
    }

    // ---------- DELETE ---------- (returns number of rows removed)
    public int deleteById(Long id) {
        return jdbcTemplate.update("DELETE FROM students WHERE id = ?", id);
    }
}

Step 7: Create below Service class

package com.example.jdbctemplate.service;

import com.example.jdbctemplate.model.Student;
import com.example.jdbctemplate.repository.StudentRepository;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Optional;

@Service
public class StudentService {

    private final StudentRepository repository;

    public StudentService(StudentRepository repository) {
        this.repository = repository;
    }

    public Student create(Student student) {
        return repository.save(student);
    }

    public List<Student> findAll(String department) {
        return (department == null || department.isBlank())
                ? repository.findAll()
                : repository.findByDepartment(department);
    }

    public Optional<Student> findById(Long id) {
        return repository.findById(id);
    }

    public Optional<Student> update(Long id, Student student) {
        if (repository.update(id, student) == 0) {
            return Optional.empty();          // no row with that id
        }
        return repository.findById(id);
    }

    public boolean delete(Long id) {
        return repository.deleteById(id) > 0;
    }

    public long count() {
        return repository.count();
    }
}

Step 8: Create Controller class

package com.example.jdbctemplate.controller;

import com.example.jdbctemplate.model.Student;
import com.example.jdbctemplate.service.StudentService;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/students")
public class StudentController {

    private final StudentService service;

    public StudentController(StudentService service) {
        this.service = service;
    }

    // CREATE
    @PostMapping
    public ResponseEntity<Object> create(@RequestBody Student student) {
        try {
            return ResponseEntity.status(HttpStatus.CREATED).body(service.create(student));
        } catch (DuplicateKeyException e) {
            return ResponseEntity.status(HttpStatus.CONFLICT).body(Map.of("error", "Email already exists"));
        }
    }

    // READ all (optional ?department=CSE)
    @GetMapping
    public List<Student> getAll(@RequestParam(required = false) String department) {
        return service.findAll(department);
    }

    @GetMapping("/count")
    public long count() {
        return service.count();
    }

    // READ one
    @GetMapping("/{id}")
    public ResponseEntity<Student> getById(@PathVariable Long id) {
        return service.findById(id)
                .map(ResponseEntity::ok)
                .orElse(ResponseEntity.notFound().build());
    }

    // UPDATE
    @PutMapping("/{id}")
    public ResponseEntity<Student> update(@PathVariable Long id, @RequestBody Student student) {
        return service.update(id, student)
                .map(ResponseEntity::ok)
                .orElse(ResponseEntity.notFound().build());
    }

    // DELETE
    @DeleteMapping("/{id}")
    public ResponseEntity<Void> delete(@PathVariable Long id) {
        return service.delete(id) ? ResponseEntity.noContent().build()
                                  : ResponseEntity.notFound().build();
    }
}

Step 9: Main Application

package com.example.jdbctemplate;

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

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

Step 10: Run the JdbcTemplateApplication

PREVIOUS
NEXT

Related Posts

Scroll to Top