05 MyBatis强大特性 动态SQL

MyBatis 的强大特性之一便是它的动态 SQL,例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态SQL 这一特性可以彻底摆脱这种痛苦

动态SQL都是应用在映射文件中的
我这里使用的是mybatis-config.xml全局配置文件,里面添加映射mapper文件,来操作Order这个类.
这是全局配置文件
1.使用了别名,这样子在映射文件中填写返回类型的时候就比较方便简洁.在其他地方需要填完全限定名的时候也比较方便
2.默认的环境,最基本的配置使用POOLED
3.mappers,我这里使用的是从resources文件中获取mapper.xml文件

要操作的数据库,这是一个Orders(table)

对应的java类为

一、动态SQL之if

最简单的查询一下,所有Orders中user_id为1的,对应的SQL映射语句,在OrdersMapper接口中,该方法传入的是Object,返回类型为List<Orders>

    <select id="getOrdersByID" resultType="Orders" >
        select * from orders where user_id = #{userID}
    </select>

但是当需求变了的时候,不仅仅通过userID来进行查询,还要通过number或者其他字段来查询的时候,
不必重新写一个方法,只需要修改SQL映射即可.
例如:查询user_id为1并且订单号为1000011的订单

<select id="getOrdersByID" resultType="Orders" >
        select * from orders where 1=1
        <if test="user_id != null">
            and user_id = #{user_id}
        </if>
        <if test="number != null">
            and number = #{number }
        </if>
    </select>

if里面的为true,就表示传入的object存在这个属性的时候,在SQL语句后面就会自动添加上 and user_id = ?;

二、动态SQL之where

在上面if的使用时,如果user_id和number都不传入,
写出来的语句就是
select* from orders where 1=1
有时候你设置的条件可能也就会变成
select* from orders where and userid= 1;
这样也会有错

这时候只需要在前面添加上<where>标签即可

		select * from orders
        <where>
            <if test="user_id != null">
                and user_id = #{user_id}
            </if>
            <if test="number != null">
                and number = #{number }
            </if>
        </where>

where 元素只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入“WHERE”子句。而且，若语句的开头为“AND”或“OR”，where 元素也会将它们去除

三、动态SQL之set

set用在修改的时候,在修改时候,需要判断要修改的内容,对应的SQL映射是

			<if test="username != null and username!= ''">
                username = #{username},
            </if>
            <if test="number != null and number != ''">
                number = #{number},
            </if>

但是后面有逗号‘,’ 添加set的目的就在于,如果标签包含的元素有返回值,就插入一个逗号,如果set后面是以逗号结尾的,就将逗号去掉.达到人工编写正确SQL的目的

四、动态SQL之choose/when/otherwise

这个就像switch case default,
比如 <where>的作用在于去除后面的and,choose标签中,他会直接返回第一个成功的when,后续的不再执行,和switch case 一样.也有一个default,也就是otherwise.

		select * from orders
        <where>
            <choose>
                <when test="user_id != null">
                    and user_id = #{user_id}
                </when>
                <when test="number != null">
                    and number = #{number}
                </when>
                <otherwise>
                    and 1=0
                </otherwise>
            </choose>
        </where>

在当前情况下,如果传入了userid就按照userid查询,如果只传入了number就按number查询,如果同时传入了,按照顺序应该是按照userid来进行查询.

五、动态SQL之for each

foreach标签实现批量数据处理，遍历集合对象
foreach包含属性如下:
collection:必填,
当参数为数组时,collection属性为array
当参数为map时,collection属性为_parameter
当参数为list时,collection属性为list
item:变量名,从collection中拿出的每个元素的名称,自己起
index:索引的属性名,在集合数组下取值为当前索引值,当对象时map时,值为map的key.
open:整个循环开始的字符串
close:整个循环内容结束的字符串
separator:每次循环的分隔符

例子:批量插入时

XML映射SQL:
   <insert id="batchAddOrders">
        insert into orders(user_id, number, createtime, note) values
        <foreach collection="list" item="orders" separator=",">
            (#{orders.user_id},#{orders.number},#{orders.createtime},#{orders.note})
        </foreach>
    </insert>
  对应的java接口:
public void batchAddOrders(List<Orders> list);

例子:查询时,使用foreach

xml映射SQL:
<select id="getOrdersByID" resultType="Orders">
        select * from orders where user_id in 
        <foreach collection="list" item = "orders" index = "index"
                 open="(" separator="," close=")">
            #{orders.user_id}
        </foreach>
    </select>
java接口:
public List<Orders> getOrdersByID(Object o);

六、动态SQL之like模糊匹配

%可以替代任意个字符
_只能替代一个字符 这是我们在模糊匹配中要用到的

1.可以在参数中直接加入 %
mapper.getOrdersByNumber(“1%”);

<select id="getOrdersByNumber" 
    select * from orders where number like #{number}
</select>

2.使用concat:concat()函数用于将多个字符串连接成一个字符串

select * from orders where number like concat(concat('%', #{number}),'%')

3.使用bind标签
bind 元素可以从 OGNL 表达式中创建一个变量并将其绑定到上下文
这样就解决了带like的模糊查找问题了

<select id="getOrdersByID" 
  <bind name="pattern" value="'%' + _parameter.getNumber() + '%'" />
  select* from orders where number like#{pattern}
</select>