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

头像
pangsir8983
    阅读 14 分钟
    1

    动态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' ]]>  
    mybatisjavajava-eeorm
    pangsir8983
    162 声望21 粉丝
    « 上一篇
    6.平凡之路-单条件模糊查询
    下一篇 »
    MySQL中group_concat函数

    引用和评论

    推荐阅读
    头像
    胡说-JavaScript函数类型

    pangsir8983阅读 1.7k

    头像
    Jerry和您聊聊Chrome开发者工具

    注销赞 9阅读 4.4k评论 3

    头像
    在Java程序中监听mysql的binlog

    huan1993赞 9阅读 1.7k

    头像
    Bitmap 和 布隆过滤器傻傻分不清？你这不应该啊

    程序员小富赞 7阅读 1.3k

    头像
    Just for fun——迅速写完快速排序

    ufdf赞 3阅读 2.5k

    头像
    Spring 实现 3 种异步流式接口，干掉接口超时烦恼

    程序员小富赞 4阅读 1.5k

    头像
    MySQL 亿级数据平滑迁移实战

    vivo互联网技术赞 2阅读 896

    0 条评论
    得票最新