动态SQL

在mybatis3之前，需要学习和了解非常多标签，现在采用OGNL表达式语言，消除了许多其他标签
剩下
if
choose
trim
foreach
bind

if用法：

if通常用于WHERE语句中，判断参数是否使用某个查询条件，UPDATE语句中判断是否更新某个字段，INSERT语句中判断是否插入某个字段。

在WHERE语句中使用if

需求：
实现一个用户管理高级查询功能，根据输入的条件去检索用户信息。当只输入用户时，需要根据用户名进行模糊查询，当只输入邮箱时，根据邮箱进行完全匹配；当同时输入用户名和邮箱时，用这两个条件去匹配用户。

<select id="selectByUser" resultType="pers.congcong.myBatis2.pojos.SysUser">
    SELECT id,
        user_name userName,
        user_password userPassward,
        user_email userEmail,
        user_info userInfo,
        head_img headImg,
        create_time createTime
    FROM sys_user
    WHERE 1=1
    <if test="userName != null and userEmail != ''">
        AND user_name LIKE concat('%', #{userName}, '%')
    </if>
    <if test="userEmail != null and userEmail != ''">
        AND user_email = #{userEmail}
    </if>
</select>

if标签有一个必填的属性 test， test的属性是一个符合OGNL要求的判断表达式，表达式的结果为TRUE或FALSE，除此之外非0都为true，0为false。

@Test
    public void testSelectByUser() {
        SqlSession sqlSession = getSqlSession();
        try {
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            SysUser sysUser = new SysUser();
            sysUser.setUserName("ad");
            List<SysUser> sysUserList = userMapper.selectByUser(sysUser);

            Assert.assertTrue(sysUserList.size()>0);

//            根据邮箱查
            sysUser.setUserEmail("test@mybatis.tk");
            sysUser.setUserName(null);
            sysUserList = userMapper.selectByUser(sysUser);


//            根据用户和邮箱查
            sysUser.setUserName("ad");
            Assert.assertTrue(sysUserList.size()==0);

        } finally {
            sqlSession.close();
        }
    }

if 用法，加一个<if test ="property != ' ' and property == null">标签
中间加 and 条件。
if中符合条件才会有and条件。

在update中使用if

需求：
只更新变化的字段。需要注意，更新的时候不能将原来有值但没发生变化的字段更新为空或者NULL。
通过IF就可以实现这种动态更新

示例：

   <update id="updateByIdSelective">
        UPDATE sys_user
        SET
        <if test="userName != null and userName !='' ">
            user_name = #{userName},
        </if>
        <if test="userPassword != null and userPassword != '' ">
            user_password = #{userPassword},
        </if>
        <if test="userEmail != null and userEmail != '' ">
            user_email = #{userEmail},
        </if>
        <if test="userInfo != null and userInfo != '' ">
            user_info = #{userInfo},
        </if>
        <if test="headImg != null">
            head_img = #{headImg, jdbcType=BLOB},
        </if>
        <if test="createTime != null">
            create_time = #{createTime, jdbcType=TIMESTAMP},
        </if>
        id = #{id}
        WHERE id = #{id}
    </update>

测试：

    @Test
    public void testUpdateByIdSelective() {
        SqlSession sqlSession = getSqlSession();
        try {
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

            SysUser sysUser = userMapper.selectById(1l);
            sysUser.setUserName("cccc");


            userMapper.updateByIdSelective(sysUser);

        } finally {
            sqlSession.rollback();
            sqlSession.close();
        }
    }
在insert中使用if

应用场景：
在数据表中插入参数是，如果参数值不为空，就传入，如果传入值为空，就使用数据库中默认的值，而不使用空值。

示例：

 <insert id="insert2" useGeneratedKeys="true" keyProperty="id">
        insert INTO sys_user(
            user_name, user_password,
            <if test="userEmail != null and userEmail != ''">
                user_email,
            </if>  user_info, head_img, create_time
        )
        VALUES (
            #{userName}, #{userPassword},
        <if test="userEmail != null and userEmail != ''">
        #{userEmail},
        </if>
         #{userInfo}, #{headImg, jdbcType= BLOB}, #{createTime, jdbcType= DATE}
        )
    </insert>

choose用法
可以实现if ...else...的逻辑

案例需求：
当参数id有值的时候，优先使用id查询，当id没有值的时候，便去判断用户名是否有值，如果有值就用用户名查询，如果没有值，就使SQL查询无结果。

方案：

 <select id="selectByIdOrUserName" resultType="pers.congcong.myBatis2.pojos.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>
                and 1 = 2
            </otherwise>
        </choose>
    </select>

测试代码：

@Test
    public void testselectByIdOrUserName() {
        SqlSession sqlSession = getSqlSession();
        try {
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

            SysUser sysUser = userMapper.selectById(1l);
            sysUser.setId(null);

            SysUser sysUser1 = userMapper.selectByIdOrUserName(sysUser);

            sysUser.setUserName(null);
            sysUser1 = userMapper.selectByIdOrUserName(sysUser);
        } finally {
            sqlSession.close();
        }
    }

where/set/trim用法：

where标签的用法：如果标签包含的元素中有返回值，就插入一个where，如果where后面的字符串是以and和or开头的，就将它们剔除。

where会自动剔除 and 或 or 开头的 and 和 or

<select id="selectByUser" resultType="pers.congcong.myBatis2.pojos.SysUser">
        SELECT id,
            user_name userName,
            user_password userPassward,
            user_email userEmail,
            user_info userInfo,
            head_img headImg,
            create_time createTime
        FROM sys_user
        <where>
        <if test="userName != null and userEmail != ''">
            AND user_name LIKE concat('%', #{userName}, '%')
        </if>
        <if test="userEmail != null and userEmail != ''">
            AND user_email = #{userEmail}
        </if>
        </where>
  </select>
set的用法：

如果标签包含元素中的返回值，就插入一个set，如果set后面中的元素是以逗号结尾的，就将这个逗号剔除。
但是如果set元素中没有内容，照样会出现SQL错误，所以，类似id = #{id}这样必然存在的赋值任然需要保留。

例子：

<update id="updateByIdSelective">
        UPDATE sys_user
        <set>
            <if test="userName != null and userName !='' ">
                user_name = #{userName},
            </if>
            <if test="userPassword != null and userPassword != '' ">
                user_password = #{userPassword},
            </if>
            <if test="userEmail != null and userEmail != '' ">
                user_email = #{userEmail},
            </if>
            <if test="userInfo != null and userInfo != '' ">
                user_info = #{userInfo},
            </if>
            <if test="headImg != null">
                head_img = #{headImg, jdbcType=BLOB},
            </if>
            <if test="createTime != null">
                create_time = #{createTime, jdbcType=TIMESTAMP},
            </if>
            id = #{id},
        </set>
        WHERE id = #{id}
    </update>
trim用法：

where和set都是通过trim实现，并且底层都是通过TrimSqlNode实现的。

<trim prefix = "SET" suffixOverrides = ",">
...
</trim>

<trim prefix = "WHERE" prefixOverrides = "AND | OR">
...
</trim>

foreach用法

SQL语句中有时会使用IN关键字，例如 id in (1, 2, 3)，可以使用$(ids)方式直接获取值，但是不能放置SQL注入，避免SQL注入就需要使用#{}的方式，这是就要配合使用foreach标签来满足需求。

foreach可以对数组、Map或实现Iterable接口的对象进行遍历

foreach实现in集合

案例需求：
根据用户ID集合查询出所有符合条件的用户

实现：

 <select id="selectByIdList" resultType="pers.congcong.myBatis2.pojos.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 id IN 
        <foreach collection="list" open="(" close=")" separator="," item="id" index="i">
            #{id}
        </foreach>
    </select>

foreach包含以下属性：
collection：必填，值为要迭代循环的属性名。这个属性值的情况很多。
item：变量名，值为从迭代对象中取出的每一个值。
index：索引的属性名，在集合数组情况下值为当前索引值，当迭代对象是Map类型是，这个值为KEY。
open、close、separator。

foreach实现批量插入：
如果数据库支持批量插入，就可以通过foreach来实现。批量插入是SQL-92新增的特性，目前支持的数据库有DB2、SQL Server8.2及其以上版本、Mysql、Sqlite3.7.11以上、H2。
 批量插入语法如下：

INSERT INTO tablename (colum-a, [colum-b, ....])
VALUES('value-1a', ['value-1b', ...]),
('value-2a', ['value-2b', ...]),
....

例子：

    <insert id="insertList" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO sys_user(
            user_name, user_password, user_email, user_info, head_img, create_time
        )
        VALUES
       <foreach collection="list" item="user" separator=",">
           (
           #{user.userName}, #{user.userPassword}, #{user.userEmail}, #{user.userInfo}, #{user.headImg, jdbcType= BLOB},
           #{user.createTime, jdbcType= DATE}
           )
       </foreach>
    </insert>

测试代码：

 @Test
    public void testInsertList() {
        SqlSession sqlSession = getSqlSession();
        try {
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

            List<SysUser> sysUserList = new ArrayList<SysUser>();

            for (int i = 0; i < 2; i++) {
                SysUser sysUser = new SysUser();
                sysUser.setUserName("cc");
                sysUser.setUserPassword("111111");
                sysUser.setUserEmail("testck@qq.com");
                sysUserList.add(sysUser);
            }

            int result = userMapper.insertList(sysUserList);

            for (SysUser s :
                    sysUserList) {
                System.out.println(s.getId());
            }

            Assert.assertEquals(2, result);
        } finally {
            //为了不影响其他测试，这里选择回滚
            sqlSession.rollback();
            sqlSession.close();
        }
    }

这里使用了useGeneratedKeys 和 keyProperty 两个属性，可以实现批量插入后返回主键。

foreach实现动态update
这个主要介绍参数是Map时，foreach怎么实现动态update。

bind标签，创建一个变量并将其绑定到上下文中。

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 216,591评论 6赞 501
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,448评论 3赞 392
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 162,823评论 0赞 353
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,204评论 1赞 292
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,228评论 6赞 388
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,190评论 1赞 299
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,078评论 3赞 418
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,923评论 0赞 274
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,334评论 1赞 310
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,550评论 2赞 333
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,727评论 1赞 348
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,428评论 5赞 343
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,022评论 3赞 326
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,672评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,826评论 1赞 269
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,734评论 2赞 368
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,619评论 2赞 354

推荐阅读更多精彩内容

  • Spring Cloud
    Spring Cloud为开发人员提供了快速构建分布式系统中一些常见模式的工具（例如配置管理，服务发现，断路器，智...
    卡卡罗2017阅读 134,652评论 18赞 139
  • Mybatis动态Sql映射标签总结
    、 、 、 :trim标签主要就是标记的作用，可以去掉if条件不满足时多余的and或者or或者，等等，和set标...
    小沙鹰168阅读 1,989评论 0赞 0
  • 强大的动态SQL
    1 动态SQL# 那么，问题来了： 什么是动态SQL? 动态SQL有什么作用？ 传统的使用JDBC的方法，相信大家...
    七寸知架构阅读 18,640评论 2赞 58
  • 【读书笔记】30年后你拿什么养活自己（缺）
    文 | 晨光花开 ①三十年后你拿什么养活自己 这个时候开始想象吧，把未来需要考虑的事情，都考虑进去。 ②如果穿越时...
    晨光花开阅读 303评论 0赞 2
  • 结构思考四特点
    今天我们开始来聊聊结构思考的基本理念，思维训练的前提是将思考过程显性化，思维的过程存在于我们的脑子里，很难被人觉察...
    帅小周阅读 1,021评论 0赞 0