1.if标签

<select id="getEmpListByParam" resultType="Emp">
   select * from t_emp where 1=1
   <if test="empName!=null and empName!=''">
     and emp_name = #{empName}
   </if>
    <if test="age!=null and age!=''">
        and age = #{age}
    </if>
    <if test="gender!=null and gender!=''">
        and gender = #{gender}
    </if>
</select>

2.where标签往往结合if一起使用

where:

1. 如果有if成立则声称where关键字
2. 去掉前面多余的and,后面多余的不会去掉
3. 如果if都不成立,where不起作用.

   <select id="getEmpListByParam" resultType="Emp">
               select * from t_emp
            <where>
                <if test="empName!=null and empName!=''">
                     emp_name = #{empName}
                </if>
                <if test="age!=null and age!=''">
                    and age = #{age}
                </if>
                <if test="gender!=null and gender!=''">
                    and gender = #{gender}
                </if>
            </where>

    </select>

3.trim标签

• prefix添加前面的关键字
• prefixOverrides去掉sql前面多余的关键字
• suffix添加后面面的关键字
• suffixOverrides去掉sql后面面多余的关键字

<select id="getEmpListByParam" resultType="Emp">
    select * from t_emp
    <trim prefix="where" prefixOverrides="and">
        <if test="empName!=null and empName!=''">
            emp_name = #{empName}
        </if>
        <if test="age!=null and age!=''">
            and age = #{age}
        </if>
        <if test="gender!=null and gender!=''">
            and gender = #{gender}
        </if>
    </trim>

</select>

4.choose when otherwise

• 相当于if..else if...else
• when最少写一次
• otherwise最多写一次
• 内部不用加and自己会生成
• 只会走一个分支

<select id="getEmpListByChoose" resultType="Emp">
    select * from t_emp
        <where>
            <choose>
                 //if
                <when test="empName!=null and empName!=''">
                    emp_name = #{empName}
                </when>
                 //else if
                <when test="age!=null and age!=''">
                    age = #{age}
                </when>
                 //else
                <otherwise>
                    gender = #{gender}
                </otherwise>
            </choose>
        </where>
</select>

5.foreach

1. collection:循环的list
2. item:项,
3. separator:分隔符
4. open :整个循环以啥开始的
5. close :一啥结束
6. index:索引

批量添加

void  batchInsertEmp(@Param("emps") List<Emp> emps);

<insert id="batchInsertEmp">
    insert into t_emp values
  <foreach collection="emps" item="emp" separator=",">
      (null ,#{emp.empName},#{emp.age},#{emp.gender},null )
  </foreach>
</insert>

public void testBatchInsertEmp(){
    SqlSession sqlSession = SqlSessionUtil.getSqlSession();
    EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
    Emp emp = new Emp(null,"小明1",34,"男");
    Emp emp1 = new Emp(null,"小明2",34,"男");
    Emp emp2 = new Emp(null,"小明3",34,"男");
    List<Emp> list = Arrays.asList(emp,emp1,emp2);
   mapper.batchInsertEmp(list);

}

批量删除

void batchDeleteEmpByIds(@Param("ids") Integer [] ids);

public void testBatchDeleteEmp(){
    SqlSession sqlSession = SqlSessionUtil.getSqlSession();
    EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
    Integer [] ids = new Integer []{6,7};
    mapper.batchDeleteEmpByIds(ids);

}

方法1

<delete id="batchDeleteEmpByIds">
    delete from t_emp where emp_id in
(
  <foreach collection="ids" item="id" separator=",">
      #{id}
  </foreach>
)
</delete>

方法2

<delete id="batchDeleteEmpByIds">
        delete from t_emp where emp_id in
      <foreach collection="ids" item="id" separator="," open="(" close=")">
          #{id}
      </foreach>
 </delete>

方法3

<delete id="batchDeleteEmpByIds">
    delete from t_emp where
    <foreach collection="ids" item="id" separator="or">
        emp_id = #{id}
    </foreach>
</delete>

6.sql,include