MyBatis(十一)：Mybatis 动态SQL语句完成多条件查询

之前文章中对in的用法做过讲解：《MyBatis(四)：mybatis中使用in查询时的注意事项》

实际上对于多个参数的用法也是这是注意的：

多参&if判空&List集合判空&in用法

    @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
    @Select(value = {
            "<script>",
            " SELECT  `id`,`title` ",
            " FROM `tb_article` ",
            " WHERE `category_id`=#{article.categoryId} ",
            "   <if test='article.status!=null'>",
            "   AND `status` = #{article.status} ",
            "   </if>",
            "   <if test='typeList!=null and !typeList.isEmpty()'>",
            "       and `article_type` in",
            "        <foreach collection=\"typeList\" index=\"index\" item=\"item\" open=\"(\" separator=\",\" close=\")\">",
            "           #{item} ",
            "        </foreach>",
            "   </if>",
            "</script>"
            })
    @ResultMap(value = {"articleResultMap"})
    List<ArticlePo> queryByCondition(final @Param("article") ArticleModel article, final @Param("typeList") List<Integer> typeList);

1）上边主要对普通参数判断空用法：<if test='article.status!=null'>

2）集合判空的用法：<if test='typeList!=null and !typeList.isEmpty()'>

3）in的用法：<foreach collection=\"typeList\" index=\"index\" item=\"item\" open=\"(\" separator=\",\" close=\")\">"；

4）多参数用法，实际上多个参数如果使用@SqlProvider方式是，在ArticleSqlProvider的类中方法中接收的参数对象为Map<String,Object>,该map集合中包含两个对象：key:article的ArticleModel对象；key:typeList的List<Integer>对象。获取方式：ArticleModel aritlce=(ArticleModel)map.get("aritcle");List<Integer> typeList=(List<Integer>)map.get("typeList");。

Mybatis使用POJO传递参数：

        @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
        @ResultMap("logResult")
        @Select(value={ 
                "<script>",
                "select * from `log` " ,
                "<where>" ,
                "     <if test=\"title!=null and title!=''\">" ,
                "          and `title` like CONCAT('%', #{title}, '%') " , 
                "     </if>" ,
                "     <if test=\"moduleType!=null \">" , 
                "          and `module_type`=#{moduleType} " , 
                "     </if>" , 
                "     <if test=\"operateType!=null \">" , 
                "          and `operate_type`=#{operateType} " , 
                "     </if>" , 
                "</where>",
                "</script>"
                })
        List<Log> getByPojo(Log log);

src/main/resources/mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!-- 引用db.properties配置文件 -->
    <properties resource="jdbc.properties"/>
    <!--配置全局属性-->
    <settings>
        <!-- 打开延迟加载的开关 -->
        <setting name="lazyLoadingEnabled" value="true"/>
        <!-- 将积极加载改为消极加载（即按需加载） -->
        <setting name="aggressiveLazyLoading" value="false"/>
        <!-- 打开全局缓存开关（二级缓存）默认值就是 true -->
        <setting name="cacheEnabled" value="true"/>
        <!--使用jdbc的getGeneratekeys获取自增主键值-->
        <setting name="useGeneratedKeys" value="true"/>
        <!--使用列别名替换别名　　默认true select name as title form table; -->
        <setting name="useColumnLabel" value="true"/>
        <!--开启驼峰命名转换-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!--打印sql日志-->
        <setting name="logImpl" value="STDOUT_LOGGING" />
    </settings>
    <typeAliases>
        <package name="com.dx.test.model"/>
    </typeAliases>
    <!-- 
　　　　元素类型为 "configuration" 的内容必须匹配 "
　　　　(properties?,settings?,typeAliases?,typeHandlers?,objectFactory?,objectWrapperFactory?,reflectorFactory?,
　　　　　　plugins?,environments?,databaseIdProvider?,mappers?)"。
　　 -->
    <typeHandlers>
        <typeHandler handler="org.apache.ibatis.type.EnumOrdinalTypeHandler" javaType="com.dx.test.model.enums.ModuleType"/>
        <typeHandler handler="org.apache.ibatis.type.EnumOrdinalTypeHandler" javaType="com.dx.test.model.enums.OperateType"/>
    </typeHandlers>
    <!-- 对事务的管理和连接池的配置 -->
    <environments default="mysql_jdbc">
        <environment id="mysql_jdbc">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${name}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <!--
    <mappers>
        <mapper resource="resources/mapper/LogMapper.xml"/>
    </mappers>
    -->
    <mappers>
        <mapper class="com.dx.test.dao.LogMapper"></mapper>
    </mappers>
</configuration>

src/main/resources/log.properties

log4j.rootLogger=DEBUG, Console
#Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
log4j.logger.java.sql.ResultSet=INFO
log4j.logger.org.apache=INFO
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

src/main/resources/jdbc.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false
name=root
password=123456

pom.xml

        <!--MyBatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </dependency>
        <!--MySql数据库驱动 -->
        <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.21</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>
        
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>3.8.1</version>
            <scope>test</scope>
        </dependency>

LogMapper.java（Mybatis mapper类）

package com.dx.test.dao; 

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.dx.test.dao.sqlprovider.LogSqlProvider;
import com.dx.test.model.Log;
import com.dx.test.model.enums.ModuleType;
import com.dx.test.model.enums.OperateType;

@Mapper
public interface LogMapper {
    /**
     * 入库日志
     * 
     * @param log 待入库实体
     * @return 影响条数
     */
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.TRUE, useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    @InsertProvider(type = LogSqlProvider.class, method = "insert")
    public int insert(Log log);

    /**
     * 根据文章id,查询日志详情
     * 
     * @param id 日志id
     * @return 返回查询到的日志详情
     */
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
    @Results(id = "logResult", value = { 
            @Result(property = "id", column = "id", id = true),
            @Result(property = "title", column = "title"), 
            @Result(property = "content", column = "content"),
            @Result(property = "moduleType", column = "module_type", javaType = ModuleType.class),
            @Result(property = "operateType", column = "operate_type", javaType = OperateType.class),
            @Result(property = "dataId", column = "data_id"),
            @Result(property = "createUser", column = "create_user"),
            @Result(property = "createUserId", column = "create_user_id"),
            @Result(property = "createTime", column = "create_time")
            })
    @Select({ "select * from `log` where `id`=#{id}" })
    Log getById(@Param("id") Long id);
    
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
    @ResultMap("logResult")
    @Select(value={ 
        "<script>",
        "select * from `log` " ,
        "<where>" ,
        "     <if test=\"title!=null and title!=''\">" ,
        "          and `title` like CONCAT('%', #{title}, '%') " , 
        "     </if>" ,
        "     <if test=\"moduleType!=null \">" , 
        "          and `module_type`=#{moduleType} " , 
        "     </if>" , 
        "     <if test=\"operateType!=null \">" , 
        "          and `operate_type`=#{operateType} " , 
        "     </if>" , 
        " </where>",
        "</script>"
        })
    List<Log> getByPojo(Log log);
    
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
    @ResultMap("logResult")
    @Select(value={ 
            "<script>",
            "select * from `log` " ,
            "<where>" ,
            "     <if test=\"title!=null and title!=''\">" ,
            "          and `title` like CONCAT('%', #{title}, '%') " , 
            "     </if>" ,
            "     <if test=\"moduleType!=null \">" , 
            "          and `module_type`=#{moduleType} " , 
            "     </if>" , 
            "     <if test=\"operateType!=null \">" , 
            "          and `operate_type`=#{operateType} " , 
            "     </if>" , 
            " </where>",
            "</script>"
            })
    List<Log>  getByParameter(@Param("title") String title,@Param("moduleType") ModuleType moduleType,@Param("operateType") OperateType operateType);
    

    @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
    @ResultMap("logResult")
    @Select(value={ 
            "<script>",
            "select * from `log` " ,
            "<where>" ,
            "     <if test=\"title!=null and title!=''\">" ,
            "          and `title` like CONCAT('%', #{title}, '%') " , 
            "     </if>" ,
            "     <if test=\"moduleType!=null \">" , 
            "          and `module_type`=#{moduleType} " , 
            "     </if>" , 
            "     <if test=\"operateType!=null \">" , 
            "          and `operate_type`=#{operateType} " , 
            "     </if>" , 
            " </where>",
            "</script>"
            })
    List<Log> getByMap(Map<String, Object> map);

    @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
    @ResultMap("logResult")
    @Select({ 
        "<script>",
        "select * from `log` " ,
        "<where>" ,
        "    <choose> ",
        "        <when test=\"dataId!=null\">",
        "            and data_id=#{dataId}",
        "        </when>",
        "        <when test=\"id!=null\">",
        "            and id=#{id}",
        "        </when>",
        "        <otherwise>",
        "            and 1=1",
        "        </otherwise>",
        "    </choose>",
        "</where>" ,
        "</script>"})
    List<Log> getList(final Log log);

    @Options(useCache = true, flushCache = Options.FlushCachePolicy.TRUE)
    @Update({ 
        "<script>",
        "update `log` " ,
        "<set>" ,
        "    <if test=\"dataId!=null\">",
        "        `data_id`=#{dataId},",
        "    </if>",
        "    <if test=\"title!=null\">",
        "        `title`=#{title},",
        "    </if>",
        "    <if test=\"content!=null\">",
        "        `content`=#{content} ",
        "    </if>",
        "</set>" ,
        " where id=#{id}",
        "</script>"})
    int update(final Log log);
    
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
    @ResultMap("logResult")
    @Select({ "select * from `log` where `id`<#{log.id}" })
    List<Log> getListWithPager(@Param("log")Log log,@Param("pageNum") int pageNum,@Param("pageSize") int pageSize);
}

LogSqlProvider.java（LogMapper中使用sql代理类）

public class LogSqlProvider {
    /**
     * 生成插入日志SQL
     * @param log 日志实体
     * @return 插入日志SQL
     * */
    public String insert(Log log) {
        return new SQL() {
            {
                INSERT_INTO("log");
                INTO_COLUMNS("title", "module_type", "operate_type","data_id", "content", "create_time","create_user","create_user_id");
                INTO_VALUES("#{title}", "#{moduleType,typeHandler=org.apache.ibatis.type.EnumOrdinalTypeHandler}", "#{operateType,typeHandler=org.apache.ibatis.type.EnumOrdinalTypeHandler}","#{dataId}", "#{content}", "now()","#{createUser}","#{createUserId}");
            }
        }.toString();
    }
}

ModuleType.java(enum)

package com.dx.test.model.enums;

public enum ModuleType {
    Unkown(0),
    /**
     * 文章模块
     */
    Article_Module(1),
    /**
     * 文章分类模块
     **/
    Article_Category_Module(2),
    /**
     * 配置模块
     */
    Settings_Module(3);
    
    private int value;

    ModuleType(int value) {
        this.value = value;
    }

    public int getValue() {
        return this.value;
    }
}

OperateType.java(enum)

package com.dx.test.model.enums;

public enum OperateType {
    /**
     * 如果0未占位，可能会出现错误。
     * */
    Unkown(0),
    /**
     * 新增
     */
    Create(1),
    /**
     * 修改
     */
    Modify(2),
    /**
     * 删除
     */
    Delete(3),
    /**
     * 查看
     */
    View(4),
    /**
     * 作废
     */
    UnUsed(5);

    private int value;

    OperateType(int value) {
        this.value = value;
    }

    public int getValue() {
        return this.value;
    }
}

Log.java(实体类)

package com.dx.test.model;

import java.util.Date;

import com.dx.test.model.enums.ModuleType;
import com.dx.test.model.enums.OperateType;
 
public class Log {
    private Long id; // 自增id
    private String title;// 日志msg
    private ModuleType moduleType;// 日志归属模块
    private OperateType operateType; // 日志操作类型
    private String dataId; // 操作数据id
    private String content; // 日志内容简介
    private Date createTime; // 新增时间
    private String createUser; // 新增人
    private String createUserId; // 新增人id

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public ModuleType getModuleType() {
        return moduleType;
    }

    public void setModuleType(ModuleType moduleType) {
        this.moduleType = moduleType;
    }

    public OperateType getOperateType() {
        return operateType;
    }

    public void setOperateType(OperateType operateType) {
        this.operateType = operateType;
    }

    public String getDataId() {
        return dataId;
    }

    public void setDataId(String dataId) {
        this.dataId = dataId;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public String getCreateUser() {
        return createUser;
    }

    public void setCreateUser(String createUser) {
        this.createUser = createUser;
    }

    public String getCreateUserId() {
        return createUserId;
    }

    public void setCreateUserId(String createUserId) {
        this.createUserId = createUserId;
    }

    @Override
    public String toString() {
        return "Log [id=" + id + ", title=" + title + ", moduleType=" + moduleType + ", operateType=" + operateType
                + ", dataId=" + dataId + ", content=" + content + ", createTime=" + createTime + ", createUser="
                + createUser + ", createUserId=" + createUserId + "]";
    }

}

MybatisTest.java(测试入口类)

package com.dx.test;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import com.dx.test.dao.LogMapper;
import com.dx.test.model.Log;
import com.dx.test.model.enums.ModuleType;
import com.dx.test.model.enums.OperateType;

public class MybatisTest {
    public static void main(String[] args) {
        InputStream config = null;
        try {
            config = Resources.getResourceAsStream("mybatis-config.xml");
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(config);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        LogMapper logMapper = sqlSession.getMapper(LogMapper.class);
        
        // choose：
        Log queryLog= new Log();
        queryLog.setDataId("1");
        List<Log> logByDataIdList=logMapper.getList(queryLog);
        for (Log item : logByDataIdList) {
            System.out.println(item);
        }
        
        System.out.println("==========================================================");
        String[] titleList = new String[] { "test", "test2", "awr", "a", "c", "tes", "ll", "gg", "dd", "22" };
        ModuleType[] moduleTypes = new ModuleType[] { ModuleType.Article_Category_Module, ModuleType.Article_Module,ModuleType.Settings_Module };
        OperateType[] operateTypes = new OperateType[] { OperateType.Create, OperateType.Delete, OperateType.Modify,OperateType.Modify, OperateType.UnUsed };
        for (int i = 0; i < 10; i++) {
            Log waitingInsertLog = new Log();

            waitingInsertLog.setTitle("log " + titleList[i]);
            waitingInsertLog.setContent("test content" + titleList[i]);
            waitingInsertLog.setCreateTime(new Date());
            waitingInsertLog.setCreateUser("test user");
            waitingInsertLog.setCreateUserId("test user id");
            waitingInsertLog.setDataId(String.valueOf(i + 100));
            waitingInsertLog.setModuleType(moduleTypes[i % 3]);
            waitingInsertLog.setOperateType(operateTypes[i % 5]);
            int newLogId = logMapper.insert(waitingInsertLog);
            System.out.println(waitingInsertLog.getId());
        }

        // set: 测试
        System.out.println("=========================================");
        Log waitingInsertLog = new Log();

        waitingInsertLog.setTitle("log");
        waitingInsertLog.setContent("test content");
        waitingInsertLog.setCreateTime(new Date());
        waitingInsertLog.setCreateUser("test user");
        waitingInsertLog.setCreateUserId("test user id");
        waitingInsertLog.setDataId("9999");
        waitingInsertLog.setModuleType(ModuleType.Article_Module);
        waitingInsertLog.setOperateType(OperateType.View);
        int newLogId = logMapper.insert(waitingInsertLog);

        System.out.println("insert result:"+logMapper.getById(waitingInsertLog.getId()));
        
        Log waitingUpdateLodLog=new Log();
        waitingUpdateLodLog.setId(waitingInsertLog.getId());
        waitingUpdateLodLog.setTitle("1111");
        waitingUpdateLodLog.setDataId("10000");
        waitingUpdateLodLog.setContent("test content test....");
        int updateStatus= logMapper.update(waitingUpdateLodLog);

        System.out.println("update result:"+logMapper.getById(waitingUpdateLodLog.getId()));
        
        // where：Pojo Parameter Map 三种传递参数的用法
        System.out.println("=========================================");

        String title = "test";
        ModuleType moduleType = ModuleType.Article_Category_Module;
        OperateType operateType = OperateType.Create;

        Log log = new Log();
        log.setTitle(title);
        log.setModuleType(moduleType);
        log.setOperateType(operateType);
        List<Log> logList = logMapper.getByPojo(log);
        for (Log item : logList) {
            System.out.println(item);
        }
        System.out.println("==========================================================");

        logList = logMapper.getByParameter(title, moduleType, operateType);
        for (Log item : logList) {
            System.out.println(item);
        }
        System.out.println("==========================================================");

        Map<String, Object> parameterMap = new HashMap<String, Object>();
        parameterMap.put("title", title);
        parameterMap.put("moduleType", moduleType);
        parameterMap.put("operateType", operateType);
        logList = logMapper.getByMap(parameterMap);
        for (Log item : logList) {
            System.out.println(item);
        }
        
        sqlSession.commit();
        sqlSession.close();
    }
}

备注：
1）这里moduleType、operateType都是enum类型，在mybatis-config.xml中已经注册typeHandlers：

    <typeHandlers>
        <typeHandler handler="org.apache.ibatis.type.EnumOrdinalTypeHandler" javaType="com.dx.test.model.enums.ModuleType"/>
        <typeHandler handler="org.apache.ibatis.type.EnumOrdinalTypeHandler" javaType="com.dx.test.model.enums.OperateType"/>
    </typeHandlers>

因此，这里完全不需要使用typeHandler、javaType属性

{fieldName,typeHandler=org.apache.ibatis.type.EnumOrdinalTypeHandler,javaType=com.dx.test.model.enums.OperateType}

，当然如果加上这两个属性也不会抛出异常。
2）如果字段属性类型为enum时，不能判定该值是否不为空字符串或者不为字符串0，这两种用法都不正确，都会导致最终抛出异常：比如：

<if test=\"moduleType!=null and moduleType!='' \">
  and `module_type`=#{moduleType}
</if> 

Exception in thread "main" org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.lang.IllegalArgumentException: invalid comparison: com.dx.test.model.enums.ModuleType and java.lang.String
### Cause: java.lang.IllegalArgumentException: invalid comparison: com.dx.test.model.enums.ModuleType and java.lang.String
        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
        at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:139)
        at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:76)
        at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
        at com.sun.proxy.$Proxy13.getByPojo(Unknown Source)
        at com.dx.test.MybatisTest.main(MybatisTest.java:71)

或

<if test=\"moduleType!=null and moduleType!='0' \">
  and `module_type`=#{moduleType}
</if>

Exception in thread "main" org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.lang.NumberFormatException: For input string: "Article_Category_Module"
### Cause: java.lang.NumberFormatException: For input string: "Article_Category_Module"
        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
        at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:139)
        at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:76)
        at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
        at com.sun.proxy.$Proxy13.getByPojo(Unknown Source)
        at com.dx.test.MybatisTest.main(MybatisTest.java:71)

3）上边例子中接收参数并未标注参数名称，如果加上参数别名标注：List<Log> getByPojo(@Param("log") Log log);，这时在<script>中的sql中访问相关属性要访问log对象下属性：

        @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
        @ResultMap("logResult")
        @Select(value={ 
                "<script>",
                "select * from `log` " ,
                "<where>" ,
                "     <if test=\"log.title!=null and log.title!=''\">" ,
                "          and `title` like CONCAT('%', #{log.title}, '%') " , 
                "     </if>" ,
                "     <if test=\"log.moduleType!=null \">" , 
                "          and `module_type`=#{log.moduleType} " , 
                "     </if>" , 
                "     <if test=\"log.operateType!=null \">" , 
                "          and `operate_type`=#{log.operateType} " , 
                "     </if>" , 
                "</where>",
                "</script>"
                })
        List<Log> getByPojo(@Param("log") Log log);

否则会找不到相关属性，抛出异常：

Exception in thread "main" org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: org.apache.ibatis.binding.BindingException: Parameter 'title' not found. Available parameters are [log, param1]
### Cause: org.apache.ibatis.binding.BindingException: Parameter 'title' not found. Available parameters are [log, param1]
        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
        at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:139)
        at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:76)
        at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
        at com.sun.proxy.$Proxy13.getByPojo(Unknown Source)
        at com.dx.test.MybatisTest.main(MybatisTest.java:71)

通过普通多个参数传递

        @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
        @ResultMap("logResult")
        @Select(value={ 
                        "<script>",
                        "select * from `log` " ,
                        "<where>" ,
                        "     <if test=\"title!=null and title!=''\">" ,
                        "          and `title` like CONCAT('%', #{title}, '%') " , 
                        "     </if>" ,
                        "     <if test=\"moduleType!=null \">" , 
                        "          and `module_type`=#{moduleType} " , 
                        "     </if>" , 
                        "     <if test=\"operateType!=null \">" , 
                        "          and `operate_type`=#{operateType} " , 
                        "     </if>" , 
                        "</where>",
                        "</script>"
                        })
        List<Log>  getByParameter(@Param("title") String title,@Param("moduleType") ModuleType moduleType,@Param("operateType") OperateType operateType);

这种方法比较容易理解，但是缺点需要逐个定义相关参数。

通过Map传递参数

        @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
        @ResultMap("logResult")
        @Select(value={ 
                        "<script>",
                        "select * from `log` " ,
                        "<where>" ,
                        "     <if test=\"title!=null and title!=''\">" ,
                        "          and `title` like CONCAT('%', #{title}, '%') " , 
                        "     </if>" ,
                        "     <if test=\"moduleType!=null \">" , 
                        "          and `module_type`=#{moduleType} " , 
                        "     </if>" , 
                        "     <if test=\"operateType!=null \">" , 
                        "          and `operate_type`=#{operateType} " , 
                        "     </if>" , 
                        "</where>",
                        "</script>"
                        })
        List<Log> getByMap(Map<String, Object> map);

备注：
1）这种方案由于传递的也是对象，和传递POJO一样，如果不定义@Param在<script>内部直接方案相关属性即可；
2）当在参数前定义了@Param时，比如：List<Log> getByMap(@Param("log") Map<String, Object> map);,此时访问属性时，必须加上map.前缀。

        @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
        @ResultMap("logResult")
        @Select(value={ 
                        "<script>",
                        "select * from `log` " ,
                        "<where>" ,
                        "     <if test=\"map.title!=null and map.title!=''\">" ,
                        "          and `title` like CONCAT('%', #{map.title}, '%') " , 
                        "     </if>" ,
                        "     <if test=\"map.moduleType!=null \">" , 
                        "          and `module_type`=#{map.moduleType} " , 
                        "     </if>" , 
                        "     <if test=\"map.operateType!=null \">" , 
                        "          and `operate_type`=#{map.operateType} " , 
                        "     </if>" , 
                        "</where>",
                        "</script>"
                        })
        List<Log> getByMap(@Param("map") Map<String, Object> map);

Trim替代Where、Set等

针对上边的用法我们可以把getByMap(Map<String,Object> map)中Script中where使用trim来替代，例如：

    @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
    @ResultMap("logResult")
    @Select(value={ 
            "<script>", 
            "select * from `log` " ,
            "<trim prefix=\"where\" prefixOverrides=\"and |or |abc \">" ,
            "     <if test=\"title!=null and title!=''\">" ,
            "          and `title` like CONCAT('%', #{title}, '%') " , 
            "     </if>" ,
            "     <if test=\"moduleType!=null \">" , 
            "          and `module_type`=#{moduleType} " , 
            "     </if>" , 
            "     <if test=\"operateType!=null \">" , 
            "          and `operate_type`=#{operateType} " , 
            "     </if>" , 
            "</trim>",
            "</script>"
            })
    List<Log> getByMap(Map<String, Object> map);

1）prefixOverrides：前缀覆盖也就是说，where的后面紧跟着的是 and\or\abc，那么这些关键字都会被忽略
2)要注意 | 后面不能有空格，例如： |a 和| a 后面这个a和|之间有空格，会导致忽略失

Choose的用法：

    @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
    @ResultMap("logResult")
    @Select({ 
        "<script>",
        "select * from `log` " ,
        "<where>" ,
        "    <choose> ",
        "        <when test=\"dataId!=null\">",
        "            and data_id=#{dataId}",
        "        </when>",
        "        <when test=\"id!=null\">",
        "            and id=#{id}",
        "        </when>",
        "        <otherwise>",
        "            and 1=1",
        "        </otherwise>",
        "    </choose>",
        "</where>" ,
        "</script>"})
    List<Log> getList(final Log log);

注：choose相当于Java中的switch语句；当第一个when满足时，就只执行第一个when中的条件。当when中的条件都不满足时，就会执行默认的代码块，也就是otherwise中的语句。

特殊用法，如果一个字段if else if else 的用法：

            "   <choose>",
            "    <when test=\"orgCodeType == '10001'\"> and t11.group_code=#{workingOrgCode}</when>",
            "    <when test=\"orgCodeType == '10002'\"> and t11.district_code=#{workingOrgCode}</when>",
            "    <when test=\"orgCodeType == '10003'\"> and t11.plaza_code=#{workingOrgCode}</when>",
            "    <when test=\"orgCodeType == '10013'\"> and t11.center_code=#{workingOrgCode}</when>",
            "    <otherwise></otherwise>",
            "   </choose>",

或者


            "    <if test=\"orgCodeType == '10001'\"> and t11.group_code=#{workingOrgCode}</if>",
            "    <if test=\"orgCodeType == '10002'\"> and t11.district_code=#{workingOrgCode}</if>",
            "    <if test=\"orgCodeType == '10003'\"> and t11.plaza_code=#{workingOrgCode}</if>",
            "    <if test=\"orgCodeType == '10013'\"> and t11.center_code=#{workingOrgCode}</if>",

Set的用法

    @Options(useCache = true, flushCache = Options.FlushCachePolicy.TRUE)
    @Update({ 
        "<script>",
        "update `log` " ,
        "<set>" ,
        "    <if test=\"dataId!=null\">",
        "        `data_id`=#{dataId},",
        "    </if>",
        "    <if test=\"title!=null\">",
        "        `title`=#{title},",
        "    </if>",
        "    <if test=\"content!=null\">",
        "        `content`=#{content} ",
        "    </if>",
        "</set>" ,
        " where id=#{id}",
        "</script>"})
    int update(final Log log);

该set用法也可以使用trim来替代：

    @Options(useCache = true, flushCache = Options.FlushCachePolicy.TRUE)
    @Update({ 
        "<script>",
        "update `log` " ,
        "<trim prefix=\"SET\" suffixOverrides=\", |abc \">" ,
        "    <if test=\"dataId!=null\">",
        "        `data_id`=#{dataId},",
        "    </if>",
        "    <if test=\"title!=null\">",
        "        `title`=#{title},",
        "    </if>",
        "    <if test=\"content!=null\">",
        "        `content`=#{content}, ",
        "    </if>",
        "</trim>" ,
        " where id=#{id}",
        "</script>"})
    int update(final Log log);

使用<trim>定义<set>规则：
1）suffixOverrides=", |abc"，定义了无论是逗号","结尾还是"abc"结尾，都会被程序忽视，上面程序正常运行；
2）文中的abc规则是我添加的，原本只有过滤逗号","。