Open In App

Spring Boot – CRUD Operations using MySQL Database

Improve
Improve
Like Article
Like
Save
Share
Report

CRUD stands for Create, Read/Retrieve, Update and Delete and these are the four basic operations that we perform on persistence storage. CRUD is data-oriented and the standardized use of HTTP methods. HTTP has a few methods which work as CRUD operations and do note they are very vital from a developmental point perspective in programming that also does helps us relate better web development and also aids us while dealing with databases. 

So, standard CRUD Operations is as follows:

  • POST: Creates a new resource
  • GET: Reads/Retrieve a resource
  • PUT: Updates an existing resource
  • DELETE: Deletes a resource

As the name suggests 

  • CREATE Operation: Performs the INSERT statement to create a new record.
  • READ Operation: Reads table records based on the input parameter.
  • UPDATE Operation: Executes an update statement on the table. It is based on the input parameter.
  • DELETE Operation: Deletes a specified row in the table. It is also based on the input parameter.

So in this article, we are going to perform some basic CRUD Operations by creating a Spring Boot Application and using the MySQL Database. So here is a brief explanation of What’s Spring Boot and What’s MySQL Database. 

Spring Boot

Spring Boot is built on the top of the spring and contains all the features of spring. And is becoming a favorite of developers these days because it’s a rapid production-ready environment that enables the developers to directly focus on the logic instead of struggling with the configuration and setup. Spring Boot is a microservice-based framework and making a production-ready application in it takes very little time.

MySQL Database

MySQL is an RDBMS (Relational Database Management System) based on the SQL (Structured Query Language), which is the popular language for accessing and managing the records in the database. MySQL is open-source and free software under the GNU license. It is supported by Oracle Company. The data in a MySQL database are stored in tables. A table is a collection of related data, and it consists of columns and rows. Some of the features of MySQL are listed below:

  1. It is a database system used on the web
  2. It runs on a server
  3. It is ideal for both small and large applications
  4. It is very fast, reliable, and easy to use
  5. It uses standard SQL
  6. It is free to download and use

So to connect and perform CRUD operation with the MySQL DB with Spring Boot application we have to just configure it inside the application.properties file as follows:

# Configuration for MySQL Database

spring.jpa.hibernate.ddl-auto=update

spring.datasource.url = jdbc:mysql://localhost:3306/schooldb 
(Datasource URL of your DB and "schooldb", here is your schema name)

spring.datasource.username=amiya559 (Your MySQL Workbench user name)
spring.datasource.password=password.123 (Your MySQL Workbench password)
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.show-sql:true

JPA Repository 

JpaRepository is a JPA (Java Persistence API) specific extension of Repository. It contains the full API of CrudRepository and PagingAndSortingRepository. So it contains API for basic CRUD operations and also API for pagination and sorting.

Syntax:

public interface JpaRepository<T,ID> extends PagingAndSortingRepository<T,ID>, QueryByExampleExecutor<T> 

Where

  • T: Domain type that repository manages (Generally the Entity/Model class name)
  • ID: Type of the id of the entity that repository manages (Generally the wrapper class of your @Id that is created inside the Entity/Model class)

Illustration:

public interface DepartmentRepository extends JpaRepository<Department, Long> {}

Example: We will be having a Spring Boot application that manages a Department entity with JpaRepository. The data is saved in the MySQL database. We use a RESTful controller.

Step 1: Create a Spring Boot project with say it be IntelliJ

Step 2: Add the following dependency as listed below as follows:

  • Spring Web
  • MySQL Database
  • Lombok
  • Spring Data JPA

Below is the complete code for the pom.xml file.

XML




<?xml version="1.0" encoding="UTF-8"?>
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.5</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.amiya</groupId>
    <artifactId>Spring-Boot-Demo-Project</artifactId>
    <version>1.0.0-SNAPSHOT</version>
    <name>Spring-Boot-Demo-Project</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>11</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
 
        <!-- Dependency for MySQL Database -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
 
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
 
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
 
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
 
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
 
    </dependencies>
 
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>
 
</project>


 
Step 3: Create 4 packages as listed and create some classes and interfaces inside these packages as seen in the below image as shown:

  • entity
  • repository
  • service
  • controller

Note:

  • Green Rounded Icon ‘I’ Buttons are Interface
  • Blue Rounded Icon ‘C’ Buttons are Classes

Step 4: Inside the entity package

It is done via creating a simple POJO class inside the Department.java file. 

Java




//  Java Program to Illustrate Department File
 
// Importing package module to code fragment
package com.amiya.springbootdemoproject.entity;
 
// Importing required classes
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
 
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
 
// Annotations
@Entity
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
 
// Class
public class Department {
 
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long departmentId;
    private String departmentName;
    private String departmentAddress;
    private String departmentCode;
}


 
Step 5: Inside the repository package

Create a simple interface and name the interface as DepartmentRepository. This interface is going to extend the JpaRepository as we have discussed above.  

Example 

Java




// Java Program to Illustrate DepartmentRepository File
 
// Importing package module to this code
package com.amiya.springbootdemoproject.repository;
// Importing required classes
import com.amiya.springbootdemoproject.entity.Department;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
 
// Annotation
@Repository
 
// Interface
public interface DepartmentRepository
    extends JpaRepository<Department, Long> {
}


 
Step 6: Inside the service package

Example 1-A Inside the package create one interface named as DepartmentService and one class named as DepartmentServiceImpl.  

Java




// Java Program to Illustrate DepartmentService File
 
// Importing package to this code fragment
package com.amiya.springbootdemoproject.service;
import com.amiya.springbootdemoproject.entity.Department;
// Importing required classes
import java.util.List;
 
// Interface
public interface DepartmentService {
 
    // Save operation
    Department saveDepartment(Department department);
 
    // Read operation
    List<Department> fetchDepartmentList();
 
    // Update operation
    Department updateDepartment(Department department,
                                Long departmentId);
 
    // Delete operation
    void deleteDepartmentById(Long departmentId);
}


 
Example 1-B  

Java




// Java Program to Illustrate DepartmentServiceImpl File
 
// Importing package module to this code
package com.amiya.springbootdemoproject.service;
 
import com.amiya.springbootdemoproject.entity.Department;
import com.amiya.springbootdemoproject.repository.DepartmentRepository;
// Importing required classes
import java.util.List;
import java.util.Objects;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
 
// Annotation
@Service
 
// Class
public class DepartmentServiceImpl
    implements DepartmentService {
 
    @Autowired
    private DepartmentRepository departmentRepository;
 
    // Save operation
    @Override
    public Department saveDepartment(Department department)
    {
        return departmentRepository.save(department);
    }
 
    // Read operation
    @Override public List<Department> fetchDepartmentList()
    {
        return (List<Department>)
            departmentRepository.findAll();
    }
 
    // Update operation
    @Override
    public Department
    updateDepartment(Department department,
                     Long departmentId)
    {
        Department depDB
            = departmentRepository.findById(departmentId)
                  .get();
 
        if (Objects.nonNull(department.getDepartmentName())
            && !"".equalsIgnoreCase(
                department.getDepartmentName())) {
            depDB.setDepartmentName(
                department.getDepartmentName());
        }
 
        if (Objects.nonNull(
                department.getDepartmentAddress())
            && !"".equalsIgnoreCase(
                department.getDepartmentAddress())) {
            depDB.setDepartmentAddress(
                department.getDepartmentAddress());
        }
 
        if (Objects.nonNull(department.getDepartmentCode())
            && !"".equalsIgnoreCase(
                department.getDepartmentCode())) {
            depDB.setDepartmentCode(
                department.getDepartmentCode());
        }
 
        return departmentRepository.save(depDB);
    }
 
    // Delete operation
    @Override
    public void deleteDepartmentById(Long departmentId)
    {
        departmentRepository.deleteById(departmentId);
    }
}


 
Step 7: Inside the controller package

Inside the package create one class named as DepartmentController. 

Java




// Java Program to Illustrate DepartmentController File
 
// Importing package module to this code
package com.amiya.springbootdemoproject.controller;
 
import com.amiya.springbootdemoproject.entity.Department;
import com.amiya.springbootdemoproject.service.DepartmentService;
import java.util.List;
import javax.validation.Valid;
// Importing required classes
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
 
// Annotation
@RestController
 
// Class
public class DepartmentController {
 
    // Annotation
    @Autowired private DepartmentService departmentService;
 
    // Save operation
    @PostMapping("/departments")
    public Department saveDepartment(
        @Valid @RequestBody Department department)
    {
        return departmentService.saveDepartment(department);
    }
 
    // Read operation
    @GetMapping("/departments")
    public List<Department> fetchDepartmentList()
    {
        return departmentService.fetchDepartmentList();
    }
 
    // Update operation
    @PutMapping("/departments/{id}")
    public Department
    updateDepartment(@RequestBody Department department,
                     @PathVariable("id") Long departmentId)
    {
        return departmentService.updateDepartment(
            department, departmentId);
    }
 
    // Delete operation
    @DeleteMapping("/departments/{id}")
    public String deleteDepartmentById(@PathVariable("id")
                                       Long departmentId)
    {
        departmentService.deleteDepartmentById(
            departmentId);
        return "Deleted Successfully";
    }
}


 
Step 8: Below is the code for the application.properties file 

server.port=8082

# Configuration for MySQL Database
spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:mysql://localhost:3306/schooldb
spring.datasource.username=amiya559
spring.datasource.password=password.123
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.show-sql:true

Now run your application and let’s test the endpoints in Postman and also refer to our H2 Database. 

Testing the Endpoint in Postman

Endpoint 1: POST – http://localhost:8082/departments/ 

Endpoint 2: GET – http://localhost:8082/departments/ 

Endpoint 3: PUT – http://localhost:8082/departments/1 

Endpoint 4: DELETE – http://localhost:8082/departments/1 

MySQL Workbench will appear as shown below: 

 



Last Updated : 24 Dec, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads