CRUD operations in Spring Boot + JDBC

CRUD stands for Create, Read, Update, and Delete. These are considered as the basic operations that can be performed on a database or data storage system.

Let's take a look at how to perform these operations with Spring Boot + plain JDBC

Step 1: Database Configuration

We will be using MySQL database, you may follow the below link and choose the configuration for wide variety of RDS or NoSQL databases.

How to connect Relational and NoSQL databases with Spring Boot
applications.properties

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

pom.xml dependencies

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-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>




Now let us create a database table called insurance_companies,

CREATE TABLE insurance_companies (
company_name VARCHAR(50) PRIMARY KEY,
headcount_2020 INT,
head_office_location VARCHAR(50)
);

Step 2: Setting up our Pojo for the CRUD Examples

package org.code2care.springboot.jdbctutorial;

public class InsuranceCompany {
    
    private String companyName;
    private int headcount2020;
    private String headOfficeLocation;
    
    public InsuranceCompany() {
    }
    
    public InsuranceCompany(String companyName, int headcount2020, String headOfficeLocation) {
        this.companyName = companyName;
        this.headcount2020 = headcount2020;
        this.headOfficeLocation = headOfficeLocation;
    }
    
    public String getCompanyName() {
        return companyName;
    }
    
    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }
    
    public int getHeadcount2020() {
        return headcount2020;
    }
    
    public void setHeadcount2020(int headcount2020) {
        this.headcount2020 = headcount2020;
    }
    
    public String getHeadOfficeLocation() {
        return headOfficeLocation;
    }
    
    public void setHeadOfficeLocation(String headOfficeLocation) {
        this.headOfficeLocation = headOfficeLocation;
    }
    
    @Override
    public String toString() {
        return "InsuranceCompany{" +
                "companyName='" + companyName + '\'' +
                ", headcount2020=" + headcount2020 +
                ", headOfficeLocation='" + headOfficeLocation + '\'' +
                '}';
    }
}

Step 3: Methods for all CRUD operations

Create Operation

Example: Insert a single record using Prepared Statement

String sql = "INSERT INTO insurance_companies (company_name, headcount_2020, head_office_location) VALUES (?, ?, ?)";
jdbcTemplate.update(sql, "MetLife", 49000, "New York");

Example: Insert multiple records using Batch Update

package org.code2care.springboot.jdbctutorial;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;

@Component
public class InsuranceCompanyRunner implements CommandLineRunner {

    @Autowired
    private JdbcTemplate jdbcTemplate;

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

        List<InsuranceCompanies> companies = new ArrayList<>();
        companies.add(new InsuranceCompanies("MetLife", 49000, "New York"));
        companies.add(new InsuranceCompanies("Prudential Financial", 50527, "Newark"));
        companies.add(new InsuranceCompanies("New York Life Insurance", 11902, "New York"));
        companies.add(new InsuranceCompanies("Aflac", 11128, "Columbus"));
        companies.add(new InsuranceCompanies("Liberty Mutual", 45000, "Boston"));

        // Prepare the SQL insert statement
        String sql = "INSERT INTO insurance_companies (company_name, headcount_2020, head_office_location) VALUES (?, ?, ?)";


        List<Object[]> batchArgs = new ArrayList<>();
        for (InsuranceCompanies company : companies) {
            Object[] params = {company.getCompanyName(), company.getHeadcount2020(), company.getHeadOfficeLocation()};
            batchArgs.add(params);
        }


        jdbcTemplate.batchUpdate(sql, batchArgs); //batch Update
    }
}

Read Operation using Prepared Statement

package org.code2care.springboot.jdbctutorial;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
public class InsuranceCompanyRunner implements CommandLineRunner {

    @Autowired
    private JdbcTemplate jdbcTemplate;

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

        String findQuery = "SELECT * FROM insurance_companies WHERE company_name = ?";

        List<InsuranceCompanies> companies = jdbcTemplate.query(
                findQuery,
                new Object[]{"MetLife"},
                (rs, rowNum) -> new InsuranceCompanies(
                        rs.getString("company_name"),
                        rs.getInt("headcount_2020"),
                        rs.getString("head_office_location")
                )
        );

        for (InsuranceCompanies company : companies) {
            System.out.println(company.toString());
        }
    }
}

InsuranceCompanies{companyName='MetLife', headcount2020=49000, headOfficeLocation='New York'}

Update Operation with Prepared Statement

String updateQuery = "UPDATE insurance_companies SET headcount_2020 = ? WHERE company_name = ?";

int rowsAffected = jdbcTemplate.update(updateQuery, 11129, "Alfac");

if (rowsAffected > 0) {
    System.out.println("Record updated successfully.");
} else {
    System.out.println("No record found with Company Name Alfac.");
}

Delete Operation with Prepared Statement

String deleteSQL = "DELETE FROM insurance_companies WHERE company_name = ?";

int rowsAffected = jdbcTemplate.update(deleteSQL, "Aflac");

if (rowsAffected > 0) {
    System.out.println("Record deleted successfully.");
} else {
    System.out.println("No record found with Company Name Aflac.");
}

Step 4: Complete CRUD Code in One Class

package org.code2care.springboot.jdbctutorial;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import java.util.List;

@Component
public class InsuranceCRUD implements CommandLineRunner {

    @Autowired
    private final JdbcTemplate jdbcTemplate;

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

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

        // 1. Create
        String sql = "INSERT INTO insurance_companies (company_name, headcount_2020, head_office_location) VALUES (?, ?, ?)";
        jdbcTemplate.update(sql, "MetLife", 49000, "New York");
        System.out.println("Created Db entry for company MetLife");

        // 2. Read
        String findQuery = "SELECT * FROM insurance_companies WHERE company_name = ?";
        List<InsuranceCompanies> companies = jdbcTemplate.query(
                findQuery,
                new Object[]{"MetLife"},
                (rs, rowNum) -> new InsuranceCompanies(
                        rs.getString("company_name"),
                        rs.getInt("headcount_2020"),
                        rs.getString("head_office_location")
                )
        );
        for (InsuranceCompanies company : companies) {
            System.out.println("Read: "+ company.toString());
        }

        // 3. Update
        String updateQuery = "UPDATE insurance_companies SET headcount_2020 = ? WHERE company_name = ?";
        int rowsAffected = jdbcTemplate.update(updateQuery, 50000, "MetLife");
        if (rowsAffected > 0) {
            System.out.println("Updated record successfully.");
        } else {
            System.out.println("No record found with Company Name MetLife.");
        }

        // 4. Delete
        String deleteSQL = "DELETE FROM insurance_companies WHERE company_name = ?";
        rowsAffected = jdbcTemplate.update(deleteSQL, "MetLife");
        if (rowsAffected > 0) {
            System.out.println("Deleted record successfully.");
        } else {
            System.out.println("No record found with Company Name MetLife.");
        }

    }
}