Spring Boot CRUD Examples using JDBCTemplate

In this example, we will see how to perform simple CRUD operations using Spring Boot and JDBCTemplate class.

1. Create Record
2. Read All Record
3. Read Record By Id
4. Update Record
5. Delete Record

Entity: Employee.java

public class Employee {

    private int employeeId;
    private String employeeName;
    private int employeeSalary;

    public Employee(int employeeId, String employeeName, int employeeSalary) {
        this.employeeId = employeeId;
        this.employeeName = employeeName;
        this.employeeSalary = 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 int getEmployeeSalary() {
        return employeeSalary;
    }

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

Repository Class: EmployeeRepository.java

import org.code2care.jdbctemplate.eg.jdbctemplateeg.entity.Employee;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class EmployeeRepository {

    private JdbcTemplate jdbcTemplate;

   public EmployeeRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate =jdbcTemplate;
    }

    private final String createQuerySQL ="INSERT INTO employee (employeeId, employeeName, employeeSalary) VALUES (?, ?, ?)";
    private final String fetchAllQuerySQL = "SELECT employeeId, employeeName, employeeSalary FROM employee";
    private final String fetchObjectQuerySQL = "SELECT employeeId, employeeName, employeeSalary FROM employee WHERE employeeId = ?";
    private final String updateQuerySQL = "UPDATE employee SET employeeName = ?, employeeSalary = ? WHERE employeeId = ?";
    private final String deleteQuerySQL = "DELETE FROM employee WHERE employeeId = ?";

    // Create Example
    public void createEmployee(Employee employee) {
        jdbcTemplate.update(createQuerySQL, employee.getEmployeeId(), employee.getEmployeeName(), employee.getEmployeeSalary());
    }

    // Fetch all Example
    public List<Employee> getAllEmployees() {
        return jdbcTemplate.query(fetchAllQuerySQL, new BeanPropertyRowMapper<>(Employee.class));
    }

    // Retrieve an object by id Example
    public Employee getEmployeeById(int employeeId) {
        return jdbcTemplate.queryForObject(fetchObjectQuerySQL, new Object[]{employeeId}, new BeanPropertyRowMapper<>(Employee.class));
    }

    // Update example
    public void updateEmployee(Employee employee) {
          jdbcTemplate.update(updateQuerySQL, employee.getEmployeeName(), employee.getEmployeeSalary(), employee.getEmployeeId());
    }

    // Delete example
    public void deleteEmployeeById(int employeeId) {
        jdbcTemplate.update(deleteQuerySQL, employeeId);
    }
}

CommandLineRunner

import org.code2care.jdbctemplate.eg.jdbctemplateeg.entity.Employee;
import org.code2care.jdbctemplate.eg.jdbctemplateeg.repository.EmployeeRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.JdbcTemplate;

@SpringBootApplication
public class JdbctemplateCURDApplication implements CommandLineRunner {

    @Autowired
    private JdbcTemplate jdbcTemplate;

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

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


        EmployeeRepository repo = new EmployeeRepository(jdbcTemplate);

        // Create an employee
        Employee newEmployee = new Employee(1, "John Smith", 5000);
        repo.createEmployee(newEmployee);

        // Read an employee by ID
        Employee employee = repo.getEmployeeById(1);
        System.out.println(employee);

        // Update an employee's name and salary
        employee.setEmployeeName("Jake Sally");
        employee.setEmployeeSalary(6000);
        repo.updateEmployee(employee);

        // Delete an employee by ID
        repo.deleteEmployeeById(1);

    }
}

application.properties

spring.datasource.url=jdbc:mysql://localhost/jdbcTemplateDb
spring.datasource.username=root
spring.datasource.password=root-pwd
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

Spring Boot Starter Dependencies

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

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.14</version>
        </dependency>
</dependencies>