SpringBoot集成Mybatis的使用

MyBatis 是一款优秀的持久层框架，它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。 MyBatis 可以使用简单的 XML 或注解来配置和映射原生信息，将接口和 Java 的 POJOs映射成数据库中的记录。

这里我使用原生的Mybatis，不使用Mybatis插件，来讲解SpringBoot如何搭建Mybatis环境，并编写接口测试案例。

Mybatis中文官网：https://mybatis.org/mybatis-3/zh/index.html

1.Maven依赖

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.4.1</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.dev</groupId>
    <artifactId>springboot-mybatis</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>springboot-mybatis</name>
    <description>Spring Boot集成Mybatis</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-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

2.创建数据库表

创建一个t_users的数据表，插入测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE `t_users`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `password` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `salt` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `create_time` datetime NOT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of t_users
-- ----------------------------
INSERT INTO `t_users` VALUES (1, '路飞', 'be59564e3e648e0f606e17d7bb5b7934', 'noJmwJ7F', '2020-12-31 23:00:02', '2021-01-07 22:59:59');
INSERT INTO `t_users` VALUES (2, 'Alen', '99cdfcdb829c4c505acd92ed77f1b536', '7Vy^!Kjp', '2021-01-28 23:00:05', '2021-01-20 23:00:11');
INSERT INTO `t_users` VALUES (3, '小明', '7cdd798bd3ad0235cfb93f67e24aa485', '@4qpfdQS', '2021-01-27 23:00:08', '2021-01-23 23:00:15');
INSERT INTO `t_users` VALUES (5, 'Luffy', '7cdd798bd3ad0235cfb93f67e24aa485', '3$^*G)df', '2021-01-11 23:27:47', '2021-01-11 23:27:47');
SET FOREIGN_KEY_CHECKS = 1;

3.配置文件

1
2
3
4
5
6
7
8
9
10
11
server.port=8031
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/shiro?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
spring.datasource.username=root
spring.datasource.password=root
#实体类扫描
mybatis.type-aliases-package=com.dev.entity
#开启驼峰命名
mybatis.configuration.map-underscore-to-camel-case=false
logging.level.com.dev.mapper=debug
debug=true

需要注意的是：

mybatis.type-aliases-package 是实体类entiy位置存放的位置

mybatis.configuration.map-underscore-to-camel-case 驼峰命名的开启 默认不开启 这个看个人习惯

4.实体类

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
62
63
64
65
66
67
68
69
70
71
72
73
/**
 * Users实体类
 * @author 路飞
 * @create 2021/1/11
 */
public class Users {
    private Integer id;
    private String username;
    private String password;
    private String salt;
    private Date create_time;
    private Date update_time;

    public Integer getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

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

    public String getSalt() {
        return salt;
    }

    public void setSalt(String salt) {
        this.salt = salt;
    }

    public Date getCreate_time() {
        return create_time;
    }

    public void setCreate_time(Date create_time) {
        this.create_time = create_time;
    }

    public Date getUpdate_time() {
        return update_time;
    }

    public void setUpdate_time(Date update_time) {
        this.update_time = update_time;
    }

    @Override
    public String toString() {
        return "Users{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", salt='" + salt + '\'' +
                ", create_time=" + create_time +
                ", update_time=" + update_time +
                '}';
    }
}

5.持久层

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
/**
 * 持久层
 * @author 路飞
 * @create 2021/1/11
 */
@Mapper
@Repository
public interface UsersMapper {

    //测试增删改查
    @Select("SELECT * FROM t_users")
    List<Users> selAllUsers();

    @Select("SELECT * FROM t_users WHERE username = #{username} ")
    Users selUser(String username);

    @Insert("INSERT INTO t_users VALUES(DEFAULT,#{username},#{password},#{salt},now(),now())")
    Integer insUser(Users users);

    @Update("UPDATE t_users SET username = #{username} WHERE id = {id}")
    Integer updUser(String username,Integer id);

    @Delete("DELETE FROM t_users WHERE username =#{username}")
    Integer delUser(String username);

}

Mybatis支持XML和注解的方式编写SQL，XML编写有一点难度，但对于复杂的SQL需要用到XML来写。这里为了测试方便，我采用注解的方式来编写CRUD测试案例。

6.service和service实现类

service.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/**
 * Users服务接口
 * @author 路飞
 * @create 2021/1/11
 */
public interface UsersService {
    List<Users> selAllUsers();

    Users selUser(String username);

    Integer insUser(Users users);

    Integer updUser(String username,Integer id);

    Integer delUser(String username);
}

serviceImpl.java

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
/**
 * Users服务接口实现
 * @author 路飞
 * @create 2021/1/11
 */
@Service
@Transactional
public class UsersServiceImpl implements UsersService {

    @Autowired
    private UsersMapper usersMapper;

    @Override
    public List<Users> selAllUsers() {
        return usersMapper.selAllUsers();
    }

    @Override
    public Users selUser(String username) {
        return usersMapper.selUser(username);
    }

    @Override
    public Integer insUser(Users users) {
        return usersMapper.insUser(users);
    }

    @Override
    public Integer updUser(String username, Integer id) {
        return usersMapper.updUser(username,id);
    }

    @Override
    public Integer delUser(String username) {
        return usersMapper.delUser(username);
    }
}

7.controller接口层

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
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
/**
 * Users接口层
 * @author 路飞
 * @create 2021/1/11
 */
@RestController
@RequestMapping("/users")
public class UsersController {

    @Autowired
    private UsersService usersService;

    /**
     * 显示所有用户
     * @return
     */
    @GetMapping("/showAll")
    public List<Users> showAllUsers(){
        List<Users> users = usersService.selAllUsers();
        return users;
    }

    /**
     * 查询用户
     * @param username
     * @return
     */
    @PostMapping("/selUser")
    public Users selUser(@RequestParam(value = "username") String username){
        Users users = usersService.selUser(username);
        return users;
    }

    /**
     * 插入用户
     * @return
     */
    @PostMapping("/insUser")
    public Map<String,Object> insUser(@RequestBody Users users){
        Map<String, Object> map = new HashMap<>();
        Integer integer = usersService.insUser(users);
        if (integer > 0 ){
            map.put("code",200);
            map.put("msg","success");
            return map;
        }else {
            map.put("code",100);
            map.put("msg","failed");
            return map;
        }
    }

    /**
     * 修改用户
     * @return
     */
    @PostMapping("/updUser")
    public Map<String,Object> updUser(@RequestParam(value = "id") Integer id,
                                      @RequestParam(value = "username")String username){
        Map<String, Object> map = new HashMap<>();
        Integer integer = usersService.updUser(username,id);
        if (integer > 0 ){
            map.put("code",200);
            map.put("msg","success");
            return map;
        }else {
            map.put("code",100);
            map.put("msg","failed");
            return map;
        }
    }

    /**
     * 删除用户
     * @param username
     * @return
     */
    @PostMapping("/delUser")
    public Map<String,Object> delUser(@RequestParam(value = "username") String username){
        Map<String, Object> map = new HashMap<>();
        Integer integer = usersService.delUser(username);
        if (integer > 0 ){
            map.put("code",200);
            map.put("msg","success");
            return map;
        }else {
            map.put("code",100);
            map.put("msg","failed");
            return map;
        }
    }
}

这里是SpringMVC的操作了，几个核心注解需要注意：

@RestController 是@Controller和@ResponseBody的整合，标明该类是接口，并把数据以JSON的格式返回

@RequestMapping 是接口映射地址，包含所有类型的请求，放在接口层最前面，把该接口的路径都规定为/users/*

@GetMapping GET请求的接口

@PostMapping POST请求的接口

@RequestParam POST请求需要在请求头中把Content-Type设置为application/x-www-form-urlencoded，常见参数提交，也是默认Ajax和Axios默认的参数提交格式

@RequestBody POST请求需要在请求头中把Content-Type设置为application/json，一般用于表单提交

8.测试类

在测试类在注入UsersService，测试查询方法是否成功生效

1
2
3
4
5
6
7
8
9
10
11
12
13
14
@SpringBootTest
public class SpringbootMybatisApplicationTests {

    private static Logger logger = LoggerFactory.getLogger(SpringbootMybatisApplicationTests.class);

    @Autowired
    private UsersService usersService;

    @Test
    public void showAll() {
        //lambadas表达式
        logger.info("-----------查询全部-------------");
        usersService.selAllUsers().forEach(System.out::println);
    }

测试成功，返回数据。

9.PostMan测试

controller层采用前后端分离的模式，后端只处理业务逻辑，编写API，这里我们用PostMan测试API是否能成功返回数据

测试POST提交参数

测试成功！

10.GitHub源码

springboot-mybatis