MyBatis——动态SQL

if

<select/insert/update/delete>
  
  <if test = "判断条件">
    SQL语句片段
  </if>
</select/insert/update/delete>

• 属性

• • test="表达式"

• • • 如果表达式值为true，则将标签中的SQL片段输出到sql语句中

• 原语句中过滤条件后加WHERE 1=1

• • 不必考虑是否有过滤条件，是否需要写WHERE
  • 每一个if标签内容前都加AND,无需考虑是不是第一个条件

简单类型

<select id="selectEmpByNo" parameterType="int" resultType="emp">
    SELECT * FROM t_emp WHERE 1=1
    <if test="emoNo != 0">
        AND empno = #{empNo}
    </if>
</select>

@Test
public void testSelectEmpByNo(){
    SqlSession sqlSession = MyUtil.getSqlSession(true);
    Emp emp = sqlSession.selectOne("com.wry.pojo.Emp.selectEmpByNo",7369);
    System.out.println(emp);
}

引用类型

Map

<select id="selectEmpByMap" parameterType="java.util.Map" resultType="emp">
    SELECT * FROM t_emp WHERE 1=1
    <if test="empNo != null">
        AND empno = #{empNo}
    </if>
    <if test="job != null">
        AND job = #{job}
    </if>
</select>

public void selectEmpByMap(){
    SqlSession sqlSession = MyUtil.getSqlSession(true);
    Map<String,Object> map = new HashMap();
    map.put("empNo",7369);
    map.put("job","CLERK");
    List<Emp> emps = sqlSession.selectList("com.wry.pojo.Emp.selectEmpByMap", map);
    System.out.println(emps);
    //[Emp{empNo = 7369, ename = SMITH, job = CLERK, sal = 800.0, hireDate = 1980-12-17, dept = null}
}

@Test
public void selectEmpByMap(){
    SqlSession sqlSession = MyUtil.getSqlSession(true);
    Map<String,Object> map = new HashMap();
    map.put("job","CLERK");
    List<Emp> emps = sqlSession.selectList("com.wry.pojo.Emp.selectEmpByMap", map);
    System.out.println(emps);
    //所有job属性为CLERK的emp对象
}

POJO类

<select id="selectEmpByPojo" parameterType="emp" resultType="emp">
    SELECT * FROM t_emp WHERE 1=1
    <if test="empNo != null">
        AND empno = #{empNo}
    </if>

    <if test="ename != null">
        AND ename = #{ename}
    </if>

    <if test="job != null">
        AND job = #{job}
    </if>
</select>

@Test
public void selectEmpByPojo(){
    SqlSession sqlSession = MyUtil.getSqlSession(true);
    List<Emp> emps = sqlSession.selectList("com.wry.pojo.Emp.selectEmpByPojo",new Emp(null,null,"CLERK",null,null,null));
    System.out.println(emps);
}

WHERE

• 专门根据数据传递的情况来决定，在SQL语句后方是否出现WHERE命令以及对应的判断条件(仅限AND)
• 特点

• • 如果所有的if标签结果都为false,此时在SQL语句后方将不会出现WHERE命令
  • 对于第一个出现在WHERE后方的条件,WHERE标签自动将条件前方AND撤销掉

<select id="selectEmpByPojo" parameterType="emp" resultType="emp">
    SELECT * FROM t_emp
    <where>
        <if test="empNo != null">
            AND empno = #{empNo}
        </if>

        <if test="ename != null">
            AND ename = #{ename}
        </if>

        <if test="job != null">
            AND job = #{job}
        </if>
    </where>
</select>

@Test
public void selectEmpByPojo(){
    SqlSession sqlSession = MyUtil.getSqlSession(true);
    List<Emp> emps = sqlSession.selectList("com.wry.pojo.Emp.selectEmpByPojo",new Emp(null,null,"CLERK",null,null,null));
    //SELECT * FROM t_emp WHERE job = ?
    System.out.println(emps);
}
@Test
public void selectEmpByPojoNull(){
    SqlSession sqlSession = MyUtil.getSqlSession(true);
    List<Emp> emps = sqlSession.selectList("com.wry.pojo.Emp.selectEmpByPojo",new Emp(null,null,null,null,null,null));
    //Preparing: SELECT * FROM t_emp
    System.out.println(emps);
}

foreach

• 作用

• • 循环遍历输出sqlSession提供List和数组中内容进入到SQL语句
  • 主要用于插入数据

• • • 实例对象List批量insert到数据库

• 属性

• • collection="要遍历的数据类型"

• • • list 遍历List集合
    • array 遍历数组

• • item="循环变量"

• • • 每次循环时将数组或集合中的数据存储到item指定的循环变量

• • open="输出内容"

• • • 设置在第一次循环开始之前，需要输出的内容

• • close="输出内容"

• • • 设置在最后一次循环结束时，需要输出的内容

• • seperator="输出分隔符"

• • • 设置输出分隔符,在第二次及以后的每次循环开始之前输出

批量添加数据到t_emp

<insert id="insertEmps">
    INSERT INTO t_emp(empno,ename,job) VALUES
    <foreach collection="list" item="emp" separator=",">
        (#{emp.empNo},#{emp.ename},#{emp.job})
    </foreach>
</insert>

@Test
public void testInsertEmp(){
    SqlSession sqlSession = MyUtil.getSqlSession(false);
    List emps= new ArrayList();
    emps.add(new Emp(7935,"张三","CLERK",null,null,null));
    emps.add(new Emp(7936,"李四","MANAGER",null,null,null));
    emps.add(new Emp(7937,"王五","ANALYST",null,null,null));
    int count = sqlSession.update("com.wry.pojo.Emp.insertEmps", emps);
    if (count == 3) {
        sqlSession.commit();
        System.out.println("添加成功");
    } else {
        sqlSession.rollback();
        System.out.println("添加失败");
    }
}

从t_emp中批量删除数据

<delete id="deleteEmpsByIds">
    DELETE FROM t_emp WHERE empno in <foreach collection="array" item="empno" open="(" close=")" separator=",">#{empno}</foreach>
</delete>

<delete id="deleteEmpsByIds">
    DELETE FROM t_emp
    <where>
        <foreach collection="array" item="empno" open="empno IN (" close=")" separator=",">
            #{empno}
        </foreach>
    </where>
</delete>

@Test
public void testDeleteEmpsByIds(){
    SqlSession sqlSession = MyUtil.getSqlSession(true);
    Integer[] empNos = {7935,7936,7937};
    int count = sqlSession.update("com.wry.pojo.Emp.deleteEmpsByIds", empNos);
    if (count == 3) {
        sqlSession.commit();
        System.out.println("删除成功");
    } else {
        sqlSession.rollback();
        System.out.println("删除失败");
    }
}

sql标签

• 提高语句复用率

• • 定义语句

• • • <sql id="语句名">SQL语句</sql>

• • 使用语句

• • • <include refid="语句名"/>

<sql id="selectFromEmp">
    SELECT * FROM t_emp
</sql>
<select id="selectEmpByEmpNo" parameterType="int" resultMap="emprs">
    <include refid="selectFromEmp"/>WHERE empno = #{empno}
</select>