Performing database operations in SpringBoot applications.

The cover image is published by Mohamed Hassan on Pixabay.

After organizing the business logic in the Service layer, the next consideration is how to perform database operations to query, insert, and perform other related data operations.

In a SpringBoot application, the Dao layer is responsible for performing database operations such as INSERT, DELETE, UPDATE, SELECT, etc., to perform CRUD operations on related data tables. The Entity layer contains individual entity classes, and the properties of the entity classes are composed of various data combinations required by the program. When performing database operations, the parameters in the SQL statement are usually replaced by the properties of the entity class, so an object of the entity class is often a record in the data table.

Taking the scenario in the previous article as an example, this article strives to explain how the Dao layer completes database operations and how the Entity layer functions in an easily understandable way.

Environment Preparation#

Deploy MySQL 8 on the local machine, which took a long time to set up on Deepin. Finally, it was installed using the apt source of Debian 10.

In MySQL, create a new database "project", and then create a data table "project_information" to record project information:

CREATE TABLE IF NOT EXISTS `project_information`(
   `id` INT UNSIGNED UNIQUE AUTO_INCREMENT COMMENT 'Operation serial code',
   `operation` varchar(5) NOT NULL COMMENT 'Operation type',
   `code` VARCHAR(5) NOT NULL COMMENT 'Project code',
   `name` VARCHAR(200) NOT NULL COMMENT 'Project name',
   `category` VARCHAR(2) NOT NULL COMMENT 'Project category',
   `time` DATETIME NOT NULL COMMENT 'Operation time',
   PRIMARY KEY ( `code`,`time` )
)ENGINE=InnoDB;

Where AUTO_INCREMENT is auto-incrementing, NOT NULL is a non-null constraint, and PRIMARY KEY is the primary key, and COMMENT is a comment.

Add Dependencies and Connection Information to the SpringBoot Project#

Dependency installation: You can refer to the previous article to find the installation method of your package manager on https://mvnrepository.com/. For Gradle projects, add the following information to dependencies{} in build.gradle:

// SpringBoot MySQL connector for version 8.0.33
implementation 'org.springframework.boot:spring-boot-starter-jdbc:3.1.4'
implementation 'com.mysql:mysql-connector-j:8.0.33'

Data connection configuration: Add the following information to application.properties:

spring.datasource.url=jdbc:mysql://localhost:3306/{database name}
spring.datasource.username={user}
spring.datasource.password={password}
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

The content wrapped in {} needs to be replaced according to your actual settings. Since I am using MySQL version 8.0 or above, the driver name on the last line needs to be changed to com.mysql.cj.jdbc.Driver.

Create Entity Class to Store Operation Information#

From the created data table above, we can see that an operation record includes an operation serial code, operation name, project code, project name, project category, and operation time. Since the operation serial code is automatically incremented each time data is inserted, the actual operation data only includes the operation name, project code, project name, project category, and operation time.

We can create a new entity class "ProjectInformationEntity" in the Entity layer to organize the data included in each operation. The definition of the class is as follows:

import java.sql.Timestamp;

public class ProjectInformationEntity {
    private String operation;
    private String code;
    private String name;
    private String category;
    private Timestamp operation_time;
    
    //get methods
    public String getOperation() {
        return operation;
    }
    public String getCode() {
        return this.code;
    }
    public String getName() {
        return this.name;
    }
    public String getCategory() {
        return this.category;
    }
    public Timestamp getTime() {
        return operation_time;
    }

    //set methods
    public void setOperation(String system_operation) {
        this.operation = system_operation;
    }

    public void setCode(String projectCode) {
        this.code = projectCode;
    }
    public void setName(String projectName) {
        this.name = projectName;
    }
    public void setCategory(String projectCategory) {
        this.category = projectCategory;
    }
    public void setTime(Timestamp operationTime) {
        this.operation_time = operationTime;
    }
}

Each instantiated object of this class is used to store operation data. When performing database operations or using operation data in business logic, relevant parameters can be passed through the get methods of the object. The advantage of doing this is that it is easy to understand the relationship between each data item, and there will be no confusion in actual use.

In the business logic, you can use a function like the following to initialize an object of the entity class:

public ProjectInformationEntity storeProjectInformation(String operation, String code, String name, String category) {
        ProjectInformationEntity entity = new ProjectInformationEntity();
        entity.setOperation(operation);
        entity.setCode(code);
        entity.setName(name);
        entity.setCategory(category);
        entity.setTime(getCurrentTime());
        return entity;
    }

Create Database Operations#

In the Dao layer, create a class "ProjectInformationDao" to perform some database operations. This class mainly uses JdbcTemplate to implement related operations. The update method is used to execute SQL statements such as INSERT, and the queryForObject method is used to query single data.

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import studio.tsukistar.demo.Entity.ProjectInformationEntity;

@Repository
public class ProjectInformationDao {
    private final JdbcTemplate jdbcTemplate;

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

    public void addOperationInformation(ProjectInformationEntity proInfo) { //Add operation record
        String sql = "insert into project_information (operation, code, name, category, operation_time) values (?,?,?,?,?)";
        jdbcTemplate.update(sql, proInfo.getOperation(), proInfo.getCode(), proInfo.getName(), proInfo.getCategory(), proInfo.getTime());
    }

    public String selectLatestProjectCode(String category) {
        String code;
        String sql = "select code from project_information where operation = 'New code' and category = ? order by id desc limit 1";
        code = jdbcTemplate.queryForObject(sql, new Object[]{category},String.class);
        return code;
    }
}

Summary#

The achievement of this stage is to call database operations to query and store data in a SpringBoot application. However, using JdbcTemplate may be a bit outdated, and more advanced methods will be used for operations in the future.

During the development process, I asked ChatGPT a lot of questions about implementing various functionalities and received accurate and executable answers. In a sense, ChatGPT has become my encyclopedia, hahaha.

Reference Articles#

Mapping datetime in MySQL to Java: https://juejin.cn/s/mysql%E4%B8%AD%E7%9A%84datetime%E5%AF%B9%E5%BA%94java

Using JdbcTemplate to access MySQL database in Spring Boot (Part 3): https://blog.csdn.net/weixin_45755816/article/details/118762188

Saving data to the database in Spring Boot (Part 4): https://blog.csdn.net/weixin_45755816/article/details/118770441

Using unsigned int in Java: https://icejoywoo.github.io/2018/08/17/unsigned-number-in-java.html