mybatis之动态sql

动态sql是mybatis最强大的语法。

为了简便演示，我将数据库简单化了：

if

我们希望传入什么就以此条件查什么。

比如有id就根据id查，有id和last_name就根据id和last_name查……

因此我们传入的实际上是一个Employee对象，并且返回符合条件的Employee集合。

接口：

package org.mybatis.lecture.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.mybatis.lecture.bean.Employee;

import java.util.List;

@Mapper
public interface EmployeeMapper {

 List<Employee> getEmployeeByConditionIf(Employee employee);
}

sql:

<resultMap id="mappingEmp" type="emp">
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="email" property="email"/>
    </resultMap>

<!--List<Employee> getEmployeeByConditionIf(Employee employee);-->
    <select id="getEmployeeByConditionIf" parameterType="emp" resultMap="mappingEmp">
        select * from employee where
        <if test="id != null">
            id=#{id}
        </if>
        <if test="lastName != null and lastName!=''">
            and last_name like #{lastName}
        </if>
        <if test="email != null and email.trim() != ''">
            and email like #{email}
        </if>
    </select>

如果id不为null，就取出id，如果lastName不为null并且不是空串，就取出lastName，如果email不为null并且去掉首位空格后不是空串的话，就取出email的值。

如果我们只给id：

  @Test
    public void testSelectByConditionIf() throws Exception {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
            Employee employee = new Employee();
            employee.setId(2);
            List<Employee> employeeByConditionIf = employeeMapper.getEmployeeByConditionIf(employee);

            for (Employee employeeIf : employeeByConditionIf) {
                System.out.println(employeeIf);
            }


            sqlSession.commit();
        } finally {
            sqlSession.close();
        }
    }

sql只带id。

如果只有id和email，sql中就会有id和email：

不过如果没有带id而带了last_name呢？

 try {
            EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
            Employee employee = new Employee();
           employee.setLastName("%e%");
            List<Employee> employeeByConditionIf = employeeMapper.getEmployeeByConditionIf(employee);

            for (Employee employeeIf : employeeByConditionIf) {
                System.out.println(employeeIf);
            }


            sqlSession.commit();
        } finally {
            sqlSession.close();
        }

这时的sql语句就错掉了，因为多出来一个and：

if与where

为了解决这个问题，我们要用where标签。

  <!--List<Employee> getEmployeeByConditionIf(Employee employee);-->
    <select id="getEmployeeByConditionIf" parameterType="emp" resultMap="mappingEmp">
        select * from employee
        <where>
            <if test="id != null">
                id=#{id}
            </if>
            <if test="lastName != null and lastName!=''">
                and last_name like #{lastName}
            </if>
            <if test="email != null and email.trim() != ''">
                and email like #{email}
            </if>
        </where>
    </select>

这个where标签会优化sql，去掉前面的那个and。

这就ok了。

不过，如果我们的and是写在后面的怎么办？

  <!--List<Employee> getEmployeeByConditionIf(Employee employee);-->
    <select id="getEmployeeByConditionIf" parameterType="emp" resultMap="mappingEmp">
        select * from employee
        <where>
            <if test="id != null">
                id=#{id} and
            </if>
            <if test="lastName != null and lastName!=''">
                 last_name like #{lastName} and
            </if>
            <if test="email != null and email.trim() != ''">
                 email like #{email}
            </if>
        </where>
    </select>

这就又出错了。

这时候我们可以用trim来去掉多余的and。

    <!--List<Employee> getEmployeeByConditionIf(Employee employee);-->
    <select id="getEmployeeByConditionIf" parameterType="emp" resultMap="mappingEmp">
        select * from employee
        <trim prefix="where" suffixOverrides="and">
            <if test="id != null">
                id=#{id} and
            </if>
            <if test="lastName != null and lastName!=''">
                last_name like #{lastName} and
            </if>
            <if test="email != null and email.trim() != ''">
                email like #{email}
            </if>
        </trim>
    </select>

trim的时候，prefix表示整个trim标签要拼上的前缀，在这里是where。suffixOverrides表示要去掉的多余的东西，这里是and。当然，还有suffix和prefixOverrides。

这样多余的跟在后面的and就会被去掉。

if与set

我们写一个更新员工的方法：

void updateEmpByIf(Employee employee);

和对应的sql：

 <!--void updateEmpByIf(Employee employee);-->
    <update id="updateEmpByIf" parameterType="emp">
        update employee
        set
        <if test="lastName != null">
            last_name=#{lastName},
        </if>
        <if test="email != null">
            email=#{email}
        </if>
        <where>
            id=#{id}
        </where>
    </update>

这实现了传进什么就update什么的逻辑。如果你想更新last_name和email完全没有问题。如果只传了last_name呢？

  EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
            Employee employee = new Employee();
            employee.setId(1);

           employee.setLastName("King");

            employeeMapper.updateEmpByIf(employee);

            sqlSession.commit();

这就会多一个逗号：

解决办法是set标签：

 <!--void updateEmpByIf(Employee employee);-->
    <update id="updateEmpByIf" parameterType="emp">
        update employee
        <set>
        <if test="lastName != null">
            last_name=#{lastName},
        </if>
        <if test="email != null">
            email=#{email}
        </if>
        </set>
        <where>
            id=#{id}
        </where>
    </update>

当然，你也可以用trim。

choose

如果有id，就取id，如果有email，就取email，如果有last_name，就取last_name。但每次只能进一个条件。

这就像switch case的语法。

在mybatis中的语法是choose when。

   select * from employee
        <where>
            <choose>
                <when test="id != null">
                    id=#{id}
                </when>
               <when test="lastName != null and lastName != ''">
                   last_name like #{lastName}
               </when>
               <when test="email != null and email.trim() != ''">
                   email like {email}
               </when>
               <otherwise>
                   1=1
               </otherwise>
            </choose>
        </where>

最后的一个otherwise，也就是如果上面一个条件都没有满足的话，那就查所有的。

我只给lastName：

 EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
            Employee employee = new Employee();
           employee.setLastName("%e%");
            List<Employee> employeeByConditionIf = employeeMapper.getEmployeeByConditionIf(employee);

            for (Employee employeeIf : employeeByConditionIf) {
                System.out.println(employeeIf);
            }

我只给lastName，那就会只进lastName判断的when语句。

如果我同时给了id，lastName，那就只会根据id来查：

因为进了id的when之后，它就不会再往下走了。

如果我什么都不给，那就所有的数据都查出来：

foreach

最后还有foreach，这些OGNL语法和JSTL和EL表达式或者SpEl都有些像。

举个例子：

 List<Employee> getEmpsByConditionForEach(List<Integer> ids);

我们想传进一个id集合，然后查出他们对应的员工。

 <!-- List<Employee> getEmpsByConditionForEach(List<Integer> nums);-->
    <select id="getEmpsByConditionForEach" resultMap="mappingEmp" parameterType="int">
        select * from employee where id in 

        <foreach collection="list" item="item_id" separator="," open="(" close=")">
            #{item_id}
        </foreach>

    </select>

collection可以为list也可以为collection。mybatis会遍历这个collection，每次取出的元素为item。我们这里的意思是每次取出一个item_id，然后拼上一个，。

比如我传1，2，3:

  EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
            List<Integer> ids = Arrays.asList(1,2,3);
            List<Employee> empsByConditionForEach = employeeMapper.getEmpsByConditionForEach(ids);
            for (Employee byConditionForEach : empsByConditionForEach) {

                System.out.println(byConditionForEach);
            }
            sqlSession.commit();

sql语句为：

传几个id就会拼几个。

如果不希望写成collection="list"这种模糊的形式，可以使用@Param注解：

List<Employee> getEmpsByConditionForEach(@Param("ids") List<Integer> ids);

 <foreach collection="ids" item="item_id" separator="," open="(" close=")">
            #{item_id}
        </foreach>