<select id="getListByPagenate" parameterType="PagenateArgs"
  resultType="Visitor">
  select * from (
  <include refid="getListSql" /> <include refid="orderBySql"/>
  ) t <!-- #{}表示参数化输出，${}表示直接输出不进行任何转义操作，自己进行转移 -->
  <if test="pageStart>-1 and pageSize>-1">
    limit #{pageStart}, #{pageSize}
  </if>
</select>
<sql id="getListSql">
  select * from Visitor where
  status>0
</sql>
<sql id="orderBySql">
  order by ${orderFieldStr} ${orderDirectionStr}
</sql>

package david.mybatis.demo;

import java.util.List;

import david.mybatis.model.BasicQueryArgs;
import david.mybatis.model.PagenateArgs;
import david.mybatis.model.Visitor;
import david.mybatis.model.VisitorWithRn;

public interface IVisitorOperation {
  /*
   * 添加访问者
   */
  public int add(Visitor visitor);
  
  /*
   * 删除访问者
   */
  public int delete(int id);
  
  /*
   * 更新访问者
   */
  public int update(Visitor visitor);
  
  /*
   * 查询访问者
   */
  public Visitor query(int id);
  
  /*
   * 查询List
   */
  public List<Visitor> getList();
  
  /*
   * 分页查询List
   */
  public List<Visitor> getListByPagenate(PagenateArgs args);
  
  /*
   * 分页查询List（包含Rownum）
   */
  public List<VisitorWithRn> getListByPagenateWithRn(PagenateArgs args);
  
  /*
   * 基础查询
   */
  public Visitor basicQuery(int id);
  
  /*
   * 动态条件查询(choose,when)实例
   */
  public List<Visitor> getListChooseWhenDemo(BasicQueryArgs args);
  
  /*
   * 动态条件查询(where,if)实例
   */
  public List<Visitor> getListWhereDemo(BasicQueryArgs args);
  
  /*
   * 动态查询(foreach)实例
   */
  public List<Visitor> getListForeachDemo(List<Integer> ids);
  
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="david.mybatis.demo.IVisitorOperation">  
  <resultMap type="Visitor" id="visitorRs">
    <id column="Id" property="id" />
    <result column="Name" property="name" />
    <result column="Email" property="email" />
    <result column="Status" property="status" />
    <result column="CreateTime" property="createTime" />
  </resultMap>
  <sql id="getListSqlConditions">
    select * from Visitor
  </sql>
  <!-- 满足其中一个条件时候用choose when操作 -->
  <select id="getListChooseWhenDemo" resultMap="visitorRs"
    parameterType="BasicQueryArgs">
    <include refid="getListSqlConditions" />
    <where>
      <if test="queryStatus>0">
        status=#{queryStatus}
      </if>
      <choose>
        <when test="queryId!=0">
          and id=#{queryId}
        </when>
        <when test="queryName!=null">
          and name like #{queryName}
        </when>
        <otherwise>
          and createTime>= #{queryTime}
        </otherwise>
      </choose>
    </where>
  </select>
</mapper>

<select id="findActiveBlogLike"
   resultType="Blog">
 SELECT * FROM BLOG
 WHERE
 <if test="state != null">
  state = #{state}
 </if>
 <if test="title != null">
  AND title like #{title}
 </if>
 <if test="author != null and author.name != null">
  AND author_name like #{author.name}
 </if>
</select>

<select id="findActiveBlogLike"
   resultType="Blog">
 SELECT * FROM BLOG
 WHERE
</select>

<select id="findActiveBlogLike"
   resultType="Blog">
  SELECT * FROM BLOG
  WHERE
  AND title like ‘someTitle'
</select>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="david.mybatis.demo.IVisitorOperation">  
  <sql id="getListSqlConditions">
    select * from Visitor
  </sql>
  <!-- 满足条件的都加上去操作 -->
  <select id="getListWhereDemo" resultMap="visitorRs"
    parameterType="BasicQueryArgs">
    <include refid="getListSqlConditions" />
    
    <where> 
      <if test="queryStatus>0"> 
        status>0 
      </if> 
      <if test="queryId>0"> 
        and id=#{queryId} 
      </if> 
      <if test="queryName!=null"> 
        and name like=#{queryName} 
      </if> 
      <if test="queryTime!=null"> 
        and createTime>=#{queryTime} 
      </if>
    </where>
    <!--
    <trim prefix="WHERE" prefixOverrides="AND |OR ">
      <if test="queryStatus>0">
        status>0
      </if>
      <if test="queryId>0">
        and id=#{queryId}
      </if>
      <if test="queryName!=null">
        and name like=#{queryName}
      </if>
      <if test="queryTime!=null">
        and createTime>=#{queryTime}
      </if>
    </trim>
    -->
  </select>
</mapper>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="david.mybatis.demo.IVisitorOperation">  
  <sql id="getListSqlConditions">
    select * from Visitor
  </sql>
  <!-- Foreach循环条件 -->
  <select id="getListForeachDemo" resultMap="visitorRs">
    <include refid="getListSqlConditions"/>
    where status>0 and id in 
    <foreach collection="list" item="item" index="index" open="(" separator="," close=")">
      ${item}
    </foreach>
  </select>
</mapper>

/*
   * 动态查询foreach实例
   */
  public static void getListForeachDemo(List<Integer> ids) {
    SqlSession session = MybatisUtils.getSqlSession();
    IVisitorOperation vOperation = session.getMapper(IVisitorOperation.class);
    List<Visitor> ls = vOperation.getListForeachDemo(ids);
    for (Visitor visitor : ls) {
      System.out.println(visitor);
    }
  }
  
  /*
   * 动态查询where if实例
   */
  public static void getListWhereCondition(int id, String name, Date createTime) {
    name = name == "" ? null : name;
    SqlSession session = MybatisUtils.getSqlSession();
    BasicQueryArgs args = new BasicQueryArgs(id, name, createTime);
    IVisitorOperation vOperation = session.getMapper(IVisitorOperation.class);
    List<Visitor> ls = vOperation.getListWhereDemo(args);
    if (ls.size() == 0)
      System.out.println("查无匹配！");
    else {
      for (Visitor visitor : ls) {
        System.out.println(visitor);
      }
    }
  }

  /*
   * 动态查询choose when实例
   */
  public static void getListChooseWhenDemo(int id, String name, Date createTime) {
    name = name == "" ? null : name;
    SqlSession session = MybatisUtils.getSqlSession();
    BasicQueryArgs args = new BasicQueryArgs(id, name, createTime);
    IVisitorOperation vOperation = session.getMapper(IVisitorOperation.class);
    List<Visitor> ls = vOperation.getListChooseWhenDemo(args);
    if (ls.size() == 0)
      System.out.println("查无匹配！");
    else {
      for (Visitor visitor : ls) {
        System.out.println(visitor);
      }
    }
  }