Mybatis框架动态sql

1. < if > 元素

在MyBatis中，< if > 是最常用的判断语句，它类似于Java中的if语句，以下是两表的sql语句

 select u.*,r.roleName FROM t_sysuser u, t_sysrole r WHERE u.roleId = r.id
        <if test="roleId !=null" >
            and u.roleId=#{roleId}
        </if>
    <if test="realName!=null and realName!=''">
        AND u.realName LIKE CONCAT('%', #{realName}, '%')
    </if>

测试方法：

public void testGetUserList() throws IOException{
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        String realName="赵";
        Integer roleId=2;
        List<SysUser> list=sqlSession.getMapper(SysUserMapper.class).selectList(realName,roleId);
        System.out.println(list.toString());
        sqlSession.commit();
        sqlSession.close();
    }

2. < where >

  select * from t_supplier
        <where>
            <if test="id != null"> and id = #{id}</if>
            <if test="supName != null">and supName like CONCAT('%', #{supName}, '%')</if>
            <if test="supPhone != null">and supPhone like CONCAT('%', #{supPhone}, '%')</if>
        </where>

3. < choose >,< when >,< otherwise >元素

<select id="getEmpByChoose" parameterType="pojo.SysSupplier" resultType="pojo.SysSupplier">
        SELECT * FROM t_supplier
        <where>
            <choose>
                <when test="supName != null and supName != ''">
                    AND supName = #{supName}
                </when>
                <when test="supPhone != null and supPhone != ''">
                    AND supPhone = #{supPhone}
                </when>
            </choose>
        </where>
    </select>

4. < trim >元素

  <select id="getSuppliersAll" resultType="pojo.SysSupplier">
        SELECT *
        FROM t_supplier
        <trim prefix="WHERE" prefixOverrides="AND|OR" >
            <if test="supCode != null and supCode != ''">
                AND supCode LIKE CONCAT('%', #{supCode}, '%')
            </if>
            <if test="supName != null and supName != ''">
                AND supName LIKE CONCAT('%', #{supName}, '%')
            </if>
            <if test="supPhone != null and supPhone != ''">
                AND supPhone LIKE CONCAT('%', #{supPhone}, '%')
            </if>
        </trim>
    </select>

5. < set >元素

  <update id="updateSupplier" parameterType="pojo.SysSupplier">
        UPDATE t_supplier
        <set>
            <!-- 根据条件更新供应商名称和电话 -->
            <if test="supName != null">
                supName = #{supName},
            </if>
            <if test="supPhone != null">
                supPhone = #{supPhone},
            </if>
        </set>
        WHERE id = #{id}
    </update>

  public void testUpdateSupplier() throws IOException {
        SqlSession sqlSession = MyBatisUtil.openSession();
        try {
            SysSupplierMapper sysSupplierMapper = sqlSession.getMapper(SysSupplierMapper.class);
            SysSupplier supplier = new SysSupplier();
            supplier.setId(3);  // 设置要更新的供应商ID
            supplier.setSupName("新的供应商名称1");
            supplier.setSupPhone("新的供应商电话1");
            sysSupplierMapper.updateSupplier(supplier);
            sqlSession.commit();
        } finally {
            sqlSession.close();
        }
    }

6. < foreach >元素

用于SQL语句执行批量操作

collection: 需做foreach(遍历)的对象，作为入参时，list、array对象时，collection属性值分别默认用"list"、“array"代替，Map对象没有默认的属性值。但是，在作为入参时可以使用@Param(“keyName”)注解来设置自定义collection属性值，设置keyName后，list、array会失效；
item： 集合元素迭代时的别名称，该参数为必选项；
index： 在list、array中，index为元素的序号索引。但是在Map中，index为遍历元素的key值，该参数为可选项；
open： 遍历集合时的开始符号，通常与close=”)“搭配使用。使用场景IN(),values()时，该参数为可选项；
separator： 元素之间的分隔符，类比在IN()的时候，separator=“,”,最终所有遍历的元素将会以设定的（,）逗号符号隔开，该参数为可选项；
close： 遍历集合时的结束符号，通常与open=”("搭配使用，该参数为可选项；

6.1 添加批量数据

 <insert id="addSysSupplier" parameterType="java.util.List">
            insert into t_supplier(supCode,supName,supDesc,supContact,supPhone,supAddress,supFax,createdTime,createdUserId)value
        <foreach item="supplier" collection="list" separator=",">
            (#{supplier.supCode},#{supplier.supName},#{supplier.supDesc},#{supplier.supContact},#{supplier.supPhone},#{supplier.supAddress},#{supplier.supFax},#{supplier.createdTime},#{supplier.createdUserId})
        </foreach>
    </insert>

6.2 批量删除数据

    <delete id="deleteSysSupplierId" parameterType="java.util.List">
        DELETE
        FROM t_supplier
        WHERE t_supplier.id IN
        <foreach collection="list" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

6.3 批量修改数据

<update id="updateMultiple" parameterType="pojo.SysSupplier">
        UPDATE t_supplier
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="supName=case" suffix="end,">
                <foreach collection="list" item="processUnitDuration">
                    WHEN id = #{processUnitDuration.id} THEN #{processUnitDuration.supName}
                </foreach>
            </trim>
            <trim prefix="supPhone=case" suffix="end,">
                <foreach collection="list" item="processUnitDuration">
                    WHEN id = #{processUnitDuration.id} THEN #{processUnitDuration.supPhone}
                </foreach>
            </trim>
        </trim>
        <where>
            <foreach collection="list" separator="or" item="processUnitDuration">
                id = #{processUnitDuration.id}
            </foreach>
        </where>
    </update>

测试类

 public  void testGetEmpByChoose(){
        SqlSession sqlSession = MyBatisUtil.openSession();
        SysSupplierMapper sysSupplierMapper = sqlSession.getMapper(SysSupplierMapper.class);
        SysSupplier emp = new SysSupplier("深圳市喜来客商贸有限公司", "2121212");
        SysSupplier emp = new SysSupplier("深圳市喜来客商", "2323243");
        sysSupplierMapper.getEmpByChoose(emp);
        sqlSession.commit();
    }

7.总结