JDBCTemplate Querying Examples with Spring Boot 3

The JdbcTemplate class from the Spring Framework provides a simplified way to work with the Java JDBC API. It provides a higher-level abstraction over the raw JDBC API. You can quickly and easily create Java applications with less boilerplate code and have your focus on your application logic.

Below are a few scenarios where you might want to consider using JdbcTemplate:

If you want more control over the SQL that is executed.
If are working with a legacy database that does not support Hibernate or JPA.
Reduce latency by reducing the overhead of ORM frameworks.
When you need to perform simple JDBC operations in your application.
If your project needs to perform bulk insert/update/delete operations.
If you are working with a small to medium-sized (or POC) application with relatively simple database requirements.
If you want to write SQL that is specific to your database platform.

Querying Examples with Spring Boot 3 + JDBCTemplate

Please follow this tutorial for setting up your Project with JDBC Template: https://code2care.org/java/spring-boot-setting-up-jdbc-template-mysql-tutorial

Let's consider that we have an Employee table in our database and the corresponding PoJo in our Project.

Table SQL:

CREATE TABLE `employee` (
  `employeeId` int(5) NOT NULL,
  `employeeName` varchar(255) NOT NULL,
  `employeeDateOfBirth` date NOT NULL,
  `employeeDepartment` varchar(255) NOT NULL,
  `employeeJoiningDate` date NOT NULL,
  `employeeSalary` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
COMMIT;

package org.code2care.jdbctemplate.eg.entities;

import java.time.LocalDate;

public class Employee {

    private int employeeId;
    private String employeeName;
    private LocalDate employeeDateOfBirth;
    private String employeeDepartment;
    private LocalDate employeeJoiningDate;
    private String employeeAddress;
    private int employeeSalary;

    public int getEmployeeId() {
        return employeeId;
    }

    public void setEmployeeId(int employeeId) {
        this.employeeId = employeeId;
    }

    public String getEmployeeName() {
        return employeeName;
    }

    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }

    public LocalDate getEmployeeDateOfBirth() {
        return employeeDateOfBirth;
    }

    public void setEmployeeDateOfBirth(LocalDate employeeDateOfBirth) {
        this.employeeDateOfBirth = employeeDateOfBirth;
    }

    public String getEmployeeDepartment() {
        return employeeDepartment;
    }

    public void setEmployeeDepartment(String employeeDepartment) {
        this.employeeDepartment = employeeDepartment;
    }

    public LocalDate getEmployeeJoiningDate() {
        return employeeJoiningDate;
    }

    public void setEmployeeJoiningDate(LocalDate employeeJoiningDate) {
        this.employeeJoiningDate = employeeJoiningDate;
    }

    public String getEmployeeAddress() {
        return employeeAddress;
    }

    public void setEmployeeAddress(String employeeAddress) {
        this.employeeAddress = employeeAddress;
    }

    public int getEmployeeSalary() {
        return employeeSalary;
    }

    public void setEmployeeSalary(int employeeSalary) {
        this.employeeSalary = employeeSalary;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "employeeId=" + employeeId +
                ", employeeName='" + employeeName + '\'' +
                ", employeeDateOfBirth=" + employeeDateOfBirth +
                ", employeeDepartment='" + employeeDepartment + '\'' +
                ", employeeJoiningDate=" + employeeJoiningDate +
                ", employeeAddress='" + employeeAddress + '\'' +
                ", employeeSalary=" + employeeSalary +
                '}';
    }
}

Select * Query Example:

import org.code2care.jdbctemplate.eg.jdbctemplateeg.entities.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
public class JDBCTemplateRunner implements CommandLineRunner {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private static String selectQuery = "Select * from employee";

    public List<Employee> getAllEmployees() {
        List<Employee> employeeList =
                jdbcTemplate.query(selectQuery,
                        new BeanPropertyRowMapper<>(Employee.class));
        return employeeList;
    }

    @Override
    public void run(String... args) throws Exception {
        System.out.println(getAllEmployees());

    }
}

Output:

[
Employee {
  employeeId=101, 
  employeeName='Sam', 
  employeeDateOfBirth=2000-04-04, 
  employeeDepartment='IT', 
  employeeJoiningDate=2022-04-01, 
  employeeAddress='null', employeeSalary=24000}, 

Employee{
  employeeId=201,
  employeeName='Alan', 
  employeeDateOfBirth=1998-04-04, 
  employeeDepartment='IT', 
  employeeJoiningDate=2010-02-01, 
  employeeAddress='null', 
  employeeSalary=55000}
]

Query for Single Row/Object using where clause

@Component
public class JDBCTemplateRunner implements CommandLineRunner {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private static String selectQuery = "Select * from employee where employeeName='Sam'";

    public Employee getEmployee() {
        Employee employee =
                jdbcTemplate.queryForObject(selectQuery,new BeanPropertyRowMapper<>(Employee.class));
        return employee;
    }

    @Override
    public void run(String... args) throws Exception {
        System.out.println(getEmployee());

    }
}

Output:

Employee {
  employeeId=101, 
  employeeName='Sam', 
  employeeDateOfBirth=2000-04-04, 
  employeeDepartment='IT', 
  employeeJoiningDate=2022-04-01, 
  employeeAddress='null', employeeSalary=24000}

Get the count of a table

@Component
public class JDBCTemplateRunner implements CommandLineRunner {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private static String selectQuery = "Select count(*) from employee";

    public int getEmployee() {
        int empCount =
                jdbcTemplate.queryForObject(selectQuery,Integer.class);
        return empCount;
    }

    @Override
    public void run(String... args) throws Exception {
        System.out.println(getEmployee());
    }
}

Output: 2

Insert in table query

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private static String insertQuery = "INSERT INTO `employee` " +
            "(`employeeId`, `employeeName`, " +
            "`employeeDateOfBirth`, `" +
            "employeeDepartment`, `employeeJoiningDate`, " +
            "`employeeSalary`) " +
            "" +
            "VALUES ('301', 'Mike', '1996-01-01', 'Finance', '2012-01-01', '75000');";

    public int insertEmployee() {
        int empCount =
                jdbcTemplate.update(insertQuery);
        return empCount;
    }

    @Override
    public void run(String... args) throws Exception {
        System.out.println(insertEmployee());
    }

Output:

Update in table query

@Component
public class JDBCTemplateRunner implements CommandLineRunner {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private static String updateQuery = "UPDATE employee \n" +
            "SET employeeName=?\n" +
            "WHERE employeeId=?;";

    public int updateEmployee() {
        int empCount =
                jdbcTemplate.update(updateQuery,"Alex",101);
        return empCount;
    }

    @Override
    public void run(String... args) throws Exception {
        System.out.println(updateEmployee()); //1 = Success
    }
}

Delete Query
Example:

Facing issues? Have Questions? Post them here! I am happy to answer!

Author Info:
Rakesh (He/Him) has over 14+ years of experience in Web and Application development. He is the author of insightful How-To articles for Code2care.

Follow him on: X

You can also reach out to him via e-mail: rakesh@code2care.org
More Posts related to Java,
More Posts:

JDBCTemplate Querying Examples with Spring Boot 3

Querying Examples with Spring Boot 3 + JDBCTemplate

Select * Query Example:

Query for Single Row/Object using where clause

Get the count of a table

Insert in table query

Update in table query

Delete Query