动态sql

动态SQL

• 基于ognl表达式
• 完成多条件查询等逻辑实现
• 用于实现动态SQL的元素主要有
  • if
  • trim
  • where
  • set
  • choose（when、otherwise）
  • foreach

if

判断参数、实现简单的条件判断

使用if标签，只要test里的表达式为true，就会执行if标签里的条件

<select id="getEmpById" parameterType="Emp" resultType="Emp">
  		select * from emp where 1=1
  		<if test="ename!=null and ename!=''">
  			and ename=#{ename}
  		</if>
  		<if test="job!=null and job!=''">
  			and job=#{job}
  		</if>
  </select>

where

简化SQL语句中where条件判断

智能处理and和or：

• 包含的标签中有返回值的话，就会插入一个where
• 标签返回的内容是以and或者or开头的，就会把它剔除掉

<select id="getEmpById" parameterType="Emp" resultType="Emp">
  		select * from emp 
  		<where>
  			<if test="ename!=null and ename!=''">
  				ename=#{ename}
  			</if>
  			<if test="job!=null and job!=''">
  				and job=#{job}
  			</if>
  		</where>
  </select>

set

set 标签可以为 SQL 语句动态的添加 set 关键字，剔除追加到条件末尾多余的逗号。

作用:

• 包含的标签中有返回值的话，就会插入一个set
• where之前的那个,就会把它剔除掉

<update id="updateEmp" parameterType="Emp">
		update emp set ename=#{ename},job=#{job} where empno=#{empno}
		update emp 
		<set>
			<if test="ename!=null and ename!=''">
				ename=#{ename},
			</if>
			<if test="job!=null and job!=''">
				job=#{job}
			</if>
		</set>
			<where>
				<if test="empno!=null">
					empno=#{empno}
				</if>
			</where>
	</update>

trim

• 属性：
  • prefix:要增加的前缀
  • prefixOverrides:要去除的前缀,去掉第一个指定的开头的内容
  • suffix:要增加的后缀
  • suffixOverrides:要去除的后缀
• 更灵活地去除多余的关键字
• 替代where和set

<!-- if+trim 替换 if+where -->
	<select id="getEmpById" parameterType="Emp" resultType="Emp">
		select * from
		<trim prefix="where" prefixOverrides="and|or">
			
			<if test="ename!=null and ename!=''">
  				ename=#{ename}
  			</if>
  			<if test="job!=null and job!=''">
  				and job=#{job}
  			</if>	
		</trim>
	</select>

<!-- if+trim 替换 if+set -->
	<update id="updateEmp" parameterType="Emp">
	update emp 
	<trim prefix="set" suffixOverrides=",">
			<if test="ename!=null and ename!=''">
				ename=#{ename},
			</if>
			<if test="job!=null and job!=''">
				job=#{job}
			</if>
	</trim>
	<trim prefix="where" prefixOverrides="and|or">
				<if test="empno!=null">
					empno=#{empno}
				</if>
	</trim>
	</update>

需要注意的是，prefix 和 suffix 属性是可选的，可以根据需要选择性地添加前缀和后缀。而 prefixOverrides 和 suffixOverrides 属性则提供了更加灵活的方式来根据条件进行字符串修剪操作

insert的动态方法

<sql id="key">
	 	<trim>
	 		<if test="ename!=null and ename!=''">ename,</if>
	 		<if test="job!=null and job!=''">job,</if>
	 		<if test="mgr!=null">mgr,</if>
	 	</trim>
	 </sql>
	 <sql id="values">
	 	<trim>
	 		<if test="ename!=null and ename!=''">#{ename},</if>
	 		<if test="job!=null and job!=''">#{job},</if>
	 		<if test="mgr!=null">#{mgr},</if>
	 	</trim>
	 </sql>
	<insert id="InsertEmp" parameterType="emp">
		insert into emp(<include refid="key"/>) values(<include refid="values"/>)
	</insert>

choose

条件分支：

​ when：用于定义条件成立时执行的代码块。它包含一个 test 属性，用于指定该条件分支的

条件

​ otherwise：用于定义默认的代码块，当所有的 < when > 条件都不成立时，将行< otherwise > 中定义的代码块

<select id="getSuppliersAll" resultType="pojo.Supplier">
   select supCode,supName,supContact,supPhone,supFax,createdTime from t_supplier
   <where>
       <choose>  <!--相当于Switch-->
           <when test="supCode!=null and supCode!=''">  <!--相当于case-->
                and supCode like #{supCode}
           </when>
           <when test="supName!=null and supName!=''">  <!--相当于case-->
                and supName like #{supName}
           </when>
       </choose>
   </where>
</select>

foreach

用来迭代任何可迭代的对象（如数组，集合）。

• collection 属性：
  • mybatis会将数组参数，封装为一个Map集合。
  • 默认：array = 数组
  • 使用@Param注解改变map集合的默认key的名称
• item 属性：本次迭代获取到的元素。
• separator 属性：集合项迭代之间的分隔符。 foreach 标签不会错误地添加多余的分隔符。也就是最后一次迭代不会加分隔符。
• open 属性：该属性值是在拼接SQL语句之前拼接的语句，只会拼接一次
• close 属性：该属性值是在拼接SQL语句拼接后拼接的语句，只会拼接一次

实现批量新增（List集合）

1.接口定义方法

    /**
     * 批量新增
     */
    int insertManySupplier(@Param("Supplier") List<Supplier> Supplier);

2.xml文件中编写SQL

    <!--批量新增-->
    <insert id="insertManySupplier">
        insert into t_supplier(supCode,supName) values
        <foreach collection="Supplier" item="supplier" separator=",">
            (#{supplier.supCode},#{supplier.supName})
        </foreach>
    </insert>

3. 测试类中调用方法

/**
     * 批量新增
     */
    @Test
    public void insertManySupplier() throws Exception {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = factory.openSession();
        List<Supplier> supplierList = new ArrayList<Supplier>();
        Supplier supplier = new Supplier();
        supplier.setSupCode("CD_9969");
        supplier.setSupName("小王");
        Supplier supplier1 = new Supplier();
        supplier1.setSupCode("CD_9970");
        supplier1.setSupName("小红");
        Supplier supplier2 = new Supplier();
        supplier2.setSupCode("CD_9971");
        supplier2.setSupName("小强");
        supplierList.add(supplier);
        supplierList.add(supplier1);
        supplierList.add(supplier2);
        int i = sqlSession.getMapper(supplierMapper.class).insertManySupplier(supplierList);
        System.out.println("新增了---->"+i);
        sqlSession.commit();
    }

实现批量删除（数组）

1.接口定义方法

    /**
     * 批量删除
     */
    int deleteManySupplier(@Param("ids") int [] ids);

2.xml文件中编写SQL

    <!--批量删除-->
    <delete id="deleteManySupplier">
        delete from t_supplier where id in
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>

3.测试类中调用方法

    /**
     * 批量删除
     */
    @Test
    public void deleteManySupplier() throws Exception{
        int ids[]={25,26,27};
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = factory.openSession();
        int i = sqlSession.getMapper(supplierMapper.class).deleteManySupplier(ids);
        System.out.println("删除了---->"+i);
        sqlSession.commit();
    }