之前有做个一个自定义报表的查询，这里使用的是一个动态的sql拼接，是前端选择了什么指标就查询什么信息！（这里的指标是多个表的字段，前端随便选择了这些指标，然后后端根据这些指标拼接sql，返回这些指标的数据）。
参数接收DTO
1 /**
2 * 自定义报表
3 */
4 public class DefinedReportFormDTO {
5 /**
6 * 指标id
7 */
8 private List<Long> ids;
9 /**
10 * 开始时间
11 */
12 @DateTimeFormat(pattern = "yyyy-MM")
13 private Date startTime;
14 /**
15 * 结束时间
16 */
17 @DateTimeFormat(pattern = "yyyy-MM")
18 private Date endTime;
19 /**
20 * 频率
21 */
22 private String timeStyle;
23 /**
24 * 机构id
25 */
26 private List companyIds;
27
28 private boolean avg =false;
29
30 private String idsParam;
31
32 private String companyIdsParam;
33
34 public void setCompanyIdsParam(String companyIdsParam) {
35 this.companyIdsParam = companyIdsParam;
36 }
37
38 public void setIdsParam(String idsParam) {
39 this.idsParam = idsParam;
40 }
41
42 public String getCompanyIdsParam() {
43 return companyIdsParam;
44 }
45
46 public String getIdsParam() {
47 return idsParam;
48 }
49 public boolean isAvg() {
50 return avg;
51 }
52
53 public void setAvg(boolean avg) {
54 this.avg = avg;
55 }
56
57
58 public Date getStartTime() {
59 return startTime;
60 }
61
62 public void setStartTime(Date startTime) {
63 this.startTime = startTime;
64 }
65
66 public Date getEndTime() {
67 return endTime;
68 }
69
70 public void setEndTime(Date endTime) {
71 this.endTime = endTime;
72 }
73
74 public String getTimeStyle() {
75 return timeStyle;
76 }
77
78 public void setTimeStyle(String timeStyle) {
79 this.timeStyle = timeStyle;
80 }
81
82 public List<Long> getIds() {
83 return ids;
84 }
85
86 public void setIds(List<Long> ids) {
87 this.ids = ids;
88 }
89
90 public List getCompanyIds() {
91 return companyIds;
92 }
93
94 public void setCompanyIds(List companyIds) {
95 this.companyIds = companyIds;
96 }
97
98 }View Code
数据返回VO
1 public class DefinedReportFormVO implements Serializable {
2 private String time;
3 private List<Map<String, Object>> arr = new ArrayList<>();
4
5 public String getTime() {
6 return time;
7 }
8
9 public void setTime(String time) {
10 this.time = time;
11 }
12
13 public List<Map<String, Object>> getArr() {
14 return arr;
15 }
16
17 public void setArr(List<Map<String, Object>> arr) {
18 this.arr = arr;
19 }
20
21
22 }View Code
控制器Controller
1 @GetMapping("/report/defindReport")
2 public JsonResponseExt defindReport(DefinedReportFormDTO definedReportFormDTO){
3
4
5
6
7 //测试数据
8
9
10 List list1 = new ArrayList<>();
11 list1.add("111");
12 definedReportFormDTO.setIds(list1);
13 definedReportFormDTO.setTimeStyle("month");
14 definedReportFormDTO.setAvg(true);
15
16
17 Calendar instance = Calendar.getInstance();
18 instance.set(2018,1,11);
19 definedReportFormDTO.setStartTime(instance.getTime());
20 instance.setTime(new Date());
21 definedReportFormDTO.setEndTime(instance.getTime());
22
23
24 return JsonResponseExt.success(dataAcquisitionFileInfoService.defindQuery(definedReportFormDTO));
25
26 }View Code
服务类Service
1 public interface DataAcquisitionFileInfoService {
2
3 List<DefinedReportFormVO> defindQuery(DefinedReportFormDTO parameter);
4
5 }View Code
实现类ServiceImpl
1 @SuppressWarnings("unchecked")
2 @Override
3 public List<DefinedReportFormVO> defindQuery(DefinedReportFormDTO parameter) {
4
5
6 /**
7
8
9 * 定义五张表的查询字符串，年月，和机构id默认查询
10 */
11 StringBuilder orgInformationCbrc = new StringBuilder("select reporting_year as reportingYear,reporting_month as reportingMonth, company_id ,");
12 StringBuilder orgBasicInformation = new StringBuilder("select reporting_year as reportingYear,reporting_month as reportingMonth, company_id,");
13 StringBuilder orgBusinessStructure = new StringBuilder("select reporting_year as reportingYear,reporting_month as reportingMonth, company_id,");
14 StringBuilder orgProfit = new StringBuilder("select reporting_year as reportingYear,reporting_month as reportingMonth, company_id,");
15 StringBuilder orgBalanceSheets = new StringBuilder("select reporting_year as reportingYear,reporting_month as reportingMonth, company_id,");
16
17 //定义机构的字符串
18 StringBuilder companyIds = new StringBuilder("");
19 //查询所有机构
20 List<Company> orgList = orgService.getOrgList();
21
22 //拼接所有机构的字符串（如果需要求平均数的话）
23 for (Company company : orgList) {
24 companyIds.append(company.getId()+",");
25 }
26
27 companyIds.deleteCharAt(companyIds.length()-1);
28 //定义每个表的字符串判断
29 Map<String ,String> bool = new HashMap<>();
30
31 //指标名
32 List<String> fieldNames = new ArrayList();
33 //返回结果
34 List<Map<String,Object>> result = new ArrayList<>();
35
36 //指标名默认添加年月机构id
37 fieldNames.add("reportingYear");
38 fieldNames.add("reportingMonth");
39 fieldNames.add("companyId");
40 //定义指标id集合
41 List ids = parameter.getIds();
42 //循环所有的指标
43 for (Object id : ids) {
44 //如果指标为空
45 if (!"".equals(id) && id != null) {
46 //根据指标id查询指标
47 OrgStatisticalIndicators orgStatisticalIndicators = orgStatisticalIndicatorsRespository.findByIdAndAndDelFlag(Long.parseLong(id.toString()));
48 if(("year".equals(parameter.getTimeStyle()) && "0".equals(orgStatisticalIndicators.getYearQuery())) || ("month".equals(parameter.getTimeStyle()) && "0".equals(orgStatisticalIndicators.getMonthQuery()))){
49 /**
50 * 判断指标所在的表，然后为各自的表拼接上表的字段
51 */
52 if ("org_information_cbrc".equals(orgStatisticalIndicators.getTableName())) {
53 orgInformationCbrc.append("ifnull("+orgStatisticalIndicators.getTableField()+",0) AS "+orgStatisticalIndicators.getField()+" ,");
54 //
55 if (bool.get("org_information_cbrc") == null) {
56 bool.put("org_information_cbrc", orgStatisticalIndicators.getTableField());
57 }
58 //如果其他表不存在这个属性则为其他表拼接null
59 orgBasicInformation.append("null as " + orgStatisticalIndicators.getField() + ",");
60 orgBalanceSheets.append("null as " + orgStatisticalIndicators.getField() + ",");
61 orgBusinessStructure.append("null as " + orgStatisticalIndicators.getField() + ",");
62 orgProfit.append("null as " + orgStatisticalIndicators.getField() + ",");
63
64 //行业平均
65 if (parameter.isAvg()) {
66 if("year".equals(parameter.getTimeStyle())){
67 orgInformationCbrc.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear AND reporting_month = '12' ) AS "+orgStatisticalIndicators.getField()+"Avg,");
68 }else{
69 orgInformationCbrc.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
70 }
71
72
73 orgBalanceSheets.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
74
75 orgBasicInformation.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
76
77 orgBusinessStructure.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
78
79 orgProfit.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
80
81
82
83
84 }
85
86
87 } else if ("org_basic_information".equals(orgStatisticalIndicators.getTableName())) {
88 if (bool.get("org_basic_information") == null) {
89 bool.put("org_basic_information", orgStatisticalIndicators.getTableField());
90 }
91
92 orgBasicInformation.append("ifnull("+orgStatisticalIndicators.getTableField()+",0) AS "+orgStatisticalIndicators.getField()+" ,");
93 orgInformationCbrc.append("null as " + orgStatisticalIndicators.getField() + ",");
94 orgBalanceSheets.append("null as " + orgStatisticalIndicators.getField() + ",");
95 orgBusinessStructure.append("null as " + orgStatisticalIndicators.getField() + ",");
96 orgProfit.append("null as " + orgStatisticalIndicators.getField() + ",");
97
98 //行业平均
99 if (parameter.isAvg()) {
100 if("year".equals(parameter.getTimeStyle())){
101 orgBasicInformation.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear AND reporting_month = '12' ) AS "+orgStatisticalIndicators.getField()+"Avg,");
102 }else{
103 orgBasicInformation.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
104 }
105
106 orgProfit.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
107 orgInformationCbrc.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
108 orgBalanceSheets.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
109 orgBusinessStructure.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
110
111 }
112
113 } else if ("org_business_structure".equals(orgStatisticalIndicators.getTableName())) {
114 orgBusinessStructure.append("ifnull("+orgStatisticalIndicators.getTableField()+",0) AS "+orgStatisticalIndicators.getField()+" ,");
115 if (bool.get("org_business_structure") == null) {
116 bool.put("org_business_structure", orgStatisticalIndicators.getTableField());
117 }
118
119
120 orgBasicInformation.append("null as " + orgStatisticalIndicators.getField() + ",");
121 orgInformationCbrc.append("null as " + orgStatisticalIndicators.getField() + ",");
122 orgBalanceSheets.append("null as " + orgStatisticalIndicators.getField() + ",");
123 orgProfit.append("null as " + orgStatisticalIndicators.getField() + ",");
124
125 //行业平均
126 if (parameter.isAvg()) {
127 if("year".equals(parameter.getTimeStyle())){
128 orgBusinessStructure.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear AND reporting_month = '12' ) AS "+orgStatisticalIndicators.getField()+"Avg,");
129 }else{
130 orgBusinessStructure.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
131 }
132
133 orgProfit.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
134 orgInformationCbrc.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
135 orgBalanceSheets.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
136 orgBasicInformation.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
137
138
139
140
141
142 }
143 } else if ("org_profit".equals(orgStatisticalIndicators.getTableName())) {
144 orgProfit.append("ifnull("+orgStatisticalIndicators.getTableField()+",0) AS "+orgStatisticalIndicators.getField()+" ,");
145 if (bool.get("org_profit") == null) {
146 bool.put("org_profit", orgStatisticalIndicators.getTableField());
147 }
148
149 orgBasicInformation.append("null as " + orgStatisticalIndicators.getField() + ",");
150 orgInformationCbrc.append("null as " + orgStatisticalIndicators.getField() + ",");
151 orgBalanceSheets.append("null as " + orgStatisticalIndicators.getField() + ",");
152 orgBusinessStructure.append("null as " + orgStatisticalIndicators.getField() + ",");
153
154 //行业平均
155 if (parameter.isAvg()) {
156 if("year".equals(parameter.getTimeStyle())){
157 orgProfit.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear AND reporting_month = '12' ) AS "+orgStatisticalIndicators.getField()+"Avg,");
158 }else{
159 orgProfit.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
160 }
161
162 orgBasicInformation.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
163 orgInformationCbrc.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
164 orgBalanceSheets.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
165 orgBusinessStructure.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
166
167
168
169 }
170
171 } else if ("org_balance_sheets".equals(orgStatisticalIndicators.getTableName())) {
172 orgBalanceSheets.append("ifnull("+orgStatisticalIndicators.getTableField()+",0) AS "+orgStatisticalIndicators.getField()+" ,");
173 if (bool.get("org_balance_sheets") == null) {
174 bool.put("org_balance_sheets", orgStatisticalIndicators.getTableField());
175 }
176
177
178 orgBasicInformation.append("null as " + orgStatisticalIndicators.getField() + ",");
179 orgInformationCbrc.append("null as " + orgStatisticalIndicators.getField() + ",");
180 orgBusinessStructure.append("null as " + orgStatisticalIndicators.getField() + ",");
181 orgProfit.append("null as " + orgStatisticalIndicators.getField() + ",");
182
183 //行业平均
184 if (parameter.isAvg()) {
185 if("year".equals(parameter.getTimeStyle())){
186 orgBalanceSheets.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear AND reporting_month = '12' ) AS "+orgStatisticalIndicators.getField()+"Avg,");
187 }else{
188 orgBalanceSheets.append("(SELECT avg("+orgStatisticalIndicators.getTableField()+") FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
189 }
190
191
192 orgProfit.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
193 orgInformationCbrc.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
194 orgBalanceSheets.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
195 orgBusinessStructure.append("(SELECT avg(null) FROM "+orgStatisticalIndicators.getTableName()+" where reporting_year = reportingYear and reporting_month = reportingMonth) AS "+orgStatisticalIndicators.getField()+"Avg,");
196
197 }
198 }
199 if (parameter.isAvg()==true) {
200 fieldNames.add(orgStatisticalIndicators.getField());
201 fieldNames.add(orgStatisticalIndicators.getField()+"Avg");
202 } else {
203 fieldNames.add(orgStatisticalIndicators.getField());
204 }
205
206 }
207
208 }
209 }
210
211
212 //拼接where条件
213 StringBuilder whereSql = new StringBuilder(" WHERE 1 = 1");
214
215
216 if("year".equals(parameter.getTimeStyle())){
217 whereSql.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' ");
218 }else{
219 whereSql.append(" and CONCAT(reporting_year , '-' ,Right(100+CAST(reporting_month as SIGNED),2) )>= :startYear and CONCAT(reporting_year , '-' ,Right(100+CAST(reporting_month as SIGNED),2) ) <= :endYear");
220 }
221
222 //获取所有机构id
223 List parameterCompanyIds = parameter.getCompanyIds();
224 //如果机构id不为空
225 if (parameterCompanyIds.size()>0) {
226 whereSql.append(" AND company_id in ( ");
227
228
229 for (int i = 0; i < parameterCompanyIds.size(); i++) {
230 whereSql.append(":s"+i+" ,");
231 }
232
233 whereSql.deleteCharAt(whereSql.length()-1);
234 whereSql.append(" )");
235 }
236
237 //定义Query
238 Query orgBalanceSheetsQuery = null;
239
240
241
242 //拼接五张表和条件
243 orgBalanceSheets.deleteCharAt(orgBalanceSheets.length()-1);
244 orgBalanceSheets.append(" from org_balance_sheets ");
245 orgBalanceSheets.append(whereSql);
246
247 orgBasicInformation.deleteCharAt(orgBasicInformation.length()-1);
248 orgBasicInformation.append(" from org_basic_information ");
249 orgBasicInformation.append(whereSql);
250
251 orgBusinessStructure.deleteCharAt(orgBusinessStructure.length()-1);
252 orgBusinessStructure.append(" from org_business_structure ");
253 orgBusinessStructure.append(whereSql);
254
255 orgInformationCbrc.deleteCharAt(orgInformationCbrc.length()-1);
256 orgInformationCbrc.append(" from org_information_cbrc ");
257 orgInformationCbrc.append(whereSql);
258
259
260 orgProfit.deleteCharAt(orgProfit.length()-1);
261 orgProfit.append(" from org_profit ");
262 orgProfit.append(whereSql);
263
264
265 //关联五张表
266 orgBalanceSheets.append(" UNION ");
267 orgBalanceSheets.append(orgBasicInformation.toString());
268
269 orgBalanceSheets.append(" UNION ");
270 orgBalanceSheets.append(orgBusinessStructure.toString());
271
272 orgBalanceSheets.append(" UNION ");
273 orgBalanceSheets.append(orgInformationCbrc.toString());
274
275 orgBalanceSheets.append(" UNION ");
276 orgBalanceSheets.append(orgProfit.toString());
277
278
279 System.out.println(">>"+orgBalanceSheets.toString());
280
281
282 //创建本地sql查询实例
283 orgBalanceSheetsQuery = entityManager.createNativeQuery(orgBalanceSheets.toString());
284
285 //如果时间为空那就获取现在的时间
286 if(parameter.getEndTime() == null){
287 parameter.setEndTime(new Date());
288 }
289 if(parameter.getStartTime() == null){
290 parameter.setStartTime(new Date());
291 }
292
293
294 if("year".equals(parameter.getTimeStyle())){
295
296 orgBalanceSheetsQuery.setParameter("startYear", com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getStartTime(),"yyyy"));
297
298 orgBalanceSheetsQuery.setParameter("endYear", com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getEndTime(),"yyyy"));
299 }else if("month".equals(parameter.getTimeStyle())){
300
301
302 orgBalanceSheetsQuery.setParameter("startYear", com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getStartTime(),"yyyy-MM"));
303
304 orgBalanceSheetsQuery.setParameter("endYear", com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getEndTime(),"yyyy-MM"));
305
306
307 }
308
309
310
311
312 if (parameterCompanyIds.size()>0) {
313
314 for (int i = 0; i < parameterCompanyIds.size(); i++) {
315 orgBalanceSheetsQuery.setParameter("s"+i, parameterCompanyIds.get(i));
316 }
317 }
318
319
320 //获取数据
321 List resultList = orgBalanceSheetsQuery.getResultList();
322
323
324 System.out.println("resultList==="+resultList);
325
326 //给数据设置属性
327 for (int i = 0; i < resultList.size(); i++) {
328 Object o = resultList.get(i);
329 Object[] cells = (Object[]) o;
330 Map<String,Object> map = new HashMap<>();
331 if(cells.length == 3){
332 continue;
333 }
334 for (int j = 0; j<cells.length; j++) {
335
336 if (cells[j] != null && !"".equals(cells[j].toString())) {
337 map.put((String) fieldNames.get(j),cells[j]);
338 }else{
339 setField(resultList,fieldNames,map,i,j);
340 }
341
342 }
343 result.add(map);
344 }
345
346 System.out.println("result == "+result);
347
348
349 List<DefinedReportFormVO> definedReportFormVOList = new ArrayList<>();
350 Map<String,List> stringListMap = new HashMap<>();
351
352
353
354 //定义返回的格式
355 for (Map<String, Object> map : result) {
356 String reportingYear = (String) map.get("reportingYear");
357 String reportingMonth = (String) map.get("reportingMonth");
358 String reportingDate = reportingYear+"-"+reportingMonth;
359 //如果时间类型是年
360 if ("year".equals(parameter.getTimeStyle())) {
361 List list = stringListMap.get(reportingYear);
362 if (list != null) {
363 list.add(map);
364 stringListMap.put(reportingYear,list);
365 }else{
366 List inner =new ArrayList();
367 inner.add(map);
368 stringListMap.put(reportingYear,inner);
369 }
370 }else{//如果为月
371
372 List list = stringListMap.get(reportingDate);
373 if (list != null) {
374 list.add(map);
375 stringListMap.put(reportingDate,list);
376 }else{
377 List inner =new ArrayList();
378 inner.add(map);
379 stringListMap.put(reportingDate,inner);
380 }
381 }
382
383 }
384
385 System.out.println("stringListMap == "+stringListMap);
386
387
388 for (Map.Entry<String,List> entry : stringListMap.entrySet()) {
389 DefinedReportFormVO formVO = new DefinedReportFormVO();
390 formVO.setTime(entry.getKey());
391
392 if(parameter.isAvg()==true){
393 formVO.setArr(setAvg(entry.getValue(),fieldNames));
394 }else{
395 formVO.setArr(entry.getValue());
396 }
397
398 definedReportFormVOList.add(formVO);
399
400 }
401
402
403 return definedReportFormVOList;
404 }View Code
指标实体
1 /**
2 * 统计指标
3 */
4 @Entity
5 @Table(name = "org_statistical_indicators", catalog = "zhsupervision")
6 public class OrgStatisticalIndicators {
7 @Id
8 @GeneratedValue
9 private Long id;
10 /**
11 * 前端显示名
12 */
13 private String name;
14 /**
15 * 表属性
16 */
17 private String tableField;
18 /**
19 * 表名称
20 */
21 private String tableName;
22 /**
23 * 创建时间
24 */
25 private Date createTime;
26 /**
27 * 更新时间
28 */
29 private Date updateTime;
30 /**
31 * 删除标识
32 */
33 private String delFlag;
34 //父节点
35 private Long pId;
36 //属性
37 private String field;
38 //该指标查询月的时候是否查询
39 private String monthQuery;
40 //该指标查询年的时候是否查询
41 private String yearQuery;
42
43 public String getMonthQuery() {
44 return monthQuery;
45 }
46
47 public void setMonthQuery(String monthQuery) {
48 this.monthQuery = monthQuery;
49 }
50
51 public String getYearQuery() {
52 return yearQuery;
53 }
54
55 public void setYearQuery(String yearQuery) {
56 this.yearQuery = yearQuery;
57 }
58
59 public String getField() {
60 return field;
61 }
62
63 public void setField(String field) {
64 this.field = field;
65 }
66
67 public Long getId() {
68 return id;
69 }
70
71 public void setId(Long id) {
72 this.id = id;
73 }
74
75 public Long getpId() {
76 return pId;
77 }
78
79 public void setpId(Long pId) {
80 this.pId = pId;
81 }
82
83 public String getName() {
84 return name;
85 }
86
87 public void setName(String name) {
88 this.name = name;
89 }
90
91 public String getTableField() {
92 return tableField;
93 }
94
95 public void setTableField(String tableField) {
96 this.tableField = tableField;
97 }
98
99 public String getTableName() {
100 return tableName;
101 }
102
103 public void setTableName(String tableName) {
104 this.tableName = tableName;
105 }
106
107 public Date getCreateTime() {
108 return createTime;
109 }
110
111 public void setCreateTime(Date createTime) {
112 this.createTime = createTime;
113 }
114
115 public Date getUpdateTime() {
116 return updateTime;
117 }
118
119 public void setUpdateTime(Date updateTime) {
120 this.updateTime = updateTime;
121 }
122
123 public String getDelFlag() {
124 return delFlag;
125 }
126
127 public void setDelFlag(String delFlag) {
128 this.delFlag = delFlag;
129 }
130
131
132 }View Code
指标Service
1 /**
2 * 统计指标服务类
3 */
4 public interface OrgStatisticalIndicatorsService {
5 /**
6 * 根据id获取
7 * @param id
8 * @return
9 */
10 OrgStatisticalIndicators findOrgStatisticalIndicatorsById(Long id);
11
12 /**
13 * 根据表名查询
14 */
15 List<OrgStatisticalIndicators> findOrgStatisticalIndicatorsByTableName(String name);
16
17 }View Code
指标serviceImpl
1 @Service
2 public class OrgStatisticalIndicatorsServiceImpl extends BaseServiceImpl<OrgStatisticalIndicators, String> implements OrgStatisticalIndicatorsService {
3
4 @Autowired
5 private OrgStatisticalIndicatorsRespository respository;
6
7 @Override
8 public OrgStatisticalIndicators findOrgStatisticalIndicatorsById(Long id) {
9 return respository.findByIdAndAndDelFlag(id);
10 }
11
12 @Override
13 public List<OrgStatisticalIndicators> findOrgStatisticalIndicatorsByTableName(String name) {
14 return respository.findOrgStatisticalIndicatorsByTableName(name);
15 }
16 }View Code
指标repository
1 public interface OrgStatisticalIndicatorsRespository extends JpaSpecificationExecutor {
2
3 @Query(value = "select * from org_statistical_indicators WHERE ID=?1 and del_flag = '0'",nativeQuery = true)
4 OrgStatisticalIndicators findByIdAndAndDelFlag(Long id);
5
6 @Query(value = "select * from org_statistical_indicators WHERE del_flag = '0' and NAME =?1",nativeQuery = true)
7 OrgStatisticalIndicators findOrgStatisticalIndicatorsByName(String name);
8
9 }View Code
这个repository要继承 extends JpaRepository<T, ID> 才可以，写漏了。
上面使用了union 进行表之间的关联查询，关联的表有点多，所以代码有些长，同时因为表多，指标（表的属性）有500多个，无法确定查询的返回实体，所以只能自己根据数据的返回给数据绑定属性。
写完之后我发现语句太长并且嵌套了子查询后执行的时间也变长了，有时候还会卡，所以我优化了一下，指标查询指标，统计查询统计这样的执行时间就变短了，而且后来需求有所改变，有区分年查询和月查询。以下是我对实现类和指标实体的修改。
指标实体：
1 /**
2 * 统计指标
3 */
4 @Entity
5 @Table(name = "org_statistical_indicators", catalog = "zhsupervision")
6 public class OrgStatisticalIndicators {
7 @Id
8 @GeneratedValue
9 private Long id;
10 /**
11 * 前端显示名
12 */
13 private String name;
14 /**
15 * 表属性
16 */
17 private String tableField;
18 /**
19 * 表名称
20 */
21 private String tableName;
22 /**
23 * 创建时间
24 */
25 private Date createTime;
26 /**
27 * 更新时间
28 */
29 private Date updateTime;
30 /**
31 * 删除标识
32 */
33 private String delFlag;
34
35 private Long pId;
36
37 private String field;
38
39 private String monthQuery;
40
41 private String yearQuery;
42
43 private String isQuery;
44
45 private String avgQuery;
46
47
48
49
50 public String getAvgQuery() {
51 return avgQuery;
52 }
53
54 public void setAvgQuery(String avgQuery) {
55 this.avgQuery = avgQuery;
56 }
57
58 public String getIsQuery() {
59 return isQuery;
60 }
61
62 public void setIsQuery(String isQuery) {
63 this.isQuery = isQuery;
64 }
65
66 public String getMonthQuery() {
67 return monthQuery;
68 }
69
70 public void setMonthQuery(String monthQuery) {
71 this.monthQuery = monthQuery;
72 }
73
74 public String getYearQuery() {
75 return yearQuery;
76 }
77
78 public void setYearQuery(String yearQuery) {
79 this.yearQuery = yearQuery;
80 }
81
82 public String getField() {
83 return field;
84 }
85
86 public void setField(String field) {
87 this.field = field;
88 }
89
90 public Long getId() {
91 return id;
92 }
93
94 public void setId(Long id) {
95 this.id = id;
96 }
97
98 public Long getpId() {
99 return pId;
100 }
101
102 public void setpId(Long pId) {
103 this.pId = pId;
104 }
105
106 public String getName() {
107 return name;
108 }
109
110 public void setName(String name) {
111 this.name = name;
112 }
113
114 public String getTableField() {
115 return tableField;
116 }
117
118 public void setTableField(String tableField) {
119 this.tableField = tableField;
120 }
121
122 public String getTableName() {
123 return tableName;
124 }
125
126 public void setTableName(String tableName) {
127 this.tableName = tableName;
128 }
129
130 public Date getCreateTime() {
131 return createTime;
132 }
133
134 public void setCreateTime(Date createTime) {
135 this.createTime = createTime;
136 }
137
138 public Date getUpdateTime() {
139 return updateTime;
140 }
141
142 public void setUpdateTime(Date updateTime) {
143 this.updateTime = updateTime;
144 }
145
146 public String getDelFlag() {
147 return delFlag;
148 }
149
150 public void setDelFlag(String delFlag) {
151 this.delFlag = delFlag;
152 }
153 }View Code
实现类Impl：
1 @SuppressWarnings("unchecked")
2 @Override
3 public List<DefinedReportFormVO> defindQuery(DefinedReportFormDTO parameter) {
4
5
6 /**
7 * 定义五张表的查询字符串，年月，和机构id默认查询
8 */
9 StringBuilder orgInformationCbrc = new StringBuilder("select ID as cbrcId, reporting_year as reportingYear,reporting_month as reportingMonth, company_id as companyId,");
10 StringBuilder orgBasicInformation = new StringBuilder("select ID as basicId ,reporting_year as reportingYear,reporting_month as reportingMonth, company_id as companyId,");
11 StringBuilder orgBusinessStructure = new StringBuilder("select ID as businessId ,reporting_year as reportingYear,reporting_month as reportingMonth, company_id as companyId,");
12 StringBuilder orgProfit = new StringBuilder("select ID as profitId ,reporting_year as reportingYear,reporting_month as reportingMonth, company_id as companyId ,");
13 StringBuilder orgBalanceSheets = new StringBuilder("select ID as balanceId, reporting_year as reportingYear,reporting_month as reportingMonth, company_id as companyId ,");
14 /**
15 * 平均数sql
16 */
17 StringBuilder orgInformationCbrcAvg = new StringBuilder("select reporting_year ,reporting_month , ");
18 StringBuilder orgBasicInformationAvg = new StringBuilder("select reporting_year ,reporting_month , ");
19 StringBuilder orgBusinessStructureAvg = new StringBuilder("select reporting_year ,reporting_month , ");
20 StringBuilder orgProfitAvg = new StringBuilder("select reporting_year ,reporting_month , ");
21 StringBuilder orgBalanceSheetsAvg = new StringBuilder("select reporting_year ,reporting_month , ");
22
23
24 //指标名
25 List<String> fieldNames = new ArrayList();
26
27 //指标名默认添加年月机构id
28 fieldNames.add("id");
29 fieldNames.add("reportingYear");
30 fieldNames.add("reportingMonth");
31 fieldNames.add("companyId");
32
33 //记录是哪一个表的平均数
34 List<String> orgInformationCbrcAvgField = new ArrayList<>();
35 List<String> orgBasicInformationAvgField = new ArrayList<>();
36 List<String> orgBusinessStructureAvgField = new ArrayList<>();
37 List<String> orgProfitAvgField = new ArrayList<>();
38 List<String> orgBalanceSheetsAvgField = new ArrayList<>();
39
40
41 orgInformationCbrcAvgField.add("reportingYear");
42 orgInformationCbrcAvgField.add("reportingMonth");
43
44 orgBasicInformationAvgField.add("reportingYear");
45 orgBasicInformationAvgField.add("reportingMonth");
46
47 orgBusinessStructureAvgField.add("reportingYear");
48 orgBusinessStructureAvgField.add("reportingMonth");
49
50 orgProfitAvgField.add("reportingYear");
51 orgProfitAvgField.add("reportingMonth");
52
53 orgBalanceSheetsAvgField.add("reportingYear");
54 orgBalanceSheetsAvgField.add("reportingMonth");
55
56
57
58
59 //返回前端的结果集
60 List<DefinedReportFormVO> definedReportFormVOList = new ArrayList<>();
61 //指标结果集
62 Map<String,List<Map<String,Object>>> stringListMap = new HashMap<String,List<Map<String,Object>>>();
63 //指标Query
64 Query orgBalanceSheetsQuery = null;
65
66 //平均数结果集
67 Map<String,List<Map<String,Object>>> avgListMap = new HashMap<String,List<Map<String,Object>>>();
68
69 //获取机构id
70 List parameterCompanyIds = parameter.getCompanyIds();
71
72 List<OrgStatisticalIndicators> orgStatisticalIndicatorsByIds = orgStatisticalIndicatorsRespository.findOrgStatisticalIndicatorsByIds(parameter.getIds());
73
74 for (OrgStatisticalIndicators orgStatisticalIndicators : orgStatisticalIndicatorsByIds) {
75 String query = "";
76 String field = orgStatisticalIndicators.getField();
77 String isQuery = orgStatisticalIndicators.getIsQuery();
78 String isAvgQuery = orgStatisticalIndicators.getAvgQuery();
79 String tableName = orgStatisticalIndicators.getTableName();
80 /**
81 * 1.前端显示后端不查询
82 * 0.前端显示后端也查询
83 */
84 if("1".equals(isQuery)){
85 continue;
86 }
87
88 /**
89 * year.按照年份查询
90 * month.按照月查询
91 */
92 if("year".equals(parameter.getTimeStyle())){
93 query = orgStatisticalIndicators.getYearQuery();
94 }else{
95 query = orgStatisticalIndicators.getMonthQuery();
96 }
97 //如果结果为空跳过本次循环
98 if(query == null || "".equals(query)){
99 continue;
100 }
101
102 /**
103 * 判断指标所在的表，然后为各自的表拼接上表的字段
104 */
105 if ("org_information_cbrc".equals(tableName)) {
106
107 if(parameterCompanyIds.size()>0){
108 orgInformationCbrc.append(query+" ,");
109
110 //如果其他表不存在这个属性则为其他表拼接null
111 orgBasicInformation.append("null as "+field+",");
112 orgBalanceSheets.append("null as "+field+",");
113 orgBusinessStructure.append("null as "+field+",");
114 orgProfit.append("null as "+field+",");
115 }
116
117 //行业平均
118 if (parameter.isAvg() ==true && !"1".equals(isAvgQuery)) {
119 orgInformationCbrcAvg.append(" avg("+query+"),");
120 orgInformationCbrcAvgField.add(field);
121
122 }
123 } else if ("org_basic_information".equals(tableName)) {
124 if(parameterCompanyIds.size()>0){
125 orgBasicInformation.append(query+" ,");
126
127 orgInformationCbrc.append("null as "+field+",");
128 orgBalanceSheets.append("null as "+field+",");
129 orgBusinessStructure.append("null as "+field+",");
130 orgProfit.append("null as "+field+",");
131 }
132
133 //行业平均
134 if (parameter.isAvg() && !"1".equals(isAvgQuery)) {
135 orgBasicInformationAvg.append("avg("+query+"),");
136 orgBasicInformationAvgField.add(field);
137
138 }
139
140 } else if ("org_business_structure".equals(tableName)) {
141 if(parameterCompanyIds.size()>0){
142 orgBusinessStructure.append(query+" ,");
143
144 orgBasicInformation.append("null as "+field+",");
145 orgInformationCbrc.append("null as "+field+",");
146 orgBalanceSheets.append("null as "+field+",");
147 orgProfit.append("null as "+field+",");
148 }
149
150 //行业平均
151 if (parameter.isAvg() && !"1".equals(isAvgQuery)) {
152 orgBusinessStructureAvg.append("avg("+query+"),");
153 orgBusinessStructureAvgField.add(field);
154 }
155 } else if ("org_profit".equals(tableName)) {
156
157 if(parameterCompanyIds.size()>0){
158 orgProfit.append(query+" AS "+field+" ,");
159 orgBasicInformation.append("null as "+field+",");
160 orgInformationCbrc.append("null as "+field+",");
161 orgBalanceSheets.append("null as "+field+",");
162 orgBusinessStructure.append("null as "+field+",");
163 }
164
165 //行业平均
166 if (parameter.isAvg() && !"1".equals(isAvgQuery)) {
167 orgProfitAvg.append("avg("+query+"),");
168 orgProfitAvgField.add(field);
169 }
170 } else if ("org_balance_sheets".equals(tableName)) {
171 if(parameterCompanyIds.size()>0){
172 orgBalanceSheets.append(query+" ,");
173
174 orgBasicInformation.append("null as "+field+",");
175 orgInformationCbrc.append("null as "+field+",");
176 orgBusinessStructure.append("null as "+field+",");
177 orgProfit.append("null as "+field+",");
178 }
179
180 //行业平均
181 if (parameter.isAvg() == true && !"1".equals(isAvgQuery)) {
182
183 orgBalanceSheetsAvg.append("avg("+query+"),");
184 orgBalanceSheetsAvgField.add(field);
185
186 }
187 }else if("org_basic_info_list".equals(tableName)){//因为这几个字段关联的是其他表所以使用子查询
188
189 if(orgStatisticalIndicators.getName().startsWith("银行")){
190 if(parameterCompanyIds.size()>0){
191 orgBasicInformation.append("(SELECT "+query+" FROM "+tableName+" where org_basic_id = ID and type ='1' limit 1) ,");
192
193 orgInformationCbrc.append("null as "+field+",");
194 orgBalanceSheets.append("null as "+field+",");
195 orgBusinessStructure.append("null as "+field+",");
196 orgProfit.append("null as "+field+",");
197 }
198
199 if(parameter.isAvg() == true && !"1".equals(isAvgQuery)){
200 orgBasicInformationAvg.append("(SELECT avg("+query+") FROM "+tableName+" where org_basic_id = ID and type ='1' limit 1) ,");
201 }
202
203 }else if(orgStatisticalIndicators.getName().startsWith("资本")){
204 if(parameterCompanyIds.size()>0){
205 orgBasicInformation.append("(SELECT "+query+" FROM "+tableName+" where org_basic_id = ID and type ='0'limit 1) ,");
206
207 orgInformationCbrc.append("null as "+field+"1,");
208 orgBalanceSheets.append("null as "+field+"1,");
209 orgBusinessStructure.append("null as "+field+"1,");
210 orgProfit.append("null as "+field+"1,");
211 }
212 if(parameter.isAvg() == true && !"1".equals(isAvgQuery)){
213 orgBasicInformationAvg.append("(SELECT avg("+query+") FROM "+tableName+" where org_basic_id = ID and type ='0' limit 1) ,");
214 }
215 }
216
217 if (parameter.isAvg()==true && !"1".equals(isAvgQuery)) {
218 orgBasicInformationAvgField.add(field);
219 }
220
221 }
222 if (!"1".equals(isQuery)) {
223 if(parameterCompanyIds.size()>0){
224 fieldNames.add(field);
225 }
226 }
227
228 }
229
230 //拼接where条件
231 StringBuilder whereSql = new StringBuilder(" WHERE 1 = 1");
232
233
234 if("year".equals(parameter.getTimeStyle())){
235 whereSql.append(" AND reporting_year >= :startYear and reporting_year <= :endYear AND reporting_month = '12' ");
236 }else{
237 whereSql.append(" and CONCAT(reporting_year , '-' ,Right(100+CAST(reporting_month as SIGNED),2) )>= :startYear and CONCAT(reporting_year , '-' ,Right(100+CAST(reporting_month as SIGNED),2) ) <= :endYear");
238 }
239
240
241 //如果机构id不为空
242 if (parameterCompanyIds.size()>0) {
243 whereSql.append(" AND company_id in ( ");
244
245
246 for (int i = 0; i < parameterCompanyIds.size(); i++) {
247 whereSql.append(":s"+i+" ,");
248 }
249
250 whereSql.deleteCharAt(whereSql.length()-1);
251 whereSql.append(" )");
252 }
253
254
255 //拼接五张表和条件
256 orgBalanceSheets.deleteCharAt(orgBalanceSheets.length()-1);
257 orgBalanceSheets.append(" from org_balance_sheets ");
258 orgBalanceSheets.append(whereSql);
259
260 orgBasicInformation.deleteCharAt(orgBasicInformation.length()-1);
261 orgBasicInformation.append(" from org_basic_information ");
262 orgBasicInformation.append(whereSql);
263
264 orgBusinessStructure.deleteCharAt(orgBusinessStructure.length()-1);
265 orgBusinessStructure.append(" from org_business_structure ");
266 orgBusinessStructure.append(whereSql);
267
268 orgInformationCbrc.deleteCharAt(orgInformationCbrc.length()-1);
269 orgInformationCbrc.append(" from org_information_cbrc ");
270 orgInformationCbrc.append(whereSql);
271
272
273 orgProfit.deleteCharAt(orgProfit.length()-1);
274 orgProfit.append(" from org_profit ");
275 orgProfit.append(whereSql);
276
277
278 //关联五张表
279 orgBalanceSheets.append(" UNION ");
280 orgBalanceSheets.append(orgBasicInformation.toString());
281
282 orgBalanceSheets.append(" UNION ");
283 orgBalanceSheets.append(orgBusinessStructure.toString());
284
285 orgBalanceSheets.append(" UNION ");
286 orgBalanceSheets.append(orgInformationCbrc.toString());
287
288 orgBalanceSheets.append(" UNION ");
289 orgBalanceSheets.append(orgProfit.toString());
290
291 //如果有选机构
292 if(parameterCompanyIds.size() > 0){
293 //创建本地sql查询实例
294 orgBalanceSheetsQuery = entityManager.createNativeQuery(orgBalanceSheets.toString());
295
296 //如果时间为空那就获取现在的时间
297 if(parameter.getEndTime() == null){
298 parameter.setEndTime(new Date());
299 }
300 if(parameter.getStartTime() == null){
301 parameter.setStartTime(new Date());
302 }
303
304 if("year".equals(parameter.getTimeStyle())){
305
306 orgBalanceSheetsQuery.setParameter("startYear", com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getStartTime(),"yyyy"));
307
308 orgBalanceSheetsQuery.setParameter("endYear", com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getEndTime(),"yyyy"));
309 }else if("month".equals(parameter.getTimeStyle())){
310
311
312 orgBalanceSheetsQuery.setParameter("startYear", com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getStartTime(),"yyyy-MM"));
313
314 orgBalanceSheetsQuery.setParameter("endYear", com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getEndTime(),"yyyy-MM"));
315
316
317 }
318
319
320 if (parameterCompanyIds.size()>0) {
321
322 for (int i = 0; i < parameterCompanyIds.size(); i++) {
323 orgBalanceSheetsQuery.setParameter("s"+i, parameterCompanyIds.get(i));
324 }
325 }
326
327 //返回结果
328 List<Map<String,Object>> result = new ArrayList<>();
329
330 //获取数据
331 List resultList = orgBalanceSheetsQuery.getResultList();
332
333 dataEncapsulation(resultList, fieldNames, result);
334
335 //
336 for (Map<String, Object> map : result) {
337 String reportingYear = (String) map.get("reportingYear");
338 String reportingMonth = (String) map.get("reportingMonth");
339 String reportingDate = reportingYear+"-"+reportingMonth;
340 //如果时间类型是年
341 if ("year".equals(parameter.getTimeStyle())) {
342 List list = stringListMap.get(reportingYear);
343 if (list != null) {
344 list.add(map);
345 stringListMap.put(reportingYear,list);
346 }else{
347 List inner =new ArrayList();
348 inner.add(map);
349 stringListMap.put(reportingYear,inner);
350 }
351 }else{//如果为月
352
353 List list = stringListMap.get(reportingDate);
354 if (list != null) {
355 list.add(map);
356 stringListMap.put(reportingDate,list);
357 }else{
358 List inner =new ArrayList();
359 inner.add(map);
360 stringListMap.put(reportingDate,inner);
361 }
362 }
363
364 }
365
366 }
367
368
369 /**
370 * 平均数
371 */
372 if (parameter.isAvg() == true) {
373
374 //定义Query
375 Query avgQuerey = null;
376
377 String where = "";
378 /**
379 * 年月查询sql
380 * 1.年查询的是本年的第12月的数据
381 * 2.月查询是查询开始到结束的数据
382 */
383 if("month".equals(parameter.getTimeStyle())){
384 where = " where CONCAT( reporting_year,'-',RIGHT (100 + CAST(reporting_month AS SIGNED),2)) >= :startTime AND CONCAT( reporting_year,'-',RIGHT (100 + CAST(reporting_month AS SIGNED),2)) <= :endTime GROUP BY reporting_year,reporting_month ";
385 }else{
386 where = " where reporting_year >= :startTime AND reporting_year<= :endTime AND reporting_month = '12' GROUP BY reporting_year,reporting_month ";
387 }
388
389 String startTime = "";
390 String endTime = "";
391 /**
392 * 年查询和月查询所给时间赋的值是不一样的
393 */
394 if("year".equals(parameter.getTimeStyle())){
395 startTime = com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getStartTime(),"yyyy");
396 endTime = com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getEndTime(),"yyyy");
397 }else{
398 startTime = com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getStartTime(),"yyyy-MM");
399 endTime = com.honebay.spv.core.utils.DateUtil.formatDate(parameter.getEndTime(),"yyyy-MM");
400 }
401
402 //如果有这个集合添加的属性超过2个（年月默认添加了）
403 if(orgBalanceSheetsAvgField.size()>2){
404 orgBalanceSheetsAvg.deleteCharAt(orgBalanceSheetsAvg.length()-1);
405 orgBalanceSheetsAvg.append(" from org_balance_sheets ");
406 orgBalanceSheetsAvg.append(where);
407 avgSetValue(avgQuerey,orgBalanceSheetsAvg,startTime,endTime,orgBalanceSheetsAvgField,parameter.getTimeStyle(),avgListMap);
408 }
409
410 if(orgProfitAvgField.size()>2){
411 orgProfitAvg.deleteCharAt(orgProfitAvg.length()-1);
412 orgProfitAvg.append(" from org_profit ");
413 orgProfitAvg.append(where);
414 avgSetValue(avgQuerey,orgProfitAvg,startTime,endTime,orgProfitAvgField,parameter.getTimeStyle(),avgListMap);
415 }
416
417 if(orgBasicInformationAvgField.size() > 2){
418 orgBasicInformationAvg.deleteCharAt(orgBasicInformationAvg.length()-1);
419 orgBasicInformationAvg.append(" from org_basic_information ");
420 orgBasicInformationAvg.append(where);
421 avgSetValue(avgQuerey,orgBasicInformationAvg,startTime,endTime,orgBasicInformationAvgField,parameter.getTimeStyle(),avgListMap);
422 }
423
424 if(orgBusinessStructureAvgField.size() > 2){
425 orgBusinessStructureAvg.deleteCharAt(orgBusinessStructureAvg.length()-1);
426 orgBusinessStructureAvg.append(" from org_business_structure ");
427 orgBusinessStructureAvg.append(where);
428 avgSetValue(avgQuerey,orgBusinessStructureAvg,startTime,endTime,orgBusinessStructureAvgField,parameter.getTimeStyle(),avgListMap);
429 }
430
431 if(orgInformationCbrcAvgField.size() > 2){
432 orgInformationCbrcAvg.deleteCharAt(orgInformationCbrcAvg.length()-1);
433 orgInformationCbrcAvg.append(" from org_information_cbrc ");
434 orgInformationCbrcAvg.append(where);
435 avgSetValue(avgQuerey,orgInformationCbrcAvg,startTime,endTime,orgInformationCbrcAvgField,parameter.getTimeStyle(),avgListMap);
436 }
437
438 }
439 /**
440 * 1.如果指标查询的集合为空将平均数的集合赋值给它
441 * 2.如果都有数据就将他们的相同时间段的添加在一起
442 * 3.如果平均数为空就不用操作直接返回指标查询
443 */
444
445 if(stringListMap.size() == 0){
446 stringListMap.putAll(avgListMap);
447 }else if(stringListMap.size() > 0 && avgListMap.size() > 0){
448
449 for (Map.Entry<String, List<Map<String, Object>>> entry : stringListMap.entrySet()) {
450 System.out.println(entry.getValue()+"=="+entry.getKey());
451 List<Map<String, Object>> maps = avgListMap.get(entry.getKey());
452 if(maps != null){
453 List<Map<String, Object>> mapList = stringListMap.get(entry.getKey());
454 for (Map<String, Object> map : maps) {
455 mapList.add(map);
456 }
457
458 }
459 }
460
461 }
462
463
464 //绑定Vo对象
465 for (Map.Entry<String, List<Map<String, Object>>> entry : stringListMap.entrySet()) {
466 DefinedReportFormVO formVO = new DefinedReportFormVO();
467 formVO.setTime(entry.getKey());
468 formVO.setArr(entry.getValue());
469 definedReportFormVOList.add(formVO);
470 }
471
472 return definedReportFormVOList;
473 }
474
475
476 /**
477 * 平均数的sql执行、参数设置和结果格式化
478 * @param avgQuerey Query对象
479 * @param orgBalanceSheetsAvg sql
480 * @param startTime 开始时间
481 * @param endTime 结束时间
482 * @param orgBalanceSheetsField 属性集合
483 * @param timeStyle 时间类型
484 * @param stringListMap 最终结果集
485 */
486 public void avgSetValue(Query avgQuerey,StringBuilder orgBalanceSheetsAvg, String startTime,String endTime,List<String> orgBalanceSheetsField,String timeStyle,Map<String,List<Map<String,Object>>> stringListMap) {
487 //创建本地sql查询实例
488 avgQuerey = entityManager.createNativeQuery(orgBalanceSheetsAvg.toString());
489
490 avgQuerey.setParameter("startTime", startTime);
491
492 avgQuerey.setParameter("endTime", endTime);
493
494 List queryResultList = avgQuerey.getResultList();
495
496 avgDataEncapsulation(queryResultList, orgBalanceSheetsField,timeStyle,stringListMap);
497
498 }
499
500 /**
501 *
502 * @param resultList 执行sql得到的结果集
503 * @param field 属性集合
504 * @param result 最终得到的数据
505 */
506 public void dataEncapsulation(List resultList,List field , List<Map<String,Object>> result){
507 //给数据设置属性
508 for (int i = 0; i < resultList.size(); i++) {
509 Object o = resultList.get(i);
510 Object[] cells = (Object[]) o;
511 Map<String,Object> map = new HashMap<>();
512 if(cells.length <= 4){
513 continue;
514 }
515 for (int j = 0; j<cells.length; j++) {
516 if (cells[j] != null && !"".equals(cells[j].toString())) {
517 map.put((String) field.get(j),cells[j]);
518 }else{
519 setField(resultList,field,map,i,j);
520 }
521 }
522
523 result.add(map);
524 }
525
526 }
527
528 /**
529 * 平均数
530 * @param resultList 执行sql得到的结果集
531 * @param fieldNames 属性集合
532 * @param timeStyle 时间类型
533 * @param stringListMap 最终得到的结果集
534 */
535 public void avgDataEncapsulation(List resultList,List fieldNames ,String timeStyle,Map<String,List<Map<String,Object>>> stringListMap){
536
537 //返回结果
538 List<Map<String,Object>> result = new ArrayList<>();
539
540 //给数据设置属性
541 for (int i = 0; i < resultList.size(); i++) {
542 Object o = resultList.get(i);
543 Object[] cells = (Object[]) o;
544 Map<String,Object> map = new HashMap<>();
545
546 for (int j = 0; j<cells.length; j++) {
547 map.put((String) fieldNames.get(j),cells[j]);
548 }
549 //与前端协议好平均数的机构id赋值上avg
550 map.put("companyId","avg");
551 result.add(map);
552 }
553
554 for (Map<String, Object> map : result) {
555 //获取年月的数据
556 String reportingYear = (String) map.get("reportingYear");
557 String reportingMonth = (String) map.get("reportingMonth");
558 String reportingDate = reportingYear+"-"+reportingMonth;
559
560 //如果时间类型是年
561 if ("year".equals(timeStyle)) {
562 List<Map<String, Object>> list = stringListMap.get(reportingYear);
563 if (list != null) {
564 /**
565 * 将相同月份的属性合并在一起
566 */
567 Map<String, Object> objectMap = list.get(0);
568 objectMap.putAll(map);
569 stringListMap.put(reportingYear,list);
570 }else{
571 List<Map<String,Object>> inner =new ArrayList();
572 Map<String,Object> field = new HashMap();
573 for (Map.Entry<String, Object> entry : map.entrySet()) {
574 field.put(entry.getKey(),entry.getValue());
575 }
576 inner.add(field);
577 stringListMap.put(reportingYear,inner);
578 }
579 }else{//如果为月
580 //查看集合中是否存在该时间段
581 List<Map<String, Object>> list = stringListMap.get(reportingDate);
582 /**
583 * 如果存在，将原本的添加上现在的，不存在就新增一个list添加进去
584 */
585 if (list != null) {
586 Map<String, Object> objectMap = list.get(0);
587 objectMap.putAll(map);
588 stringListMap.put(reportingDate ,list);
589 }else{
590 List<Map<String,Object>> inner =new ArrayList();
591 Map<String,Object> field = new HashMap();
592 //循环将所有的属性添加进集合
593 for (Map.Entry<String, Object> entry : map.entrySet()) {
594 field.put(entry.getKey(),entry.getValue());
595 }
596 inner.add(field);
597 //设置一个新的时间段并添加上该数据
598 stringListMap.put(reportingDate,inner);
599 }
600 }
601
602 }
603 }
604
605
606
607
608 /**
609 * 设置属性
610 * @param resultList 结果集合
611 * @param fieldNames 属性集合
612 * @param map map封装
613 * @param num 当前第几个
614 * @param index 下标
615 */
616 public void setField(List resultList,List fieldNames,Map map,int num,int index){
617
618 int i = num;
619 i++;
620 if(i>=resultList.size()){
621 return;
622 }
623
624 Object o = resultList.get(i);
625 Object[] cells = (Object[]) o;
626 //判断当前这个位置是否存在值，
627 if (cells[index] != null && !"".equals(cells[index].toString())) {
628 if(i==resultList.size()){
629 map.put((String) fieldNames.get(index),null);
630 }
631 map.put((String) fieldNames.get(index),cells[index].toString());
632 }else{
633 setField(resultList,fieldNames,map,i,index);
634 }
635
636 }View Code
我这里是把平均查询和普通的指标的查询区分开了，对应的我对这些数据的操作就增多了。
我这里有用到机构，做的时候不用影响也不会很大，
以前发生一个请求要很久，现在比之前快了许多，所以有关的一些统计的查询和子查询还是要分开的好一点，不然会执行会很慢。可以分开查询，或者后端进行统计之类的。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
