Java服务_ck服务弹性接口service层和dao层设计实战1

发表于 | 分类于

Java服务_ck服务弹性接口service层和dao层设计实战1

1.背景

在ck等olap数据服务层，为了抽象化我们创建的接口，扩展接口的可服用性，我们一般会把接口设计成弹性接口。弹性接口的意思就是，传入的维度参数列表是可选的、可变的，传出的指标数据列表也是可选的、可变的。

如下表所描述的一个接口为例来对弹性接口进行理解：

serviceName getBrandIndBrandRank
demensions STime_ETime_SecondIndId_[ThirdIndId]_[ShopType]_[TerminalId]_[BrandId]_[PriceRangeId]
indicators BrandId_BrandName_RankRound_DealAmt_DealProNum_PV_UV_DealCustPriceAvg_DealRate_CartUser
options PageNum_RowNum_OrderBy
attributes

首先，维度组合是该接口的入参列表，按照属性我们一般将维度划分为demensions、options。对于弹性接口，就设置了一些可传可不穿的参数，上表中中括号中的demensions维度即为弹性维度，全部options都是弹性维度。通过控制传入的维度参数，就可以通过一个接口实现多维度数据的查询。

指标组合就是该接口返回的出参列表，按照属性我们一般将指标划分为indicators、attributes。弹性接口的指标就全部都是可选的，只需要在入参中执行本次请求需要哪些指标即可。

排序字段和分页字段就是比较典型的options维度。

2.接口文档设计

1.交易-交易特征-渠道特征

serviceName getTradeChannelFeature
demensions STime_ETime_ShopId_ShopLevel_MainItemScndCatgCd
indicators DealUserNum_DealOrderNum_DealProNum_DealAmt_DealRate_CatgAvgDealAmt_ChannelId_SecondChannelId
options
attributes

2.交易-交易特征-类目特征

serviceName getTradeCategoryFeature
demensions STime_ETime_ShopId_[ChannelId]
indicators DealUserNum_DealOrderNum_DealProNum_DealAmt_DealRate_SecondIndId_ThirdIndId_ThirdIndName
options
attributes

3.交易-交易特征-品牌特征

serviceName getTradeBrandFeature
demensions STime_ETime_ShopId_[ChannelId]
indicators DealUserNum_DealOrderNum_DealProNum_DealAmt_BrandId_BrandName
options PageNum_RowNum_OrderBy
attributes

4.交易-交易特征-商品价格带特征

serviceName getTradePriceDistrictFeature
demensions STime_ETime_ShopId_PriceDistrictList_[ChannelId]
indicators DealUserNum_DealOrderNum_DealProNum_DealAmt_DealRate_PriceDistrict
options
attributes

5.交易-交易特征-新老客户特征

serviceName getTradeOldNewCustomerFeature
demensions STime_ETime_ShopId_[ChannelId]
indicators DealUserNum_DealOrderNum_DealAmt_CustomerType
options
attributes

6.交易-交易特征-支付方式特征

serviceName getTradePayTypeFeature
demensions STime_ETime_ShopId_[ChannelId]
indicators DealUserNum_DealOrderNum_DealAmt_PayTypeId_PayTypeName
options
attributes

7.交易-交易特征-客单件数特征

serviceName getTradeSaleQuantityFeature
demensions STime_ETime_ShopId_SaleQuantityList_[ChannelId]
indicators DealUserNum_DealOrderNum_DealAmt_SaleQuantity
options
attributes

3.创建dao层param类

dao层的param类实际上就是承接接口入参列表的bean类，一般直接借助fastjson组件将jsonObject对象转化为param类对象。

此处我们想要使上述七个弹性接口共用一个param类，那么只需要将这七个接口所涉及到的维度组合全部放进来即可。

创建param类如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
package com.jd.ad.dao.bean.sztrade;

import com.alibaba.fastjson.annotation.JSONField;

import java.util.Set;

import static com.jd.ad.utils.common.ReqUtils.getQuotationStrBySet;
import static com.jd.ad.utils.common.ReqUtils.getStrBySet;

public class SZTradeFeatureParam {
    private static final long serialVersionUID = 1L;

    // 请求唯一标识
    private String uuid;

    // 开始时间
    @JSONField(name = "STime")
    private String sTime;
    // 结束时间
    @JSONField(name = "ETime")
    private String eTime;
    // 店铺
    @JSONField(name = "ShopId")
    private Set<String> shopId;
    // 终端
    @JSONField(name = "ChannelId")
    private Set<String> channelId;
    // 二级终端
    @JSONField(name = "SecondChannelId")
    private Set<String> secondChannelId;
    // 二级类目
    @JSONField(name = "SecondIndId")
    private Set<String> secondIndId;
    // 三级类目
    @JSONField(name = "ThirdIndId")
    private Set<String> thirdIndId;
    // 价格带列表
    @JSONField(name = "PriceDistrictList")
    private Set<String> priceDistrictList;
    // 客单件列表
    @JSONField(name = "SaleQuantityList")
    private Set<String> saleQuantityList;
    // 品牌
    @JSONField(name = "BrandId")
    private Set<String> brandId;
    // 新老客类型
    @JSONField(name = "CustomerType")
    private Set<String> customerType;
    // 支付方式
    @JSONField(name = "PayTypeId")
    private Set<String> payTypeId;
    // 店铺级别
    @JSONField(name = "ShopLevel")
    private Set<String> shopLevel;
    // 店铺主营二级类目
    @JSONField(name = "MainItemScndCatgCd")
    private Set<String> mainItemScndCatgCd;
    // 排序
    @JSONField(name = "OrderBy")
    private String orderBy;
    // 页数
    @JSONField(name = "PageNum")
    private int pageNum = 1;
    // 显示条数
    @JSONField(name = "RowNum")
    private int rowNum = 0;

    //起始价格带
    private double sPrice;
    //结束价格带
    private double ePrice;
    //起始客单件数
    private int sSaleQtty;
    //结束客单件数
    private int eSaleQtty;

    private Set<String> firstQueryCols;
    private Set<String> dealQueryCols;
    private Set<String> trafficQueryCols;
    private Set<String> indexQueryCols;

    public String getUuid() {
        return uuid;
    }

    public void setUuid(String uuid) {
        this.uuid = uuid;
    }

    public String getsTime() {
        return sTime;
    }

    public void setsTime(String sTime) {
        this.sTime = sTime;
    }

    public String geteTime() {
        return eTime;
    }

    public void seteTime(String eTime) {
        this.eTime = eTime;
    }

    public String getShopId() {
        return getQuotationStrBySet(shopId);
    }

    public void setShopId(Set<String> shopId) {
        this.shopId = shopId;
    }

    public String getChannelId() {
        return getQuotationStrBySet(channelId);
    }

    public void setChannelId(Set<String> channelId) {
        this.channelId = channelId;
    }

    public String getSecondChannelId() {
        return getQuotationStrBySet(secondChannelId);
    }

    public void setSecondChannelId(Set<String> secondChannelId) {
        this.secondChannelId = secondChannelId;
    }

    public String getSecondIndId() {
        return getQuotationStrBySet(secondIndId);
    }

    public void setSecondIndId(Set<String> secondIndId) {
        this.secondIndId = secondIndId;
    }

    public String getThirdIndId() {
        return getQuotationStrBySet(thirdIndId);
    }

    public void setThirdIndId(Set<String> thirdIndId) {
        this.thirdIndId = thirdIndId;
    }

    public Set<String> getPriceDistrictList() {
        return priceDistrictList;
    }

    public void setPriceDistrictList(Set<String> priceDistrictList) {
        this.priceDistrictList = priceDistrictList;
    }

    public Set<String> getSaleQuantityList() {
        return saleQuantityList;
    }

    public void setSaleQuantityList(Set<String> saleQuantityList) {
        this.saleQuantityList = saleQuantityList;
    }

    public String getBrandId() {
        return getQuotationStrBySet(brandId);
    }

    public void setBrandId(Set<String> brandId) {
        this.brandId = brandId;
    }

    public String getCustomerType() {
        return getQuotationStrBySet(customerType);
    }

    public void setCustomerType(Set<String> customerType) {
        this.customerType = customerType;
    }

    public String getPayTypeId() {
        return getQuotationStrBySet(payTypeId);
    }

    public void setPayTypeId(Set<String> payTypeId) {
        this.payTypeId = payTypeId;
    }

    public String getShopLevel() {
        return getQuotationStrBySet(shopLevel);
    }

    public void setShopLevel(Set<String> shopLevel) {
        this.shopLevel = shopLevel;
    }

    public String getMainItemScndCatgCd() {
        return getQuotationStrBySet(mainItemScndCatgCd);
    }

    public void setMainItemScndCatgCd(Set<String> mainItemScndCatgCd) {
        this.mainItemScndCatgCd = mainItemScndCatgCd;
    }

    public String getOrderBy() {
        return orderBy;
    }

    public void setOrderBy(String orderBy) {
        this.orderBy = orderBy;
    }

    public int getPageNum() {
        return pageNum;
    }

    public void setPageNum(int pageNum) {
        this.pageNum = pageNum;
    }

    public int getRowNum() {
        return rowNum;
    }

    public void setRowNum(int rowNum) {
        this.rowNum = rowNum;
    }

    public double getsPrice() {
        return sPrice;
    }

    public void setsPrice(double sPrice) {
        this.sPrice = sPrice;
    }

    public double getePrice() {
        return ePrice;
    }

    public void setePrice(double ePrice) {
        this.ePrice = ePrice;
    }

    public int getsSaleQtty() {
        return sSaleQtty;
    }

    public void setsSaleQtty(int sSaleQtty) {
        this.sSaleQtty = sSaleQtty;
    }

    public int geteSaleQtty() {
        return eSaleQtty;
    }

    public void seteSaleQtty(int eSaleQtty) {
        this.eSaleQtty = eSaleQtty;
    }

    public String getFirstQueryCols() {
        return getStrBySet(firstQueryCols);
    }

    public void setFirstQueryCols(Set<String> firstQueryCols) {
        this.firstQueryCols = firstQueryCols;
    }

    public String getDealQueryCols() {
        return getStrBySet(dealQueryCols);
    }

    public void setDealQueryCols(Set<String> dealQueryCols) {
        this.dealQueryCols = dealQueryCols;
    }

    public String getTrafficQueryCols() {
        return getStrBySet(trafficQueryCols);
    }

    public void setTrafficQueryCols(Set<String> trafficQueryCols) {
        this.trafficQueryCols = trafficQueryCols;
    }

    public String getIndexQueryCols() {
        return getStrBySet(indexQueryCols);
    }

    public void setIndexQueryCols(Set<String> indexQueryCols) {
        this.indexQueryCols = indexQueryCols;
    }
}

注意添加@JSONField注解设置json字段与param属性的对应关系。

4.创建工具类将set转化为字符串

可以看到param类中的属性都有get、set方法，其中get方法非常重要，是mybatis动态代理生成dao方法时获取这些属性所使用的方法。也就是说mapper.xml文件中的占位符参数真实值都是通过这些get方法获取的。

那么对于Set<String>类型的属性，毫无疑问我们需要在get方法中将它转化为一个String类型的值再放入sql占位符中。

最常见的两种转化方法：

1.将Set中的字符串元素直接用逗号拼接起来：

1
2
3
4
5
6
7
8
9
10
11
12
13
public static String getStrBySet(Set<String> sets) {
    String paramStr = null;
    if (sets != null && sets.size() > 0) {
        StringBuffer sb = new StringBuffer();
        for (String str : sets) {
            sb.append(str).append(",");
        }

        paramStr = sb.toString();
        paramStr = paramStr.substring(0, paramStr.length() - 1);
    }
    return paramStr;
}

2.将Set中的每个字符串元素都加上单引号，再用逗号拼接起来，因为在ck中使用单引号标记字符串类型：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
public static String getQuotationStrBySet(Collection<String> sets) {
    String paramStr = null;
    if (sets != null && sets.size() > 0) {
        StringBuffer sb = new StringBuffer();
        for (String str : sets) {
            sb.append(str).append(",");
        }

        paramStr = sb.toString();
        paramStr = paramStr.substring(0, paramStr.length() - 1).replace(",", "','");
        paramStr = "'" + paramStr + "'";
    }
    return paramStr;
}

5.创建dao层的mapper接口类

dao层的mapper接口类是mybatis基于spring框架生成dao方法实例的基础，供service层依赖引用。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
package com.jd.ad.dao.mapper.sztrade;

import com.jd.ad.dao.bean.sztrade.SZTradeFeatureParam;
import org.springframework.stereotype.Repository;

import java.util.LinkedHashMap;
import java.util.List;

@Repository
public interface SZTradeFeatureMapper {
    //交易-交易特征-渠道特征
    List<LinkedHashMap<String, Object>> getTradeChannelFeature(SZTradeFeatureParam param);

    //交易-交易特征-类目特征
    List<LinkedHashMap<String, Object>> getTradeCategoryFeature(SZTradeFeatureParam param);

    //交易-交易特征-品牌特征
    List<LinkedHashMap<String, Object>> getTradeBrandFeature(SZTradeFeatureParam param);

    //交易-交易特征-商品价格带特征
    List<LinkedHashMap<String, Object>> getTradePriceDistrictFeature(SZTradeFeatureParam param);

    //交易-交易特征-新老客户特征
    List<LinkedHashMap<String, Object>> getTradeOldNewCustomerFeature(SZTradeFeatureParam param);

    //交易-交易特征-支付方式特征
    List<LinkedHashMap<String, Object>> getTradePayTypeFeature(SZTradeFeatureParam param);

    //交易-交易特征-客单件数特征
    List<LinkedHashMap<String, Object>> getTradeSaleQuantityFeature(SZTradeFeatureParam param);
}

6.创建继承整个微服务抽象类的service方法

整个ck数据微服务的service层使用一个抽象类来获取所有数据查询接口的方法名等信息：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
package com.jd.ad.service;

import java.lang.reflect.Method;
import java.util.*;

public abstract class BrandService {
    public Map<String, Method> getAllMethods() {
        Map<String, Method> methodMap = new HashMap<>();
        Method[] methods = this.getClass().getDeclaredMethods();
        for (Method method : methods) {
            String methodName = method.getName();
            methodMap.put(methodName, method);
        }
        return methodMap;
    }
}

然后所有的service类都需要继承该抽象类。

我们在service类中，为了优雅和美观，将七个接口抽象到同一个query方法中：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
package com.jd.ad.service.impl.sztrade;

import com.alibaba.fastjson.JSONObject;
import com.jd.ad.dao.bean.sztrade.SZTradeFeatureParam;
import com.jd.ad.dao.mapper.sztrade.SZTradeFeatureMapper;
import com.jd.ad.exception.impl.ErrorEnum;
import com.jd.ad.exception.impl.ValidateException;
import com.jd.ad.log.BrandLog;
import com.jd.ad.service.BrandService;
import com.jd.ad.utils.common.ReqUtils;
import com.jd.ad.utils.common.bizUtil.SZTradeFeatureUtil;
import com.jd.ad.utils.common.transform.impl.TransformForClickHouse;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Set;

@Service
public class SZTradeFeatureService extends BrandService {
    @Resource
    SZTradeFeatureMapper szTradeFeatureMapper;

    private JSONObject query(JSONObject jsonObject, String methodName) {
        List<LinkedHashMap<String, Object>> result = new ArrayList<>();
        JSONObject obj;
        try {
            // 校验时间参数，已修改到serviceList.ini中校验
            // CheckUtils.checkDimensionsTime(jsonObject);
            SZTradeFeatureParam param = (SZTradeFeatureParam) ReqUtils.generateParam(jsonObject, SZTradeFeatureParam.class);

            Set<String> cols = ReqUtils.generateCols(jsonObject);
            if (cols == null || cols.size() == 0) {
                BrandLog.logError("no query any message:" + jsonObject.toJSONString());
                throw new ValidateException(ErrorEnum.DATA_QUERY_ERROR.code, "no query any message");
            }
            // 设置查询列信息
            SZTradeFeatureUtil.setQueryCols(cols, param);
            Method m = szTradeFeatureMapper.getClass().getMethod(methodName, SZTradeFeatureParam.class);

            //遍历价格带列表，每一个价格带请求一次db
            if (methodName.equals("getTradePriceDistrictFeature")) {
                Set<String> priceDistrictList = param.getPriceDistrictList();
                if (priceDistrictList == null || priceDistrictList.size() == 0) {
                    BrandLog.logError("priceDistrictList is null" + jsonObject.toJSONString());
                    throw new ValidateException(ErrorEnum.DATA_QUERY_ERROR.code, "priceDistrictList is null");
                }else {
                    for (String priceDistrict : priceDistrictList) {
                        SZTradeFeatureUtil.setPriceDistrict(priceDistrict, param);
                        List<LinkedHashMap<String, Object>> ckData = (List<LinkedHashMap<String, Object>>) m.invoke(szTradeFeatureMapper, param);
                        if (ckData != null && ckData.size() > 0) {
                            ckData.get(0).put("PriceDistrict", priceDistrict);//将价格带放进去
                            result.addAll(ckData);
                        }
                    }
                }
            }
            //遍历客单价带列表，每一个客单价带请求一次db
            else if (methodName.equals("getTradeSaleQuantityFeature")) {
                Set<String> saleQuantityList = param.getSaleQuantityList();
                if (saleQuantityList == null || saleQuantityList.size() == 0) {
                    BrandLog.logError("saleQuantityList is null" + jsonObject.toJSONString());
                    throw new ValidateException(ErrorEnum.DATA_QUERY_ERROR.code, "saleQuantityList is null");
                }else {
                    for (String saleQuantity : saleQuantityList) {
                        SZTradeFeatureUtil.setSaleQuantity(saleQuantity, param);
                        List<LinkedHashMap<String, Object>> ckData = (List<LinkedHashMap<String, Object>>) m.invoke(szTradeFeatureMapper, param);
                        if (ckData != null && ckData.size() > 0) {
                            ckData.get(0).put("SaleQuantity", saleQuantity);//将客单价带放进去
                            result.addAll(ckData);
                        }
                    }
                }
            }else {
                List<LinkedHashMap<String, Object>> ckData = (List<LinkedHashMap<String, Object>>) m.invoke(szTradeFeatureMapper, param);
                if (ckData != null && ckData.size() > 0) {
                    result.addAll(ckData);
                }
            }

            obj = new TransformForClickHouse().transform(result);

        } catch (Exception e) {
            BrandLog.logError(e.getMessage(), e);
            throw new ValidateException(ErrorEnum.DATA_QUERY_ERROR.code, e.getMessage());
        }
        return obj;
    }

    //交易-交易特征-渠道特征
    public JSONObject getTradeChannelFeature(JSONObject jsonObject) {
        return query(jsonObject, "getTradeChannelFeature");
    }

    //交易-交易特征-类目特征
    public JSONObject getTradeCategoryFeature(JSONObject jsonObject) {
        return query(jsonObject, "getTradeCategoryFeature");
    }

    //交易-交易特征-品牌特征
    public JSONObject getTradeBrandFeature(JSONObject jsonObject) {
        return query(jsonObject, "getTradeBrandFeature");
    }

    //交易-交易特征-商品价格带特征
    public JSONObject getTradePriceDistrictFeature(JSONObject jsonObject) {
        return query(jsonObject, "getTradePriceDistrictFeature");
    }

    //交易-交易特征-新老客户特征
    public JSONObject getTradeOldNewCustomerFeature(JSONObject jsonObject) {
        return query(jsonObject, "getTradeOldNewCustomerFeature");
    }

    //交易-交易特征-支付方式特征
    public JSONObject getTradePayTypeFeature(JSONObject jsonObject) {
        return query(jsonObject, "getTradePayTypeFeature");
    }

    //交易-交易特征-客单件数特征
    public JSONObject getTradeSaleQuantityFeature(JSONObject jsonObject) {
        return query(jsonObject, "getTradeSaleQuantityFeature");
    }
}

可以看到query方法中有几个比较重要的工具方法：

1.ReqUtils.generateParam()将jsonObject转化为parambean类对象

2.ReqUtils.generateCols()生成查询信息

3.SZTradeFeatureUtil.setQueryCols()设置查询列信息

4.SZTradeFeatureUtil.setPriceDistrict()设置价格带信息

5.SZTradeFeatureUtil.setSaleQuantity()设置客单价带信息

可以看到上述工具方法前两个是整个service层共用的，后三个是该service类七个弹性接口共用的，下面我们会一一创建并介绍。

7.创建ReqUtils类与工具方法

一个请求jsonObject实例如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
{
    "header": {
        "serviceName": "getTradeChannelFeature",
        "uuid": "test",
        "appkey": "BRAND"
    },
    "body": {
        "indicators": [
            "DealUserNum",
            "DealOrderNum",
            "DealProNum",
            "DealAmt",
            "DealRate",
            "CatgAvgDealAmt",
            "ChannelId",
            "SecondChannelId"
        ],
        "dimensions": {
            "STime": "2022-11-16",
            "ETime": "2022-11-17",
            "ShopId": [
                "10026"
            ],
          	"ShopLevel": [
                "4"
            ],
      			"MainItemScndCatgCd": [
                "738"
            ]
        }
    }
}

1.generateParam()方法将jsonObject转化为parambean类对象：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
public static Object generateParam(JSONObject jsonObject, Class<?> clazz) {

    JSONObject bodyMsg = jsonObject.getJSONObject("body");
    JSONObject dimsMsg = bodyMsg.getJSONObject("dimensions");
    JSONObject optionsMsg = bodyMsg.getJSONObject("options");
    String uuid = jsonObject.getJSONObject("header").getString("uuid");
    String pin = jsonObject.getJSONObject("header").getString("BRAND_PIN");
    JSONObject uuidMsg = new JSONObject();
    uuidMsg.put("uuid", uuid);

    JSONObject conditionMsg = new JSONObject();
    conditionMsg.putAll(dimsMsg);
    if (optionsMsg != null) {
        conditionMsg.putAll(optionsMsg);
    }
    conditionMsg.putAll(uuidMsg);
    conditionMsg.put("BRAND_PIN", pin);

    return JSONObject.toJavaObject(conditionMsg, clazz);
}

很明显，该方法中最主要的步骤，就是把传入的维度组合dimensions、options放入param类对象中。

2.generateCols()生成查询信息：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
public static Set generateCols(JSONObject jsonObject) {

    JSONObject bodyMsg = jsonObject.getJSONObject("body");
    JSONArray indicatorsMsg = bodyMsg.getJSONArray("indicators");
    JSONArray attributesMsg = bodyMsg.getJSONArray("attributes");

    JSONArray conditionMsg = new JSONArray();
    conditionMsg.addAll(indicatorsMsg);
    if (null != attributesMsg)
        conditionMsg.addAll(attributesMsg);

    Object[] colsArray = conditionMsg.toArray();
    Set<String> cols = new HashSet<>();
    for (Object col : colsArray) {
        cols.add(String.valueOf(col));
    }
    return cols;
}

很明显，该方法最主要的步骤，就是从传入的参数中获取本次请求所需要的指标组合indicators、attributes。

8.写出一个接口查询全量指标的sql

做这一步的目的是为了观察sql，从7个sql中抽象出共性较大的弹性查询字段：

getTradeChannelFeature
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
select
DealUserNum as DealUserNum,
DealOrderNum as DealOrderNum,
DealProNum as DealProNum,
DealAmt as DealAmt,
DealUserNum/uv as DealRate,
CatgAvgDealAmt as CatgAvgDealAmt,
ChannelId as ChannelId,
SecondChannelId as SecondChannelId
from
(
select
	uniq(userLogAcct) as DealUserNum,
	uniq(saleOrdId) as DealOrderNum,
	sum(saleQtty) as DealProNum,
	sum(saleAmt) as DealAmt,
	terminalId as ChannelId,
  secondTerminalId as SecondChannelId
from
	SZ.ck_sz_trade_featrue_shop_deal_det_d
where
	dateTime >= '2022-11-14'
	and dateTime <= '2022-11-14'
	and shopId in ('10026')
group by
	terminalId,
  secondTerminalId
) a 
left join 
(
  select
  	uniq(brwsUniqId) as uv,
  	terminalId as ChannelId
  from
  	SZ.ck_sz_trade_featrue_shop_traffic_det_d
  where
    dateTime >= '2022-11-14'
    and dateTime <= '2022-11-14'
    and shopId in ('10026')
  group by 
		ChannelId
) b on a.ChannelId = b.ChannelId
left join
  (
    select
    	avg(ordAmt) as CatgAvgDealAmt,
    	terminalId as ChannelId
    from
    	SZ.ck_sz_trade_featrue_shop_index_info_d
    where
    	dateTime >= '2022-11-14'
			and dateTime <= '2022-11-14'
    	and shopLevel in ('4')
    	and mainItemScndCatgCd in ('738')
    group by 
    	ChannelId
  ) c on a.ChannelId = c.ChannelId
getTradeCategoryFeature
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
select
DealUserNum as DealUserNum,
DealOrderNum as DealOrderNum,
DealProNum as DealProNum,
DealAmt as DealAmt,
DealUserNum/uv as DealRate,
SecondIndId as SecondIndId,
ThirdIndId as ThirdIndId,
ThirdIndName as ThirdIndName
from
(
select
	uniq(userLogAcct) as DealUserNum,
	uniq(saleOrdId) as DealOrderNum,
	sum(saleQtty) as DealProNum,
	sum(saleAmt) as DealAmt,
  secondIndId as SecondIndId,
  thirdIndId as ThirdIndId,
  any(thirdIndName) as ThirdIndName
from
	SZ.ck_sz_trade_featrue_shop_deal_det_d
where
	dateTime >= '2022-11-16'
	and dateTime <= '2022-11-16'
	and shopId in ('10026')
  and terminalId in ('2')
group by
	secondIndId,
  thirdIndId
) a 
left join 
(
  select
  	uniq(brwsUniqId) as uv,
  	secondIndId as SecondIndId,
  	thirdIndId as ThirdIndId
  from
  	SZ.ck_sz_trade_featrue_shop_traffic_det_d
  where
    dateTime >= '2022-11-16'
    and dateTime <= '2022-11-16'
    and shopId in ('10026')
  	and terminalId in ('2')
  group by 
		secondIndId,
  	thirdIndId
) b on a.SecondIndId = b.SecondIndId
and a.ThirdIndId = b.ThirdIndId
getTradeBrandFeature
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select
	uniq(userLogAcct) as DealUserNum,
	uniq(saleOrdId) as DealOrderNum,
	sum(saleQtty) as DealProNum,
	sum(saleAmt) as DealAmt,
  brandId as BrandId,
  any(brandName) as BrandName
from
	SZ.ck_sz_trade_featrue_shop_deal_det_d
where
	dateTime >= '2022-11-16'
	and dateTime <= '2022-11-16'
	and shopId in ('10026')
	and terminalId in ('2')
group by
	brandId
getTradePriceDistrictFeature
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
select
DealUserNum as DealUserNum,
DealOrderNum as DealOrderNum,
DealProNum as DealProNum,
DealAmt as DealAmt,
DealUserNum/uv as DealRate
from
(
select
	1 as joinkey,
	uniq(userLogAcct) as DealUserNum,
	uniq(saleOrdId) as DealOrderNum,
	sum(saleQtty) as DealProNum,
	sum(saleAmt) as DealAmt
from
	SZ.ck_sz_trade_featrue_shop_deal_det_d
where
	dateTime >= '2022-11-16'
	and dateTime <= '2022-11-16'
	and shopId in ('10026')
	--and terminalId in ('2')
  and skuJdPrc > 0
  and skuJdPrc <= 999
) a 
left join 
(
  select
  	1 as joinkey,
  	uniq(brwsUniqId) as uv
  from
  	SZ.ck_sz_trade_featrue_shop_traffic_det_d
  where
    dateTime >= '2022-11-16'
    and dateTime <= '2022-11-16'
    and shopId in ('10026')
  	--and terminalId in ('2')
  	and skuJdPrc > 0
  	and skuJdPrc <= 999
) b on a.joinkey = b.joinkey;
getTradeOldNewCustomerFeature
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
select
	uniq(userLogAcct) as DealUserNum,
	uniq(saleOrdId) as DealOrderNum,
	sum(saleAmt) as DealAmt,
	case when isAllNew = '1' then '0'
	else '1' end as CustomerType
from
	SZ.ck_sz_trade_featrue_shop_deal_det_d
where
	dateTime >= '2022-11-16'
	and dateTime <= '2022-11-16'
	and shopId in ('10026')
	--and terminalId in ('2')
 group by 
 	case when isAllNew = '1' then '0'
	else '1' end
union all
select
	uniq(userLogAcct) as DealUserNum,
	uniq(saleOrdId) as DealOrderNum,
	sum(saleAmt) as DealAmt,
	case when is180New = '1' then '2'
	else '3' end as CustomerType
from
	SZ.ck_sz_trade_featrue_shop_deal_det_d
where
	dateTime >= '2022-11-16'
	and dateTime <= '2022-11-16'
	and shopId in ('10026')
	--and terminalId in ('2')
 group by 
 	case when is180New = '1' then '2'
	else '3' end
	union all
select
	uniq(userLogAcct) as DealUserNum,
	uniq(saleOrdId) as DealOrderNum,
	sum(saleAmt) as DealAmt,
	case when is730New = '1' then '4'
	else '5' end as CustomerType
from
	SZ.ck_sz_trade_featrue_shop_deal_det_d
where
	dateTime >= '2022-11-16'
	and dateTime <= '2022-11-16'
	and shopId in ('10026')
	--and terminalId in ('2')
 group by 
 	case when is730New = '1' then '4'
	else '5' end
getTradePayTypeFeature
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select
	uniq(userLogAcct) as DealUserNum,
	uniq(saleOrdId) as DealOrderNum,
	sum(saleAmt) as DealAmt,
  payMdeCd as PayTypeId,
  any(payMdeDsc) as PayTypeName
from
	SZ.ck_sz_trade_featrue_shop_deal_det_d
where
	dateTime >= '2022-11-16'
	and dateTime <= '2022-11-16'
	and shopId in ('10026')
	--and terminalId in ('2')
group by
	payMdeCd
getTradeSaleQuantityFeature
1
2
3
4
5
6
7
8
9
10
11
12
13
select
	uniq(userLogAcct) as DealUserNum,
	uniq(saleOrdId) as DealOrderNum,
	sum(saleAmt) as DealAmt
from
	SZ.ck_sz_trade_featrue_shop_deal_det_d
where
	dateTime >= '2022-11-16'
	and dateTime <= '2022-11-16'
	and shopId in ('10026')
	--and terminalId in ('2')
  and saleQtty >= 1
  and saleQtty <= 1

从以上7个sql中明显可以抽象出4个比较常用的指标字段组合：

1.与表没有绑定关系的一个外层指标字段组合：

1
2
3
4
5
6
DealUserNum as DealUserNum,
DealOrderNum as DealOrderNum,
DealProNum as DealProNum,
DealAmt as DealAmt,
DealUserNum/uv as DealRate,
CatgAvgDealAmt as CatgAvgDealAmt

2.与ck_sz_trade_featrue_shop_deal_det_d表绑定的一个指标字段组合：

1
2
3
4
uniq(userLogAcct) as DealUserNum,
uniq(saleOrdId) as DealOrderNum,
sum(saleQtty) as DealProNum,
sum(saleAmt) as DealAmt

3.与ck_sz_trade_featrue_shop_traffic_det_d表绑定的一个指标字段组合：

1
uniq(brwsUniqId) as uv

4.与ck_sz_trade_featrue_shop_index_info_d表绑定的一个指标字段组合：

1
avg(ordAmt) as CatgAvgDealAmt

9.创建SZTradeFeatureUtil类与工具方法

1.setQueryCols()设置可弹性变化的查询列：

在上述总结的可以弹性变化的指标字段组合的基础之上，创建对应的四个查询指标组合Set<String>字段，根据入参中传入的indicators和attributes指标组合来构建这四个字段，并放入到param类对象中，方便后续以占位符的形式放入sql中。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
public static void setQueryCols(Set<String> cols, SZTradeFeatureParam param) {
    //一级指标
    Set<String> firstQueryCols = new HashSet<String>();
    //成交指标
    Set<String> dealQueryCols = new HashSet<String>();
    //流量指标
    Set<String> trafficQueryCols = new HashSet<String>();
    //同行同级指标
    Set<String> indexQueryCols = new HashSet<String>();

    //成交用户人数
    if (cols.contains("DealUserNum")) {
        firstQueryCols.add("DealUserNum as DealUserNum");
        dealQueryCols.add("uniq(userLogAcct) as DealUserNum");
    }

    //成交单量
    if (cols.contains("DealOrderNum")) {
        firstQueryCols.add("DealOrderNum as DealOrderNum");
        dealQueryCols.add("uniq(saleOrdId) as DealOrderNum");
    }

    //成交商品件数
    if (cols.contains("DealProNum")) {
        firstQueryCols.add("DealProNum as DealProNum");
        dealQueryCols.add("sum(saleQtty) as DealProNum");
    }

    //成交金额
    if (cols.contains("DealAmt")) {
        firstQueryCols.add("DealAmt as DealAmt");
        dealQueryCols.add("sum(saleAmt) as DealAmt");
    }

    //成交转化率
    if (cols.contains("DealRate")) {
        firstQueryCols.add("DealUserNum/uv as DealRate");
        dealQueryCols.add("uniq(userLogAcct) as DealUserNum");
        trafficQueryCols.add("uniq(brwsUniqId) as uv");
    }

    //同行同级成交金额
    if (cols.contains("CatgAvgDealAmt")) {
        firstQueryCols.add("CatgAvgDealAmt as CatgAvgDealAmt");
        indexQueryCols.add("avg(ordAmt) as CatgAvgDealAmt");
    }

    param.setFirstQueryCols(firstQueryCols);
    param.setDealQueryCols(dealQueryCols);
    param.setTrafficQueryCols(trafficQueryCols);
    param.setIndexQueryCols(indexQueryCols);
}

2.setPriceDistrict()设置价格带上限和下限：

对于价格带和客单件带这种可随机变化的区间查询条件，很明显难以在一次db请求中以较为优雅的sql把结果查询出来，那么我这里采用循环查询的方式，每一个价格带请求一次db。该工具方法用来将价格带上、下限参数放入到param类对象中，方便后续以占位符的形式放入sql中。

1
2
3
4
5
public static void setPriceDistrict(String priceDistrict, SZTradeFeatureParam param) {
    String[] priceStrings = priceDistrict.split("-");
    param.setsPrice(Double.parseDouble(priceStrings[0]));
    param.setePrice(Double.parseDouble(priceStrings[1]));
}

3.setSaleQuantity()设置客单件带上限和下限：

与价格带同理，该工具方法用来将客单件带上、下限参数放入到param类对象中，方便后续以占位符的形式放入sql中。

1
2
3
4
5
public static void setSaleQuantity(String saleQuantity, SZTradeFeatureParam param) {
    String[] saleQuantityStrings = saleQuantity.split("-");
    param.setsSaleQtty(Integer.parseInt(saleQuantityStrings[0]));
    param.seteSaleQtty(Integer.parseInt(saleQuantityStrings[1]));
}

10.完善service类中的query方法

我们统一使用List<LinkedHashMap<String, Object>>数据类型来承接ck数据库查询sql返回的查询结果，该List中的每个LinkedHashMap元素就是返回的一行数据；然后该LinkedHashMap中的每个entry元素就是一列数据，key为列名，value为数据值。

所以在query方法中我们可以使用如下代码将多次价格带请求合并在一起，并分别打上PriceDistrict字段的值：

1
2
3
4
5
6
7
8
for (String priceDistrict : priceDistrictList) {
    SZTradeFeatureUtil.setPriceDistrict(priceDistrict, param);
    List<LinkedHashMap<String, Object>> ckData = (List<LinkedHashMap<String, Object>>) m.invoke(szTradeFeatureMapper, param);
    if (ckData != null && ckData.size() > 0) {
        ckData.get(0).put("PriceDistrict", priceDistrict);//将价格带放进去
        result.addAll(ckData);
    }
}

11.创建dao层的mapper.xml配置文件

创建这个文件最重要的就是优雅，实现优雅的根本秘诀就是抽象与复用，大体顺序就是resultMap-select-dbsql-tablesql-wheresql：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
<?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="com.jd.ad.dao.mapper.sztrade.SZTradeFeatureMapper">

    <resultMap type="java.util.LinkedHashMap" id="returnHashMap">
    </resultMap>

    <!--行业-交易特征-渠道特征-->
    <select id="getTradeChannelFeature" resultMap="returnHashMap"
            parameterType="com.jd.ad.dao.bean.sztrade.SZTradeFeatureParam">
        <if test="firstQueryCols!=null and firstQueryCols.length()>0">
            select
            ${firstQueryCols},ChannelId as ChannelId, SecondChannelId as SecondChannelId
            from (
            select <if test="dealQueryCols!=null and dealQueryCols.length()>0">${dealQueryCols},</if>terminalId as ChannelId, secondTerminalId as SecondChannelId
            from <include refid="database"/><include refid="dealDetTable"/><include refid="where"/>
            group by terminalId, secondTerminalId
            ) a
            <if test="trafficQueryCols!=null and trafficQueryCols.length()>0">
                left join
                (select
                ${trafficQueryCols},terminalId as ChannelId
                from <include refid="database"/><include refid="trafficDetTable"/><include refid="where"/>
                group by ChannelId
                ) b on a.ChannelId = b.ChannelId
            </if>
            <if test="indexQueryCols!=null and indexQueryCols.length()>0">
                left join
                (select
                ${indexQueryCols},terminalId as ChannelId
                from <include refid="database"/><include refid="indexInfoTable"/><include refid="where"/><include refid="indexInfoWhere"/>
                group by ChannelId
                ) c on a.ChannelId = c.ChannelId
            </if>
        </if>
    </select>

    <!--行业-交易特征-类目特征-->
    <select id="getTradeCategoryFeature" resultMap="returnHashMap"
            parameterType="com.jd.ad.dao.bean.sztrade.SZTradeFeatureParam">
        <if test="firstQueryCols!=null and firstQueryCols.length()>0">
            select
            ${firstQueryCols}, SecondIndId as SecondIndId, ThirdIndId as ThirdIndId, ThirdIndName as ThirdIndName
            from (
            select <if test="dealQueryCols!=null and dealQueryCols.length()>0">${dealQueryCols},</if>secondIndId as SecondIndId, thirdIndId as ThirdIndId, any(thirdIndName) as ThirdIndName
            from <include refid="database"/><include refid="dealDetTable"/><include refid="where"/><include refid="terminalWhere"/>
            group by secondIndId, thirdIndId
            ) a
            <if test="trafficQueryCols!=null and trafficQueryCols.length()>0">
                left join
                (select
                ${trafficQueryCols}, secondIndId as SecondIndId, thirdIndId as ThirdIndId
                from <include refid="database"/><include refid="trafficDetTable"/><include refid="where"/><include refid="terminalWhere"/>
                group by secondIndId, thirdIndId
                ) b on a.SecondIndId = b.SecondIndId and a.ThirdIndId = b.ThirdIndId
            </if>
        </if>
    </select>

    <!--行业-交易特征-品牌特征-->
    <select id="getTradeBrandFeature" resultMap="returnHashMap"
            parameterType="com.jd.ad.dao.bean.sztrade.SZTradeFeatureParam">
        <if test="dealQueryCols!=null and dealQueryCols.length()>0">
            select
            ${dealQueryCols}, brandId as BrandId, any(brandName) as BrandName
            from <include refid="database"/><include refid="dealDetTable"/><include refid="where"/><include refid="terminalWhere"/>
            group by brandId
        </if>
    </select>

    <!--行业-交易特征-商品价格带特征-->
    <select id="getTradePriceDistrictFeature" resultMap="returnHashMap"
            parameterType="com.jd.ad.dao.bean.sztrade.SZTradeFeatureParam">
        <if test="firstQueryCols!=null and firstQueryCols.length()>0">
            select
            ${firstQueryCols}
            from (
            select 1 as joinkey<if test="dealQueryCols!=null and dealQueryCols.length()>0">,${dealQueryCols}</if>
            from <include refid="database"/><include refid="dealDetTable"/><include refid="where"/><include refid="terminalWhere"/><include refid="priceWhere"/>
            ) a
            <if test="trafficQueryCols!=null and trafficQueryCols.length()>0">
                left join
                (select
                1 as joinkey,${trafficQueryCols}
                from <include refid="database"/><include refid="trafficDetTable"/><include refid="where"/><include refid="terminalWhere"/><include refid="priceWhere"/>
                ) b on a.joinkey = b.joinkey
            </if>
        </if>
    </select>

    <!--行业-交易特征-新老客户特征-->
    <select id="getTradeOldNewCustomerFeature" resultMap="returnHashMap"
            parameterType="com.jd.ad.dao.bean.sztrade.SZTradeFeatureParam">
        <if test="dealQueryCols!=null and dealQueryCols.length()>0">
            select
            ${dealQueryCols}, case when isAllNew = '1' then '0' else '1' end as CustomerType
            from <include refid="database"/><include refid="dealDetTable"/><include refid="where"/><include refid="terminalWhere"/>
            group by case when isAllNew = '1' then '0' else '1' end
            union all
            select
            ${dealQueryCols}, case when is180New = '1' then '2' else '3' end as CustomerType
            from <include refid="database"/><include refid="dealDetTable"/><include refid="where"/><include refid="terminalWhere"/>
            group by case when is180New = '1' then '2' else '3' end
            union all
            select
            ${dealQueryCols}, case when is730New = '1' then '4' else '5' end as CustomerType
            from <include refid="database"/><include refid="dealDetTable"/><include refid="where"/><include refid="terminalWhere"/>
            group by case when is730New = '1' then '4' else '5' end
        </if>
    </select>

    <!--行业-交易特征-支付方式特征-->
    <select id="getTradePayTypeFeature" resultMap="returnHashMap"
            parameterType="com.jd.ad.dao.bean.sztrade.SZTradeFeatureParam">
        <if test="dealQueryCols!=null and dealQueryCols.length()>0">
            select
            ${dealQueryCols}, payMdeCd as PayTypeId, any(payMdeDsc) as PayTypeName
            from <include refid="database"/><include refid="dealDetTable"/><include refid="where"/><include refid="terminalWhere"/>
            group by payMdeCd
        </if>
    </select>

    <!--行业-交易特征-客单件数特征-->
    <select id="getTradeSaleQuantityFeature" resultMap="returnHashMap"
            parameterType="com.jd.ad.dao.bean.sztrade.SZTradeFeatureParam">
        <if test="dealQueryCols!=null and dealQueryCols.length()>0">
            select
            ${dealQueryCols}
            from <include refid="database"/><include refid="dealDetTable"/><include refid="where"/><include refid="terminalWhere"/><include refid="saleQttyWhere"/>
        </if>
    </select>

    <!--库-->
    <sql id="database">SZ.</sql>

    <!--表-->
    <sql id="dealDetTable">ck_sz_trade_featrue_shop_deal_det_d</sql>
    <sql id="trafficDetTable">ck_sz_trade_featrue_shop_traffic_det_d</sql>
    <sql id="indexInfoTable">ck_sz_trade_featrue_shop_index_info_d</sql>

    <!--查询条件-->
    <sql id="where">
        where dateTime &gt;=#{sTime} and dateTime &lt;= #{eTime} and shopId in (${shopId})
    </sql>

    <!--渠道条件-->
    <sql id="terminalWhere">
        <if test="channelId != null and channelId.length()>0">
            and terminalId in (${channelId})
        </if>
    </sql>

    <!--同行同级条件-->
    <sql id="indexInfoWhere">
        <if test="shopLevel != null and shopLevel.length()>0">
            and shopLevel in (${shopLevel})
        </if>
        <if test="mainItemScndCatgCd != null and mainItemScndCatgCd.length()>0">
            and mainItemScndCatgCd in (${mainItemScndCatgCd})
        </if>
    </sql>

    <!--价格带条件-->
    <sql id="priceWhere">
        and skuJdPrc &gt; #{sPrice}
        and skuJdPrc &lt;= #{ePrice}
    </sql>

    <!--客单件数条件-->
    <sql id="saleQttyWhere">
        and saleQtty &gt;= #{sSaleQtty}
        and saleQtty &lt;= #{eSaleQtty}
    </sql>

    <!--排序-->
    <sql id="orderBy">
        <if test="orderBy != null and orderBy.length()>0">
            order by ${orderBy}
        </if>
    </sql>

    <!--翻页-->
    <sql id="limit">
        <if test="rowNum >0 ">
            limit ${(pageNum-1)*rowNum},${rowNum}
        </if>
    </sql>

</mapper>

12.在spring-config-mybatis配置文件中创建与ck数据源bean和mapper的bean

读取配置文件中配置的ck主备集群url、username、password，通过xml配置文件的形式创建ck数据源bean和mapper的bean：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
<!-- 商家版离线交易特征数据源 -->
<bean id="clickhousePropertiesSZTrade" class="ru.yandex.clickhouse.settings.ClickHouseProperties">
    <property name="user" value="${clickhouse.username.sztrade}"/>
    <property name="password" value="${clickhouse.password.sztrade}"/>
    <property name="socketTimeout" value="80000"/>
</bean>

<bean id="ClickhouseDataSourceSZTrade" class="ru.yandex.clickhouse.BalancedClickhouseDataSource">
    <constructor-arg index="0" value="${clickhouse.jdbc.url.sztrade}"/>
    <constructor-arg index="1" ref="clickhousePropertiesSZTrade"/>
</bean>

<bean id="clickhousePropertiesHotSZTrade" class="ru.yandex.clickhouse.settings.ClickHouseProperties">
    <property name="user" value="${clickhouse.username.sztrade.hot}"/>
    <property name="password" value="${clickhouse.password.sztrade.hot}"/>
    <property name="socketTimeout" value="80000"/>
</bean>

<bean id="ClickhouseDataSourceHotSZTrade" class="ru.yandex.clickhouse.BalancedClickhouseDataSource">
    <constructor-arg index="0" value="${clickhouse.jdbc.url.sztrade.hot}"/>
    <constructor-arg index="1" ref="clickhousePropertiesHotSZTrade"/>
</bean>

<bean id="dynamicDataSourceSZTrade" class="com.jd.ad.utils.common.datasource.DynamicDataSource" >
    <property name="targetDataSources">
        <map key-type="java.lang.String">
            <entry value-ref="ClickhouseDataSourceSZTrade" key="dataSourceDefaultSZTrade"></entry>
            <entry value-ref="ClickhouseDataSourceHotSZTrade" key="dataSourceHotSZTrade"></entry>
        </map>
    </property>
    <property name="defaultTargetDataSource" ref="ClickhouseDataSourceSZTrade" ></property>
    <property name="dataSourceId" value="dataSourceIdSZTrade" ></property>
</bean>

<bean id="brandManagerSZTrade" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dynamicDataSourceSZTrade" />
</bean>

<bean id="clickhouseSqlSessionFactorySZTrade" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dynamicDataSourceSZTrade"/>
    <property name="configLocation" value="classpath:/spring/mybatis-configuration.xml" />
    <property name="mapperLocations">
        <array>
            <value>classpath:dao/impl/mapper/sztrade/*.xml</value>
        </array>
    </property>
</bean>

<bean id="mapperScannerSZTrade" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <property name="basePackage" value="com.jd.ad.dao.mapper.sztrade"/>
    <property name="sqlSessionFactoryBeanName" value="clickhouseSqlSessionFactorySZTrade"/>
</bean>