CRUD operations in Spring Boot + JDBC


This tutorial covers snippets and not complete code. It is assumed that you already have a downloaded and setup your Spring Boot + JDBC project using Spring Initializr in your IDE.

We extensively covered how to setup up Spring Boot + Maven + JDBC + MySQL database in this tutorial: https://code2care.org/tutorial/setting-up-spring-boot-3-maven-mysql-jdbc-example

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.

    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
        }
    }
    
    Records inserted using Spring Boot and JDBC

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.");
            }
    
        }
    }
    
Created Db entry for company MetLife
Read: InsuranceCompanies{companyName='MetLife', headcount2020=49000, headOfficeLocation='New York'}
Updated record successfully.
Deleted record successfully.
-

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


Author Info:

Rakesh (He/Him) is a seasoned developer with over 10 years of experience in web and app development, and a deep knowledge of operating systems. 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

Copyright © Code2care 2024 | Privacy Policy | About Us | Contact Us | Sitemap