SpringBoot使用MyBatis连接MySQL

Posted on In , , , Waline:

[!NOTE]

本教程是《SpringBoot系列基础教程》之一，教程目录：https://laisc7301.github.io/blog/2024/01/29/202401290001SpringBoot%E7%B3%BB%E5%88%97%E5%9F%BA%E7%A1%80%E6%95%99%E7%A8%8B/

首先按照图示新建项目：

添加以下依赖：

找到application.properties文件，并写入下面内容：

1
2
3
4
5
6
7
8
9
10
11
12
server.port=8001

#设置数据库名为testdata
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/testdata?characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#下面两行设置数据库用户名和密码
spring.datasource.username=root
spring.datasource.password=root1234


mybatis.mapper-locations=classpath:mapper/*.xml
mybatis.configuration.map-underscore-to-camel-case=true

注意 testdata 就是数据库名。

整个项目的文件结构如下图所示：

先把代码贴出来：

UserController.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
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
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
package com.laisc.example3.controller;

import com.laisc.example3.entity.User;
import com.laisc.example3.mapper.UserMapper;
import com.laisc.example3.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Controller
@RequestMapping("/test")
public class UserController {

    @Autowired
    UserService userService;

    @ResponseBody
    @RequestMapping("/insert")
    public String insert() { // 插入数据
        User u = new User();
        u.setUsername("admin");
        u.setPassword("admin123");
        u.setRole("root");
        int num = userService.insertUser(u);
        return "成功添加" + num + "条记录";
    }

    @ResponseBody
    @RequestMapping("/findone")
    public String findone() { //仅搜索一条结果，结果多于一条时，会报错
        User u = new User();
        u.setUsername("admin");
        User ret = userService.findOneUser(u);
        return ret.toString();
    }

    @ResponseBody
    @RequestMapping("/findmany")
    public String findMany() { //搜索所有符合条件的结果
        User u = new User();
        u.setUsername("admin");
        List<User> ret = userService.findManyUser(u);
        return ret.toString();
    }

    @ResponseBody
    @RequestMapping("/findmany2")
    public String findMany2() { // 和 findMany() 方法一样，只是这次在mapper配置文件里的resultType属性上使用了User类
        User u = new User();
        u.setUsername("admin");
        List<User> ret = userService.findManyUser2(u);
        return ret.toString();
    }

    @ResponseBody
    @RequestMapping("/findmany3")
    public String findMany3() { // 和 findMany() 方法一样，只是这次ret的类型是List<Map>。
        User u = new User();
        u.setUsername("admin");
        List<Map> ret = userService.findManyUser3(u);
        return ret.toString();
    }

    @ResponseBody
    @RequestMapping("/findmany4")
    public String findMany4() { // 和 findMany() 方法一样，只是这次传入的参数的类型是Map，ret的类型也是List<Map>。
        Map<String, String> map = new HashMap<String, String>();
        map.put("username", "admin");

        List<Map> ret = userService.findManyUser4(map);
        return ret.toString();
    }

    @ResponseBody
    @RequestMapping("/update1")
    public String update1() { // 更新数据库
        User u = new User();
        u.setUsername("admin");
        u.setPassword("abcd1234");
        u.setRole("user");
        int num = userService.updateUserPasswordAndRole(u);
        return "成功更新" + num + "条记录";
    }

    @ResponseBody
    @RequestMapping("/update2")
    public String update2() { // 更新数据库，但是不更新对象u中值为空的数据
        User u = new User();
        u.setUsername("admin");
        //u.setPassword("abcd1234");
        u.setRole("user");
        int num = userService.updateUser(u);
        return "成功更新" + num + "条记录";
    }

    @ResponseBody
    @RequestMapping("/delete")
    public String delete() { // 删除记录
        int num = userService.deleteUser("admin");
        return "成功删除" + num + "条记录";
    }
}

UserService.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
package com.laisc.example3.service;

import com.laisc.example3.entity.User;

import java.util.List;
import java.util.Map;

public interface UserService {
    public int insertUser(User user);

    public User findOneUser(User user);//仅搜索一条结果，结果多于一条时，会报错

    public List<User> findManyUser(User user);

    public List<User> findManyUser2(User user);

    public List<Map> findManyUser3(User user);

    public List<Map> findManyUser4(Map map);

    public int updateUserPasswordAndRole(User user);

    public int updateUser(User user);

    public int deleteUser(String username);
}

UserServiceImpl.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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
package com.laisc.example3.service.impl;

import com.laisc.example3.entity.User;
import com.laisc.example3.mapper.UserMapper;
import com.laisc.example3.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    UserMapper userMapper;
    @Override
    public int insertUser(User user) {
        return userMapper.insertUser(user);
    }

    @Override
    public User findOneUser(User user) {
        return userMapper.findOneUser(user);
    }

    @Override
    public List<User> findManyUser(User user) {
        return userMapper.findManyUser(user);
    }

    @Override
    public List<User> findManyUser2(User user) {
        return userMapper.findManyUser2(user);
    }

    @Override
    public List<Map> findManyUser3(User user) {
        return userMapper.findManyUser3(user);
    }

    @Override
    public List<Map> findManyUser4(Map map) {
        return userMapper.findManyUser4(map);
    }

    @Override
    public int updateUserPasswordAndRole(User user) {
        return userMapper.updateUserPasswordAndRole(user);
    }

    @Override
    public int updateUser(User user) {
        return userMapper.updateUser(user);
    }

    @Override
    public int deleteUser(String username) {
        return userMapper.deleteUser(username);
    }
}

UserMapper.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
package com.laisc.example3.mapper;


import com.laisc.example3.entity.User;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;
import java.util.Map;

@Mapper
public interface UserMapper {

    public int insertUser(User user);

    public User findOneUser(User user);//仅搜索一条结果，结果多于一条时，会报错

    public List<User> findManyUser(User user);

    public List<User> findManyUser2(User user);

    public List<Map> findManyUser3(User user);

    public List<Map> findManyUser4(Map map);

    public int updateUserPasswordAndRole(User user);

    public int updateUser(User user);

    public int deleteUser(String username);
}

User.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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
package com.laisc.example3.entity;

public class User {
    private int id = -1;
    private String username= "";
    private String password= "";
    private String role= "";

    public User() {
    }

    public User(int id, String username, String password, String role) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.role = role;
    }

    public int getId() {
        return id;
    }

    public void setId(int 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 getRole() {
        return role;
    }

    public void setRole(String role) {
        this.role = role;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", role='" + role + '\'' +
                '}';
    }
}

UserMapper.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
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
<?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="com.laisc.example3.mapper.UserMapper">

    <insert id="insertUser" parameterType="com.laisc.example3.entity.User">
        insert into myuser (username, password, role)
        VALUES (#{username}, #{password}, #{role})
    </insert>


    <resultMap id="myfinduser" type="com.laisc.example3.entity.User">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="password" column="password"/>
        <result property="role" column="role"/>
    </resultMap>
    <select id="findOneUser" resultMap="myfinduser" parameterType="com.laisc.example3.entity.User">
        select myuser.id       id,
               myuser.username username,
               myuser.password password,
               myuser.role     role
        from myuser
        where username = #{username}
    </select>

    <select id="findManyUser" resultMap="myfinduser" parameterType="com.laisc.example3.entity.User">
        select myuser.id       id,
               myuser.username username,
               myuser.password password,
               myuser.role     role
        from myuser
        where username = #{username}
    </select>

    <select id="findManyUser2" resultType="com.laisc.example3.entity.User" parameterType="com.laisc.example3.entity.User">
        select myuser.id       id,
               myuser.username username,
               myuser.password password,
               myuser.role     role
        from myuser
        where username = #{username}
    </select>

    <select id="findManyUser3" resultType="Map" parameterType="com.laisc.example3.entity.User">
        select myuser.id       id,
               myuser.username username,
               myuser.password password,
               myuser.role     role
        from myuser
        where username = #{username}
    </select>

    <select id="findManyUser4" resultType="Map" parameterType="Map">
        select myuser.id       id,
               myuser.username username,
               myuser.password password,
               myuser.role     role
        from myuser
        where username = #{username}
    </select>

    <update id="updateUserPasswordAndRole" parameterType="com.laisc.example3.entity.User">
        update myuser
        <set>
            myuser.password=#{password},
            myuser.role=#{role}
        </set>
        where username = #{username}
    </update>


    <update id="updateUser" parameterType="com.laisc.example3.entity.User">-- 不更新值为空的数据
        update myuser
        <set>
            <if test="id != null and id != -1 ">id=#{id},</if>
            <if test="password != null and password != '' ">password=#{password},</if>
            <if test="role != null and role != '' ">role=#{role},</if>

        </set>
        where username = #{username}
    </update>


    <delete id="deleteUser" parameterType="String">
        delete
        from myuser
        where username = #{username}
    </delete>

</mapper>

数据库(myuser @testdata)：

id username password role
admin abc123 root
admin 123 root

看UserController.java：

1
2
@Autowired
UserService userService;

UserService.java 里调用了UserServiceImpl.java

UserServiceImpl.java

1
2
@Autowired
UserMapper userMapper;

打上这段代码后，我们就可以使用UserMapper了。

另外，不要忘记在UserMapper.java里写上这一行：

1
@Mapper

在UserServiceImpl.java里写上这一行：

1
@Service

然后，UserMapper.xml需要这样配置：

1
2
3
4
5
6
<?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="com.laisc.example3.mapper.UserMapper">
</mapper>

看这一段：

UserController.java

1
2
3
4
5
6
7
8
9
10
@ResponseBody
@RequestMapping("/insert")
public String insert() { // 插入数据
    User u = new User();
    u.setUsername("admin");
    u.setPassword("admin123");
    u.setRole("root");
    int num = userService.insertUser(u);
    return "成功添加" + num + "条记录";
}

UserService.java、UserMapper.java

1
public int insertUser(User user);

UserServiceImpl.java

1
2
3
4
@Override
public int insertUser(User user) {
    return userMapper.insertUser(user);
}

UserMapper.xml

1
2
3
4
<insert id="insertUser" parameterType="com.laisc.example3.entity.User">
    insert into myuser (username, password, role)
    VALUES (#{username}, #{password}, #{role})
</insert>

运行代码，访问 http://localhost:8001/test/insert ，你将看到：

成功添加1条记录

看这几段：

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
@ResponseBody
@RequestMapping("/findone")
public String findone() { //仅搜索一条结果，结果多于一条时，会报错
    User u = new User();
    u.setUsername("admin");
    User ret = userService.findOneUser(u);
    return ret.toString();
}

@ResponseBody
@RequestMapping("/findmany")
public String findMany() { //搜索所有符合条件的结果
    User u = new User();
    u.setUsername("admin");
    List<User> ret = userService.findManyUser(u);
    return ret.toString();
}

@ResponseBody
@RequestMapping("/findmany2")
public String findMany2() { // 和 findMany() 方法一样，只是这次在mapper配置文件里的resultType属性上使用了User类
    User u = new User();
    u.setUsername("admin");
    List<User> ret = userService.findManyUser2(u);
    return ret.toString();
}

@ResponseBody
@RequestMapping("/findmany3")
public String findMany3() { // 和 findMany() 方法一样，只是这次ret的类型是List<Map>。
    User u = new User();
    u.setUsername("admin");
    List<Map> ret = userService.findManyUser3(u);
    return ret.toString();
}

@ResponseBody
@RequestMapping("/findmany4")
public String findMany4() { // 和 findMany() 方法一样，只是这次传入的参数的类型是Map，ret的类型也是List<Map>。
    Map<String, String> map = new HashMap<String, String>();
    map.put("username", "admin");

    List<Map> ret = userService.findManyUser4(map);
    return ret.toString();
}
1
2
3
4
5
6
7
8
9
public User findOneUser(User user);//仅搜索一条结果，结果多于一条时，会报错

public List<User> findManyUser(User user);

public List<User> findManyUser2(User user);

public List<Map> findManyUser3(User user);

public List<Map> findManyUser4(Map map);
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
<resultMap id="myfinduser" type="com.laisc.example3.entity.User">
    <id property="id" column="id"/>
    <result property="username" column="username"/>
    <result property="password" column="password"/>
    <result property="role" column="role"/>
</resultMap>
<select id="findOneUser" resultMap="myfinduser" parameterType="com.laisc.example3.entity.User">
    select myuser.id       id,
           myuser.username username,
           myuser.password password,
           myuser.role     role
    from myuser
    where username = #{username}
</select>

<select id="findManyUser" resultMap="myfinduser" parameterType="com.laisc.example3.entity.User">
    select myuser.id       id,
           myuser.username username,
           myuser.password password,
           myuser.role     role
    from myuser
    where username = #{username}
</select>

<select id="findManyUser2" resultType="com.laisc.example3.entity.User" parameterType="com.laisc.example3.entity.User">
    select myuser.id       id,
           myuser.username username,
           myuser.password password,
           myuser.role     role
    from myuser
    where username = #{username}
</select>

<select id="findManyUser3" resultType="Map" parameterType="com.laisc.example3.entity.User">
    select myuser.id       id,
           myuser.username username,
           myuser.password password,
           myuser.role     role
    from myuser
    where username = #{username}
</select>

<select id="findManyUser4" resultType="Map" parameterType="Map">
    select myuser.id       id,
           myuser.username username,
           myuser.password password,
           myuser.role     role
    from myuser
    where username = #{username}
</select>

此时访问 findmany、findmany2、findmany3、findmany4 分别会返回查询结果。访问 findone会出错，因为符合条件的结果不止一个。

看下面代码：

1
2
3
4
5
6
7
8
9
10
@ResponseBody
@RequestMapping("/update1")
public String update1() { // 更新数据库
    User u = new User();
    u.setUsername("admin");
    u.setPassword("abcd1234");
    u.setRole("user");
    int num = userService.updateUserPasswordAndRole(u);
    return "成功更新" + num + "条记录";
}
1
public int updateUserPasswordAndRole(User user);
1
2
3
4
5
6
7
8
<update id="updateUserPasswordAndRole" parameterType="com.laisc.example3.entity.User">
    update myuser
    <set>
        myuser.password=#{password},
        myuser.role=#{role}
    </set>
    where username = #{username}
</update>

访问 http://localhost:8001/test/update1 ，将得到下面信息：

成功更新2条记录

看一下数据库，数据库会变成下面这样：

id username password role
admin abcd1234 user
admin abcd1234 user

看这一段：

1
2
3
4
5
6
7
8
9
10
@ResponseBody
@RequestMapping("/update2")
public String update2() { // 更新数据库，但是不更新对象u中值为空的数据
    User u = new User();
    u.setUsername("admin");
    //u.setPassword("abcd1234");
    u.setRole("user");
    int num = userService.updateUser(u);
    return "成功更新" + num + "条记录";
}
1
public int updateUser(User user);
1
2
3
4
5
6
7
8
9
10
<update id="updateUser" parameterType="com.laisc.example3.entity.User">-- 不更新值为空的数据
    update myuser
    <set>
        <if test="id != null and id != -1 ">id=#{id},</if>
        <if test="password != null and password != '' ">password=#{password},</if>
        <if test="role != null and role != '' ">role=#{role},</if>

    </set>
    where username = #{username}
</update>

访问 http://localhost:8001/test/update2 ，将得到下面信息：

成功更新2条记录

看一下数据库，数据库会变成下面这样：

id username password role
admin abc123 user
admin 123 user

最后看这一段：

1
2
3
4
5
6
@ResponseBody
@RequestMapping("/delete")
public String delete() { // 删除记录
    int num = userService.deleteUser("admin");
    return "成功删除" + num + "条记录";
}
1
public int deleteUser(String username);
1
2
3
4
5
<delete id="deleteUser" parameterType="String">
    delete
    from myuser
    where username = #{username}
</delete>

这一段比较简单，就是删除所有username值为admin的记录。

访问 http://localhost:8001/test/delete ，你将看到：

成功删除2条记录

项目源代码下载：https://pan.baidu.com/s/1w0yKDOJsbOazc27OLM0WUQ?pwd=zvx3

上一篇：SpringBoot使用Thymeleaf：https://laisc7301.github.io/blog/2024/01/04/202401040000SpringBoot%E4%BD%BF%E7%94%A8Thymeleaf/

下一篇：SpringBoot使用MongoDB：https://laisc7301.github.io/blog/2024/01/12/202401120000SpringBoot%E4%BD%BF%E7%94%A8MongoDB/