7.平凡之路-动态SQL语句

动态SQL语句是核心之一,这里我们通过几个示例来演示

一多条件查询专题

1.通过恒等式完成动态SQL语句

涉及到if标签

<mapper namespace="com.shxt.model.User">
    <resultMap type="com.shxt.model.User" id="BaseResultMapper">
        <id column="user_id" property="user_id" />
        <result column="user_name" property="user_name" />
        <result column="sex" property="sex" />
        <result column="money" property="money" />
        <result column="birthday" property="birthday" />
    </resultMap>

    <sql id="oa_user_columns">
        user_id,user_name,sex,money,birthday
    </sql>

    <sql id="oa_user_columns_alias">
        ${alias}.user_id,${alias}.user_name,${alias}.sex,${alias}.money,${alias}.birthday
    </sql>
</mapper>

映射文件

    <!-- 1.姓名和性别的条件查询 -->
    <!-- A.通过恒等式完成动态SQL语句 -->
    <select id="if01" parameterType="map" resultMap="BaseResultMapper">
        SELECT
            <include refid="oa_user_columns" />
        FROM
            oa_user
        WHERE 1=1
            <if test="name != null && name != ''">
                AND user_name LIKE CONCAT('%',#{name},'%')
            </if>
    
            <if test="sex != null and sex != ''">
                AND sex=#{sex}
            </if>

    </select>

Java测试代码

    @Test
    public void IF标签01(){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();
            Map<String,Object> query = new HashMap<>();
            query.put("name", "悟");
            query.put("sex", "男");

            List<User> userList =
                    sqlSession.selectList(User.class.getName()+".if01", query);
            System.out.println(userList);


        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

图解说明

恒等式

2.where标签和if标签组合

如果发现标签內有内容,那么会在内容的最前面加入关键字 where
如果有内容,会检查内容的最前面是否含有 AND空格或者 OR空格 ,自动将其抹掉
映射文件

    <!-- B.推荐方式 WHERE标签 -->
    <select id="if02" parameterType="map" resultMap="BaseResultMapper">
        SELECT
            <include refid="oa_user_columns" />
        FROM
            oa_user
        <where>
            <if test="name != null && name != ''">
                AND      user_name LIKE CONCAT('%',#{name},'%')
            </if>
    
            <if test="sex != null and sex != ''">
                AND sex=#{sex}
            </if>
        </where>

    </select>

Java测试代码

    @Test
    public void IF标签02(){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();
            Map<String,Object> query = new HashMap<>();
            query.put("name", "悟");
            query.put("sex", "男");

            List<User> userList =
                    sqlSession.selectList(User.class.getName()+".if02", query);
            System.out.println(userList);


        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

图解说明

where标签

3.trim标签和if标签

<trim
            prefix="当发现有内容的时候,你在内容的最前面想加入什么内容"
            prefixOverrides="当发现有内容的时候,你在内容的最前面想抹掉什么内容"
            suffix="当发现有内容的时候,你在内容的最后面面想加入什么内容"
            suffixOverrides="当发现有内容的时候,你在内容的最后面想抹掉什么内容"
        >
</trim>

映射文件

     <!-- C.trim标签 -->
    <select id="if03" parameterType="map" resultMap="BaseResultMapper">
        SELECT
            <include refid="oa_user_columns" />
        FROM
            oa_user
        <trim prefix="WHERE " prefixOverrides="AND |OR ">
            <if test="name != null && name != ''">
                AND user_name LIKE CONCAT('%',#{name},'%')
            </if>
    
            <if test="sex != null and sex != ''">
                AND sex=#{sex}
            </if>
        </trim>
    </select>

Java测试代码

    @Test
    public void trim标签(){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();
            Map<String,Object> query = new HashMap<>();
            query.put("name", "悟");
            query.put("sex", "男");

            List<User> userList =
                    sqlSession.selectList(User.class.getName()+".if03", query);
            System.out.println(userList);


        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

图解说明

trim标签

二更新操作

1.set标签

当你发现有内容的时候,在内容的最前面加入 set
当你发现有内容的时候,检查内容的最后面是否有逗号"," 如果将其抹掉
映射文件

    <update id="update01" parameterType="com.shxt.model.User">
        UPDATE
            oa_user
        <set>
            <if test="user_name != null and user_name != ''">
                user_name=#{user_name},
            </if>
            <if test="sex != null and sex != ''">
                sex=#{sex},
            </if>
            <if test="money != null">
                money=#{money},
            </if>
            <if test="birthday != null">
                birthday=#{birthday},
            </if>
        </set>
        WHERE
            user_id=#{user_id}
    
    </update>

Java测试代码

    @Test
    public void 更新操作_变更数据库(){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();
            //数据
            User user = new User();
            user.setUser_id(3);
            user.setUser_name("天蓬元帅");
            //日期的转换
            String date = "1998-09-09";
            DateFormat df = new SimpleDateFormat("yyyy-MM-dd");

            user.setBirthday(df.parse(date));

            int row =sqlSession.update(User.class.getName()+".update01", user);
            System.out.println(row);

            //事务的提交
            sqlSession.commit();

        }catch (Exception ex) {
            ex.printStackTrace();
        }finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

图解说明

set标签

2.trim标签完成更新

映射文件

    <update id="update02" parameterType="com.shxt.model.User">
        UPDATE
            oa_user
        <trim prefix="SET " suffixOverrides=",">
            <if test="user_name != null and user_name != ''">
                user_name=#{user_name},
            </if>
            <if test="sex != null and sex != ''">
                sex=#{sex},
            </if>
            <if test="money != null">
                money=#{money},
            </if>
            <if test="birthday != null">
                birthday=#{birthday},
            </if>
       </trim>
        WHERE
            user_id=#{user_id}
    </update>

Java测试代码

    @Test
    public void 更新操作_TRIM标签_变更数据库(){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();
            //数据
            User user = new User();
            user.setUser_id(3);
            user.setUser_name("天蓬元帅123");
            //日期的转换
            String date = "1998-10-09";
            DateFormat df = new SimpleDateFormat("yyyy-MM-dd");

            user.setBirthday(df.parse(date));

            int row =sqlSession.update(User.class.getName()+".update02", user);
            System.out.println(row);

            //事务的提交
            sqlSession.commit();

        }catch (Exception ex) {
            ex.printStackTrace();
        }finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

三 choose 标签简单使用

映射文件

    <select id="choose01" parameterType="map" resultMap="BaseResultMapper">
        SELECT
            <include refid="oa_user_columns"/>
        FROM
            oa_user
        WHERE
            <choose>
                <when test='sex != null and sex=="男"'>
                    money>777
                </when>
                <when test='sex != null and sex=="女"'>
                    money>666
                </when>
                <otherwise>
                    1=1
                </otherwise>
            </choose>
    </select>

Java测试代码

    @Test
    public void choose标签(){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();
            Map<String,Object> query = new HashMap<>();
            query.put("sex", "女123213");

            List<User> userList =
                    sqlSession.selectList(User.class.getName()+".choose01", query);
            System.out.println(userList);


        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

四小于号问题

映射文件

    <select id="less01" parameterType="double" 
        resultMap="BaseResultMapper">
        SELECT
            <include refid="oa_user_columns"/>
        FROM oa_user
        WHERE
            money <= #{money}
    </select>
    <select id="less02" parameterType="double" 
        resultMap="BaseResultMapper">
        SELECT
            <include refid="oa_user_columns"/>
        FROM oa_user
        WHERE
            <!-- 里面不能包含标签 -->
            <![CDATA[
              money <= #{money}
            ]]>
          
    </select>

Java测试代码

    @Test
    public void 小于号的解决问题(){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();

            List<User> userList =
                    sqlSession.selectList(User.class.getName()+".less01", 1.0*800);
            System.out.println(userList);

            userList =
                    sqlSession.selectList(User.class.getName()+".less02",  1.0*600);
            System.out.println(userList);


        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

图解说明

小于号问题解决问题

请参考附录1说明

五动态添加语句

映射文件

    <insert id="add01" parameterType="com.shxt.model.User"
        useGeneratedKeys="true" keyProperty="user_id"
    >
        INSERT INTO oa_user
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="user_name != null and user_name!=''">
                    user_name,
                </if>
                <if test="sex != null and sex!=''">
                    sex,
                </if>
                <if test="money != null">
                    money,
                </if>
                <if test="birthday != null">
                    birthday,
                </if>
            </trim>
            <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
                 <if test="user_name != null and user_name!=''">
                    #{user_name},
                </if>
                <if test="sex != null and sex!=''">
                   #{sex},
                </if>
                <if test="money != null">
                   #{money},
                </if>
                <if test="birthday != null">
                    #{birthday},
                </if>
            </trim>
    </insert>

Java测试代码

    @Test
    public void 动态的添加语句(){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();
            //数据
            User user = new User();
            user.setUser_name("刘备12333333");
            //日期的转换
            String date = "1998-10-09";
            DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            user.setBirthday(df.parse(date));

            user.setMoney(1111.11);

            int row =sqlSession.insert(User.class.getName()+".add01", user);
            System.out.println(row);

            //事务的提交
            sqlSession.commit();

            System.out.println(user);

        }catch (Exception ex) {
            ex.printStackTrace();
        }finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

六 foreach标签

1.数组方式

映射方式

    <!-- 数组删除,如果数组的话,请不要去设置paramterType,让其自动识别 -->
    <delete id="delete01" >
        DELETE FROM
            oa_user
        WHERE user_id in 
        <!-- 
            对数组进行遍历 
            如果你只是传了一个数组或者一个集合
            collection="array|list"
        -->
        <foreach 
            collection="array" item="shxt"
            open="(" close=")" separator=","
        >
            #{shxt}
        </foreach>
    </delete>

Java测试代码

    @Test
    public void 传递数组删除规则(){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();
            int row = sqlSession.delete(User.class.getName()+".delete01", new int[]{5,6});
            //事务的提交
            sqlSession.commit();
            System.out.println(row);
        }catch (Exception ex) {
            ex.printStackTrace();
        }finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

2.List方式

映射文件

     <!-- List集合 -->
    <delete id="delete02" parameterType="list">
        DELETE FROM
            oa_user
        WHERE user_id in 
        <foreach 
            collection="list" item="shxt"
            open="(" close=")" separator=","
        >
            #{shxt}
        </foreach>
    </delete>

Java测试代码

    @Test
    public void 传递集合删除规则(){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();
            List<Integer> tempList = new ArrayList<>();
            tempList.add(8);
            tempList.add(9);
            int row = sqlSession.delete(User.class.getName()+".delete02", tempList);
            //事务的提交
            sqlSession.commit();
            System.out.println(row);
        }catch (Exception ex) {
            ex.printStackTrace();
        }finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

3.Map方式

映射方式

    <delete id="delete03" parameterType="map">
        DELETE FROM
            oa_user
        WHERE user_id in 
        <foreach 
            collection="id_array" item="shxt"
            open="(" close=")" separator=","
        >
            #{shxt}
        </foreach>
    </delete>

Java测试代码

    @Test
    public void 传递Map删除规则(){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();
            List<Integer> tempList = new ArrayList<>();
            tempList.add(7);
            tempList.add(10);

            Map<String, Object> map = new HashMap<String, Object>();
            map.put("id_array", tempList);

            int row = sqlSession.delete(User.class.getName()+".delete03", map);
            //事务的提交
            sqlSession.commit();
            System.out.println(row);
        }catch (Exception ex) {
            ex.printStackTrace();
        }finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

图解方式

个人比较喜欢

4.批量添加

映射文件

    <insert id="add02" parameterType="list">
        INSERT INTO
        oa_user
        VALUES
        <foreach collection="list" item="user" separator=","
        >
        (#{user.user_name},#{user.sex},#{user.money})
        </foreach>
    </insert>

附录1 : MyBatis在xml文件中处理大于号小于号的方法

第一种方法：

用了转义字符把>和<替换掉，然后就没有问题了。

SELECT * FROM test WHERE 1 = 1 AND start_date  <= CURRENT_DATE AND end_date >= CURRENT_DATE

附：XML转义字符

转义图

第二种方法

因为这个是xml格式的，所以不允许出现类似“>”这样的字符，但是都可以使用<![CDATA[ ]]>符号进行说明，将此类符号不进行解析
你的可以写成这个：
mapper文件示例代码

<![CDATA[ when min(starttime)<='12:00' and max(endtime)<='12:00' ]]>

最后编辑于：2017.12.10 03:59:36

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

7.平凡之路-动态SQL语句

一 多条件查询专题

1.通过恒等式完成动态SQL语句

2.where标签和if标签组合

3.trim标签和if标签

二 更新操作

1.set标签

2.trim标签完成更新

三 choose 标签简单使用

四 小于号问题

五 动态添加语句

六 foreach标签

1.数组方式

2.List方式

3.Map方式

4.批量添加

附录1 : MyBatis在xml文件中处理大于号小于号的方法

第一种方法：

第二种方法

推荐阅读更多精彩内容

一多条件查询专题

二更新操作

四小于号问题

五动态添加语句