MyBatis动态SQL和resultMap

2022-04-15 MyBatis PV:

动态SQL

mybatis动态SQL可以根据Java变量的不同动态拼接SQL，其本质就是按照我们每次向接口中传入的变量和我们指定的规则来帮助我们拼接SQL语句，简化了SQL语句的编写。

其主要有以下几组标签，其语义与在Java中相同：

if
choose (when, otherwise)
trim (where, set)
foreach

if

if即条件判断，一般多用于where后的判断。

例如：

<select id="findActiveBlogWithTitleLike" resultType="Blog">
  SELECT * FROM BLOG
  WHERE state = 'ACTIVE'
  <if test="title != null">
    AND title like #{title}
  </if>
</select>

这里只有当title != null成立的时候，其内部的语句AND title like #{title}才会添加到SQL语句中。

choose、when、otherwise

这一组标签类似于Java中的switch语句或者if...else if...else语句。

即当会依次检验when是否满足，如果一个满足就按该语句拼接；如果都不满足，就按照otherwise来拼接。

例如：

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

这里当title != null满足的时候，就按第一个when拼接，后面的都不再执行；否则检验author != null and author.name != null，如果满足，则按该when拼接，否则就直接按otherwise来拼接。

where、trim、set

where

这一组标签是为了解决前面标签存在的问题。

有下面的sql：

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG
  WHERE
  <if test="state != null">
    state = #{state}
  </if>
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="author != null and author.name != null">
    AND author_name like #{author.name}
  </if>
</select>

现在思考，

假设state != null、title != null、author != null and author.name != null都验证失败，则SQL语句拼接为如下：
1
2
SELECT * FROM BLOG
WHERE
很明显这是一个错误的SQL。
假设state != null验证失败，但title != null和author != null and author.name != null验证成功，则SQL语句拼接如下：
1
2
3
4
SELECT * FROM BLOG
WHERE
AND title like #{title}
AND author_name like #{author.name}
很明显，这也是一个错误的SQL。

而where标签就是为了解决这两种情况，其特点如下：

如果条件都不满足，则自动去掉WHERE关键字。
如果第一个条件不满足，则自动去掉AND、OR关键字。

将上面的改写如下：

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG
  <where>
      <if test="state != null">
        state = #{state}
      </if>
      <if test="title != null">
        AND title like #{title}
      </if>
      <if test="author != null and author.name != null">
        AND author_name like #{author.name}
      </if>
  </where>
</select>

如此上面两种情况分别生成的SQL如下：

1	SELECT * FROM BLOG

1
2
3

SELECT * FROM BLOG
WHERE title like #{title}
AND author_name like #{author.name}

set

与where一样，set关键字如果直接拼接也会面临问题。

有下面的sql：

<update>
UPDATE Author SET
          <if test="username != null">username=#{username},</if>
          <if test="password != null">password=#{password},</if>
          <if test="email != null">email=#{email},</if>
          <if test="bio != null">bio=#{bio}</if>
         WHERE id=#{id}
</update>

现在思考，假如只有bio != null验证失败，其他都成功，则拼接的SQL如下：

1	UPDATE Author SET username = #{username}, password=#{password}, email=#{email}, WHERE id=#{id}

很明显，这也是一个错误的SQL，因为多了一个逗号,。

而set标签就是为了解决该问题而生的。

其操作为：会自动去掉后缀的都好,。

例如：

<update>
UPDATE Author
	<set>
          <if test="username != null">username=#{username},</if>
          <if test="password != null">password=#{password},</if>
          <if test="email != null">email=#{email},</if>
          <if test="bio != null">bio=#{bio}</if>
         WHERE id=#{id}
      </set>
</update>

trim

该标签与Java中意思一致，即去前缀或后缀，不同的是，Java中只能去除空格，而这里可以自定义要去除的对象以及哪个关键字的前后缀。

实际上上面的where标签和set标签都是trim标签的特殊情况，因为上面的本质就是前后缀的问题。

例如要实现上面where标签的功能。

其代码就如下：

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG
  <trim prefix="where" prefixOverrides="AND |OR">		
      <if test="state != null">
        state = #{state}
      </if>
      <if test="title != null">
        AND title like #{title}
      </if>
      <if test="author != null and author.name != null">
        AND author_name like #{author.name}
      </if>
  </trim>
</select>

其属性有以下四个：

prefix：前缀
prefixOverrides：要去除的前缀
suffix：后缀
prefixOverrides：要去除的后缀

即prefix和suffix分别会被加到语句中，而prefixOverrides和prefixOverrides会被去掉。

再例如update语句中去除拼接时的,：

<update>
	UPDATE Author
	<trim prefix="SET" prefixOverrides=",">
          <if test="username != null">username=#{username},</if>
          <if test="password != null">password=#{password},</if>
          <if test="email != null">email=#{email},</if>
          <if test="bio != null">bio=#{bio}</if>
	</trim>
	WHERE id=#{id}
</update>

本例中，则会自动加上SET，并且会去掉后缀,。

foreach

与Java一致，该标签用于遍历接口传入的集合（List, Array, Map）。

其包含以下属性：

collection：表示迭代集合的名称
item：表示本次迭代获取的元素，若collection为List、Set或者数组，则表示其中的元素；若collection为map，则代表key-value的value，该参数为必选
open：表示该语句以什么开始，最常用的是左括弧(，注意:mybatis会将该字符拼接到整体的sql语句之前，并且只拼接一次，该参数为可选项
close：表示该语句以什么结束，最常用的是右括弧)，注意:mybatis会将该字符拼接到整体的sql语句之后，该参数为可选项
separator：mybatis会在每次迭代后给sql语句append上separator属性指定的字符，一般为逗号,，该参数为可选项
index：在list、Set和数组中,index表示当前迭代的位置，在map中，index代指是元素的key，该参数是可选项。

由于包含多个元素，所以其一般和IN一起用，例如：

<select id="queryUserByIdsList"  resultType="user">
		select * from user
		<where> id in 
			<foreach collection="list" item="item" open="(" close=")" separator=",">
				#{item}
			</foreach>
		</where>
</select>

补充：该标签也可以配合mybaits的函数split，将一个字段分割，然后每个部分依次再拼接。

如tags是按id1;id2;id3这样拼接的：

<select id="queryUserByIdsList"  resultType="user">
		select * from user
		<where> id in 
			<foreach collection="tags.split(';')" item="item" open="(" close=")" separator=",">
				#{item}
			</foreach>
		</where>
</select>

bind

bind标签允许我们创建一个变量，并将其绑定到当前的上下文。

如：

<select id="selectBlogsLike" resultType="Blog">
  <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
  SELECT * FROM BLOG
  WHERE title LIKE #{pattern}
</select>

补充：

多参数的接口

一般我们再定义接口的时候只有一个参数，但是当有多个参数的时候，mybatis的行为就与我们直觉的不太相同。

因为只有一个参数var的时候，我们使用#{name}取值时，实际上是取var.name属性。但是当具有多个参数时，这种方法就明显有歧义的。

因此其默认行为改为：

将参数自动命名为paramX（X为参数的序号-从1开始）。
也可以直接通过#{X}来获取第X个参数-从0开始。

例如：

<select id="selectUser" resultMap="UserResultMap">
    select * from user
    where user_name = #{param1} and dept_id = #{param2}
</select>

<select id="selectUser" resultMap="UserResultMap">
    select * from user
    where user_name = #{0} and dept_id = #{1}
</select>

这两种都不直观，更好的解决办法是，通过注解@Param来为其命名。

如：

1	public User selectUser(@Param("userName") String name, int @Param("deptId") deptId);

<select id="selectUser" resultMap="UserResultMap">
    select * from user
    where user_name = #{userName} and dept_id = #{deptId}
</select>

或者将其封装到map或Java bean中。

mybatis内置参数

mybatis内置了几个参数：

_parameter：所有参数，如果为单参数，则其就为该参数；如果是多参数，就会将其封装为一个map。
- 第二种情况可以直接通过.来获取属性值，如_parameter.uId

_databaseId：用户所指定的对应的数据库厂商标识。需要再mybaits配置文件中进行配置：

<databaseIdProvider type="DB_VENDOR">
    <property name="MySQL" value="mysql"/>
    <!--//多个数据库提供商配置...-->
</databaseIdProvider>

（一般用的不多）

#{value}：当参数只有一个的时候，该参数可以直接使用#{value}来引用，（也可以使用参数名字来引入）。这个一般配合resultMap的嵌套查询，因为其参数的名字没法指定。

resultMap

resultMap可以让我们将数据库中的字段与Java对象中的属性按照指定的方法匹配起来，mybatis将会自动按照该匹配进行封装。

例如：

1、Java对象

public class User{
    private int myId;
    private String myName;
    private String myHobby;
}

2、表字段：

id
name
hobby

此时要将其对应起来，自动就无能为力了，需要我们编写resultMap来手动匹配：

<resultMap  id="userResultMap" type="user">
  <id property="myId" column="id" />
  <result property="myName" column="name"/>
  <result property="myHobby" column="hobby"/>
</resultMap>

然后再sql中使用该resultMap：

<select id="selectUsers" resultMap="userResultMap">
  select id, name, hobby
  from users
  where id = #{id}
</select>

如此就能完成对应工作。

半自动匹配

如果我们表中和对象中只有部分不匹配，则可以只手动编写不匹配的，然后将autoMapping设置为true。

高级匹配

resultMap不光可以完成简单字段的匹配，也可以完成对象和集合的匹配。

例如：

1、User对象

public class User{
    private int id;
    private String name;
    private String hobby;
    private Person mentor;
    private Person[] friends;
}

Person对象

public class Person{
	private int id;
    private String name;
}

2、表

users：
- id
- name
- mentor
- friends
persons:
- id
- fId
- name

3、resultMap

<resultMap  id="userResultMap" type="user" autoMapping="true">
  <association property="mentor" javaType="Person">
  	<id property="id" column="MId" />
    <result property="name" column="MName"/>
  </association>
  <collection property="friends"  javaType="Person" >
    	 <id property="id" column="FId" />
    	<result property="name" column="FName"/>
    </collection>
</resultMap>

4、SQL

<select id="findUser" resultType="user">
	SELECT userT.id as id, userT.name as name, userT.hobby as hobby, 
    mentorT.id = MId, mentorT.name = MName, 
    friendsT.id = FId, friendsT.name = FName
    FROM users userT left outer join persons personsT ON userT.mentor = mentorT.id
    left outer join firends friendsT ON userT.id = friendsT.fId
</select>

这样就能自动装配好其对象和集合属性。

嵌套查询

有时候Java实体中的属性不能简单的通过表的连接来查询，需要更加复杂的查询。例如将一张表中的某个字段分割过后，使用分割后的元素进行查询，此时除了使用较为复杂的存储过程外，还可以使用resultMap中的sql属性来配置子查询。

如：

1、User对象

public class User{
    private int id;
    private String name;
    private String hobby;
    private Person[] friends;
}

Person对象

public class Person{
	private int id;
    private String name;
}

2、表（与上一致）

注意此时数据库中users表的friends是按照frinedId1;frinedId2;frinedId3这样存储的。

3、resultMap

1
2
3

<resultMap  id="userResultMap" type="user" autoMapping="true">
  <collection property="friends"  javaType="Person" sql="_findPerson"  autoMapping="true"/>  <!--由于获取的字段名字相同了，所以可以直接自动匹配了-->
</resultMap>

4、SQL

<select id="findUser" resultType="user">
    SELECT id, name, friends FROM users
</select>

<select id="_findPerson" resultType="person">
    SELECT id, name FROM friends WHERE id IN 
    <foreach collection="_parameter.split(';')" separator="," open="(" close=")" item="itemId">
        #{itemId}
    </foreach>
</select>

值得注意的是这里的_parameter，就如上面说的，此时相当于也是一个接口，但是我们并不是直接传参，所以并不知道具体的参数名字，则_parameter就可以直接使用了。