mybatis总结

一.基本用法

1.select

1.1 使用resultType

SQL语句需要为所有列名和model属性名不一致的列设置别名,来实现自动映射

1
2
3
4
5
6
7
8
9
10
<select id="selectAll" resultType="tk.mybatis.simple.model.SysUser">
select id,
user_name userName,
user_password userPassword,
user_email userEmail,
user_info userInfo,
head_img headImg,
create_time createTime
from sys_user
</select>
1.2使用resultMap设置结果映射

property对应model的属性,column对应数据库的列

1
2
3
4
5
6
7
8
9
10
11
12
13
<resultMap id="userMap" type="tk.mybatis.simple.model.SysUser">
<id property="id" column="id"/>
<result property="userName" column="user_name"/>
<result property="userPassword" column="user_password"/>
<result property="userEmail" column="user_email"/>
<result property="userInfo" column="user_info"/>
<result property="headImg" column="head_img" jdbcType="BLOB"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
</resultMap >

<select id="selectById" resultMap="userMap">
select * from sys_user where id = #{id}
</select>

2.insert

useGeneratedKeys:使用jdbc返回主键自增的值
keyProperty:MyBatis通过getGeneratedKeys获取主键值后将要赋值的属性名

1
2
3
4
5
6
7
8
<insert id="insert" useGeneratedKeys="true" keyProperty="id">
insert into sys_user (
id, user_name, user_password, user_email,
user_info, head_img, create_time)
values (#{id}, #{userName}, #{userPassword}, #{userEmail},
#{userInfo}, #{headImg, jdbcType=BLOB},
#{createTime, jdbcType=TIMESTAMP})
</insert>

3.update

1
2
3
4
5
6
7
8
9
10
<update id="updateById">
update sys_user
set user_name = #{userName},
user_password = #{userPassword},
user_email = #{userEmail},
user_info = #{userInfo},
head_img = #{headImg, jdbcType=BLOB},
create_time = #{createTime, jdbcType=TIMESTAMP}
where id = #{id}
</update>

4.delete

1
2
3
<delete id="deleteById">
delete from sys_user where id = #{id}
</delete>

5.接口参数类型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
public interface UserMapper {

//参数类型 >> 基本类型
SysUser selectById(Long id);
//参数类型 >> javaBean
int insert(SysUser sysUser);
//参数类型 >> 多参数 使用Param注解
List<SysRole> selectRoleByUserIdAndRoleEnabled(
@Param("userId") Long userId,
@Param("enabled") Integer enabled);
//参数类型 >> 数组
List<SysUser> selectByIdList((Long[] idArray);
//参数类型 >> List
int insertList(List<SysUser> userList);
//参数类型 >> Map
int updateByMap(Map<String, Object> map);
//参数类型 >> 多个数组
List<SysUser> selectByIdOrUserNameList(@Param("idArray") Long[] idArray,
@Param("nameArray") String[] nameArray);

}
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
<!-- 多参数 -->
<select id="selectRoleByUserIdAndRoleEnabled" resultType="tk.mybatis.simple.model.SysRole">
select r.id,
r.role_name roleName,
r.enabled,
r.create_by createBy,
r.create_time createTime
from sys_user u inner join sys_user_role ur on u.id = ur.user_id
inner join sys_role r on ur.role_id = r.id
where u.id = #{userId} and r.enabled = #{enabled}
</select>

<!-- 参数为数组 xml中collection默认array-->
<select id="selectByIdList" resultType="tk.mybatis.simple.model.SysUser">
select id,
user_name userName,
user_password userPassword,
user_email userEmail,
user_info userInfo,
head_img headImg,
create_time createTime
from sys_user
where id in
<foreach collection="array" open="(" close=")" separator="," item="id" index="i">
#{id}
</foreach>
</select>

<!-- 参数为List xml中collection默认list -->
<insert id="insertList">
insert into sys_user (
user_name, user_password, user_email,
user_info, head_img, create_time
) values
<foreach collection="list" item="user" separator=",">
(
#{user.userName}, #{user.userPassword}, #{user.userEmail},
#{user.userInfo}, #{user.headImg, jdbcType=BLOB},
#{user.createTime, jdbcType=TIMESTAMP}
)
</foreach>
</insert>

<!--参数为Map xml中collection默认_parameter -->
<update id="updateByMap">
update sys_user
set
<foreach collection="_parameter" index="key" item="val" separator="," >
${key} = #{val}
</foreach>
where id = #{id}
</update>

<!--参数为多个数组 xml中collection=Param注解对应的名字-->
<select id="selectByIdOrUserNameList" resultType="tk.mybatis.simple.model.SysUser">
select id,
user_name userName,
user_password userPassword,
user_email userEmail,
user_info userInfo,
head_img headImg,
create_time createTime
from sys_user
where id in
<foreach collection="idArray" open="(" close=")" separator="," item="id" index="i">
#{id}
</foreach>
and user_name in
<foreach collection="nameArray" open="(" close=")" separator="," item="name" index="i">
#{name}
</foreach>

</select>

二.动态SQL

1.if

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
<!-- 1=1 if都不满足, 则select所有 -->
<select id="selectByUser" resultType="tk.mybatis.simple.model.SysUser">
select id,
user_name userName,
user_password userPassword,
user_email userEmail,
user_info userInfo,
head_img headImg,
create_time createTime
from sys_user
where 1 = 1
<if test="userName != null and userName != ''">
and user_name like concat('%', #{userName}, '%')
</if>
<if test="userEmail != null and userEmail != ''">
and user_email = #{userEmail}
</if>
</select>

<!-- id=#{id}, if都不满足, 则update id -->
<update id="updateByIdSelective">
update sys_user
set
<if test="userName != null and userName != ''">
user_name = #{userName},
</if>
<if test="userPassword != null and userPassword != ''">
user_password = #{userPassword},
</if>
<if test="userEmail != null and userEmail != ''">
user_email = #{userEmail},
</if>
<if test="userInfo != null and userInfo != ''" >
user_info = #{userInfo},
</if>
<if test="headImg != null">
head_img = #{headImg, jdbcType=BLOB},
</if>
<if test="createTime != null">
create_time = #{createTime, jdbcType=TIMESTAMP},
</if>
id = #{id}
where id = #{id}
</update>

<!--插入检查空值,如果空值则走数据库默认值-->
<insert id="insert2" useGeneratedKeys="true" keyProperty="id">
insert into sys_user (
user_name, user_password,
<if test="userEmail != null and userEmail != ''" >
user_email,
</if>
user_info, head_img, create_time)
values (#{userName}, #{userPassword},
<if test="userEmail != null and userEmail != ''" >
#{userEmail},
</if>
#{userInfo}, #{headImg, jdbcType=BLOB},
#{createTime, jdbcType=TIMESTAMP})
</insert>

2.choose

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<!-- 类似switch case default -->
<select id="selectByIdOrUserName" resultType="tk.mybatis.simple.model.SysUser">
select id,
user_name userName,
user_password userPassword,
user_email userEmail,
user_info userInfo,
head_img headImg,
create_time createTime
from sys_user
where 1 = 1
<choose>
<when test="id != null">
and id = #{id}
</when>
<when test="userName != null and userName !=''">
and user_name = #{userName}
</when>
<otherwise>
and 1 = 2
</otherwise>
</choose>
</select>

3.where

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<!--where标签,如果不满足,则无内容,如果where中if满足,where标签会自动把开头的and去掉 -->
<select id="selectByUser" resultType="tk.mybatis.simple.model.SysUser">
select id,
user_name userName,
user_password userPassword,
user_email userEmail,
user_info userInfo,
head_img headImg,
create_time createTime
from sys_user
<where>
<if test="userName != null and userName != ''">
and user_name like concat('%', #{userName}, '%')
</if>
<if test="userEmail != null and userEmail != ''">
and user_email = #{userEmail}
</if>
</where>
</select>

4.set

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
<!-- set标签, 如果标签内有返回值, 则插入一个set, 如果set后面字符串以,结尾, 则删除该逗号  -->
<update id="updateByIdSelective">
update sys_user
<set>
<if test="userName != null and userName != ''">
user_name = #{userName},
</if>
<if test="userPassword != null and userPassword != ''">
user_password = #{userPassword},
</if>
<if test="userEmail != null and userEmail != ''">
user_email = #{userEmail},
</if>
<if test="userInfo != null and userInfo != ''" >
user_info = #{userInfo},
</if>
<if test="headImg != null">
head_img = #{headImg, jdbcType=BLOB},
</if>
<if test="createTime != null">
create_time = #{createTime, jdbcType=TIMESTAMP},
</if>
id = #{id},
</set>
where id = #{id}
</update>

5.trim

1
2
3
4
5
6
7
<!--prefixOverrides 当trim元素内包含内容时, 会把内容匹配的前缀字符串去掉 -->
<trim prefix ="WHERE" prefixOverrides ="AND |OR "
</trim>

<!--suffixOverrides 当trim元素内包含内容时, 会把内容中匹配的后缀字符串去掉 -->
<trim prefix ="SET" suffixOverrides =","
</trim>

6.foreach

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<!-- 
collection: 迭代循环的属性名,数组为array, List类型为list, Map为_parameter, 如果使用Param注解参数,则为对应的注解名
item: 变量名
index: 索引的属性名, 当循环对象是Map类型时,这个值为Map的key(键值)
open: 整个循环内容开头的字符串
close: 整个循环内容结束的字符串
separator: 每次循环的分割符
-->
<select id="selectByIdList" resultType="tk.mybatis.simple.model.SysUser">
select id,
user_name userName,
user_password userPassword,
user_email userEmail,
user_info userInfo,
head_img headImg,
create_time createTime
from sys_user
where id in
<foreach collection="array" open="(" close=")" separator="," item="id" index="i">
#{id}
</foreach>
</select>

7.bind

1
2
3
4
5
6
7
8
9
10
<!-- bind标签可以使用OGNL表达式创建一个变量井将其绑定 到上下文中 -->
<if test ="userName != null and userName ! = ''">
and user_name like concat('%', #{userName}, '%')
</if>

改为
<if test ="userName != null and userName !=''">
<bind name="userNameLike" value = "'%'+ userName + '%'"/>
and user_name like #{userNameLike}
</if>

三.高级查询

1
2
3
4
5
6
public interface UserMapper {
//一对一
SysUser selectUserAndRoleById(Long id);
//一对多
List<SysUser> selectAllUserAndRoles();
}

1. 一对一 association

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
<!--UserMapper.xml-->
<!--extends继承别的resultMap-->
<!--
associations属性
property: 对应实体类的属性名
javaType: 属性对应的java类型
columnPrefix: 查询列的前缀, 配置前缀后, 子标签配置result的column可以省略前缀
resultMap: 使用已有的resultMap
-->

<resultMap id="userRoleMap" type="tk.mybatis.simple.model.SysUser" extends="userMap">
<association property="role" columnPrefix="role_"
javaType="tk.mybatis.simple.model.SysRole"
resultMap="tk.mybatis.simple.mapper.RoleMapper.roleMap">
</association>

</resultMap>
<!--一次把user相关的role信息查询-->
<select id="selectUserAndRoleById" resultMap="userRoleMap">
select
u.id,
u.user_name userName,
u.user_password userPassword,
u.user_email userEmail,
u.user_info userInfo,
u.head_img headImg,
u.create_time createTime,
r.id role_role_id,
r.role_name role_role_name,
r.enabled role_enabled,
r.create_by role_create_by,
r.create_time role_create_Time
from sys_user u
inner join sys_user_role ur on u.id = ur.user_id
inner join sys_role r on ur.role_id = r.id
where u.id = #{id}
</select>

<!--
associations属性
select: 另一个映射查询的id
column: 列名, 将主查询中列的结果作为嵌套查询的参数
fetchType: 数据加载类型, lazy和eager,延迟加载/积极加载,配置会覆盖全局的lazyLoadingEnabled
-->

<!-- 按需懒加载
1.执行user.getRole()触发嵌套查询
2.mybatis提供lazyLoadTriggerMethods 当调用配置中的方法时, 加载全部的延迟数据
默认值为 equals clone hashCode toString
-->
<resultMap id="userRoleMapSelect" extends="userMap"
type="tk.mybatis.simple.model.SysUser" >
<association property="role" column="{id=role_id}"
select="tk.mybatis.simple.mapper.RoleMapper.selectRoleById"
fetchType="lazy"
/>
</resultMap>
<select id="selectUserRoleMapSelect" resultMap="userRoleMapSelect">
select
u.id,
u.user_name,
u.user_password,
u.user_email,
u.user_info,
u.head_img,
u.create_time,
ur.role_id
from sys_user u
inner join sys_user_role ur on u.id = ur.user_id
where u.id = #{id}
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<!--RoleMapper.xml-->

<resultMap id="roleMap" type="tk.mybatis.simple.model.SysRole">
<id property="id" column="id"/>
<result property="roleName" column="role_name"/>
<result property="enabled" column="enabled"/>
<association property="createInfo" javaType="tk.mybatis.simple.model.CreateInfo">
<result property="createBy" column="create_by"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
</association>
</resultMap>

<select id="selectRoleById" resultMap="roleMap">
select * from sys_role where id = #{id}
</select>

2. 一对多 collection

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
<!-- collection属性
property: 对应实体类的属性名
columnPrefix: 查询列的前缀, 配置前缀后, 子标签配置result的column可以省略前缀
resultMap: 使用已有的resultMap
-->
<resultMap id="userRoleListMap" extends="userMap" type="tk.mybatis.simple.model.SysUser">
<collection property="roleList" columnPrefix="role_"
resultMap="tk.mybatis.simple.mapper.RoleMapper.rolePrivilegeListMap"/>
</resultMap>

<select id="selectAllUserAndRoles" resultMap="userRoleListMap">
select
u.id,
u.user_name,
u.user_password,
u.user_email,
u.user_info,
u.head_img,
u.create_time,
r.id role_id,
r.role_name role_role_name,
r.enabled role_enabled,
r.create_by role_create_by,
r.create_time role_create_Time,
p.id role_privilege_id,
p.privilege_name role_privilege_name,
p.privilege_url role_privilege_url
from sys_user u
inner join sys_user_role ur on u.id = ur.user_id
inner join sys_role r on ur.role_id = r.id
inner join sys_role_privilege rp on rp.role_id = r.id
inner join sys_privilege p on p.id = rp.privilege_id
</select>

<!--
collection属性
select: 另一个映射查询的id
column: 列名, 将主查询中列的结果作为嵌套查询的参数
fetchType: 数据加载类型, lazy和eager,延迟加载/积极加载,配置会覆盖全局的lazyLoadingEnabled
-->

<resultMap id="userRoleListMapSelect" extends="userMap"
type="tk.mybatis.simple.model.SysUser">
<collection property="roleList"
fetchType="lazy" select="tk.mybatis.simple.mapper.RoleMapper.selectRoleByUserId"
column="{userId=id}"/>
</resultMap>

<select id="selectAllUserAndRolesSelect" resultMap="userRoleListMapSelect">
select
u.id,
u.user_name,
u.user_password,
u.user_email,
u.user_info,
u.head_img,
u.create_time
from sys_user u
where u.id = #{id}
</select>

备注
github地址: https://github.com/tmacjx/mybatislearn
《mybatis从入门到精通》一书总结
参考
https://bfchengnuo.com/2017/09/28/MyBatis%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/

坚持原创技术分享,您的支持将鼓励我继续创作!