Step-by-Step Guide to Database Integration in Spring Boot with CRUD Operations

When starting my web project, I had no prior experience with Spring Boot. However, I recognized its importance for completing my project and decided to learn it. Rather than first focusing on theoretical concepts, I chose to adopt a practical approach to gain hands-on experience with the framework.

In this blog, I provide a step-by-step guide on how to connect a MySQL database and perform CRUD (Create, Read, Update, Delete) operations using an illustrative example.

1. Create a project

First, go to the Spring Initializr and select “Maven” as the project type, following the instructions in the image above. Then, add the “Spring Web” dependency by searching for it under the “Add Dependencies” option.

2. Connection between backend and data base.

For this purpose, we need two additional dependencies: Spring Data JPA and MySQL Driver. These dependencies are essential for establishing a connection to the database.

<!-- Spring data JPA -->
<dependency>  
    <groupId>org.springframework.boot</groupId>  
    <artifactId>spring-boot-starter-data-jpa</artifactId>  
</dependency>

<!-- Database Connecter -->
<dependency>  
    <groupId>com.mysql</groupId>  
    <artifactId>mysql-connector-j</artifactId>  
    <scope>runtime</scope>  
</dependency>

Now that we have added the main dependencies, we need to create the application.properties file. This is the key component for configuring the database connection, and these settings typically remain unchanged.

First, download and install MySQL. After that, add the following code to the appropriate configuration file:

spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:mysql://localhost:3306/your_database_name?createDatabaseIfNotExist=true
spring.datasource.username=your_username
spring.datasource.password=your_password
spring.datasource.driver-class-name =com.mysql.cj.jdbc.Driver
spring.jpa.show-sql: true

Replace your_database_name, your_username, and your_password with your actual MySQL database details. For example, use userdatabase as the database name.

After that, you can run the program and go to the MySQL command line client. Log in using your password, and then execute the following query:

SHOW DATABASES;

This will display the list of databases, including the one you created.

3. Inserting Data into Your MySQL Database

Inserting Data into Your MySQL Database with Spring Boot

4. Fetch data from Database.

We have successfully inserted the data into the database. Let us now retrieve that data.

To achieve this, we will focus exclusively on modifying the UserService and UserController classes to retrieve the data from the database.

We can create a new method named getAllUsers() within the UserService.java class. This method will be responsible for fetching all user data stored in the database.

package com.example.demo.service;

import com.example.demo.dto.UserDTO;
import com.example.demo.entity.User;
import com.example.demo.repository.UserRepo;
import org.modelmapper.ModelMapper;
import org.modelmapper.TypeToken;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Service
@Transactional
public class UserService {
    @Autowired
    private UserRepo userRepo;
    @Autowired
    private ModelMapper modelMapper;
    public UserDTO saveUser(UserDTO userDTO){
        userRepo.save(modelMapper.map(userDTO, User.class));
        return userDTO;
    }

    public List<UserDTO> getAllUsers(){
        List<User> users = userRepo.findAll();
        return modelMapper.map(users, new TypeToken<List<UserDTO>>(){}.getType());
    }
}

In the UserController class, we can create a new method called getUsers().

package com.example.demo.controller;

import com.example.demo.dto.UserDTO;
import com.example.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping(value = "api/v1/user")
@CrossOrigin
public class UserController {
    @Autowired
    UserService userService;

    @PostMapping("/saveUser")
    public UserDTO saveUser(@RequestBody UserDTO userDTO){
        return userService.saveUser(userDTO);
    }

    @GetMapping("/getUsers")
    public List<UserDTO> getUser(){
        return userService.getAllUsers();
    }
}

5. Delete data from database

We can create a new method named deleteUser(long id) within the UserService.java class. This method will be responsible for deleting a user from the database based on the provided user ID.

public boolean deleteUser(UserDTO userDTO){  
    userRepo.delete(modelMapper.map(userDTO, User.class));  
    return true;  
}

You can now incorporate the following method into the UserController class.

    @DeleteMapping("/deleteUser")
    public boolean deleteUser(@RequestBody UserDTO userDTO){
        return userService.deleteUser(userDTO);
    }

We can now send a DELETE request using Postman to delete a specific user, and we should observe that the corresponding data is successfully removed from the database.

6. Update data in data base

We can introduce a new method named updateUser(long id, User user) within the UserService.java class. This method will be responsible for updating user information in the database based on the provided user ID (id) and the updated user details (user object).

public UserDTO updateUser(UserDTO userDTO){  
    userRepo.save(modelMapper.map(userDTO, User.class));  
    return userDTO;  
}

In controller class

@PutMapping("/updateUser")  
public UserDTO updateUser(@RequestBody UserDTO userDTO){  
    return userService.updateUser(userDTO);  
}

You can now send a PUT request to update existing user data. This request should include the updated information you want to apply to the specific user. Upon successful execution, the user’s data in the database will be modified accordingly.

By following the step-by-step instructions provided in this blog, you can successfully establish a connection to your database and execute CRUD (Create, Read, Update, Delete) operations on your data. This process involves setting up necessary configurations, defining methods for each operation, and interacting with the database through your application’s logic.