在报表类应用中,通常需要根据不同的维度去组合复杂的查询条件,然后构造SQL去执行查询。如果只是通过在程序中简单地拼接SQL语句,工作量会非常大,而且代码可能也非常难以维护。Mybatis支持动态SQL查询功能,可以通过配置动态的SQL来简化程序代码中复杂性,不过,这个颇有点XML编程的韵味,通过XML来处理复杂的数据判断、循环的功能,其实也很好理解。
准备工作
下面,我们首先创建一个MySQL示例表,如下所示:
01 |
CREATE 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 ,
|
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表中一条记录的数据,我们简单取几个字段,如下所示:
01 |
package org.shirdrn.mybatis;
|
03 |
import java.io.Serializable;
|
05 |
public class TrafficInfo implements Serializable {
|
07 |
private static final long serialVersionUID = -8696613205078899594L;
|
16 |
public void setId( int id) {
|
19 |
public String getDomain() {
|
22 |
public void setDomain(String domain) {
|
25 |
public String getMonth() {
|
28 |
public void setMonth(String month) {
|
31 |
public int getMonthlyTraffic() {
|
32 |
return monthlyTraffic;
|
34 |
public void setMonthlyTraffic( int monthlyTraffic) {
|
35 |
this .monthlyTraffic = monthlyTraffic;
|
39 |
public String toString() {
|
40 |
return "[id=" + id + ", domain=" + domain + ", month=" +
|
41 |
month + ", monthlyTraffic=" + monthlyTraffic + "]" ;
|
- org.shirdrn.mybatis.mapper.TrafficInfoMapper接口类
该类定义了一个与SQL配置进行映射的基本操作,实际的SQL配置有专门的XML文件来进行配置。该接口定义了如下操作:
01 |
package org.shirdrn.mybatis.mapper;
|
03 |
import java.util.List;
|
06 |
import org.shirdrn.mybatis.TrafficInfo;
|
08 |
public interface TrafficInfoMapper {
|
11 |
* 根据指定id去查询记录,结果至多只有一条
|
15 |
TrafficInfo getTrafficInfo( int id);
|
18 |
* 根据指定的domain参数查询记录,返回一个记录的列表
|
22 |
List<TrafficInfo> getTrafficInfoList(String domain);
|
25 |
* 根据一个 字段domain进行查询,但是存在多个domain的值,传入一个数组
|
29 |
List<TrafficInfo> getMultiConditionsList(String[] domains);
|
32 |
* 根据多个字段进行查询,每个字段可能有多个值,所以参数是Map类型
|
36 |
List<TrafficInfo> getMapConditionsList(Map<String, Object> conditions);
|
上面接口中定义的操作,一个比一个复杂,我们通过这一系列操作来说明在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" ?>
|
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" />
|
12 |
< select id = "getTrafficInfo" resultType = "TrafficInfo" parameterType = "int" >
|
13 |
SELECT * FROM domain_db.traffic_info WHERE id = #{id}
|
16 |
< select id = "getTrafficInfoList" resultType = "TrafficInfo" parameterType = "string" >
|
17 |
SELECT * FROM domain_db.traffic_info WHERE domain = #{domain}
|
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 = ")" >
|
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 = ")" >
|
32 |
AND status = 0 AND month IN
|
33 |
< foreach collection = "months" index = "index" item = "month" open = " (" separator = "," close = ")" >
|
如果你之前用过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" |
07 |
< typeAlias type = "org.shirdrn.mybatis.TrafficInfo" alias = "TrafficInfo" />
|
10 |
< mapper resource = "org/shirdrn/mybatis/mapper/TrafficInfoMapper.xml" />
|
- Spring配置文件applicationContext.xml
01 |
<? xml version = "1.0" encoding = "UTF-8" ?>
|
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" >
|
33 |
< value >classpath*:/proxool.properties</ value >
|
38 |
< context:component-scan base-package = "org.shirdrn.mybatis" />
|
40 |
< aop:aspectj-autoproxy proxy-target-class = "true" />
|
41 |
< aop:config proxy-target-class = "true" />
|
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}" />
|
63 |
< bean id = "dataSource0" class = "org.jdbcdslog.ConnectionPoolDataSourceProxy" >
|
64 |
< property name = "targetDSDirect" ref = "dataSource" />
|
69 |
< bean id = "sqlSessionFactory" class = "org.mybatis.spring.SqlSessionFactoryBean" >
|
70 |
< property name = "dataSource" ref = "dataSource0" />
|
71 |
< property name = "configLocation" value = "classpath:sqlMapConfig.xml" />
|
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" />
|
77 |
< bean id = "trafficInfoService" class = "org.shirdrn.mybatis.TrafficInfoService" >
|
78 |
< property name = "trafficInfoMapper" ref = "trafficInfoMapper" />
|
简单说明一下:
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查询操作,实现代码如下所示:
01 |
package org.shirdrn.mybatis;
|
03 |
import java.util.List;
|
06 |
import org.shirdrn.mybatis.mapper.TrafficInfoMapper;
|
08 |
public class TrafficInfoService {
|
10 |
private TrafficInfoMapper trafficInfoMapper;
|
12 |
public void setTrafficInfoMapper(TrafficInfoMapper trafficInfoMapper) {
|
13 |
this .trafficInfoMapper = trafficInfoMapper;
|
16 |
public TrafficInfo getTrafficInfo( int id) {
|
17 |
return trafficInfoMapper.getTrafficInfo(id);
|
20 |
public List<TrafficInfo> getTrafficInfoList(String domain) {
|
21 |
return trafficInfoMapper.getTrafficInfoList(domain);
|
24 |
public List<TrafficInfo> getMultiConditionsList(String[] domains) {
|
25 |
return trafficInfoMapper.getMultiConditionsList(domains);
|
28 |
List<TrafficInfo> getMapConditionsList(Map<String, Object> conditions) {
|
29 |
return trafficInfoMapper.getMapConditionsList(conditions);
|
按照上面的配置,我们就能够实现从单个字段的查询,到多个字段的组合复杂查询。可以通过与实际编写代码来控制这些逻辑相比较,使用Mybatis可能配置上相对复杂一些,但是或得到的好处是非常多的,如代码可维护性好,看起来配置比较直观,出错的几率会大大减小。实际上,如果熟练的这种配置方式,就会在实际开发过程中,更好地去处理更加复杂的统计查询条件的组合逻辑。
测试用例
测试用例可以检测我们上面的配置是否生效,实现代码:
01 |
package org.shirdrn.mybatis;
|
03 |
import java.util.Arrays;
|
04 |
import java.util.HashMap;
|
05 |
import java.util.List;
|
08 |
import org.junit.Test;
|
09 |
import org.junit.runner.RunWith;
|
10 |
import org.springframework.beans.factory.annotation.Autowired;
|
11 |
import org.springframework.test.context.ContextConfiguration;
|
12 |
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
|
14 |
@RunWith (SpringJUnit4ClassRunner. class )
|
15 |
@ContextConfiguration (locations = { "classpath:/applicationContext*.xml" })
|
16 |
public class TestTrafficInfoService {
|
19 |
private TrafficInfoService trafficInfoService;
|
22 |
public void getTraffic() {
|
24 |
TrafficInfo result = trafficInfoService.getTrafficInfo(id);
|
25 |
System.out.println(result);
|
29 |
public void getTrafficList() {
|
30 |
String domain = "make-the-cut.com" ;
|
31 |
List<TrafficInfo> results = trafficInfoService.getTrafficInfoList(domain);
|
32 |
System.out.println(results);
|
36 |
public void getMultiConditionsList() {
|
37 |
String[] domains = new String[] {
|
38 |
"make.tv" , " make-the-cut.com" , "makgrills.com" , "makino.com"
|
40 |
List<TrafficInfo> results = trafficInfoService.getMultiConditionsList(domains);
|
41 |
System.out.println(results);
|
45 |
public void getMapConditionsList() {
|
46 |
String[] domains = new String[] {
|
47 |
"make.tv" , " make-the-cut.com" , "makgrills.com" , "makino.com"
|
49 |
List<String> months = Arrays.asList( new String[] {
|
50 |
"201203" , "201204" , "201205"
|
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);
|
查询进阶
这里,给出一个实际的例子,是对每日报表的一个统计实例,为简单起见,只拿出2张表做LEFT JOIN连接。这个需求,要求查询时可以对每个维度取过得查询条件值,如对于维度osName,值可以使包含Android、IOS,对于另一个维度statDate,可以取最近2天(昨天和前天),等等,并且,这些组合条件可有可无。
对应的Mybatis映射配置文件,内容如下所示:
001 |
<? xml version = "1.0" encoding = "UTF-8" ?>
|
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" />
|
034 |
< select id = "getDailyAppUserListByPage" resultMap = "dailyAppUserMap" >
|
035 |
< include refid = "getDailyAppUserList" />
|
036 |
LIMIT #{offset}, #{limit}
|
039 |
< select id = "getDailyAppUserListForReport" resultMap = "dailyAppUserMap" >
|
040 |
< include refid = "getDailyAppUserList" />
|
043 |
< sql id = "getDailyAppUserList" >
|
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,
|
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" >
|
078 |
< foreach collection = "osNames" index = "index" item = "osName" open = " (" separator = "," close = ")" >
|
082 |
< if test = "channelNames!=null" >
|
084 |
< foreach collection = "channelNames" index = "index" item = "channelName" open = " (" separator = " OR " close = ")" >
|
085 |
(d.channel_name LIKE CONCAT('%', CONCAT(#{channelName}, '%')))
|
088 |
< if test = "versions!=null" >
|
090 |
< foreach collection = "versions" index = "index" item = "version" open = " (" separator = "," close = ")" >
|
094 |
< if test = "merchantNames!=null" >
|
096 |
< foreach collection = "merchantNames" index = "index" item = "merchantName" open = " (" separator = " OR " close = ")" >
|
097 |
(m.merchant_name LIKE CONCAT('%', CONCAT(#{%merchantName%}, '%')))
|
100 |
< if test = "primaryCategories!=null" >
|
101 |
AND d.primary_category_id IN
|
102 |
< foreach collection = "primaryCategories" index = "index" item = "primaryCategory" open = " (" separator = "," close = ")" >
|
106 |
< if test = "secondaryCategories!=null" >
|
107 |
AND d.secondary_category_id IN
|
108 |
< foreach collection = "secondaryCategories" index = "index" item = "secondaryCategory" open = " (" separator = "," close = ")" >
|
112 |
< if test = "cooperationModes!=null" >
|
113 |
AND d.cooperation_model IN
|
114 |
< foreach collection = "cooperationModes" index = "index" item = "cooperationMode" open = " (" separator = "," close = ")" >
|
上述映射配置对应的Mapper定义,接口如下所示:
01 |
package org.shirdrn.data.mappers;
|
03 |
import java.util.List;
|
06 |
import org.shirdrn.data.beans.DailyAppUser;
|
08 |
public class DailyAppUserMapper {
|
10 |
List<DailyAppUser> getDailyAppUserListByPage(Map<String, Object> conditions);
|
11 |
List<DailyAppUser> getDailyAppUserListForReport(Map<String, Object> conditions);
|
需要说明的是,如果多个表,一定要设置好Mapper映射配置中每个select元素的resultMap属性,属性值就是前部分的resultMap定义的id。如果只从单个表查询数据,完全可以使用resultType,对应resultMap元素中配置的type属性所指定的别名。
实际上,我们需要通过Map来传递参数,也就是把查询的条件值都收集起来,然后放到Map中,示例如下:
01 |
Map<String, Object> conditions = new HashMap<String, Object>();
|
03 |
conditions.put(DailyAppUserMapper.KEY_OS_NAMES, osNames);
|
05 |
if (channelNames != null ) {
|
06 |
conditions.put(DailyAppUserMapper.KEY_CHANNEL_NAMES, channelNames);
|
08 |
if (versions != null ) {
|
09 |
conditions.put(DailyAppUserMapper.KEY_VERSIONS, versions);
|
11 |
if (merchantNames != null ) {
|
12 |
conditions.put(DailyAppUserMapper.KEY_MERCHANT_NAMES, merchantNames);
|
14 |
if (primaryCategories != null ) {
|
15 |
conditions.put(DailyAppUserMapper.KEY_PRIMARY_CATEGORIES, primaryCategories);
|
17 |
if (secondaryCategories != null ) {
|
18 |
conditions.put(DailyAppUserMapper.KEY_SECONDARY_CATEGORIES, secondaryCategories);
|
20 |
if (cooperationModes != null ) {
|
21 |
conditions.put(ChannelDayMapper.KEY_COOPERATION_MODES, cooperationModes);
|
上面对应的DailyAppUserMapper中定义的一些Key常量名称,要和Mapper配置文件中foreach元素的collection属性值一致。
时间: 2024-12-02 21:21:22