在报表类应用中，通常需要根据不同的维度去组合复杂的查询条件，然后构造SQL去执行查询。如果只是通过在程序中简单地拼接SQL语句，工作量会非常大，而且代码可能也非常难以维护。Mybatis支持动态SQL查询功能，可以通过配置动态的SQL来简化程序代码中复杂性，不过，这个颇有点XML编程的韵味，通过XML来处理复杂的数据判断、循环的功能，其实也很好理解。
准备工作
下面，我们首先创建一个MySQL示例表，如下所示：
CREATE` `TABLE` ``traffic_info` (`` ```id` ``int``(11) ``NOT` `NULL` `AUTO_INCREMENT,`` ```domain` ``varchar``(64) ``NOT` `NULL``,`` ```traffic_host` ``varchar``(64) ``NOT` `NULL``,`` `````month``` ``varchar``(8) ``NOT` `NULL``,`` ```monthly_traffic` ``int``(11) ``DEFAULT` `'0'``,`` ```global_traffic_rank` ``int``(11) ``DEFAULT` `'0'``,`` ```native_traffic_rank` ``int``(11) ``DEFAULT` `'0'``,`` ```rank_in_country` ``varchar``(64) ``DEFAULT` `NULL``,`` ```address` ``varchar``(200) ``DEFAULT` `NULL``,`` ```email` ``varchar``(50) ``DEFAULT` `NULL``,`` ```traffic_type` ``int``(2) ``DEFAULT` `'-1'``,`` ```status` ``int``(2) ``DEFAULT` `'0'``,`` ```created_at` ``date` `DEFAULT` `NULL``,`` ```updated_at` ``timestamp` `NOT` `NULL` `DEFAULT` `CURRENT_TIMESTAMP` `ON` `UPDATE` `CURRENT_TIMESTAMP``,`` ```f1` ``varchar``(255) ``DEFAULT` `NULL``,`` ```f2` ``varchar``(255) ``DEFAULT` `NULL``,`` ```f3` ``varchar``(255) ``DEFAULT` `NULL``,`` ``PRIMARY` `KEY` `(`id`),`` ``UNIQUE` `KEY` ``idx_traffic` (`domain`,```month```,`traffic_type`)``) ENGINE=MyISAM AUTO_INCREMENT=1 ``DEFAULT` `CHARSET=utf8;
这个表用来存储域名的流量信息，流量信息我们从互联网上像Alexa、Compete、Quantcast等提供商获取，通过Crawler抓取的方式实现。我们先从简单的查询做起，只是根据某个字段进行查询，说明如何配置使用Mybatis，这里面也包含如何与Spring进行集成。
配置实践
下面是用到的一些资源的定义：
- org.shirdrn.mybatis.TrafficInfo类
该类对应于traffic_info表中一条记录的数据，我们简单取几个字段，如下所示：
package` `org.shirdrn.mybatis;` `import` `java.io.Serializable;` `public` `class` `TrafficInfo ``implements` `Serializable {`` ` ` ``private` `static` `final` `long` `serialVersionUID = -8696613205078899594L;`` ``int` `id;`` ``String domain;`` ``String month;`` ``int` `monthlyTraffic;`` ` ` ``public` `int` `getId() {`` ``return` `id;`` ``}`` ``public` `void` `setId(``int` `id) {`` ``this``.id = id;`` ``}`` ``public` `String getDomain() {`` ``return` `domain;`` ``}`` ``public` `void` `setDomain(String domain) {`` ``this``.domain = domain;`` ``}`` ``public` `String getMonth() {`` ``return` `month;`` ``}`` ``public` `void` `setMonth(String month) {`` ``this``.month = month;`` ``}`` ``public` `int` `getMonthlyTraffic() {`` ``return` `monthlyTraffic;`` ``}`` ``public` `void` `setMonthlyTraffic(``int` `monthlyTraffic) {`` ``this``.monthlyTraffic = monthlyTraffic;`` ``}`` ` ` ``@Override`` ``public` `String toString() {`` ``return` `"[id="` `+ id + ``", domain="` `+ domain + ``", month="` `+`` ``month + ``", monthlyTraffic="` `+ monthlyTraffic + ``"]"``;`` ``}`` ` `}
- org.shirdrn.mybatis.mapper.TrafficInfoMapper接口类
该类定义了一个与SQL配置进行映射的基本操作，实际的SQL配置有专门的XML文件来进行配置。该接口定义了如下操作：
package` `org.shirdrn.mybatis.mapper;` `import` `java.util.List;``import` `java.util.Map;` `import` `org.shirdrn.mybatis.TrafficInfo;` `public` `interface` `TrafficInfoMapper {` ` ``/**`` ``* 根据指定id去查询记录，结果至多只有一条`` ``* @param id`` ``* @return`` ``*/`` ``TrafficInfo getTrafficInfo(``int` `id);`` ` ` ``/**`` ``* 根据指定的domain参数查询记录，返回一个记录的列表`` ``* @param domain`` ``* @return`` ``*/`` ``List<TrafficInfo> getTrafficInfoList(String domain);`` ` ` ``/**`` ``* 根据一个 字段domain进行查询，但是存在多个domain的值，传入一个数组`` ``* @param domains`` ``* @return`` ``*/`` ``List<TrafficInfo> getMultiConditionsList(String[] domains);`` ` ` ``/**`` ``* 根据多个字段进行查询，每个字段可能有多个值，所以参数是Map类型`` ``* @param conditions`` ``* @return`` ``*/`` ``List<TrafficInfo> getMapConditionsList(Map<String, Object> conditions);` `}
上面接口中定义的操作，一个比一个复杂，我们通过这一系列操作来说明在Mybatis中如果使用各种查询功能。
- org/shirdrn/mybatis/mapper/TrafficInfoMapper.xml映射配置文件
这个文件TrafficInfoMapper.xml对应了上面的org.shirdrn.mybatis.mapper.TrafficInfoMapper中定义的操作，通过XML的方式将对应的SQL查询构造出来，这个是Mybatis的核心功能。该文件的内容示例如下所示：
<?``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``=``"org.shirdrn.mybatis.mapper.TrafficInfoMapper"``>`` ``<``resultMap` `type``=``"TrafficInfo"` `id``=``"tfMap"``>`` ``<``id` `property``=``"id"` `column``=``"id"` `/>`` ``<``result` `property``=``"domain"` `column``=``"domain"` `/>`` ``<``result` `property``=``"month"` `column``=``"month"` `/>`` ``<``result` `property``=``"monthlyTraffic"` `column``=``"monthlyTraffic"` `/>`` ``</``resultMap``>`` ` ` ``<``select` `id``=``"getTrafficInfo"` `resultType``=``"TrafficInfo"` `parameterType``=``"int"``>`` ``SELECT * FROM domain_db.traffic_info WHERE id = #{id}`` ``</``select``>`` ` ` ``<``select` `id``=``"getTrafficInfoList"` `resultType``=``"TrafficInfo"` `parameterType``=``"string"``>`` ``SELECT * FROM domain_db.traffic_info WHERE domain = #{domain}`` ``</``select``>`` ` ` ``<``select` `id``=``"getMultiConditionsList"` `resultMap``=``"tfMap"``>`` ``SELECT * FROM domain_db.traffic_info WHERE domain IN`` ``<``foreach` `collection``=``"array"` `index``=``"index"` `item``=``"domain"` `open``=``" ("` `separator``=``","` `close``=``")"``> `` ``#{domain} `` ``</``foreach``>`` ``</``select``>`` ` ` ``<``select` `id``=``"getMapConditionsList"` `resultMap``=``"tfMap"``>`` ``SELECT * FROM domain_db.traffic_info WHERE domain IN`` ``<``foreach` `collection``=``"domains"` `index``=``"index"` `item``=``"domain"` `open``=``" ("` `separator``=``","` `close``=``")"``> `` ``#{domain} `` ``</``foreach``>`` ``AND status = 0 AND month IN`` ``<``foreach` `collection``=``"months"` `index``=``"index"` `item``=``"month"` `open``=``" ("` `separator``=``","` `close``=``")"``> `` ``#{month} `` ``</``foreach``>`` ``</``select``>` `</``mapper``>
如果你之前用过ibatis，应该很熟悉上面这个配置文件。上面： namespace指定该SQL映射配置文件的Mapper接口类，其中定义了基本的SQL查询操作（以我们给出的例子为例）； resultMap中的type的值这里是一个别名，当然也可以使用对应的具体类全名（包名+类名），我们会在Mybatis的总的映射配置文件中进行配置，详见后面说明； select是查询SQL的配置，可以通过不同的元素进行动态构造，如if、foreach等；
- Mybatis全局映射配置文件sqlMapConfig.xml
该文件可以指定数据库连接池配置、别名配置、SQL映射配置文件组等内容，这里示例的配置内容如下所示：
<?``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``>`` ``<``typeAliases``>`` ``<``typeAlias` `type``=``"org.shirdrn.mybatis.TrafficInfo"` `alias``=``"TrafficInfo"` `/>`` ``</``typeAliases``>`` ``<``mappers``>`` ``<``mapper` `resource``=``"org/shirdrn/mybatis/mapper/TrafficInfoMapper.xml"` `/>`` ``</``mappers``>``</``configuration``>
- Spring配置文件applicationContext.xml
<?``xml` `version``=``"1.0"` `encoding``=``"UTF-8"``?>``<``beans` `xmlns``=``"http://www.springframework.org/schema/beans"`` ``xmlns:xsi``=``"http://www.w3.org/2001/XMLSchema-instance"` `xmlns:context``=``"http://www.springframework.org/schema/context"`` ``xmlns:aop``=``"http://www.springframework.org/schema/aop"` `xmlns:tx``=``"http://www.springframework.org/schema/tx"`` ``xsi:schemaLocation="http://www.springframework.org/schema/beans` `http://www.springframework.org/schema/beans/spring-beans-3.0.xsd` `http://www.springframework.org/schema/context` `http://www.springframework.org/schema/context/spring-context-3.0.xsd` `http://www.springframework.org/schema/aop` `http://www.springframework.org/schema/aop/spring-aop-3.0.xsd` `http://www.springframework.org/schema/tx` `http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">` ` ``<``bean`` ``class``=``"org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"``>`` ``<``property` `name``=``"systemPropertiesModeName"` `value``=``"SYSTEM_PROPERTIES_MODE_OVERRIDE"` `/>`` ``<``property` `name``=``"ignoreResourceNotFound"` `value``=``"true"` `/>`` ``<``property` `name``=``"locations"``>`` ``<``list``>`` ``<``value``>classpath*:/proxool.properties</``value``>`` ``</``list``>`` ``</``property``>`` ``</``bean``>` ` ``<``context:component-scan` `base-package``=``"org.shirdrn.mybatis"` `/>` ` ``<``aop:aspectj-autoproxy` `proxy-target-class``=``"true"` `/>`` ``<``aop:config` `proxy-target-class``=``"true"` `/>` ` ``<``bean` `id``=``"dataSource"` `class``=``"org.shirdrn.mybatis.utils.ProxoolDataSource"``>`` ``<``property` `name``=``"driver"` `value``=``"${jdbc-0.proxool.driver-class}"` `/>`` ``<``property` `name``=``"driverUrl"` `value``=``"${jdbc-0.proxool.driver-url}"` `/>`` ``<``property` `name``=``"user"` `value``=``"${jdbc-0.user}"` `/>`` ``<``property` `name``=``"password"` `value``=``"${jdbc-0.password}"` `/>`` ``<``property` `name``=``"alias"` `value``=``"${jdbc-0.proxool.alias}"` `/>`` ``<``property` `name``=``"prototypeCount"` `value``=``"${jdbc-0.proxool.prototype-count}"` `/>`` ``<``property` `name``=``"maximumActiveTime"` `value``=``"${jdbc-0.proxool.maximum-active-time}"` `/>`` ``<``property` `name``=``"maximumConnectionCount"` `value``=``"${jdbc-0.proxool.maximum-connection-count}"` `/>`` ``<``property` `name``=``"minimumConnectionCount"` `value``=``"${jdbc-0.proxool.minimum-connection-count}"` `/>`` ``<``property` `name``=``"simultaneousBuildThrottle"`` ``value``=``"${jdbc-0.proxool.simultaneous-build-throttle}"` `/>`` ``<``property` `name``=``"verbose"` `value``=``"${jdbc-0.proxool.verbose}"` `/>`` ``<``property` `name``=``"trace"` `value``=``"${jdbc-0.proxool.trace}"` `/>`` ``<``property` `name``=``"houseKeepingTestSql"` `value``=``"${jdbc-0.proxool.house-keeping-test-sql}"` `/>`` ``<``property` `name``=``"houseKeepingSleepTime"` `value``=``"${jdbc-0.proxool.house-keeping-sleep-time}"` `/>`` ``<``property` `name``=``"maximumConnectionLifetime"`` ``value``=``"${jdbc-0.proxool.maximum-connection-lifetime}"` `/>`` ``</``bean``>` ` ``<``bean` `id``=``"dataSource0"` `class``=``"org.jdbcdslog.ConnectionPoolDataSourceProxy"``>`` ``<``property` `name``=``"targetDSDirect"` `ref``=``"dataSource"` `/>`` ``</``bean``>` ` ``<!-- http://mybatis.github.io/spring/getting-started.html -->`` ``<!-- http://mybatis.github.io/spring/zh/ -->`` ``<``bean` `id``=``"sqlSessionFactory"` `class``=``"org.mybatis.spring.SqlSessionFactoryBean"``>`` ``<``property` `name``=``"dataSource"` `ref``=``"dataSource0"` `/>`` ``<``property` `name``=``"configLocation"` `value``=``"classpath:sqlMapConfig.xml"``/>`` ``</``bean``>`` ``<``bean` `id``=``"trafficInfoMapper"` `class``=``"org.mybatis.spring.mapper.MapperFactoryBean"``>`` ``<``property` `name``=``"mapperInterface"` `value``=``"org.shirdrn.mybatis.mapper.TrafficInfoMapper"` `/>`` ``<``property` `name``=``"sqlSessionFactory"` `ref``=``"sqlSessionFactory"` `/>`` ``</``bean``>`` ``<``bean` `id``=``"trafficInfoService"` `class``=``"org.shirdrn.mybatis.TrafficInfoService"``>`` ``<``property` `name``=``"trafficInfoMapper"` `ref``=``"trafficInfoMapper"` `/>`` ``</``bean``>` `</``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查询操作，实现代码如下所示：
package` `org.shirdrn.mybatis;` `import` `java.util.List;``import` `java.util.Map;` `import` `org.shirdrn.mybatis.mapper.TrafficInfoMapper;` `public` `class` `TrafficInfoService {` ` ``private` `TrafficInfoMapper trafficInfoMapper;`` ` ` ``public` `void` `setTrafficInfoMapper(TrafficInfoMapper trafficInfoMapper) {`` ``this``.trafficInfoMapper = trafficInfoMapper;`` ``}` ` ``public` `TrafficInfo getTrafficInfo(``int` `id) {`` ``return` `trafficInfoMapper.getTrafficInfo(id);`` ``}`` ` ` ``public` `List<TrafficInfo> getTrafficInfoList(String domain) {`` ``return` `trafficInfoMapper.getTrafficInfoList(domain);`` ``}`` ` ` ``public` `List<TrafficInfo> getMultiConditionsList(String[] domains) {`` ``return` `trafficInfoMapper.getMultiConditionsList(domains);`` ``}`` ` ` ``List<TrafficInfo> getMapConditionsList(Map<String, Object> conditions) {`` ``return` `trafficInfoMapper.getMapConditionsList(conditions);`` ``}` `}
按照上面的配置，我们就能够实现从单个字段的查询，到多个字段的组合复杂查询。可以通过与实际编写代码来控制这些逻辑相比较，使用Mybatis可能配置上相对复杂一些，但是或得到的好处是非常多的，如代码可维护性好，看起来配置比较直观，出错的几率会大大减小。实际上，如果熟练的这种配置方式，就会在实际开发过程中，更好地去处理更加复杂的统计查询条件的组合逻辑。
测试用例
测试用例可以检测我们上面的配置是否生效，实现代码：
package` `org.shirdrn.mybatis;` `import` `java.util.Arrays;``import` `java.util.HashMap;``import` `java.util.List;``import` `java.util.Map;` `import` `org.junit.Test;``import` `org.junit.runner.RunWith;``import` `org.springframework.beans.factory.annotation.Autowired;``import` `org.springframework.test.context.ContextConfiguration;``import` `org.springframework.test.context.junit4.SpringJUnit4ClassRunner;` `@RunWith``(SpringJUnit4ClassRunner.``class``)``@ContextConfiguration``(locations = { ``"classpath:/applicationContext*.xml"` `})``public` `class` `TestTrafficInfoService {` ` ``@Autowired`` ``private` `TrafficInfoService trafficInfoService;` ` ``@Test`` ``public` `void` `getTraffic() {`` ``int` `id = ``1196``;`` ``TrafficInfo result = trafficInfoService.getTrafficInfo(id);`` ``System.out.println(result);`` ``}`` ` ` ``@Test`` ``public` `void` `getTrafficList() {`` ``String domain = ``"make-the-cut.com"``;`` ``List<TrafficInfo> results = trafficInfoService.getTrafficInfoList(domain);`` ``System.out.println(results);`` ``}`` ` ` ``@Test`` ``public` `void` `getMultiConditionsList() {`` ``String[] domains = ``new` `String[] {`` ``"make.tv"``, ``" make-the-cut.com"``, ``"makgrills.com"``, ``"makino.com"`` ``};`` ``List<TrafficInfo> results = trafficInfoService.getMultiConditionsList(domains);`` ``System.out.println(results);`` ``}`` ` ` ``@Test`` ``public` `void` `getMapConditionsList() {`` ``String[] domains = ``new` `String[] {`` ``"make.tv"``, ``" make-the-cut.com"``, ``"makgrills.com"``, ``"makino.com"`` ``};`` ``List<String> months = Arrays.asList(``new` `String[] {`` ``"201203"``, ``"201204"``, ``"201205"`` ``});`` ``Map<String, Object> conditions = ``new` `HashMap<String, Object>(``2``);`` ``conditions.put(``"domains"``, domains);`` ``conditions.put(``"months"``, months);`` ``List<TrafficInfo> results = trafficInfoService.getMapConditionsList(conditions);`` ``System.out.println(results);`` ``}` `}
查询进阶
这里，给出一个实际的例子，是对每日报表的一个统计实例，为简单起见，只拿出2张表做LEFT JOIN连接。这个需求，要求查询时可以对每个维度取过得查询条件值，如对于维度osName，值可以使包含Android、IOS，对于另一个维度statDate，可以取最近2天（昨天和前天），等等，并且，这些组合条件可有可无。 对应的Mybatis映射配置文件，内容如下所示：
<?``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``=``"org.shirdrn.data.mappers.DailyAppUserMapper"``>`` ``<``resultMap` `id``=``"dailyAppUserMap"` `type``=``"DailyAppUser"``>`` ``<``id` `property``=``"id"` `column``=``"id"` `/>`` ``<``result` `property``=``"primaryCategoryId"` `column``=``"primary_category_id"` `/>`` ``<``result` `property``=``"primaryCategoryName"` `column``=``"primary_category_name"` `/>`` ``<``result` `property``=``"secondaryCategoryId"` `column``=``"secondary_category_id"` `/>`` ``<``result` `property``=``"secondaryCategoryName"` `column``=``"secondary_category_name"` `/>`` ``<``result` `property``=``"cooperationMode"` `column``=``"cooperation_mode"` `/>`` ``<``result` `property``=``"merchantId"` `column``=``"merchant_id"` `/>`` ``<``result` `property``=``"merchantName"` `column``=``"merchant_name"` `/>`` ``<``result` `property``=``"osName"` `column``=``"osName"` `/>`` ``<``result` `property``=``"channelId"` `column``=``"channel_id"` `/>`` ``<``result` `property``=``"channelName"` `column``=``"channel_name"` `/>`` ``<``result` `property``=``"version"` `column``=``"version"` `/>`` ``<``result` `property``=``"statDate"` `column``=``"stat_date"` `/>`` ``<``result` `property``=``"newUserOpen"` `column``=``"new_user_open"` `/>`` ``<``result` `property``=``"activeUserOpen"` `column``=``"active_user_open"` `/>`` ``<``result` `property``=``"activeUserPlay"` `column``=``"active_user_play"` `/>`` ``<``result` `property``=``"oldUserOpen"` `column``=``"old_user_open"` `/>`` ``<``result` `property``=``"oldUserPlay"` `column``=``"old_user_play"` `/>`` ``<``result` `property``=``"averageTime"` `column``=``"average_time"` `/>`` ``<``result` `property``=``"newUserAverageTime"` `column``=``"new_user_average_time"` `/>`` ``<``result` `property``=``"oldUserAverageTime"` `column``=``"old_user_average_time"` `/>`` ``<``result` `property``=``"newUserOpen2Retention"` `column``=``"new_user_open_2retention"` `/>`` ``<``result` `property``=``"newUserOpen3Retention"` `column``=``"new_user_open_3retention"` `/>`` ``<``result` `property``=``"newUserOpen7Retention"` `column``=``"new_user_open_7retention"` `/>`` ``<``result` `property``=``"newUserOpen15Retention"` `column``=``"new_user_open_15retention"` `/>`` ``<``result` `property``=``"newUserOpen30Retention"` `column``=``"new_user_open_30retention"` `/>`` ``</``resultMap``>` ` ``<``select` `id``=``"getDailyAppUserListByPage"` `resultMap``=``"dailyAppUserMap"``>`` ``<``include` `refid``=``"getDailyAppUserList"``/>`` ``LIMIT #{offset}, #{limit}`` ``</``select``>`` ` ` ``<``select` `id``=``"getDailyAppUserListForReport"` `resultMap``=``"dailyAppUserMap"``>`` ``<``include` `refid``=``"getDailyAppUserList"``/>`` ``</``select``>`` ` ` ``<``sql` `id``=``"getDailyAppUserList"` `>`` ``SELECT `` ``d.id AS id,`` ``d.primary_category_id AS primary_category_id,`` ``d.primary_category_name AS primary_category_name,`` ``d.secondary_category_id AS secondary_category_id,`` ``d.secondary_category_name AS secondary_category_name,`` ``d.cooperation_mode AS cooperation_mode,`` ``d.merchant_id AS merchant_id,`` ``d.osName AS osName,`` ``d.channel_id AS channel_id,`` ``(CASE WHEN d.channel_name IS NOT NULL THEN d.channel_name ELSE d.channel_id END) AS channel_name,`` ``d.version AS version,`` ``d.stat_date AS stat_date,`` ``d.new_user_open AS new_user_open,`` ``d.new_user_play AS new_user_play,`` ``d.active_user_open AS active_user_open,`` ``d.active_user_play AS active_user_play,`` ``d.old_user_open AS old_user_open,`` ``d.old_user_play AS old_user_play,`` ``d.average_time AS average_time,`` ``d.new_user_average_time AS new_user_average_time,`` ``d.old_user_average_time AS old_user_average_time,`` ``d.new_user_open_2retention AS new_user_open_2retention,`` ``d.new_user_open_3retention AS new_user_open_3retention,`` ``d.new_user_open_7retention AS new_user_open_7retention,`` ``d.new_user_open_15retention AS new_user_open_15retention,`` ``d.new_user_open_30retention AS new_user_open_30retention,`` ``d.uninstall_cnt AS uninstall_cnt,`` ``m.merchant_name AS merchant_name`` ``FROM daily_app_user d `` ``LEFT JOIN merchant m ON d.merchant_id=m.id `` ``WHERE d.stat_date = #{statDate}`` ``<``if` `test``=``"osNames!=null"``>`` ``AND d.osName IN`` ``<``foreach` `collection``=``"osNames"` `index``=``"index"` `item``=``"osName"` `open``=``" ("` `separator``=``","` `close``=``")"``>`` ``#{osName}`` ``</``foreach``>`` ``</``if``>`` ``<``if` `test``=``"channelNames!=null"``>`` ``AND`` ``<``foreach` `collection``=``"channelNames"` `index``=``"index"` `item``=``"channelName"` `open``=``" ("` `separator``=``" OR "` `close``=``")"``>`` ``(d.channel_name LIKE CONCAT('%', CONCAT(#{channelName}, '%')))`` ``</``foreach``>`` ``</``if``>`` ``<``if` `test``=``"versions!=null"``>`` ``AND d.version IN`` ``<``foreach` `collection``=``"versions"` `index``=``"index"` `item``=``"version"` `open``=``" ("` `separator``=``","` `close``=``")"``>`` ``#{version}`` ``</``foreach``>`` ``</``if``>`` ``<``if` `test``=``"merchantNames!=null"``>`` ``AND`` ``<``foreach` `collection``=``"merchantNames"` `index``=``"index"` `item``=``"merchantName"` `open``=``" ("` `separator``=``" OR "` `close``=``")"``>`` ``(m.merchant_name LIKE CONCAT('%', CONCAT(#{%merchantName%}, '%')))`` ``</``foreach``>`` ``</``if``>`` ``<``if` `test``=``"primaryCategories!=null"``>`` ``AND d.primary_category_id IN`` ``<``foreach` `collection``=``"primaryCategories"` `index``=``"index"` `item``=``"primaryCategory"` `open``=``" ("` `separator``=``","` `close``=``")"``>`` ``#{primaryCategory}`` ``</``foreach``>`` ``</``if``>`` ``<``if` `test``=``"secondaryCategories!=null"``>`` ``AND d.secondary_category_id IN`` ``<``foreach` `collection``=``"secondaryCategories"` `index``=``"index"` `item``=``"secondaryCategory"` `open``=``" ("` `separator``=``","` `close``=``")"``>`` ``#{secondaryCategory}`` ``</``foreach``>`` ``</``if``>`` ``<``if` `test``=``"cooperationModes!=null"``>`` ``AND d.cooperation_model IN`` ``<``foreach` `collection``=``"cooperationModes"` `index``=``"index"` `item``=``"cooperationMode"` `open``=``" ("` `separator``=``","` `close``=``")"``>`` ``#{cooperationMode}`` ``</``foreach``>`` ``</``if``>`` ``</``sql``>`` ` `</``mapper``>
上述映射配置对应的Mapper定义，接口如下所示：
package` `org.shirdrn.data.mappers;` `import` `java.util.List;``import` `java.util.Map;` `import` `org.shirdrn.data.beans.DailyAppUser;` `public` `class` `DailyAppUserMapper {` ` ``List<DailyAppUser> getDailyAppUserListByPage(Map<String, Object> conditions);`` ``List<DailyAppUser> getDailyAppUserListForReport(Map<String, Object> conditions);``}
需要说明的是，如果多个表，一定要设置好Mapper映射配置中每个select元素的resultMap属性，属性值就是前部分的resultMap定义的id。如果只从单个表查询数据，完全可以使用resultType，对应resultMap元素中配置的type属性所指定的别名。 实际上，我们需要通过Map来传递参数，也就是把查询的条件值都收集起来，然后放到Map中，示例如下：
Map<String, Object> conditions = ``new` `HashMap<String, Object>();``if``(osNames != ``null``) {`` ``conditions.put(DailyAppUserMapper.KEY_OS_NAMES, osNames);``}``if``(channelNames != ``null``) {`` ``conditions.put(DailyAppUserMapper.KEY_CHANNEL_NAMES, channelNames);``}``if``(versions != ``null``) {`` ``conditions.put(DailyAppUserMapper.KEY_VERSIONS, versions);``}``if``(merchantNames != ``null``) {`` ``conditions.put(DailyAppUserMapper.KEY_MERCHANT_NAMES, merchantNames);``}``if``(primaryCategories != ``null``) {`` ``conditions.put(DailyAppUserMapper.KEY_PRIMARY_CATEGORIES, primaryCategories);``}``if``(secondaryCategories != ``null``) {`` ``conditions.put(DailyAppUserMapper.KEY_SECONDARY_CATEGORIES, secondaryCategories);``}``if``(cooperationModes != ``null``) {`` ``conditions.put(ChannelDayMapper.KEY_COOPERATION_MODES, cooperationModes);``}
上面对应的DailyAppUserMapper中定义的一些Key常量名称，要和Mapper配置文件中foreach元素的collection属性值一致。