MyBatis动态SQL

发表于 | 分类于
字数统计: 713 | 阅读时长 ≈ 3

动态SQL可以极大的简化拼装SQL的操作。类似于JSTL。

if标签

动态传入查询所需要的条件。

1
2
3
public interface EmployeeMapperDynamicSQL {
    public List<Employee> getEmpByConditionIf(Employee emp);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<mapper namespace="dao.EmployeeMapperDynamicSQL">
   <!-- 查询某些条件没带上可能会出现sql拼接问题
        1.给where后面加上1=1,以后的条件都and xxx
        2.去掉where,使用<where></where>标签将所有查询条件包括在内
            只会去掉前面的多余的and或or-->
    <select id="getEmpByConditionIf" resultType="bean.Employee">
        select * from employee
        <where>
        <if test="id!=null">id=#{id}</if>
        <if test="name!=null and name.trim()!=''">and name like #{name}</if>
        <if test="email!=null and email.trim()!=''">and email=#{email}</if>
        <if test="deptId!=null"> and dept_id=#{deptId}</if>
        <if test="gender==0 or gender==1">gender=#{gender}</if>
        </where>
    </select>
</mapper>

choose标签

1
2
3
4
5
6
7
8
9
10
11
12
<select id="getEmpByConditionChoose" resultType="bean.Employee">
    select * from employee
    <where>
    <!-- 如果带了id就用id查，如果带了name就用name查；只会进入其中一个-->
        <choose>
            <when test="id!=null">id=#{id}</when>
            <when test="name!=null">name like #{name}</when>
            <when test="email!=null"> emial=#{email}</when>
            <otherwise>gender=0</otherwise>
        </choose>
    </where>
</select>

set标签

1
2
3
4
5
6
7
8
9
10
<update id="updateEmp">
    update employee
    <set>
        <if test="name!=null and name.trim()!=''"> name=#{name},</if>
        <if test="email!=null and email.trim()!=''">email=#{email},</if>
        <if test="deptId!=null">dept_id=#{deptId},</if>
        <if test="gender==0 or gender==1">gender=#{gender}</if>
    </set>
    where id=#{id}
</update>

foreach标签

1
2
// 查询员工 id在给定集合中
public List<Employee> getEmpByConditionForeach( @Param("ids") List<Integer> ids);
1
2
3
4
5
6
7
8
9
10
11
12
13
<select id="getEmpByConditionForeach" resultType="bean.Employee">
    select  * from employee where id in
    <!-- collection 指定要遍历的集合  list类型的参数会被封装成map
                    item  将当前遍历出的元素赋值给指定变量
                    separator  每个元素之间的分割符
                    #{变量名}  取出遍历的值
                    open 遍历出所有的结果拼接一个起始字符
                    close 遍历出所有的结果拼接一个结束字符
                    index 遍历list时是索引  遍历map时是key-->
    <foreach collection="ids" item="item_id" separator="," open="(" close=")">
        #{item_id}
    </foreach>
</select>
1
2
3
4
List<Employee> employeeList = mapper.getEmpByConditionForeach(Arrays.asList(5, 6, 7));
for(Employee emp:employeeList){
    System.out.println(emp);
}

foreach批量添加数据

1
2
// 批量添加
public void addEmps(@Param("emps") List<Employee> emps);
1
2
3
4
5
6
7
<insert id="addEmps">
    INSERT INTO employee(name,email,gender,dept_id)
    VALUES
    <foreach collection="emps" item="emp" separator=",">
        (#{emp.name},#{emp.email},#{emp.gender},#{emp.deptId})
    </foreach>
</insert>
1
2
3
4
5
List<Employee> emps = new ArrayList<Employee>();
emps.add(new Employee(null,"wd123","1","wd123@qq.com",1,new Department()));
emps.add(new Employee(null,"lz123","1","lz123@qq.com",2,new Department()));
mapper.addEmps(emps);
sqlSession.commit();