Mybatis-动态sql-17

if

根据传进来的参数情况进行查询

<!--• if-->
    <!--• choose (when, otherwise)-->
    <!--• trim (where, set) • foreach-->

<!--查询员工,要求携带了哪个字段查询条件就带上这个字段的值-->
    <!--public List<Employee> getEmpsByConditionIf(Employee employee);-->
    <!--#       test  判断表达式OGNL(apache)   具体使用方法可参见官方文档-->
    <!--#       遇见特殊符号   要用转义字符  例如  "  用  &quot;-->
    <!--#         ognl会进行字符串和数字的转译-->

    <!--查询条件-->
    <select id= "getEmpsByConditionIf"  resultType="com.stayreal.mybatis.Employee" databaseId="mysql" parameterType="com.stayreal.mybatis.Employee">
        select * from tbl_employee  where
        <if  test="id!=null">
            id = #{id}
        </if>
        <if  test="lastName!=null and lastName!='' ">
           and last_name = #{lastName}
        </if>
        <if  test="email!=null and email.trim()!=&quot;&quot;" >
            and email = #{email}
        </if>
        <if  test="gender==0 or gender==1">
            and gender = #{gender}
        </if>
    </select>

choose, when, otherwise

有点像java里面的switch 选择一个查询条件进行查询

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

trim, where, set

多个查询条件，可能会出现 where and …的问题

解决方案：
1. 写where 1=1
2. 使用mybatis的标签，90%的情况下生效，需要
3. 使用mybatis的标签，可定义trim的前缀或后缀

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

<update id="updateAuthorIfNecessary">
  update Author
      <trim prefix="SET" suffixOverrides=",">
      <if test="username != null">username=#{username},</if>
      <if test="password != null">password=#{password},</if>
      <if test="email != null">email=#{email},</if>
      <if test="bio != null">bio=#{bio}</if>
    </trim>
  where id=#{id}
</update>

foreach

注意 可以传入的只能是list或这是array

    public List<Employee> getEmpsByConditionForeach(List<Integer> item);

<!--public List<Employee> getEmpsByConditionForeach(List<Integer> item);-->
    <select id="getEmpsByConditionForeach" resultType="com.stayreal.mybatis.Employee">
        SELECT *
        FROM tbl_employee P
        WHERE ID in
        <foreach item="item" collection="list"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

@Test
    public void testForeach(){
        session = sqlSessionFactory.openSession();

        EmployeeMapperDynamicSql mapper = session.getMapper(EmployeeMapperDynamicSql.class);
        List<Integer> list = new ArrayList<Integer>(2);
        list.add(1);
        list.add(2);
        List<Employee> emps = mapper.getEmpsByConditionForeach(list);
        for(Employee emp:emps){
            System.out.println(emp);
        }
    }
// Employee{id=1, lastName='Jerry', email='jerry@123', gender='0'}
// Employee{id=2, lastName='Jerry', email='jerry@123', gender='0'}