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 不能并用)
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="Object">
    select * from student where id=#{id}
</select>

2.2 insert标签

属性

  • id :唯一的标识符
  • parameterType:传给此语句的参数的全路径名或别名 例:com.test.poso.User
<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
<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 语句中,通过判断参数值来决定是否使用某个查询条件、判断是否更新某一个字段、判断是否插入某个字段的值。

<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)的关系。

<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中

<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的值都不为空的话,会执行如下语句:

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

会忽略最后一个“,” 。

3.5.2 在select中

<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的值都不为空的话,会执行如下语句:

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

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

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

<!--将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中

<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 标签较多时,这样的组合可能会导致错误。 如下:

<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 开头的,则它会剔除掉。

<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
关键字,和剔除追加到条件末尾的任何不相关的逗号。

<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 则不进行更新,而是保持数据库原值。

<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 中对集合进行迭代。也常用到批量删除、添加等操作中。

<!-- 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的类型

<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>  

测试代码:

@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是这样的:

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

3.8.2 单参数array数组的类型

<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代码:

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

测试代码:

@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的类型

<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代码:

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

测试代码:

@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, 对象图形化导航语言)表达式创建一个变量并将其绑定到上下文中。如以下代码:

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

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

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

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

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

Mapper.xml

<!-- 动态根据输入的用户名和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

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

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

<!-- <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参数打印结果

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

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

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

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 创作都市 设计师: CSDN官方博客
应支付0元
点击重新获取
扫码支付

支付成功即可阅读