RuoYi 阶段三：MyBatis 与数据库操作

NOTE
本文是 RuoYi 学习路线的阶段三，聚焦 MyBatis 数据库操作。学完本篇你应该能：

理解 MyBatis 的 Mapper 接口和 XML 映射

掌握动态 SQL 的编写

知道 RuoYi 是如何进行分页查询的

前端视角：MyBatis 像什么？#

MyBatis	前端类比
Mapper 接口	API 接口定义
XML 映射文件	GraphQL Schema / SQL 模板
`#{param}`	模板字符串 `${param}`
动态 SQL	条件渲染 `{condition && <Component />}`
ResultMap	数据转换/序列化

MyBatis 核心概念#

1. Mapper 接口#

Mapper 接口定义了数据库操作方法，不需要写实现类：

1
package com.ruoyi.system.mapper;
2

3
import java.util.List;
4
import com.ruoyi.system.domain.SysUser;
5

6
/**
7
 * 用户表 数据层
8
 *
9
 * @author ruoyi
10
 */
11
public interface SysUserMapper {
12
    /**
13
     * 根据条件分页查询用户列表
14
     */
15
    public List<SysUser> selectUserList(SysUser user);
16

17
    /**
18
     * 通过用户名查询用户
19
     */
20
    public SysUser selectUserByUserName(String userName);
21

22
    /**
23
     * 通过用户ID查询用户
24
     */
25
    public SysUser selectUserById(Long userId);
26

27
    /**
28
     * 新增用户信息
29
     */
30
    public int insertUser(SysUser user);
31

32
    /**
33
     * 修改用户信息
34
     */
35
    public int updateUser(SysUser user);
36

37
    /**
38
     * 删除用户信息
39
     */
40
    public int deleteUserById(Long userId);
41

42
    /**
43
     * 批量删除用户信息
44
     */
45
    public int deleteUserByIds(String[] userIds);
46
}

2. XML 映射文件#

XML 文件定义了 SQL 语句和结果映射：

1
<?xml version="1.0" encoding="UTF-8" ?>
2
<!DOCTYPE mapper
3
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
4
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
5
<mapper namespace="com.ruoyi.system.mapper.SysUserMapper">
6

7
    <resultMap type="SysUser" id="SysUserResult">
8
        <id     property="userId"       column="user_id"      />
9
        <result property="deptId"       column="dept_id"      />
10
        <result property="loginName"    column="login_name"   />
11
        <result property="userName"     column="user_name"    />
12
        <result property="email"        column="email"        />
13
        <result property="phonenumber"  column="phonenumber"  />
14
        <result property="sex"          column="sex"          />
15
        <result property="avatar"       column="avatar"       />
16
        <result property="password"     column="password"     />
17
        <result property="salt"         column="salt"         />
18
        <result property="status"       column="status"       />
19
        <result property="delFlag"      column="del_flag"     />
20
        <result property="loginIp"      column="login_ip"     />
21
        <result property="loginDate"    column="login_date"   />
22
        <result property="createBy"     column="create_by"    />
23
        <result property="createTime"   column="create_time"  />
24
        <result property="updateBy"     column="update_by"    />
25
        <result property="updateTime"   column="update_time"  />
26
        <result property="remark"       column="remark"       />
27
        <association property="dept"    column="dept_id" javaType="SysDept" resultMap="deptResult" />
28
        <collection  property="roles"   javaType="java.util.List"           resultMap="RoleResult" />
29
    </resultMap>
30

31
    <resultMap id="deptResult" type="SysDept">
32
        <id     property="deptId"   column="dept_id"     />
33
        <result property="parentId" column="parent_id"   />
34
        <result property="deptName" column="dept_name"   />
35
        <result property="orderNum" column="order_num"   />
36
        <result property="leader"   column="leader"      />
37
        <result property="status"   column="dept_status" />
38
    </resultMap>
39

40
    <resultMap id="RoleResult" type="SysRole">
41
        <id     property="roleId"       column="role_id"        />
42
        <result property="roleName"     column="role_name"      />
43
        <result property="roleKey"      column="role_key"       />
44
        <result property="roleSort"     column="role_sort"      />
45
        <result property="dataScope"     column="data_scope"    />
46
        <result property="status"       column="role_status"    />
47
    </resultMap>
48

49
    <sql id="selectUserVo">
50
        select u.user_id, u.dept_id, u.login_name, u.user_name, u.email, u.phonenumber,
51
               u.sex, u.avatar, u.password, u.salt, u.status, u.del_flag,
52
               u.login_ip, u.login_date, u.create_by, u.create_time, u.update_by,
53
               u.update_time, u.remark,
54
               d.dept_id, d.parent_id, d.dept_name, d.order_num, d.leader, d.status as dept_status,
55
               r.role_id, r.role_name, r.role_key, r.role_sort, r.data_scope, r.status as role_status
56
        from sys_user u
57
        left join sys_dept d on u.dept_id = d.dept_id
58
        left join sys_user_role ur on u.user_id = ur.user_id
59
        left join sys_role r on r.role_id = ur.role_id
60
    </sql>
61

62
    <select id="selectUserList" parameterType="SysUser" resultMap="SysUserResult">
63
        <include refid="selectUserVo"/>
64
        where u.del_flag = '0'
65
        <if test="loginName != null and loginName != ''">
66
            AND u.login_name like concat('%', #{loginName}, '%')
67
        </if>
68
        <if test="status != null and status != ''">
69
            AND u.status = #{status}
70
        </if>
71
        <if test="phonenumber != null and phonenumber != ''">
72
            AND u.phonenumber like concat('%', #{phonenumber}, '%')
73
        </if>
74
        <if test="beginTime != null and beginTime != ''">
75
            AND date_format(u.create_time,'%y%m%d') &gt;= date_format(#{beginTime},'%y%m%d')
76
        </if>
77
        <if test="endTime != null and endTime != ''">
78
            AND date_format(u.create_time,'%y%m%d') &lt;= date_format(#{endTime},'%y%m%d')
79
        </if>
80
        <if test="deptId != null and deptId != 0">
81
            AND (u.dept_id = #{deptId} OR u.dept_id IN (
82
                SELECT t.dept_id FROM sys_dept t
83
                WHERE find_in_set(#{deptId}, ancestors)
84
            ))
85
        </if>
86
    </select>
87

88
    <select id="selectUserById" parameterType="Long" resultMap="SysUserResult">
89
        <include refid="selectUserVo"/>
90
        where u.user_id = #{userId}
91
    </select>
92

93
    <insert id="insertUser" parameterType="SysUser" useGeneratedKeys="true" keyProperty="userId">
94
        insert into sys_user(
95
            <if test="userId != null and userId != 0">user_id,</if>
96
            <if test="deptId != null and deptId != 0">dept_id,</if>
97
            <if test="loginName != null and loginName != ''">login_name,</if>
98
            <if test="userName != null and userName != ''">user_name,</if>
99
            <if test="email != null and email != ''">email,</if>
100
            <if test="phonenumber != null and phonenumber != ''">phonenumber,</if>
101
            <if test="sex != null and sex != ''">sex,</if>
102
            <if test="avatar != null and avatar != ''">avatar,</if>
103
            <if test="password != null and password != ''">password,</if>
104
            <if test="salt != null and salt != ''">salt,</if>
105
            <if test="status != null and status != ''">status,</if>
106
            <if test="createBy != null and createBy != ''">create_by,</if>
107
            <if test="remark != null and remark != ''">remark,</if>
108
            create_time
109
        )values(
110
            <if test="userId != null and userId != 0">#{userId},</if>
111
            <if test="deptId != null and deptId != 0">#{deptId},</if>
112
            <if test="loginName != null and loginName != ''">#{loginName},</if>
113
            <if test="userName != null and userName != ''">#{userName},</if>
114
            <if test="email != null and email != ''">#{email},</if>
115
            <if test="phonenumber != null and phonenumber != ''">#{phonenumber},</if>
116
            <if test="sex != null and sex != ''">#{sex},</if>
117
            <if test="avatar != null and avatar != ''">#{avatar},</if>
118
            <if test="password != null and password != ''">#{password},</if>
119
            <if test="salt != null and salt != ''">#{salt},</if>
120
            <if test="status != null and status != ''">#{status},</if>
121
            <if test="createBy != null and createBy != ''">#{createBy},</if>
122
            <if test="remark != null and remark != ''">#{remark},</if>
123
            sysdate()
124
        )
125
    </insert>
126

127
    <update id="updateUser" parameterType="SysUser">
128
        update sys_user
129
        <set>
130
            <if test="deptId != null and deptId != 0">dept_id = #{deptId},</if>
131
            <if test="loginName != null and loginName != ''">login_name = #{loginName},</if>
132
            <if test="userName != null and userName != ''">user_name = #{userName},</if>
133
            <if test="email != null and email != ''">email = #{email},</if>
134
            <if test="phonenumber != null and phonenumber != ''">phonenumber = #{phonenumber},</if>
135
            <if test="sex != null and sex != ''">sex = #{sex},</if>
136
            <if test="avatar != null and avatar != ''">avatar = #{avatar},</if>
137
            <if test="password != null and password != ''">password = #{password},</if>
138
            <if test="salt != null and salt != ''">salt = #{salt},</if>
139
            <if test="status != null and status != ''">status = #{status},</if>
140
            <if test="loginIp != null and loginIp != ''">login_ip = #{loginIp},</if>
141
            <if test="loginDate != null">login_date = #{loginDate},</if>
142
            <if test="updateBy != null and updateBy != ''">update_by = #{updateBy},</if>
143
            <if test="remark != null">remark = #{remark},</if>
144
            update_time = sysdate()
145
        </set>
146
        where user_id = #{userId}
147
    </update>
148

149
    <delete id="deleteUserById" parameterType="Long">
150
        delete from sys_user where user_id = #{userId}
151
    </delete>
152

153
    <delete id="deleteUserByIds" parameterType="Long">
154
        update sys_user set del_flag = '2' where user_id in
155
        <foreach collection="array" item="userId" open="(" separator="," close=")">
156
            #{userId}
157
        </foreach>
158
    </delete>
159

160
</mapper>

动态 SQL#

MyBatis 提供了强大的动态 SQL 功能：

1. if 条件判断#

1
<if test="name != null and name != ''">
2
    AND name = #{name}
3
</if>

2. where 标签#

自动处理 WHERE 和 AND/OR：

1
<select id="selectUser" resultMap="UserResult">
2
    SELECT * FROM sys_user
3
    <where>
4
        <if test="name != null">
5
            AND name = #{name}
6
        </if>
7
        <if test="status != null">
8
            AND status = #{status}
9
        </if>
10
    </where>
11
</select>

3. set 标签#

自动处理 SET 和逗号：

1
<update id="updateUser">
2
    UPDATE sys_user
3
    <set>
4
        <if test="name != null">name = #{name},</if>
5
        <if test="status != null">status = #{status},</if>
6
    </set>
7
    WHERE user_id = #{userId}
8
</update>

4. foreach 循环#

1
<select id="selectUserByIds">
2
    SELECT * FROM sys_user WHERE user_id IN
3
    <foreach collection="array" item="id" open="(" separator="," close=")">
4
        #{id}
5
    </foreach>
6
</select>

5. choose/when/otherwise#

类似 Java 的 switch：

1
<choose>
2
    <when test="order == 'asc'">
3
        ORDER BY create_time ASC
4
    </when>
5
    <otherwise>
6
        ORDER BY create_time DESC
7
    </otherwise>
8
</choose>

分页查询#

RuoYi 使用 PageHelper 实现分页：

1. 配置 PageHelper#

1
pagehelper:
2
  helperDialect: mysql
3
  reasonable: true
4
  supportMethodsArguments: true
5
  params: count=countSql

2. Controller 中使用#

1
protected void startPage() {
2
    PageDomain pageDomain = TableSupport.buildPageRequest();
3
    Integer pageNum = pageDomain.getPageNum();
4
    Integer pageSize = pageDomain.getPageSize();
5
    if (StringUtils.isNotNull(pageNum) && StringUtils.isNotNull(pageSize)) {
6
        String orderBy = SqlUtil.escapeOrderBySql(pageDomain.getOrderBy());
7
        PageHelper.startPage(pageNum, pageSize, orderBy);
8
    }
9
}
10

11
// SysUserController.java
12
@PostMapping("/list")
13
@ResponseBody
14
public TableDataInfo list(SysUser user) {
15
    startPage();  // 开启分页
16
    List<SysUser> list = userService.selectUserList(user);
17
    return getDataTable(list);  // 封装分页结果
18
}

3. 分页结果封装#

1
public class TableDataInfo {
2
    private long total;        // 总记录数
3
    private List<?> rows;      // 列表数据
4
    private int code;          // 状态码
5
    private String msg;        // 消息
6

7
    public TableDataInfo(List<?> list, int total) {
8
        this.rows = list;
9
        this.total = total;
10
    }
11
}

事务管理#

RuoYi 使用 @Transactional 注解管理事务：

1
@Service
2
public class SysUserServiceImpl implements ISysUserService {
3

4
    @Override
5
    @Transactional
6
    public int insertUser(SysUser user) {
7
        // 新增用户信息
8
        int rows = userMapper.insertUser(user);
9
        // 新增用户岗位关联
10
        insertUserPost(user);
11
        // 新增用户与角色管理
12
        insertUserRole(user);
13
        return rows;
14
        // 如果任何一步失败，整个事务回滚
15
    }
16
}

ResultMap 详解#

ResultMap 用于映射数据库字段和 Java 对象属性：

1
<resultMap type="SysUser" id="SysUserResult">
2
    <!-- 主键映射 -->
3
    <id property="userId" column="user_id" />
4

5
    <!-- 普通字段映射 -->
6
    <result property="userName" column="user_name" />
7

8
    <!-- 一对一关联 -->
9
    <association property="dept" javaType="SysDept" resultMap="deptResult" />
10

11
    <!-- 一对多关联 -->
12
    <collection property="roles" javaType="java.util.List" resultMap="RoleResult" />
13
</resultMap>

RuoYi 中的 MyBatis 配置#

1
mybatis:
2
  # 实体类包路径（自动扫描）
3
  typeAliasesPackage: com.ruoyi.**.domain
4
  # Mapper XML 文件位置
5
  mapperLocations: classpath:mapper/**/*.xml
6
  # MyBatis 配置文件
7
  configLocation: classpath:mybatis/mybatis-config.xml

1
<?xml version="1.0" encoding="UTF-8" ?>
2
<!DOCTYPE configuration
3
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
4
"http://mybatis.org/dtd/mybatis-3-config.dtd">
5
<configuration>
6
    <settings>
7
        <!-- 开启驼峰命名转换 -->
8
        <setting name="mapUnderscoreToCamelCase" value="true"/>
9
        <!-- 开启延迟加载 -->
10
        <setting name="lazyLoadingEnabled" value="true"/>
11
        <!-- 设置超时时间 -->
12
        <setting name="defaultStatementTimeout" value="25000"/>
13
    </settings>
14
</configuration>

复习卡片#

1
<!-- MyBatis 核心标签记忆 -->
2
<!-- 查询 -->
3
<select id="selectUser" resultMap="UserResult">
4
<!-- 插入 -->
5
<insert id="insertUser" useGeneratedKeys="true" keyProperty="userId">
6
<!-- 更新 -->
7
<update id="updateUser">
8
<!-- 删除 -->
9
<delete id="deleteUser">
10

11
<!-- 动态 SQL -->
12
<if test="name != null">AND name = #{name}</if>
13
<where>...</where>
14
<set>...</set>
15
<foreach collection="array" item="id">...</foreach>
16
<choose><when>...</when><otherwise>...</otherwise></choose>
17

18
<!-- 结果映射 -->
19
<resultMap type="User" id="UserResult">
20
    <id property="id" column="user_id"/>
21
    <result property="name" column="user_name"/>
22
    <association property="dept" javaType="Dept"/>
23
    <collection property="roles" javaType="List"/>
24
</resultMap>

概念	说明
Mapper 接口	定义数据库操作方法
XML 映射	编写 SQL 语句
`#{param}`	预编译参数（防 SQL 注入）
`${param}`	字符串替换（慎用）
ResultMap	字段映射配置
动态 SQL	条件拼接 SQL
PageHelper	分页插件

TIP
下一步：阶段四：Shiro 权限框架
在阶段四中，我们将深入理解 Shiro 的认证和授权机制，以及 RuoYi 是如何实现权限控制的。