2023-06-06
原文作者:惑边 原文地址:https://blog.csdn.net/my_momo_csdn

动态SQL

  • MyBatis的强大特性之一是它的动态SQL。使用JDBC或其它类似框架,根据不同条件拼接SQL语句是痛苦的。例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号等着用的诸多语法细节。利用动态SQL这一特性可以彻底摆脱这种痛苦,让我们更加关注sql本身的逻辑语义。虽然在以前使用动态SQL并非一件易事,但正是MyBatis 提供了可以被用在任意SQL映射语句中的强大的动态SQL语言得以改进这种情形。动态SQL元素和JSTL或基于类似XML的文本处理器相似。在 MyBatis 之前的版本中,有很多元素
    需要花时间了解。MyBatis3大大精简了元素种类,现在只需学习原来一半的元素便可。MyBatis采用功能强大的基于OGNL的表达式来淘汰其它大部分元素。

一、if语句

1.1 新增语句

  • 新增语句中对入参进行判断。这样当插入的属性没有赋值的话,就会使用默认值,默认是空的,数据库中不会有记录
    <!--if动态标签-->
        <insert id="addPeopleIf" parameterType="com.intellif.mozping.entity.People">
    		insert into tb_people(
            <if test="id != null">
                id,
            </if>
            <if test="name != null">
                name,
            </if>
            <if test="age != null">
                age,
            </if>
            <if test="address != null">
                address,
            </if>
            <if test="edu != null">
                edu
            </if>
    		)
    		values(
            <if test="id != null">
                #{id,jdbcType=INTEGER},
            </if>
            <if test="name != null">
                #{name,jdbcType=VARCHAR},
            </if>
            <if test="age != null">
                #{age,jdbcType=INTEGER},
            </if>
            <if test="address != null">
                #{address,jdbcType=VARCHAR},
            </if>
            <if test="edu != null">
                #{edu,jdbcType=VARCHAR}
            </if>
    		)
    	</insert>

1.2 查询语句

  • 查询语句中对入参进行判断,这里原本有2个条件,经过判断之后可以输入任意一个条件或者都为null。
  • 映射文件
      <!--动态if标签查询-->
        <select id="findByNameAndAddressIf" resultType="com.intellif.mozping.entity.People">
            select *
            from tb_people p
            <where>
                <if test="name != null and name != ''">
                    and p.name = #{name}
                </if>
                <if test="address != address and address != ''">
                    and p.address = #{address}
                </if>
            </where>
        </select>
  • 测试
        @Test
        public void query() {
            SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactoryInstaceByConfig(CONFIG_FILE_PATH).openSession();
            PeopleMapper peopleMapper = sqlSession.getMapper(PeopleMapper.class);
            List<People> byNameAndAddressIf = peopleMapper.findByNameAndAddressIf("Ma yun", null);
            for (People p : byNameAndAddressIf) {
                System.out.println(p);
            }
        }

二、choose + when + otherwise

  • 如果不想应用到所有的条件语句,而只想从中择其一项。针对这种情况,MyBatis 提供了choose 元素,它有点像 Java 中的 switch语句
  • 映射文件
    <!--choose + when + otherwise-->
        <select id="findByNameAndAddressChooseWhenOtherwise" resultType="com.intellif.mozping.entity.People">
            select *
            from tb_people p where
            <choose>
                <when test="name != null and name != ''">
                    p.name = #{name}
                </when>
                <when test="address != null and address != ''">
                    p.address = #{address}
                </when>
                <otherwise>
                    1 = 1
                </otherwise>
            </choose>
        </select>
  • 测试
    @Test
        public void queryChooseWhenOtherwise() {
            SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactoryInstaceByConfig(CONFIG_FILE_PATH).openSession();
            PeopleMapper peopleMapper = sqlSession.getMapper(PeopleMapper.class);
    
            //List<People> byNameAndAddressIf = peopleMapper.findByNameAndAddressChooseWhenOtherwise("Ma yun", "tianjing");
            //List<People> byNameAndAddressIf = peopleMapper.findByNameAndAddressChooseWhenOtherwise(null, "hangzhou");
            List<People> byNameAndAddressIf = peopleMapper.findByNameAndAddressChooseWhenOtherwise(null, null);
            for (People p : byNameAndAddressIf) {
                System.out.println(p);
            }
        }
  • 测试效果:我们在测试程序里面的3个查询,第一个传了name和address,但是会查出name是"Ma yun"的全部记录,和address无关,第二个语句当name为null的时候,会查出address为"hangzhou"的全部记录,如果2个参数都是null,那么会查出全部数据库记录,这就是这三个标签的作用,他可以做一定逻辑上的优先级。

三、where

  • 当我们做where条件的动态拼接时,比如按照之前的使用if,如果我们按照第一种写法,在条件缺失的时候会有问题,第二种则不会有问题。
    第一种的where是写死的,如果if全部为null,那么就没有条件,语法上就是错的,有时候会写1=1,但是那样是可能造成sql注入的,并且也不
    是很好的处理,使用where标签的话,即使条件全部都没有,mybatis也能够正确的处理
  • 映射文件
        <!--第一种:-->
        <select id="findByNameAndAddressIf" resultType="com.intellif.mozping.entity.People">
            select *
            from tb_people p 
            where
                <if test="name != null and name != ''">
                    and p.name = #{name}
                </if>
                <if test="address != address and address != ''">
                    and p.address = #{address}
                </if>
        </select>
        <!--条件都为null时,打印出来的预编译语句是:select * from tb_people p where ,语法会报错-->
         
        <!--第二种:-->
        <select id="findByNameAndAddressIf" resultType="com.intellif.mozping.entity.People">
            select *
            from tb_people p
            <where>
                <if test="name != null and name != ''">
                    and p.name = #{name}
                </if>
                <if test="address != address and address != ''">
                    and p.address = #{address}
                </if>
            </where>
        </select>
        <!--条件都为null时,打印出来的预编译语句是:select * from tb_people p -->

四、set

  • set主要用在更新的场景。
  • 映射文件
        <!--使用set更新-->
        <update id="updateWithSet" parameterType="com.intellif.mozping.entity.People">
            update tb_people
            <set>
                <if test="name!=null"> name=#{name},</if>
                <if test="age!=null"> age=#{age},</if>
                <if test="address!=null"> address=#{address},</if>
                <if test="edu!=null"> edu=#{edu},</if>
            </set>
            where id=#{id};
        </update>
  • 测试
    //测试set标签
        @Test
        public void updateWithSet() {
            SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactoryInstaceByConfig(CONFIG_FILE_PATH).openSession();
            PeopleMapper peopleMapper = sqlSession.getMapper(PeopleMapper.class);
            People people = new People();
            people.setId(11);
            people.setAge(54);
            people.setName("Ma hua teng");
            people.setAddress("shenzhen");
            people.setEdu("Bachelor");
    
            int rowAffected = peopleMapper.updateWithSet(people);
            System.out.println("rowAffected: "+rowAffected);
            //显示提交事务
            sqlSession.commit();
            sqlSession.close();
        }
    打印:
    15:25:31.027 [main] DEBUG c.i.m.dao.PeopleMapper.updateWithSet - ==>  Preparing: update tb_people SET name=?, age=?, address=?, edu=? where id=?; 
    15:25:31.071 [main] DEBUG c.i.m.dao.PeopleMapper.updateWithSet - ==> Parameters: Ma hua teng(String), 54(Integer), shenzhen(String), Bachelor(String), 11(Integer)
    15:25:31.073 [main] DEBUG c.i.m.dao.PeopleMapper.updateWithSet - <==    Updates: 1
    rowAffected: 1

五、trim

  • trim标记是一个格式化的标记,可以完成set或者是where标记的功能,主要包含前缀/后缀的处理,去掉第一个指定内容或者去掉最后一个指定内容
属性 功能
prefix 前缀
prefixOverrides 去掉第一个指定内容
suffix 后缀
suffixoverride 去掉最后一个指定内容
  • 映射文件
     <!--trim标签,-->
        <select id="findByNameAndAddressTrim" resultType="com.intellif.mozping.entity.People">
            select *
            from tb_people p
            <trim prefix="where" prefixOverrides="AND|OR">
                <if test="name != null and name != ''">
                    and p.name = #{name}
                </if>
                <if test="address != address and address != ''">
                    and p.address = #{address}
                </if>
            </trim>
        </select>
  • 代码
         @Test
        public void queryTrim() {
            SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactoryInstaceByConfig(CONFIG_FILE_PATH).openSession();
            PeopleMapper peopleMapper = sqlSession.getMapper(PeopleMapper.class);
            List<People> byNameAndAddressIf = peopleMapper.findByNameAndAddressTrim("Parker", "tianjing");
            for (People p : byNameAndAddressIf) {
                System.out.println(p);
            }
        }
        
        打印:
        15:41:58.869 [main] DEBUG c.i.m.d.P.findByNameAndAddressTrim - ==>  Preparing: select * from tb_people p where p.name = ? 
        15:41:58.889 [main] DEBUG c.i.m.d.P.findByNameAndAddressTrim - ==> Parameters: Parker(String)
        15:41:58.906 [main] DEBUG c.i.m.d.P.findByNameAndAddressTrim - <==      Total: 1
        People(id=2, name=Parker, age=20, address=tianjing, edu=Bachelor)
            
        
        注意:
        prefix="where" 会自动给我们加上where前缀
        prefixOverrides="AND|OR"的作用是去除第一个And或者OR,为什么呢,在这个测试中传了2个条件,因此没有这个的话,是where and name = "Parker" and address = "tianjing" ,sql语法是错误的。
        假如这里连续多个条件,那么每一个条件都有可能不传,不传的那个会被忽略,因此其实并不知道哪一个if里面的and是第一个and,所以无法确定哪一个里面的and不写,所以只能都写了,让框架去去除
        第一个and。
  • 在set赋值更新语句中也是一样,需要将最后面的逗号给去掉,原理和前缀类似
        <trim  suffixOverrides=",">
    		<if test="username!=null">
    			name = #{username},
    		</if>
    		<if test="age != 0">
    			age = #{age},
    		</if>
    	</trim>

六、foreach

  • 用于遍历数组或者集合,比如一个集合中包含很多主键id,要查询这些id对应的数据。
属性 说明
collection collection属性的值有三个分别是list、array、map三种
open 前缀
close 后缀
separator 分隔符,表示迭代时每个元素之间以什么分隔
item 表示在迭代过程中每一个元素的别名
index 用一个变量名表示当前循环的索引位置

6.1 查询

  • 映射配置:
        <!--forEach-->
        <select id="findByIdForEach" resultType="com.intellif.mozping.entity.People">
            select *
            from tb_people  where id  in
            <foreach collection="ids" open="(" close=")" separator="," item="id" >
                #{id}
            </foreach>
        </select>
  • 测试
        //Java接口:
        List<People> findByIdForEach(@Param("ids")List<Integer> ids);
        
        //测试:
         @Test
        public void queryForEach() {
            SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactoryInstaceByConfig(CONFIG_FILE_PATH).openSession();
            PeopleMapper peopleMapper = sqlSession.getMapper(PeopleMapper.class);
            ArrayList<Integer> ids = new ArrayList<>();
            for (int i = 1; i < 10; i++) {
                ids.add(i);
            }
            List<People> peopleList = peopleMapper.findByIdForEach(ids);
            for (People p : peopleList) {
                System.out.println(p);
            }
        }

6.2 插入

  • 插入操作也类似,比如插入一个多个对象,插入一个集合
    <insert id="insertPeopleList">
    	insert into t_people(name,age) values
    	<foreach collection="users" item="user" separator=",">
    		(#{user.name},#{user.age})
    	</foreach>
    </insert>

七、bind

  • bind 元素可以从OGNL表达式中创建一个变量并将其绑定到上下文。说起来比较抽象,看示例。
  • 映射文件
     <!--forEach-->
        <select id="findByBind" resultType="com.intellif.mozping.entity.People">
            <!-- 声明了一个参数queryId,在后面就可以使用了 -->
            <bind name="queryId" value="1"/>
            select * from tb_people where id = ${queryId}
        </select>
  • 代码
        //测试bind
        @Test
        public void queryByBind() {
            SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactoryInstaceByConfig(CONFIG_FILE_PATH).openSession();
            PeopleMapper peopleMapper = sqlSession.getMapper(PeopleMapper.class);
    
            List<People> peopleList = peopleMapper.findByBind();
            for (People p : peopleList) {
                System.out.println(p);
            }
        }
        //这里不知道为什么,我用字符串,按照name查找就报错了,不太清楚OGNL

八、sql

  • sql块是可以重复使用的sql语句块,如下:
        <!--sql-->
        <sql id="baseSql">
            name, age
        </sql>
    
        <select id="findBySql" resultType="com.intellif.mozping.entity.People">
            select
            <include refid="baseSql"/>
            from tb_people
        </select>
  • 相当于定义一个可以重复使用的语句块,减少重复工作。

九、参考

阅读全文