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 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 statements 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, after struggling on Deepin for a long time, it is finally 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 number',
   `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 SpringBoot Project#

Dependency installation: You can refer to the previous article to find the dependency installation method for 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 8.0 or above, 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 number, operation name, project code, project name, project category, and operation time. Since the operation serial number 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, the 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 entity class object:

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 ProjectInformationDao class to perform some database operations. This class mainly uses JdbcTemplate to implement related operations, executes SQL statements such as INSERT using the update method, and queries single data using the queryForObject method.

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 interim achievement of this time is to call database operations to query and store data in a SpringBoot application. However, using JdbcTemplate may be a bit outdated, and I will use more advanced methods for operations next.

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 Database in Spring Boot (Part 4): https://blog.csdn.net/weixin_45755816/article/details/118770441

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