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/

Contents

一.基本用法

1.select

1.1 使用resultType

1.2使用resultMap设置结果映射

2.insert

3.update

4.delete

5.接口参数类型

二.动态SQL

1.if

2.choose

3.where

4.set

5.trim

6.foreach

7.bind

三.高级查询

1. 一对一 association

2. 一对多 collection