Java Spring-Boot and Mysql CRUD Application Tutorial in VS Code

Java Spring-Boot Knowledge Sharing #1

7 min readMay 24

Java Spring-Boot and Mysql CRUD Application Tutorial in VS Code

This article will explain how we can perform CRUD operations using Spring Boot and MySQL. These are the clear steps you want to follow to achieve it. Finally, you can see the result in Postman. Here I did the implementations in VS Code editor. You can start with VS code without having any prior knowledge. When you are implementing from scratch, you will face some technical issues. Please comment on them here before it comes to a headache. Let’s follow the steps below.

Prerequisites

Download and Extract the JDK from here.

Download and Install VS Code Editor.
Download and Install MySQL Workbench.
Download and Install Postman.

Step 01: Setup the Spring Boot Environment in Spring Boot

To achieve this step, you have to do three things.

When you go to the above link and click the Download buttons, you can install the to your VS code. Or else you can install those as extensions in VS Code. Finally, you can see the window to set up the JDK path. Give the path to the JDK folder you downloaded and extracted above.

Step 02: Create the new Spring Boot Project

To create your first Spring Boot project in VS Code, Follow the steps below.

Open the command palette by pressing the ctrl+shift+P
Type and enter the Spring Initializr: Specify Spring Boot version
Then follow the steps there.
You've done it!

Step 03: Create the necessary files and Implement the Code

We are ready to do coding implementations. First, check the pom.xml file to confirm whether you added the required dependencies.

 <dependencies>
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-data-jpa</artifactId>
  </dependency>
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-web</artifactId>
  </dependency>

  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-devtools</artifactId>
   <scope>runtime</scope>
   <optional>true</optional>
  </dependency>
  <dependency>
   <groupId>org.projectlombok</groupId>
   <artifactId>lombok</artifactId>
   <optional>true</optional>
  </dependency>
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-test</artifactId>
   <scope>test</scope>
  </dependency>
 </dependencies>

Then create the folder structure as follows. We need to implement the following files under the relevant folders.

File and Folder Structure of the Spring Boot project

Then we will update all the files we created according to the above image.

EmployeeController.java

Here we define the four routes we need to do the CRUD operations.

package com.example.employee.EmployeeController;
import com.example.employee.DTO.EmployeeDTO;
import com.example.employee.DTO.EmployeeSaveDTO;
import com.example.employee.DTO.EmployeeUpdateDTO;
import com.example.employee.service.EmployeeService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@CrossOrigin
@RequestMapping("api/v1/employee")
public class EmployeeController
{
    @Autowired
    private EmployeeService employeeService;

    @PostMapping(path = "/save")

    public String saveEmployee(@RequestBody EmployeeSaveDTO employeeSaveDTO)
    {
        String id = employeeService.addEmployee(employeeSaveDTO);
        return id;
    }

    @GetMapping(path = "/getAllEmployee")
    public List<EmployeeDTO> getAllEmployee()
    {
       List<EmployeeDTO> allEmployees = employeeService.getAllEmployee();
       return allEmployees;
    }

    @PutMapping(path = "/update")

    public String updateEmployee(@RequestBody EmployeeUpdateDTO employeeUpdateDTO)
    {
        String id = employeeService.updateEmployees(employeeUpdateDTO);
        return id;
    }

    @DeleteMapping(path = "/deleteemployee/{id}")
    public String deleteEmployee(@PathVariable(value = "id") int id)
    {
        boolean deleteemployee = employeeService.deleteEmployee(id);
        return "deleted";
    }

}

EmployeeDTO.java

package com.example.employee.DTO;
 
public class EmployeeDTO {
 
 
    private int employeeid;
    private String employeename;
    private String employeeaddress;
    private int mobile;
 
    public EmployeeDTO(int employeeid, String employeename, String employeeaddress, int mobile) {
        this.employeeid = employeeid;
        this.employeename = employeename;
        this.employeeaddress = employeeaddress;
        this.mobile = mobile;
    }
 
    public EmployeeDTO() {
    }
 
    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 String getEmployeeaddress() {
        return employeeaddress;
    }
 
    public void setEmployeeaddress(String employeeaddress) {
        this.employeeaddress = employeeaddress;
    }
 
    public int getMobile() {
        return mobile;
    }
 
    public void setMobile(int mobile) {
        this.mobile = mobile;
    }
 
    @Override
    public String toString() {
        return "EmployeeDTO{" +
                "Employeeid=" + employeeid +
                ", employeename='" + employeename + '\'' +
                ", employeeaddress='" + employeeaddress + '\'' +
                ", mobile=" + mobile +
                '}';
    }
}

EmployeeSaveDTO.java

package com.example.employee.DTO;

public class EmployeeSaveDTO {

    private String employeename;
    private String employeeaddress;
    private int mobile;

    public EmployeeSaveDTO(String employeename, String employeeaddress, int mobile) {
        this.employeename = employeename;
        this.employeeaddress = employeeaddress;
        this.mobile = mobile;
    }

    public EmployeeSaveDTO() {
    }

    public String getEmployeename() {
        return employeename;
    }

    public void setEmployeename(String employeename) {
        this.employeename = employeename;
    }

    public String getEmployeeaddress() {
        return employeeaddress;
    }

    public void setEmployeeaddress(String employeeaddress) {
        this.employeeaddress = employeeaddress;
    }

    public int getMobile() {
        return mobile;
    }

    public void setMobile(int mobile) {
        this.mobile = mobile;
    }

    @Override
    public String toString() {
        return "EmployeeSaveDTO{" +
                "employeename='" + employeename + '\'' +
                ", employeeaddress='" + employeeaddress + '\'' +
                ", mobile=" + mobile +
                '}';
    }
}

EmployeeUpdateDTO.java

package com.example.employee.DTO;

public class EmployeeUpdateDTO {
    private int employeeid;
    private String employeename;
    private String employeeaddress;
    private int mobile;

    public EmployeeUpdateDTO(int employeeid, String employeename, String employeeaddress, int mobile) {
        this.employeeid = employeeid;
        this.employeename = employeename;
        this.employeeaddress = employeeaddress;
        this.mobile = mobile;
    }

    public EmployeeUpdateDTO() {
    }

    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 String getEmployeeaddress() {
        return employeeaddress;
    }

    public void setEmployeeaddress(String employeeaddress) {
        this.employeeaddress = employeeaddress;
    }

    public int getMobile() {
        return mobile;
    }

    public void setMobile(int mobile) {
        this.mobile = mobile;
    }

    @Override
    public String toString() {
        return "EmployeeDTO{" +
                "employeeid=" + employeeid +
                ", employeename='" + employeename + '\'' +
                ", employeeaddress='" + employeeaddress + '\'' +
                ", mobile=" + mobile +
                '}';
    }
}

EmployeeRepo.java

package com.example.employee.EmployeeRepo;
import com.example.employee.entity.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.stereotype.Repository;
 
@EnableJpaRepositories
@Repository
public interface EmployeeRepo extends JpaRepository<Employee,Integer> {
}

Employee.java

Create the employee table.

package com.example.employee.entity;

import jakarta.persistence.*;
@Entity
@Table(name = "employee")
public class Employee {
    @Id
    @Column(name = "employee_id", length = 50)
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int employeeid;
 
    @Column(name = "employee_name", length = 50)
    private String employeename;
 
    @Column(name = "employee_address", length = 60)
    private String employeeaddress;
 
    @Column(name = "mobile", length = 12)
    private int mobile;
 
    public Employee(int employeeid, String employeename, String employeeaddress, int mobile) {
        this.employeeid = employeeid;
        this.employeename = employeename;
        this.employeeaddress = employeeaddress;
        this.mobile = mobile;
    }
 
    public Employee() {
    }
 
    public Employee(String employeename, String employeeaddress, int mobile) {
        this.employeename = employeename;
        this.employeeaddress = employeeaddress;
        this.mobile = mobile;
    }
 
    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 String getEmployeeaddress() {
        return employeeaddress;
    }
 
    public void setEmployeeaddress(String employeeaddress) {
        this.employeeaddress = employeeaddress;
    }
 
    public int getMobile() {
        return mobile;
    }
 
    public void setMobile(int mobile) {
        this.mobile = mobile;
    }
 
    @Override
    public String toString() {
        return "Employee{" +
                "employeeid=" + employeeid +
                ", employeename='" + employeename + '\'' +
                ", employeeaddress='" + employeeaddress + '\'' +
                ", mobile=" + mobile +
                '}';
    }
}

EmployeeService.java

package com.example.employee.service;
import com.example.employee.DTO.EmployeeDTO;
import com.example.employee.DTO.EmployeeSaveDTO;
import com.example.employee.DTO.EmployeeUpdateDTO;
 
import java.util.List;
 
public interface EmployeeService {
    String addEmployee(EmployeeSaveDTO employeeSaveDTO);
 
    List<EmployeeDTO> getAllEmployee();
 
    String updateEmployees(EmployeeUpdateDTO employeeUpdateDTO);
 
    boolean deleteEmployee(int id);
}

EmployeeServiceIMPL.java

package com.example.employee.service;
import com.example.employee.EmployeeRepo.EmployeeRepo;
import com.example.employee.DTO.EmployeeDTO;
import com.example.employee.DTO.EmployeeSaveDTO;
import com.example.employee.DTO.EmployeeUpdateDTO;
import com.example.employee.entity.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
 
import java.util.ArrayList;
import java.util.List;
 
@Service
public class EmployeeServiceIMPL implements EmployeeService
{
    @Autowired
    private EmployeeRepo employeeRepo;
 
    @Override
    public String addEmployee(EmployeeSaveDTO employeeSaveDTO)
    {
        Employee employee = new Employee(
 
                employeeSaveDTO.getEmployeename(),
                employeeSaveDTO.getEmployeeaddress(),
                employeeSaveDTO.getMobile()
        );
        employeeRepo.save(employee);
        return employee.getEmployeename();
    }
 
    @Override
    public List<EmployeeDTO> getAllEmployee() {
       List<Employee> getemployees = employeeRepo.findAll();
       List<EmployeeDTO> employeeDTOList = new ArrayList<>();
       for(Employee a:getemployees)
       {
            EmployeeDTO employeeDTO = new EmployeeDTO(
 
                   a.getEmployeeid(),
                   a.getEmployeename(),
                   a.getEmployeeaddress(),
                   a.getMobile()
           );
           employeeDTOList.add(employeeDTO);
       }
 
       return  employeeDTOList;
    }
 
    @Override
    public String updateEmployees(EmployeeUpdateDTO employeeUpdateDTO)
    {
        if (employeeRepo.existsById(employeeUpdateDTO.getEmployeeid())) {
            Employee employee = employeeRepo.getById(employeeUpdateDTO.getEmployeeid());
 
 
            employee.setEmployeename(employeeUpdateDTO.getEmployeename());
            employee.setEmployeeaddress(employeeUpdateDTO.getEmployeeaddress());
            employee.setMobile(employeeUpdateDTO.getMobile());
            employeeRepo.save(employee);
        }
            else
            {
                System.out.println("Employee ID do not Exist");
            }
 
                return null;
        }
 
    @Override
    public boolean deleteEmployee(int id) {
 
        if(employeeRepo.existsById(id))
        {
            employeeRepo.deleteById(id);
        }
        else
        {
            System.out.println("Employee id not found");
        }
 
        return true;
    }
}

EmployeeApplication.java

package com.example.employee;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class EmployeeApplication {

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

}

So far, we have implemented all the necessary Java files and pom.xml files. Then we need to connect the Spring Boot project with MySQL Workbench.

Step 04: Connect with MySQL Workbench

First, we need to create a new connection in the workbench. Then update those connection details in application.properties file as follows.

spring.application.name=employee

server.port=8084

spring.jpa.hibernate.ddl-auto=update
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource.url=jdbc:mysql://localhost:3306/employee?createDatabaseIfNotExist=true&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=root

#jpa vendor adapter configuration
spring.jpa.database-platform=org.hibernate.dialect.MySQL57Dialect
spring.jpa.generate-ddl=true
spring.jpa.show-sql=true