动态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' ]]>