MyBatis动态SQL

1 什么是MyBatis？

MyBatis 是一个可以自定义 SQL、存储过程和高级映射的持久层框架。

• Mybatis是一个半ORM（对象关系映射）框架，它内部封装了JDBC，开发时只需要关注SQL语句本身，不需要花费精力去处理加载驱动、创建连接、创建statement等繁杂的过程。程序员直接编写原生态sql，可以严格控制sql执行性能，灵活度高。
• MyBatis 可以使用 XML 或注解来配置和映射原生信息，将 POJO映射成数据库中的记录，避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。
• 通过xml 文件或注解的方式将要执行的各种 statement 配置起来，并通过java对象和 statement中sql的动态参数进行映射生成最终执行的sql语句，最后由mybatis框架执行sql并将结果映射为java对象并返回。（从执行sql到返回result的过程）。

特点

• 简单易学：本身就很小且简单。没有任何第三方依赖，最简单安装只要两个jar文件 配置几个sql映射文件易于学习，易于使用，通过文档和源代码，可以比较完全的掌握它的设计思路和实现。
• 灵活：mybatis不会对应用程序或者数据库的现有设计强加任何影响。 sql写在xml里，便于统一管理和优化。通过sql语句可以满足操作数据库的所有需求。
• 解除sql与程序代码的耦合：通过提供DAO层，将业务逻辑和数据访问逻辑分离，使系统的设计更清晰，更易维护，更易单元测试。sql和代码的分离，提高了可维护性。
• 提供映射标签，支持对象与数据库的orm字段关系映射。
• 提供对象关系映射标签，支持对象关系组建维护。
• 提供xml标签，支持编写动态sql。

2 定义SQL语句

2.1 select标签

属性

• id :唯一的标识符.
• parameterType:传给此语句的参数的全路径名或别名 例:com.test.poso.User或user
• resultType :语句返回值类型或别名。注意：如果是集合，那么这里填写的是集合的泛型，而不是集合本身（resultType与resultMap 不能并用）

代码语言：javascript复制

<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="Object">
    select * from student where id=#{id}
</select>

2.2 insert标签

属性

• id :唯一的标识符
• parameterType:传给此语句的参数的全路径名或别名 例:com.test.poso.User

代码语言：javascript复制

<insert id="insert" parameterType="Object">
    insert into student
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="name != null"> NAME, </if>
    </trim>
    <trim prefix="values(" suffix=")" suffixOverrides=",">
        <if test="name != null"> #{name}, </if>
     </trim>
</insert>

2.3 delete标签

属性

• id :唯一的标识符
• parameterType:传给此语句的参数的全路径名或别名 例:com.test.poso.User

代码语言：javascript复制

<delete id="deleteByPrimaryKey" parameterType="Object">
    delete from student where id=#{id}
</delete>

2.4 update标签

属性

• id :唯一的标识符
• parameterType:传给此语句的参数的全路径名或别名 例:com.test.poso.User

3 动态SQL讲解

传统的使用JDBC的方法，相信大家在组合复杂的的SQL语句的时候，需要去拼接，稍不注意哪怕少了个空格，都会导致错误。MyBatis的动态SQL功能正是为了解决这种问题， 其通过 if、choose、when、otherwise、trim、where、set、foreach和bind等9种标签，可组合成非常灵活的SQL语句，从而提高开发人员的效率。

MyBatis动态SQL是做什么的？

• MyBatis动态SQL可以让我们在 Xml 映射文件内，以标签的形式编写动态SQL，完成逻辑 判断和动态拼接SQL的功能。

动态SQL的执行原理

• 使用OGNL从SQL参数对象中计算表达式的值，根据表达式的值动态拼接SQL，以此来完成动态SQL的功能。

3.1 if标签

if 标签通常用于 WHERE 语句、UPDATE 语句、INSERT 语句中，通过判断参数值来决定是否使用某个查询条件、判断是否更新某一个字段、判断是否插入某个字段的值。

代码语言：javascript复制

<if test="name != null and name != ''">
    and NAME = #{name}
</if>

3.2 choose标签

有时候我们并不想应用所有的条件，而只是想从多个选项中选择一个。MyBatis提供了 choose 元素，按顺序判断 when 中的条件出否成立，如果有一个成立，则 choose 结束。当 choose 中所有 when的条件都不满则时，则执行 otherwise 中的 sql。类似于 Java 的 switch 语句，choose 为 switch，when 为 case，otherwise 则为 default。

if 是与(and)的关系，而 choose 是或（or）的关系。

代码语言：javascript复制

<select id="getStudentListChoose" parameterType="Student" resultMap="BaseResultMap">
    SELECT * from STUDENT WHERE 1=1
    <where>
        <choose>
            <when test="Name!=null and student!='' ">
                AND name LIKE CONCAT(CONCAT('%', #{student}),'%')
            </when>
            <when test="hobby!= null and hobby!= '' ">
                AND hobby = #{hobby}
            </when>
            <otherwise>
                AND AGE = 15
            </otherwise>
        </choose>
    </where>
</select>

3.3 when标签

参见3.2，choose、when、otherwise这三个标签需要组合在一起使用，类似于 Java 中的 switch、case、default。只有一个条件生效，也就是只执行满足的条件 when，没有满足的条件就执行 otherwise，表示默认条件。

3.4 otherwise标签

参见3.2。

3.5 trim标签

trim标记是一个格式化的标记，主要用于拼接sql的条件语句（前缀或后缀的添加或忽略），可以完成set或者是where标记的功能。

属性

• prefix：在trim标签内sql语句加上前缀
• suffix：在trim标签内sql语句加上后缀
• prefixOverrides：指定去除多余的前缀内容，如：prefixOverrides=“AND | OR”，去除trim标签内sql语句多余的前缀"and"或者"or"。
• suffixOverrides：指定去除多余的后缀内容。

3.5.1 在update中

代码语言：javascript复制

<update id="updateByPrimaryKey" parameterType="Object">
	update student set 
	<trim  suffixOverrides=",">
		<if test="name != null">
		    NAME=#{name},
		</if>
		<if test="hobby != null">
		    HOBBY=#{hobby},
		</if>
	</trim> 
	where id=#{id}
</update>

如果name和hobby的值都不为空的话，会执行如下语句：

代码语言：javascript复制

update student set NAME='XX',HOBBY='XX' /*,*/ where id='XX'

会忽略最后一个“，” 。

3.5.2 在select中

代码语言：javascript复制

<select id="selectByNameOrHobby" resultMap="BaseResultMap">
	select * from student 
	<trim prefix="WHERE" prefixOverrides="AND | OR">
		<if test="name != null and name.length()>0"> 
			AND name=#{name}
		</if>
		<if test="hobby != null and hobby.length()>0">
			AND hobby=#{hobby}
		</if>
	</trim>
</select>

如果name和hobby的值都不为空的话，会执行如下语句：

代码语言：javascript复制

select*fromuserWHERE /*and*/ name = ‘xx’ and hobby= ‘xx’

会为片段添加 “WHERE” 前缀，并忽略第一个 “and” 。

当然，避免出现“WHERE AND”还有其他方法，如下：

代码语言：javascript复制

<!--将where提取出来，并加上“1=1”的查询条件 -->
select * from student where 1=1
	<trim suffixOverrides=",">
		<if test="name != null and name != ''">
			and NAME = #{name}
		</if>
		<if test="hobby != null and hobby != ''">
			and HOBBY = #{hobby}
		</if>
	</trim>

3.5.3 在insert中

代码语言：javascript复制

<insert id="insert" parameterType="Object">
    insert into student 
	<trim prefix="(" suffix=")" suffixOverrides=",">
		<if test="name != null">
			NAME,
		</if>
		<if test="hobby != null  ">
			HOBBY,
		</if>
	</trim>
	<trim prefix="values(" suffix=")" suffixOverrides=",">
		<if test="name != null  ">
			#{name},
		</if>
		<if test="hobby != null  ">
			#{hobby},
		</if>
	</trim>
</insert>

3.6 where标签

当 if 标签较多时，这样的组合可能会导致错误。 如下：

代码语言：javascript复制

<select id="getStudentListWhere" parameterType="Object" resultMap="BaseResultMap">
    SELECT * from STUDENT WHERE
    <if test="name!=null and name!='' ">
        NAME LIKE CONCAT(CONCAT('%', #{name}),'%')
    </if>
    <if test="hobby!= null and hobby!= '' ">
        AND hobby = #{hobby}
    </if>
</select>

当 name 值为 null 时，查询语句会出现 “WHERE AND” 的情况，解决该情况除了将"WHERE"改为“WHERE 1=1”之外，还可以利用 where标签。这个“where”标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果标签返回的内容是以 AND 或 OR 开头的，则它会剔除掉。

代码语言：javascript复制

<select id="getStudentListWhere" parameterType="Object" resultMap="BaseResultMap">
    SELECT * from STUDENT
    <where>
        <if test="name!=null and name!='' ">
            NAME LIKE CONCAT(CONCAT('%', #{name}),'%')
        </if>
        <if test="hobby!= null and hobby!= '' ">
            AND hobby = #{hobby}
        </if>
    </where>
</select>

3.7 set标签

没有使用 if 标签时，如果有一个参数为 null，都会导致错误。当在 update 语句中使用 if 标签时，如果最后的 if 没有执行，则或导致逗号多余错误。使用 set 标签可以将动态的配置 set 关键字，和剔除追加到条件末尾的任何不相关的逗号。

代码语言：javascript复制

<update id="updateStudent" parameterType="Object">
    UPDATE STUDENT
    SET NAME = #{name},
    MAJOR = #{major},
    HOBBY = #{hobby}
    WHERE ID = #{id};
</update>

<update id="updateStudent" parameterType="Object">
    UPDATE STUDENT SET
    <if test="name!=null and name!='' ">
        NAME = #{name},
    </if>
    <if test="hobby!=null and hobby!='' ">
        MAJOR = #{major},
    </if>
    <if test="hobby!=null and hobby!='' ">
        HOBBY = #{hobby}
    </if>
    WHERE ID = #{id};
</update>

使用 set if 标签修改后，如果某项为 null 则不进行更新，而是保持数据库原值。

代码语言：javascript复制

<update id="updateStudent" parameterType="Object">
    UPDATE STUDENT
    <set>
        <if test="name!=null and name!='' ">
            NAME = #{name},
        </if>
        <if test="hobby!=null and hobby!='' ">
            MAJOR = #{major},
        </if>
        <if test="hobby!=null and hobby!='' ">
            HOBBY = #{hobby}
        </if>
    </set>
    WHERE ID = #{id};
</update>

3.8 foreach标签

foreach 标签主要用于构建 in 条件，可在 sql 中对集合进行迭代。也常用到批量删除、添加等操作中。

代码语言：javascript复制

<!-- in查询所有，不分页 -->
<select id="selectIn" resultMap="BaseResultMap">
    select name,hobby from student where id in
    <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
        #{item}
    </foreach>
</select>

属性

• collection：collection 属性的值有三个分别是 list、array、map三种，分别对应的参数类型为：List、数组、map 集合。
• item：表示在迭代过程中每一个元素的别名
• index：表示在迭代过程中每次迭代到的位置（下标）
• open：前缀
• close ：后缀
• separator：分隔符，表示迭代时每个元素之间以什么分隔

在使用foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的，但是在不同情况下，该属性的值是不一样的，主要有一下3种情况：

1. 如果传入的是单参数且参数类型是一个List的时候，collection属性值为list
2. 如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array
3. 如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封装成map，实际上如果你在传入参数的时候，在MyBatis里面也是会把它封装成一个Map的，map的key就是参数名，所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key

下面分别来看看上述三种情况的示例代码：

3.8.1 单参数List的类型

代码语言：javascript复制

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

测试代码：

代码语言：javascript复制

@Test  
public void dynamicForeachTest() {  
    SqlSession session = Util.getSqlSessionFactory().openSession();  
    BlogMapper blogMapper = session.getMapper(BlogMapper.class);  
    List<Integer> ids = new ArrayList<Integer>();  
    ids.add(1);  
    ids.add(3);  
    ids.add(6);  
    List<Blog> blogs = blogMapper.dynamicForeachTest(ids);  
    for (Blog blog : blogs)  
        System.out.println(blog);  
    session.close();  
}

上述collection的值为list，对应的Mapper是这样的：

代码语言：javascript复制

public List<Blog> dynamicForeachTest(List<Integer> ids);

3.8.2 单参数array数组的类型

代码语言：javascript复制

<select id="dynamicForeach2Test" resultType="Blog">  
    select * from t_blog where id in  
    <foreach collection="array" index="index" item="item" open="(" separator="," close=")">  
        #{item}  
    </foreach>  
</select>  

上述collection为array，对应的Mapper代码：

代码语言：javascript复制

public List<Blog> dynamicForeach2Test(int[] ids);

测试代码：

代码语言：javascript复制

@Test  
public void dynamicForeach2Test() {  
    SqlSession session = Util.getSqlSessionFactory().openSession();  
    BlogMapper blogMapper = session.getMapper(BlogMapper.class);  
    int[] ids = new int[] {1,3,6,9};  
    List<Blog> blogs = blogMapper.dynamicForeach2Test(ids);  
    for (Blog blog : blogs)  
        System.out.println(blog);  
    session.close();  
}  

3.8.3 自己把参数封装成Map的类型

代码语言：javascript复制

<select id="dynamicForeach3Test" resultType="Blog">  
    select * from t_blog where title like "%"#{title}"%" and id in  
    <foreach collection="ids" index="index" item="item" open="(" separator="," close=")">  
        #{item}  
    </foreach>  
</select>  

上述collection的值为ids，是传入的参数Map的key，对应的Mapper代码：

代码语言：javascript复制

public List<Blog> dynamicForeach3Test(Map<String, Object> params); 

测试代码：

代码语言：javascript复制

@Test  
public void dynamicForeach3Test() {  
    SqlSession session = Util.getSqlSessionFactory().openSession();  
    BlogMapper blogMapper = session.getMapper(BlogMapper.class);  
    final List<Integer> ids = new ArrayList<Integer>();  
    ids.add(1);  
    ids.add(2);  
    ids.add(3);  
    ids.add(6);  
    ids.add(7);  
    ids.add(9);  
    Map<String, Object> params = new HashMap<String, Object>();  
    params.put("ids", ids);  
    params.put("title", "中国");  
    List<Blog> blogs = blogMapper.dynamicForeach3Test(params);  
    for (Blog blog : blogs)  
        System.out.println(blog);  
    session.close();  
}

3.9 bind标签

标签可以使用ONGL(Object-Graph Navigation Language, 对象图形化导航语言)表达式创建一个变量并将其绑定到上下文中。如以下代码：

代码语言：javascript复制

<if test="userName != null and userName != ''">
	and user_name like concat('%', #{userName} ,'%') 
</if>

由于不同的数据库，concat函数的用法不一样，Mysql的有3个参数，而oracle的只有2个。这就造成了这条SQL在不同环境中运行可能会出错。为了避免这种错误，可以用标签，如下：

代码语言：javascript复制

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

标签的两个属性都是必选的，name为绑定到上下文的变量名，value为OGNL表达式。创建了标签的变量后，就可以在下面直接使用了。如下例子，先写一个静态方法，然后在Mapper.xml中调用该方法设置默认值：

代码语言：javascript复制

package ex.mybatis.rbac.mapper;
public class BindTest {
	public static String setName() {
		return "test";
	}
}

Mapper.xml

代码语言：javascript复制

<!-- 动态根据输入的用户名和id查询用户信息<choose>标签用法 -->
<select id="selectByIdOrName" resultMap="BaseResultMap">
	<!-- 使用<bind>标签调用的方法拿出来的默认值 -->
  	<bind name="name" value="@ex.mybatis.rbac.mapper.BindTest@setName()"/>
  	
    select id, user_name, user_password, user_email, create_time, user_info, head_img
    from sys_user
    <!-- 加上1=1是为了防止2个查询条件都为空的时候，SQL不会报错。学了where标签后可以去掉 -->
    where 1=1 
    <choose>
    	<when test="id != null">
    		and id = #{id}
    	</when>
    	<when test="userName != null and userName != ''">
    		and user_name like concat('%', #{userName} ,'%') 
    	</when>
    	<otherwise>
    		<!-- 当所有条件都空时，使用<bind>标签调用的方法拿出来的默认值 -->
    		and user_name=#{name}
    	</otherwise>
    </choose>
</select>

使用标签打印入参信息，如下：

先定义静态方法print

代码语言：javascript复制

package ex.mybatis.rbac.mapper;
public class BindTest {
	public static void print(Object param) {
		System.out.println(param);
	}
}

在Mapper.xml中使用标签，调用该方法

代码语言：javascript复制

<!-- <foreach>标签实现更新，参数为map -->
<update id="updateByMap">
  	<bind name="print" value="@ex.mybatis.rbac.mapper.BindTest@print(_parameter)"/>
	update sys_user 
	set 
	<foreach collection="_parameter" item="val" index="key" separator=",">
		${key} = #{val}
	</foreach>
	where id = #{id}
</update>

updateByMap参数打印结果

代码语言：javascript复制

{user_email=test2@mybatis.ex, user_password=1111333, id=1001}

4 #{}和${}的区别是什么？

• #{}是预编译处理，${}是字符串替换。
• Mybatis 在处理#{}时，会将 sql 中的#{}替换为?号，调用 PreparedStatement 的 set 方法来赋值。
• Mybatis 在处理 {}时，就是把{}替换成变量的值。
• 使用#{}可以有效的防止SQL注入，提高系统安全性。

对象 集合 数据库 sql mybatis

0 人点赞