Merikanto

一簫一劍平生意，負盡狂名十五年

CRUD with Spring Boot & MyBatis

Veröffentlicht am in
Symbole im Artikel gezählt: 1.1k Wörter Lesezeit ≈ 1 min

In this post, we will use Spring Boot and MyBatis to do CRUD operations on MySQL databases. MyBatis is a Java persistence framework that couples objects with stored procedures or SQL statements, using annotations or an XML descriptor.


Preparation

We will start the configuration based on the setup and codes in the previous post.


Project Structure

Before we start, the project structure looks like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
├── java
│   └── merikanto
│       └── demo
│           ├── controller
│           │   ├── HelloController.java
│           │   ├── JdbcController.java
│           │   └── MyBatisController.java
│           ├── dao
│           │   └── UserDao.java
│           ├── entity
│           │   └── User.java
│           └── DemoApplication.java
└── resources
    ├── application.properties
    └── mapper
        └── UserDao.xml

And MySQL settings are the same as the those mentioned in the previous post. Please also make sure to include the two classes HelloController and JdbcController.


Pom Dependency

Add the following to pom.xml:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.5.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>merikanto</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        
        <!-- 引入 MyBatis 场景启动器，包含其自动配置类及 MyBatis 3 相关依赖 -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.2</version>
        </dependency>
        
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
    
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>


application.properties

Configure the file application.properties under resources:

1
2
3
4
5
6
spring.datasource.url=jdbc:mysql://localhost:3306/demo_springboot?useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=false
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=

mybatis.mapper-locations=classpath:mapper/*Dao.xml

Add @MapperScan

Add @MapperScan to the main class:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
package merikanto.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.mybatis.spring.annotation.MapperScan;

@SpringBootApplication
@MapperScan("merikanto.demo.dao")
public class DemoApplication {

    public static void main(String[] args) {
        System.out.println("[+] STARTING Spring Boot...");
        SpringApplication.run(DemoApplication.class, args);
    }
}

CRUD with MyBatis

In the post about Hibernate configuration, I mentioned what CRUD is about: Create, Read, Update, Delete.

Now that we’re done with the preparation work, we can move on to setting up MyBatis.


User Entity

First we create the User class user the entity package.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
package merikanto.demo.entity;

public class User {

    private Integer id;
    private String name;
    private String password;

    public Integer getId() {
        return id; }

    public void setId(Integer id) {
        this.id = id; }

    public String getName() {
        return name; }

    public void setName(String name) {
        this.name = name; }

    public String getPassword() {
        return password; }

    public void setPassword(String password) {
        this.password = password; }
}

UserDao

Then we add UserDao under the package dao, and define the interfaces for CRUD operations:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
package merikanto.demo.dao;

import merikanto.demo.entity.User;

import java.util.List;

public interface UserDao {
  
    // @return
    List<User> findAllUsers();

    // @param User
    // @return
    int insertUser(User User);

    // @param User
    // @return
    int updUser(User User);

    // @param id
    // @return
    int delUser(Integer id);
}

UserDao.xml

Add UserDao.xml under /resources/mapper:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="merikanto.demo.dao.UserDao">
    <resultMap type="merikanto.demo.entity.User" id="UserResult">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="password" column="password"/>
    </resultMap>

    <select id="findAllUsers" resultMap="UserResult">
        select id,name,password from user
        order by id desc
    </select>


    <insert id="insertUser" parameterType="merikanto.demo.entity.User">
        insert into user(name,password)
        values(#{name},#{password})
    </insert>

    <update id="updUser" parameterType="merikanto.demo.entity.User">
        update user
        set
        name=#{name},password=#{password}
        where id=#{id}
    </update>

    <delete id="delUser" parameterType="int">
        delete from user where id=#{id}
    </delete>

</mapper>

MyBatisController for CRUD

Add MyBatisController under controller for CRUD. 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
package merikanto.demo.controller;

import merikanto.demo.dao.UserDao;
import merikanto.demo.entity.User;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.util.List;

@RestController
public class MyBatisController {

    @Resource
    UserDao userDao;

    // === READ === 
    @GetMapping("/users/mybatis/queryAll")
    public List<User> queryAll() {
        return userDao.findAllUsers();
    }

    // === CREATE === 
    @GetMapping("/users/mybatis/insert")
    public Boolean insert(String name, String password) {
        if (StringUtils.isEmpty(name) || StringUtils.isEmpty(password)) {
            return false;
        }
        User user = new User();
        user.setName(name);
        user.setPassword(password);
        return userDao.insertUser(user) > 0;
    }

    // === UPDATE === 
    @GetMapping("/users/mybatis/update")
    public Boolean insert(Integer id, String name, String password) {
        if (id == null || id < 1 || StringUtils.isEmpty(name) || StringUtils.isEmpty(password)) {
            return false;
        }
        User user = new User();
        user.setId(id);
        user.setName(name);
        user.setPassword(password);
        return userDao.updUser(user) > 0;
    }

    // === DELETE === 
    @GetMapping("/users/mybatis/delete")
    public Boolean insert(Integer id) {
        if (id == null || id < 1) {
            return false;
        }
        return userDao.delUser(id) > 0;
    }
}

Testing

Use the following in the browser to test CRUD:

1
2
3
4
5
6
7
8
9
/users/mybatis/insert?name=merikanto&password=123	# Create

/users/mybatis/insert?name=mybatis&password=123	# Create

/users/mybatis/queryAll	# Read

/users/mybatis/update?id=2&name=mybatis&password=456	# Update

/users/mybatis/delete?id=2	# Delete