动态SQL

多条件动态SQL查询

<select id="getByCondition" 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 = #{age}
    </if>
</select>

<select id="getByCondition" resultType="Emp">
    select * from t_emp
    <!--条件都不成立时,不会生成where关键字-->
    <!--where后如果直接跟and,会自动去掉and-->
    <where>
        <if test="empName != null and empName !=''">
            and emp_name = #{empName}
        </if>
        <if test="age != null">
            and age = #{age}
        </if>
    </where>
</select>

<select id="getByCondition" resultType="Emp">
    select * from t_emp
    <!--prefix|suffix:在trim标签内容前面或后面加上指定内容-->
    <!--prefixOverrides|suffixOverrides:在trim标签内容前面或后面去掉指定内容-->
    <trim prefix="where" suffixOverrides="and|or">
        <if test="empName != null and empName != ''">
            emp_name = #{empName} and
        </if>
        <if test="age != null and age != ''">
            age = #{age} or
        </if>
    </trim>
</select>

单条件动态SQL查询

when相当于if，elseif，至少要有一个
otherwise相当于else，最多只有一个

<!--List<Emp> getByCondition(Emp emp);-->
<select id="getByCondition" resultType="Emp">
    select * from t_emp
    <where>
        <choose>
            <!--只会有一个条件被拼接上去,otherwise是都不满足时被拼接-->
            <when test="empName != null and empName != ''">
                emp_name = #{empName}
            </when>
            <when test="age != null">
                age = #{age}
            </when>
            <otherwise>
                did = 1
            </otherwise>
        </choose>
    </where>
</select>

foreach标签

批量删除

<!--int deleteBatch(Integer[] eids);-->
<delete id="deleteBatch">
    delete from t_emp where eid in
    <!--参数是一个数组,mybatis将之封装到map集合中,键名默认是array-->
    <!--open是以什么开始-->
    <foreach collection="array" item="ei" separator="," open="(" close=")">
        #{ei}
    </foreach>
</delete>
<!--delete from t_emp where eid in (?,?,?)-->
<!--另一种写法-->
<delete id="deleteBatch">
    delete from t_emp where
    <foreach collection="array" item="ei" separator="or">
        eid = #{ei}
    </foreach>
</delete>

DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
int result = mapper.deleteBatch(new Integer[]{11,12});
System.out.println(result);

批量添加

<!--int insertBatch(List<Emp> emps);-->
<insert id="insertBatch">
    insert into t_emp values
    <!--参数是List,mybatis将其封装到map集合中,键是collection和list-->
    <foreach collection="list" item="emp" separator=",">
        (null,#{emp.empName},#{emp.age},null)
    </foreach>
</insert>

DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
Emp emp1 = new Emp(null,"a1",12,null);
Emp emp2 = new Emp(null,"a2",12,null);
Emp emp3 = new Emp(null,"a3",12,null);
List<Emp> emps = Arrays.asList(emp1, emp2, emp3);
int result = mapper.insertBatch(emps);
System.out.println(result);

sql标签

sql片段

<sql id="deptColumns">did,dept_name</sql>
<select id="getAll" resultType="Dept">
    select <include refid="deptColumns"></include> from t_dept
</select>

官方文档
update动态SQL