Mybatis强大特性之一就是动态sql,可以在xml映射文件里面,以标签的形式动态的拼接sql语句,完成逻辑判断和动态拼接sql的功能。

1．if标签

if标签有一个必填的属性test,test的属性值符合ognl要求的判断表达式,表达式的结果可以是true或false,除此之外所有的非0值都是true,只有0为false

1. 判断条件property !=null 或者property == null
适用于任何类型的字眼,用于判断属性值是否为空,
2. 判断条件property != ‘’ 或者 property ==’’
仅适用于String类型的字段,用于判断是否为空字符串.
3. and和or
当有多个判断条件时候,使用and或or进行连接,嵌套的判断可以使用小括号分组,and相当于Java中的与(&&),or相当于Java中的或(||) .


需要注意一下就是一定要有个where标签或者 where 1=1,如果if条件都不满足的话最后生成的sql就会是以where结束,这样不符合sql规范就会报错.就上where1=1 或者where标签就可以杜绝这个问题.
还有and或者or都是需要手动添加的.不然也会不符合sql规范报错

2．foreach用法

ü foreach属性:
1. collection: 值是array 或者是list ,是什么值写你传递进来的就行了
2. item: 变量名,值为从迭代对象中取出来的每一个值
3. index: 索引的属性名,在集合数组的情况下值为当前索引值,当迭代循环的对象是Map类型时候,这个值为Map的key(键值).
4. open: 整合循环内容开头的字符串 , 比如说左括号
5. close : 整个循环内容结尾的字符串, 比如说右括号
6. separator : 每次循环的分隔符.

在in语句等列举条件常用，常用于实现批量操作

sql语句中有时候会使用in关键字,列如 id in(1,2,3),可以使用 ${ids}方式直接获取值,但是这种写法不能防止sql注入,想要避免sql注入就需要使用#{}的方式,这时候就要配合使用foreach标签来满足需求了.
foreach可以对数组,Map或者实现了Iterable接口(如List,set)的对象进行遍历,数组在处理的时候会转化成list对象,
因此foreach遍历的对象可以分为两大类:
iterable类型和Map类型.

ü foreach使用场景:
1. 前端传过来多个查询条件数据

比如 in (1,2,3)

2.批量新增

形参参数问题

只有一个数组参数或集合参数
当参数类型为集合的时候,默认会转换为Map类型,并添加一个key为collection的值(Mybatis3.3.0版本中增加),如果参数类型是List集合,那么就继续添加一个key为list的值(mybatis3.2.8及低版本中只有这一个key),这样,当collection=”list”时,就能得到这个集合,并对它进行循环操作.

当参数类型为数组的时候,也会转换成Map类型,默认的key为array,当采用如下的方法使用数组参数时,就需要把foreach标签中的collection属性值设置为array
List selectByIdList(Long[] idArray);
上面提到的是数组或者集合类型的参数默认的名字,推荐使用@Param来指定参数的名字,这时,collection就设置为通过@Param注解指定的名字.

ü 有多个参数
当有多个参数的时候,要使用@Param注解给每个参数都指定一个名字,否则在Sql中使用参数时就会不方便,因此将collection设置为@Param注解指定的名字即可.

ü 参数是map类型
使用Map和使用@Param注解方式类似,将collection指定为对应的Map中的key即可,如果要循环所传入的Map,推荐使用@Param注解指定名字,此时可以将collection设置为指定的名字,如果不想指定名字,就使用默认值 _parameter

ü 参数是一个对象
这个情况下指定为对象的属性名即可.当使用对象内多层嵌套的对象时,使用属性.属性(集合和数组可以使用下标取值)的方式可以指定深层的属性值.

批量插入

批量插入有两种方式:
1. 通过foreach动态拼装SQL语句
2. 使用BATCH类型的excutor,

ü 多少条数据执行一次批量插入呢
字段比较多的表就几百条提交一次,如果字段占用比较少,比如就三五个字段,那么一万条提交一次也是可以的
具体也要看业务场景,但是同时还需要思考占用内存问题(数据积压在集合里面是占用内存的)

实现动态update(foreach)

当参数类型是Map的时候, foreach 如何实现动态update.
当参数是map类型的时候,foreach标签的index属性值对应的不是索引值,而是map中的key,利用这个key可以使用动态update.
现在需要通过制定的列名和对应的值去更新数据,实现代码如下


dao接口
int updateByMap(Map map);

daomapper
<update id=”updateByMap”>
update sysuser
set
<foreach collection=”_parameter” item=”val” index=”key” separator=”,”>
${key} = #{val}
</foreach>
where id = #{id}
</update>

测试类
@Test
public void testUpdateByMap(){
SqlSession sqlSession = getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//从数据库查询 1 个 user 对象
Map map = new HashMap();
map.put(“id”, 1L);
map.put(“user_email”, “test@mybatis.tk”);
map.put(“user_password”, “12345678”);
//更新数据
userMapper.updateByMap(map);
//根据当前 id 查询修改后的数据
SysUser user = userMapper.selectById(1L);
Assert._assertEquals(“test@mybatis.tk”, user.getUserEmail());
} finally {
//为了不影响数据库中的数据导致其他测试失败，这里选择回滚
sqlSession.rollback();
//不要忘记关闭 sqlSession
sqlSession.close();
}
}

3．bind的用法

4．动态新增

在数据库表中插入数据时候,如果某一列的参数值不为空,就使用传入的值,如果传入参数为空,就使用数据库中的默认值(默认值通常是空),而不使用传入的空值,使用if就可以实现动态插入列的功能
<insert id=”insert2” useGeneratedKeys=”true” keyProperty=”id”>
insert into sys_user(
user_name, user_password,
<if test=”userEmail != null”>
<if test=”userEmail != ‘’”>
user_email,
</if>
</if>
user_info, head_img, create_time)
values(
#{userName}, #{userPassword},
<if test=”userEmail != null”>
<if test=”userEmail != ‘’”>
#{userEmail},
</if>
</if>
#{userInfo}, #{headImg, jdbcType=BLOB}, #{createTime, jdbcType=TIMESTAMP})
</insert>

//*
<insert id=”insertSelective” parameterType=”com.pinyougou.pojo.TbGoodsDesc” >
insert into tb_goods_desc
<trim prefix=”(“ suffix=”)” suffixOverrides=”,” >
<if test=”goodsId != null” >
goods_id,
</if>
<if test=”introduction != null” >
introduction,
</if>
<if test=”specificationItems != null” >
specification_items,
</if>
<if test=”customAttributeItems != null” >
custom_attribute_items,
</if>
<if test=”itemImages != null” >
item_images,
</if>
<if test=”packageList != null” >
package_list,
</if>
<if test=”saleService != null” >
sale_service,
</if>
</trim>
<trim prefix=”values (“ suffix=”)” suffixOverrides=”,” >
<if test=”goodsId != null” >
#{goodsId,jdbcType=BIGINT},
</if>
<if test=”introduction != null” >
#{introduction,jdbcType=VARCHAR},
</if>
<if test=”specificationItems != null” >
#{specificationItems,jdbcType=VARCHAR},
</if>
<if test=”customAttributeItems != null” >
#{customAttributeItems,jdbcType=VARCHAR},
</if>
<if test=”itemImages != null” >
#{itemImages,jdbcType=VARCHAR},
</if>
<if test=”packageList != null” >
#{packageList,jdbcType=VARCHAR},
</if>
<if test=”saleService != null” >
#{saleService,jdbcType=VARCHAR},
</if>
</trim>
</insert>


ü trim标签作用

where标签和set标签的功能都可以用trim标签来实现,并且在底层就是通过TrimSqlNode实现的.

where标签对应trim的实现如下

…


提示:
这里的AND和OR后面的空格不能省略,为了避免匹配到andes,orders等单词.


ü trim标签有如下的属性
prefix:当trim元素内包含内容时,会给内容增加prefix指定的前缀.
prefixOverrides:当trim元素内包含内容时,会把内容中匹配的前缀字符串去掉,
suffix: 当trim元素内包含内容时,会给内容增加suffix指定的后缀.
suffixOverrides: 当trim元素内包含内容时,会把内容中匹配的后缀字符串去掉/

5．动态修改

ü set标签作用
如果该标签包含的元素中有返回值,就插入一个set,如果set后面的字符串是以逗号为结尾的,就讲这个逗号去掉.


只更新有变化的字段,需要注意,更新的时候不能将原来有值但是没有发生变化的字段更新为空或者null,通过if标签可以实现这种动态列更新.

要结合业务层的逻辑判断,确保最终产生的sql语句没有语法错误,需要注意两点:
1. 每个if元素里面sql语句后面的逗号
2. where关键字前面的id=#{id} 这个条件

如果有id = #{id}这个条件,最终的sql如下:
update sys_user set id = #{id} where id = #{id} — sql是正确的
如果没有这个条件,最终的sql如下,
update sys_user set where id = #{id} — 这个sql很明显是错误的,set关键字后面没有内容,直接是where关键字,不符合语句规范

查询条件只有一个不是null也不是空(假设是userName)
如果有id = #{id}这个条件,最终的sql如下.
update sys_user set user_name = #{userName} , id = #{id} where id = #{id}
如果没有这个条件,最终的sql如下.
update sys_user set user_name = #{userName} , where id = #{id} — where关键字前面直接就是一个逗号,这个sql语句也是错误的.

从上面两种情况来看,id = #{id} 这个条件可以最大限度保证方法不出错,除了使用这种方式外,还可以结合业务层的逻辑判断调整xml文件中的sql来确保最终的sql语句的正确性,也可以通过where和set标签来解决这些问题.

6．动态查询

ü where标签作用
where标签作用是,
1. 如果标签内的 if 条件满足了,就会在判断条件前面添加where 关键字
2. 把第一个标签的and或者or去掉.

当if条件都不满足的时候,where元素中没有内容,所以在sql中不会出现where标签.



Oracle的案例
判断的是判断的传入的参数 带where标签的
<select id=”conditionSearchByPlanVo” parameterType=”io.mergency.entity.Vo.PlanVo”
resultMap=”emergencyPlanMap”>
select _* _from EMERGENCY_PLAN

<**where**><br />        <**if test="gradeId != null and gradeId != ''"**><br />            and  GRADE_ID = #{gradeId}<br />        </**if**>
    <**if test="name != null and name != ''"**><br />             and name LIKE    '%${name}%'<br />        </**if**><br />        <**if test="entryTime != null and entryTime != ''"**><br />            and ENTRY_TIME  between<br />            TO_DATE(#{startTime},'yyyy-mm-dd hh24:mi:ss')<br />            and<br />            TO_DATE(#{endTime},'yyyy-mm-dd hh24:mi:ss')<br />        </**if**>
</**where**>

</select>
也可以去掉where标签 直接在后面 where 1 = 1
<select id=”getAPPAdvertisingDetailList” resultMap=”APPAdvertisingDetailMap” >
select ba.*, bu.NAME as userName, bu.id as userId
from B_ADVERTISEMENT_INFO ba
inner join B_USER bu on ba.USER_ID = bu.ID
<if test=”effectSign != null and effectSign != ‘’”>
and ba.EFFECT_SIGN = #{effectSign}
</if>

<**if test="userName != null and userName != ''"**><br />        and bu.NAME like '%${userName}%'<br />    </**if**><br />    <**if test="advertisementSign != null and advertisementSign != ''"**><br />        and ba.ADVERTISEMENT_SIGN =  #{advertisementSign}<br />    </**if**><br />            where 1 = 1<br /></**select**><br /> <br /> <br /> <br /> 

if else查询

choose的用法


ü 测试类
@Test
public void testSelectByIdOrUserName(){
SqlSession sqlSession = getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//只查询用户名时
SysUser query = new SysUser();
query.setId(1L);
query.setUserName(“admin”);
SysUser user = userMapper.selectByIdOrUserName(query);
Assert.assertNotNull(user);
//当没有 id 时
query.setId(null);
user = userMapper.selectByIdOrUserName(query);
Assert.assertNotNull(user);
//当 id 和 name 都为空时
query.setUserName(null);
user = userMapper.selectByIdOrUserName(query);
Assert.assertNull(user);
} finally {
//不要忘记关闭 sqlSession
sqlSession.close();
}
}
ü dao接口
/ 根据用户 id 或用户名查询 */
SysUser selectByIdOrUserName(SysUser sysUser);
ü
ü daoxml文件
<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>
limit 0
</otherwise>
</choose>
</select**>

————————————————

<select id="queryRegionShoppingGuideDetail2" resultType="com.feihe.vo.form.sales.GDDetailListVO">
        SELECT regionInfo.lev3_name AS province,
        regionInfo.lev4_name AS district,
        regionInfo.lev5_name AS area,
        br.shop_name AS shopName,
        br.shop_id AS shopId,
        br.shop_code AS shopCode,
        br.guide_code AS guideCode,
        br.account_id AS accountId,
        br.account_month AS accountMonth,
        br.guide_id AS guideId,
        br.guide_name AS guideName,
        br.scan_sale_count AS scanSaleCount,
        br.nature_sale_count AS natureSaleCount,
        cast(br.sale_price AS CHAR) AS saleOrEntryMoney
        FROM t_form_sale_br_month_report br
        LEFT JOIN t_organization_region_info t2 ON br.fk_region_id = t2.pk_org_region_id
        LEFT JOIN t_organization_region_info regionInfo ON regionInfo.pk_org_region_id = br.fk_region_id
        <where>
            br.account_month = #{params.accountMonth}
            AND br.is_delete = 0
            <choose>
                <when test="dmRegionIds != null and dmRegionIds.size() > 0 and pmRegionIds != null and pmRegionIds.size() > 0">
                    AND (
                    t2.lev4_code IN
                    <foreach collection="dmRegionIds" open="(" close=")" item="item" separator=",">
                        #{item}
                    </foreach>
                    OR t2.lev3_code IN
                    <foreach collection="pmRegionIds" open="(" close=")" item="item" separator=",">
                        #{item}
                    </foreach>
                    )
                </when>
                <otherwise>
                    <if test="dmRegionIds != null and dmRegionIds.size() > 0">
                        AND t2.lev4_code IN
                        <foreach collection="dmRegionIds" open="(" close=")" item="item" separator=",">
                            #{item}
                        </foreach>
                    </if>
                    <if test="pmRegionIds != null and pmRegionIds.size() > 0">
                        AND t2.lev3_code IN
                        <foreach collection="pmRegionIds" open="(" close=")" item="item" separator=",">
                            #{item}
                        </foreach>
                    </if>
                </otherwise>
            </choose>
            <if test="params.guideCode != null and params.guideCode != ''">
                AND br.guide_code like concat(#{params.guideCode}, '%')
            </if>
            <if test="params.guideName != null and params.guideName != ''">
                AND br.guide_name like concat(#{params.guideName}, '%')
            </if>
            AND t2.`level` = 5
            ORDER BY br.scan_sale_count DESC
        </where>
    </select>