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 +
                '}';
    }
}

1. 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}
   ]
   





2. 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}





3. 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




4. 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:

   1





5. 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
       }
   }





6. Delete Query

   Example:
   
   -
   
   
   
   
   
   Have Questions? Post them here!
   
   More Posts related to Java,

   • Create a Zip file using Java Code programmatically
   • Eclipse : A java Runtime Environment (JRE) or Java Development kit (JDK) must be available
   • How to Sort a LinkedList in Java
   • Loading class com.mysql.jdbc.Driver. This is deprecated. The new driver class is com.mysql.cj.jdbc.Driver
   • How to declare and initialize Array in Java Programming
   • [Fix] java: integer number too large compilation error
   • Java JDBC Connection with MySQL Driver in VS Code + Troubleshooting
   • Reading .xls and .xlsx Excel file using Apache POI Java Library
   • IntelliJ: Error: Could not find or load main class, java.lang.ClassNotFoundException
   • How to get Client IP address using Java Code Example
   • Truncate table using Java JDBC Example
   • Struts 2 : There is no Action mapped for namespace [/] and action name [form] associated with context path [/proj]
   • How to get file path in Idea IntelliJ IDE
   • Java Generics explained with simple definition and examples
   • Java SE 8 Update 301 available with various bug fixes and security improvements
   • Java: Collect Stream as ArrayList or LinkedList
   • Java JDBC Connection with PostgreSQL Driver Example
   • How to check if Java main thread is alive
   • How to fix Java nio NoSuchFileException wile reading a file
   • Java 8+ get Day of the Week Examples with LocalDateTime, DateTime, ZonalDateTime and Instant classes
   • Ways to Convert Integer or int to Long in Java
   • [Java] How to throws Exception using Functional Interface and Lambda code
   • [Fix] Spring Boot: mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
   • Java: The value of the local variable string is not used
   • Java JDBC: Insert Java 8 LocalDate and Time using PreparedStatement
   More Posts:

   • How to change Git Default Author and Committer details in Eclipse - Git
   • Users experience call quality issue, voice distortion, disconnection with Microsoft Teams call and meeting - Teams
   • Installing Home-brew on Ubuntu - Ubuntu
   • [Solution] Exception in thread main java.util.EmptyStackException - Java
   • Remove Trailing zeros BigDecimal Java - Java
   • Java JDBC IN Clause Example with PreparedStatement MySQL - Java
   • Go to Specific file path using Mac Finder - MacOS
   • Java 8 java.util.Function and BiFunction Examples - Java