Spring+Mybatis实现动态SQL查询

最新推荐文章于 2023-01-16 16:11:24 发布

Jansens520

最新推荐文章于 2023-01-16 16:11:24 发布

阅读量529

点赞数

分类专栏： Spring MyBatis

本文链接：https://blog.csdn.net/wangdachui1234/article/details/77989406

版权

Spring 同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

MyBatis

1 篇文章 0 订阅

订阅专栏

在报表类应用中，通常需要根据不同的维度去组合复杂的查询条件，然后构造SQL去执行查询。如果只是通过在程序中简单地拼接SQL语句，工作量会非常大，而且代码可能也非常难以维护。Mybatis支持动态SQL查询功能，可以通过配置动态的SQL来简化程序代码中复杂性，不过，这个颇有点XML编程的韵味，通过XML来处理复杂的数据判断、循环的功能，其实也很好理解。

准备工作

下面，我们首先创建一个MySQL示例表，如下所示：

 
   01CREATE TABLE `traffic_info` (
 
   02  `id`  int(11) NOT NULL AUTO_INCREMENT,
 
   03  `domain`  varchar(64) NOT NULL,
 
   04  `traffic_host` varchar(64) NOT NULL,
 
   05  `month` varchar(8) NOT NULL,
 
   06  `monthly_traffic` int(11) DEFAULT '0',
 
   07  `global_traffic_rank` int(11) DEFAULT '0',
 
   08  `native_traffic_rank` int(11) DEFAULT '0',
 
   09  `rank_in_country` varchar(64) DEFAULT NULL,
 
   10  `address`  varchar(200) DEFAULT NULL,
 
   11  `email`  varchar(50) DEFAULT NULL,
 
   12  `traffic_type` int(2) DEFAULT '-1',
 
   13  `status`  int(2) DEFAULT '0',
 
   14  `created_at` date DEFAULT  NULL,
 
   15  `updated_at` timestamp NOT  NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE  CURRENT_TIMESTAMP,
 
   16  `f1`  varchar(255) DEFAULT NULL,
 
   17  `f2`  varchar(255) DEFAULT NULL,
 
   18  `f3`  varchar(255) DEFAULT NULL,
 
   19  PRIMARY KEY (`id`),
 
   20  UNIQUE KEY `idx_traffic` (`domain`,`month`,`traffic_type`)
 
   21) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

这个表用来存储域名的流量信息，流量信息我们从互联网上像Alexa、Compete、Quantcast等提供商获取，通过Crawler抓取的方式实现。我们先从简单的查询做起，只是根据某个字段进行查询，说明如何配置使用Mybatis，这里面也包含如何与Spring进行集成。

配置实践

下面是用到的一些资源的定义：

org.shirdrn.mybatis.TrafficInfo类

该类对应于traffic_info表中一条记录的数据，我们简单取几个字段，如下所示：

 
   01package org.shirdrn.mybatis;
 
   02 
 
   03import java.io.Serializable;
 
   04 
 
   05public class TrafficInfo implements  Serializable {
 
   06     
 
   07     private static final  long serialVersionUID = -8696613205078899594L;
 
   08     int id;
 
   09     String domain;
 
   10     String month;
 
   11     int monthlyTraffic;
 
   12     
 
   13     public int getId() {
 
   14          return id;
 
   15     }
 
   16     public void setId(int id) {
 
   17          this.id = id;
 
   18     }
 
   19     public String getDomain() {
 
   20          return domain;
 
   21     }
 
   22     public void setDomain(String domain) {
 
   23          this.domain = domain;
 
   24     }
 
   25     public String getMonth() {
 
   26          return month;
 
   27     }
 
   28     public void setMonth(String month) {
 
   29          this.month = month;
 
   30     }
 
   31     public int getMonthlyTraffic() {
 
   32          return monthlyTraffic;
 
   33     }
 
   34     public void setMonthlyTraffic(int monthlyTraffic) {
 
   35          this.monthlyTraffic = monthlyTraffic;
 
   36     }
 
   37     
 
   38     @Override
 
   39     public String toString() {
 
   40          return "[id=" + id + ", domain=" + domain + ", month="  +
 
   41                    month + ", monthlyTraffic=" + monthlyTraffic + "]";
 
   42     }
 
   43     
 
   44}

org.shirdrn.mybatis.mapper.TrafficInfoMapper接口类

该类定义了一个与SQL配置进行映射的基本操作，实际的SQL配置有专门的XML文件来进行配置。该接口定义了如下操作：

 
   01package org.shirdrn.mybatis.mapper;
 
   02 
 
   03import java.util.List;
 
   04import java.util.Map;
 
   05 
 
   06import org.shirdrn.mybatis.TrafficInfo;
 
   07 
 
   08public interface TrafficInfoMapper {
 
   09 
 
   10     /**
 
   11     * 根据指定id去查询记录，结果至多只有一条
 
   12     * @param id
 
   13     * @return
 
   14     */
 
   15     TrafficInfo getTrafficInfo(int id);
 
   16     
 
   17     /**
 
   18     * 根据指定的domain参数查询记录，返回一个记录的列表
 
   19     * @param domain
 
   20     * @return
 
   21     */
 
   22     List<TrafficInfo> getTrafficInfoList(String domain);
 
   23     
 
   24     /**
 
   25     * 根据一个 字段domain进行查询，但是存在多个domain的值，传入一个数组
 
   26     * @param domains
 
   27     * @return
 
   28     */
 
   29     List<TrafficInfo> getMultiConditionsList(String[] domains);
 
   30     
 
   31     /**
 
   32     * 根据多个字段进行查询，每个字段可能有多个值，所以参数是Map类型
 
   33     * @param conditions
 
   34     * @return
 
   35     */
 
   36     List<TrafficInfo> getMapConditionsList(Map<String, Object> conditions);
 
   37 
 
   38}

上面接口中定义的操作，一个比一个复杂，我们通过这一系列操作来说明在Mybatis中如果使用各种查询功能。

org/shirdrn/mybatis/mapper/TrafficInfoMapper.xml映射配置文件

这个文件TrafficInfoMapper.xml对应了上面的org.shirdrn.mybatis.mapper.TrafficInfoMapper中定义的操作，通过XML的方式将对应的SQL查询构造出来，这个是Mybatis的核心功能。该文件的内容示例如下所示：

 
   01<?xml version="1.0" encoding="UTF-8" ?> 
 
   02<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 
   03 
 
   04<mapper namespace="org.shirdrn.mybatis.mapper.TrafficInfoMapper">
 
   05     <resultMap type="TrafficInfo" id="tfMap">
 
   06          <id property="id" column="id" />
 
   07          <result property="domain" column="domain" />
 
   08          <result property="month" column="month" />
 
   09          <result property="monthlyTraffic" column="monthlyTraffic" />
 
   10     </resultMap>
 
   11     
 
   12     <select id="getTrafficInfo" resultType="TrafficInfo" parameterType="int">
 
   13          SELECT * FROM domain_db.traffic_info WHERE id = #{id}
 
   14     </select>
 
   15     
 
   16     <select id="getTrafficInfoList" resultType="TrafficInfo" parameterType="string">
 
   17          SELECT * FROM domain_db.traffic_info WHERE domain = #{domain}
 
   18     </select>
 
   19     
 
   20     <select id="getMultiConditionsList" resultMap="tfMap">
 
   21          SELECT * FROM domain_db.traffic_info WHERE domain IN
 
   22          <foreach collection="array" index="index" item="domain" open=" (" separator="," close=")"> 
 
   23             #{domain} 
 
   24         </foreach>
 
   25     </select>
 
   26     
 
   27     <select id="getMapConditionsList" resultMap="tfMap">
 
   28          SELECT * FROM domain_db.traffic_info WHERE domain IN
 
   29          <foreach collection="domains" index="index" item="domain" open=" (" separator="," close=")"> 
 
   30             #{domain} 
 
   31         </foreach>
 
   32         AND status = 0 AND month IN
 
   33         <foreach collection="months" index="index" item="month" open=" (" separator="," close=")"> 
 
   34             #{month} 
 
   35         </foreach>
 
   36     </select>
 
   37 
 
   38</mapper>

如果你之前用过ibatis，应该很熟悉上面这个配置文件。上面：
namespace指定该SQL映射配置文件的Mapper接口类，其中定义了基本的SQL查询操作（以我们给出的例子为例）；
resultMap中的type的值这里是一个别名，当然也可以使用对应的具体类全名（包名+类名），我们会在Mybatis的总的映射配置文件中进行配置，详见后面说明；
select是查询SQL的配置，可以通过不同的元素进行动态构造，如if、foreach等；

Mybatis全局映射配置文件sqlMapConfig.xml

该文件可以指定数据库连接池配置、别名配置、SQL映射配置文件组等内容，这里示例的配置内容如下所示：

 
   01<?xml version="1.0" encoding="UTF-8" ?>
 
   02<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 
   03"http://mybatis.org/dtd/mybatis-3-config.dtd">
 
   04 
 
   05<configuration>
 
   06     <typeAliases>
 
   07          <typeAlias type="org.shirdrn.mybatis.TrafficInfo" alias="TrafficInfo" />
 
   08     </typeAliases>
 
   09     <mappers>
 
   10          <mapper resource="org/shirdrn/mybatis/mapper/TrafficInfoMapper.xml" />
 
   11     </mappers>
 
   12</configuration>

Spring配置文件applicationContext.xml

 
   01<?xml version="1.0" encoding="UTF-8"?>
 
   02<beans xmlns="http://www.springframework.org/schema/beans"
 
   03     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
 
   04     xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
 
   05     xsi:schemaLocation="http://www.springframework.org/schema/beans
 
   06 
 
   07http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
 
   08 
 
   09 
 
   10http://www.springframework.org/schema/context
 
   11 
 
   12 
 
   13http://www.springframework.org/schema/context/spring-context-3.0.xsd
 
   14 
 
   15 
 
   16http://www.springframework.org/schema/aop
 
   17 
 
   18 
 
   19http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
 
   20 
 
   21 
 
   22http://www.springframework.org/schema/tx
 
   23 
 
   24 
 
   25http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">
 
   26 
 
   27     <bean
 
   28          class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
 
   29          <property name="systemPropertiesModeName" value="SYSTEM_PROPERTIES_MODE_OVERRIDE" />
 
   30          <property name="ignoreResourceNotFound" value="true" />
 
   31          <property name="locations">
 
   32               <list>
 
   33                    <value>classpath*:/proxool.properties</value>
 
   34               </list>
 
   35          </property>
 
   36     </bean>
 
   37 
 
   38     <context:component-scan base-package="org.shirdrn.mybatis" />
 
   39 
 
   40     <aop:aspectj-autoproxy proxy-target-class="true" />
 
   41     <aop:config proxy-target-class="true" />
 
   42 
 
   43     <bean id="dataSource" class="org.shirdrn.mybatis.utils.ProxoolDataSource">
 
   44          <property name="driver" value="${jdbc-0.proxool.driver-class}" />
 
   45          <property name="driverUrl" value="${jdbc-0.proxool.driver-url}" />
 
   46          <property name="user" value="${jdbc-0.user}" />
 
   47          <property name="password" value="${jdbc-0.password}" />
 
   48          <property name="alias" value="${jdbc-0.proxool.alias}" />
 
   49          <property name="prototypeCount" value="${jdbc-0.proxool.prototype-count}" />
 
   50          <property name="maximumActiveTime" value="${jdbc-0.proxool.maximum-active-time}" />
 
   51          <property name="maximumConnectionCount" value="${jdbc-0.proxool.maximum-connection-count}" />
 
   52          <property name="minimumConnectionCount" value="${jdbc-0.proxool.minimum-connection-count}" />
 
   53          <property name="simultaneousBuildThrottle"
 
   54               value="${jdbc-0.proxool.simultaneous-build-throttle}" />
 
   55          <property name="verbose" value="${jdbc-0.proxool.verbose}" />
 
   56          <property name="trace" value="${jdbc-0.proxool.trace}" />
 
   57          <property name="houseKeepingTestSql" value="${jdbc-0.proxool.house-keeping-test-sql}" />
 
   58          <property name="houseKeepingSleepTime" value="${jdbc-0.proxool.house-keeping-sleep-time}" />
 
   59          <property name="maximumConnectionLifetime"
 
   60               value="${jdbc-0.proxool.maximum-connection-lifetime}" />
 
   61     </bean>
 
   62 
 
   63     <bean id="dataSource0" class="org.jdbcdslog.ConnectionPoolDataSourceProxy">
 
   64          <property name="targetDSDirect" ref="dataSource" />
 
   65     </bean>
 
   66 
 
   67     <!--  http://mybatis.github.io/spring/getting-started.html -->
 
   68     <!--  http://mybatis.github.io/spring/zh/ -->
 
   69     <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
 
   70          <property name="dataSource" ref="dataSource0" />
 
   71          <property name="configLocation" value="classpath:sqlMapConfig.xml"/>
 
   72     </bean>
 
   73     <bean id="trafficInfoMapper" class="org.mybatis.spring.mapper.MapperFactoryBean">
 
   74          <property name="mapperInterface" value="org.shirdrn.mybatis.mapper.TrafficInfoMapper" />
 
   75          <property name="sqlSessionFactory" ref="sqlSessionFactory" />
 
   76     </bean>
 
   77     <bean id="trafficInfoService" class="org.shirdrn.mybatis.TrafficInfoService">
 
   78          <property name="trafficInfoMapper" ref="trafficInfoMapper" />
 
   79     </bean>
 
   80 
 
   81</beans>

简单说明一下：
dataSource使用的Proxool连接池组件；
sqlSessionFactory是Mybatis的SessionFactory，注入了前面获取到的dataSource，同时指定了Mybatis的总的映射配置文件classpath:sqlMapConfig.xml，属性名为configLocation；
trafficInfoMapper直接由Spring的org.mybatis.spring.mapper.MapperFactoryBean进行代理，需要注入属性mapperInterface（即我们定义的SQL Mapper操作的接口类）和sqlSessionFactory（前面的SessionFactory实例）；
trafficInfoService是我们最终在其中进行调用的服务类，注入了我们定义的SQL Mapper接口类的实例trafficInfoMapper。

org.shirdrn.mybatis.TrafficInfoService服务类

为简单起见，我们就不定义服务接口了，直接在该类中实现，调用SQL Mapper中预定义的SQL查询操作，实现代码如下所示：

 
   01package org.shirdrn.mybatis;
 
   02 
 
   03import java.util.List;
 
   04import java.util.Map;
 
   05 
 
   06import org.shirdrn.mybatis.mapper.TrafficInfoMapper;
 
   07 
 
   08public class TrafficInfoService {
 
   09 
 
   10     private TrafficInfoMapper trafficInfoMapper;
 
   11     
 
   12     public void setTrafficInfoMapper(TrafficInfoMapper trafficInfoMapper) {
 
   13          this.trafficInfoMapper = trafficInfoMapper;
 
   14     }
 
   15 
 
   16     public TrafficInfo getTrafficInfo(int id) {
 
   17          return trafficInfoMapper.getTrafficInfo(id);
 
   18     }
 
   19     
 
   20     public List<TrafficInfo> getTrafficInfoList(String domain) {
 
   21          return trafficInfoMapper.getTrafficInfoList(domain);
 
   22     }
 
   23     
 
   24     public List<TrafficInfo> getMultiConditionsList(String[] domains) {
 
   25          return trafficInfoMapper.getMultiConditionsList(domains);
 
   26     }
 
   27     
 
   28     List<TrafficInfo> getMapConditionsList(Map<String, Object> conditions) {
 
   29          return trafficInfoMapper.getMapConditionsList(conditions);
 
   30     }
 
   31 
 
   32}

按照上面的配置，我们就能够实现从单个字段的查询，到多个字段的组合复杂查询。可以通过与实际编写代码来控制这些逻辑相比较，使用Mybatis可能配置上相对复杂一些，但是或得到的好处是非常多的，如代码可维护性好，看起来配置比较直观，出错的几率会大大减小。实际上，如果熟练的这种配置方式，就会在实际开发过程中，更好地去处理更加复杂的统计查询条件的组合逻辑。

测试用例

测试用例可以检测我们上面的配置是否生效，实现代码：

 
   01package org.shirdrn.mybatis;
 
   02 
 
   03import java.util.Arrays;
 
   04import java.util.HashMap;
 
   05import java.util.List;
 
   06import java.util.Map;
 
   07 
 
   08import org.junit.Test;
 
   09import org.junit.runner.RunWith;
 
   10import org.springframework.beans.factory.annotation.Autowired;
 
   11import org.springframework.test.context.ContextConfiguration;
 
   12import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
 
   13 
 
   14@RunWith(SpringJUnit4ClassRunner.class)
 
   15@ContextConfiguration(locations = { "classpath:/applicationContext*.xml"  })
 
   16public class TestTrafficInfoService {
 
   17 
 
   18     @Autowired
 
   19     private TrafficInfoService trafficInfoService;
 
   20 
 
   21     @Test
 
   22     public void getTraffic() {
 
   23          int id = 1196;
 
   24          TrafficInfo result = trafficInfoService.getTrafficInfo(id);
 
   25          System.out.println(result);
 
   26     }
 
   27     
 
   28     @Test
 
   29     public void getTrafficList() {
 
   30          String domain = "make-the-cut.com";
 
   31          List<TrafficInfo> results = trafficInfoService.getTrafficInfoList(domain);
 
   32          System.out.println(results);
 
   33     }
 
   34     
 
   35     @Test
 
   36     public void getMultiConditionsList() {
 
   37          String[] domains = new String[] {
 
   38                    "make.tv", " make-the-cut.com",  "makgrills.com", "makino.com"
 
   39          };
 
   40          List<TrafficInfo> results = trafficInfoService.getMultiConditionsList(domains);
 
   41          System.out.println(results);
 
   42     }
 
   43     
 
   44     @Test
 
   45     public void getMapConditionsList() {
 
   46          String[] domains = new String[] {
 
   47                    "make.tv", " make-the-cut.com",  "makgrills.com", "makino.com"
 
   48          };
 
   49          List<String> months = Arrays.asList(new String[] {
 
   50                    "201203", "201204", "201205"
 
   51          });
 
   52          Map<String, Object> conditions = new HashMap<String, Object>(2);
 
   53          conditions.put("domains", domains);
 
   54          conditions.put("months", months);
 
   55          List<TrafficInfo> results = trafficInfoService.getMapConditionsList(conditions);
 
   56          System.out.println(results);
 
   57     }
 
   58 
 
   59}

查询进阶

这里，给出一个实际的例子，是对每日报表的一个统计实例，为简单起见，只拿出2张表做LEFT JOIN连接。这个需求，要求查询时可以对每个维度取过得查询条件值，如对于维度osName，值可以使包含Android、IOS，对于另一个维度statDate，可以取最近2天（昨天和前天），等等，并且，这些组合条件可有可无。
对应的Mybatis映射配置文件，内容如下所示：

 
   001<?xml version="1.0" encoding="UTF-8" ?> 
 
   002<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 
   003 
 
   004<mapper namespace="org.shirdrn.data.mappers.DailyAppUserMapper">
 
   005     <resultMap id="dailyAppUserMap" type="DailyAppUser">
 
   006          <id property="id" column="id" />
 
   007          <result property="primaryCategoryId" column="primary_category_id" />
 
   008          <result property="primaryCategoryName" column="primary_category_name" />
 
   009          <result property="secondaryCategoryId" column="secondary_category_id" />
 
   010          <result property="secondaryCategoryName" column="secondary_category_name" />
 
   011          <result property="cooperationMode" column="cooperation_mode" />
 
   012          <result property="merchantId" column="merchant_id" />
 
   013          <result property="merchantName" column="merchant_name" />
 
   014          <result property="osName" column="osName" />
 
   015          <result property="channelId" column="channel_id" />
 
   016          <result property="channelName" column="channel_name" />
 
   017          <result property="version" column="version" />
 
   018          <result property="statDate" column="stat_date" />
 
   019          <result property="newUserOpen" column="new_user_open" />
 
   020          <result property="activeUserOpen" column="active_user_open" />
 
   021          <result property="activeUserPlay" column="active_user_play" />
 
   022          <result property="oldUserOpen" column="old_user_open" />
 
   023          <result property="oldUserPlay" column="old_user_play" />
 
   024          <result property="averageTime" column="average_time" />
 
   025          <result property="newUserAverageTime" column="new_user_average_time" />
 
   026          <result property="oldUserAverageTime" column="old_user_average_time" />
 
   027          <result property="newUserOpen2Retention" column="new_user_open_2retention" />
 
   028          <result property="newUserOpen3Retention" column="new_user_open_3retention" />
 
   029          <result property="newUserOpen7Retention" column="new_user_open_7retention" />
 
   030          <result property="newUserOpen15Retention" column="new_user_open_15retention" />
 
   031          <result property="newUserOpen30Retention" column="new_user_open_30retention" />
 
   032     </resultMap>
 
   033 
 
   034     <select id="getDailyAppUserListByPage" resultMap="dailyAppUserMap">
 
   035          <include refid="getDailyAppUserList"/>
 
   036          LIMIT #{offset}, #{limit}
 
   037     </select>
 
   038      
 
   039     <select id="getDailyAppUserListForReport" resultMap="dailyAppUserMap">
 
   040          <include refid="getDailyAppUserList"/>
 
   041     </select>
 
   042      
 
   043     <sql id="getDailyAppUserList" >
 
   044          SELECT 
 
   045               d.id AS id,
 
   046               d.primary_category_id AS primary_category_id,
 
   047               d.primary_category_name AS primary_category_name,
 
   048               d.secondary_category_id AS secondary_category_id,
 
   049               d.secondary_category_name AS secondary_category_name,
 
   050               d.cooperation_mode AS cooperation_mode,
 
   051               d.merchant_id AS merchant_id,
 
   052               d.osName AS osName,
 
   053               d.channel_id AS channel_id,
 
   054               (CASE WHEN d.channel_name IS NOT NULL THEN d.channel_name ELSE d.channel_id END) AS channel_name,
 
   055               d.version AS version,
 
   056               d.stat_date AS stat_date,
 
   057               d.new_user_open AS new_user_open,
 
   058               d.new_user_play AS new_user_play,
 
   059               d.active_user_open AS active_user_open,
 
   060               d.active_user_play AS active_user_play,
 
   061               d.old_user_open AS old_user_open,
 
   062               d.old_user_play AS old_user_play,
 
   063               d.average_time AS average_time,
 
   064               d.new_user_average_time AS new_user_average_time,
 
   065               d.old_user_average_time AS old_user_average_time,
 
   066               d.new_user_open_2retention AS new_user_open_2retention,
 
   067               d.new_user_open_3retention AS new_user_open_3retention,
 
   068               d.new_user_open_7retention AS new_user_open_7retention,
 
   069               d.new_user_open_15retention AS new_user_open_15retention,
 
   070               d.new_user_open_30retention AS new_user_open_30retention,
 
   071               d.uninstall_cnt AS uninstall_cnt,
 
   072               m.merchant_name AS merchant_name
 
   073          FROM daily_app_user d 
 
   074          LEFT JOIN merchant m ON d.merchant_id=m.id 
 
   075          WHERE d.stat_date = #{statDate}
 
   076          <if test="osNames!=null">
 
   077               AND d.osName IN
 
   078               <foreach collection="osNames" index="index" item="osName" open=" (" separator="," close=")">
 
   079                    #{osName}
 
   080               </foreach>
 
   081          </if>
 
   082          <if test="channelNames!=null">
 
   083               AND
 
   084               <foreach collection="channelNames" index="index" item="channelName" open=" (" separator=" OR " close=")">
 
   085                    (d.channel_name LIKE CONCAT('%', CONCAT(#{channelName}, '%')))
 
   086               </foreach>
 
   087          </if>
 
   088          <if test="versions!=null">
 
   089               AND d.version IN
 
   090               <foreach collection="versions" index="index" item="version" open=" (" separator="," close=")">
 
   091                    #{version}
 
   092               </foreach>
 
   093          </if>
 
   094          <if test="merchantNames!=null">
 
   095               AND
 
   096               <foreach collection="merchantNames" index="index" item="merchantName" open=" (" separator=" OR " close=")">
 
   097                    (m.merchant_name LIKE CONCAT('%', CONCAT(#{%merchantName%}, '%')))
 
   098               </foreach>
 
   099          </if>
 
   100          <if test="primaryCategories!=null">
 
   101               AND d.primary_category_id IN
 
   102               <foreach collection="primaryCategories" index="index" item="primaryCategory" open=" (" separator="," close=")">
 
   103                    #{primaryCategory}
 
   104               </foreach>
 
   105          </if>
 
   106          <if test="secondaryCategories!=null">
 
   107               AND d.secondary_category_id IN
 
   108               <foreach collection="secondaryCategories" index="index" item="secondaryCategory" open=" (" separator="," close=")">
 
   109                    #{secondaryCategory}
 
   110               </foreach>
 
   111          </if>
 
   112          <if test="cooperationModes!=null">
 
   113               AND d.cooperation_model IN
 
   114               <foreach collection="cooperationModes" index="index" item="cooperationMode" open=" (" separator="," close=")">
 
   115                    #{cooperationMode}
 
   116               </foreach>
 
   117          </if>
 
   118     </sql>
 
   119      
 
   120</mapper>

上述映射配置对应的Mapper定义，接口如下所示：

 
   01package org.shirdrn.data.mappers;
 
   02 
 
   03import java.util.List;
 
   04import java.util.Map;
 
   05 
 
   06import org.shirdrn.data.beans.DailyAppUser;
 
   07 
 
   08public class DailyAppUserMapper {
 
   09 
 
   10     List<DailyAppUser> getDailyAppUserListByPage(Map<String, Object> conditions);
 
   11     List<DailyAppUser> getDailyAppUserListForReport(Map<String, Object> conditions);
 
   12}

需要说明的是，如果多个表，一定要设置好Mapper映射配置中每个select元素的resultMap属性，属性值就是前部分的resultMap定义的id。如果只从单个表查询数据，完全可以使用resultType，对应resultMap元素中配置的type属性所指定的别名。
实际上，我们需要通过Map来传递参数，也就是把查询的条件值都收集起来，然后放到Map中，示例如下：

 
   01Map<String, Object> conditions = new HashMap<String, Object>();
 
   02if(osNames != null) {
 
   03     conditions.put(DailyAppUserMapper.KEY_OS_NAMES, osNames);
 
   04}
 
   05if(channelNames != null) {
 
   06     conditions.put(DailyAppUserMapper.KEY_CHANNEL_NAMES, channelNames);
 
   07}
 
   08if(versions != null) {
 
   09     conditions.put(DailyAppUserMapper.KEY_VERSIONS, versions);
 
   10}
 
   11if(merchantNames != null) {
 
   12     conditions.put(DailyAppUserMapper.KEY_MERCHANT_NAMES, merchantNames);
 
   13}
 
   14if(primaryCategories != null) {
 
   15     conditions.put(DailyAppUserMapper.KEY_PRIMARY_CATEGORIES, primaryCategories);
 
   16}
 
   17if(secondaryCategories != null) {
 
   18     conditions.put(DailyAppUserMapper.KEY_SECONDARY_CATEGORIES, secondaryCategories);
 
   19}
 
   20if(cooperationModes != null) {
 
   21     conditions.put(ChannelDayMapper.KEY_COOPERATION_MODES, cooperationModes);
 
   22}