前言
之前有做个一个自定义报表的查询，这里使用的是一个动态的sql拼接，是前端选择了什么指标就查询什么信息！（这里的指标是多个表的字段，前端随便选择了这些指标，然后后端根据这些指标拼接sql，返回这些指标的数据）。
参数接受dto
|
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
|
public class definedreportformdto { /** * 指标id */ private list ids; /** * 开始时间 */ @datetimeformat(pattern = "yyyy-mm") private date starttime; /** * 结束时间 */ @datetimeformat(pattern = "yyyy-mm") private date endtime; /** * 频率 */ private string timestyle; private boolean avg =false; private string idsparam; private string companyidsparam; public void setcompanyidsparam(string companyidsparam) { this.companyidsparam = companyidsparam; } public void setidsparam(string idsparam) { this.idsparam = idsparam; } public string getcompanyidsparam() { return companyidsparam; } public string getidsparam() { return idsparam; } public boolean isavg() { return avg; } public void setavg(boolean avg) { this.avg = avg; } public date getstarttime() { return starttime; } public void setstarttime(date starttime) { this.starttime = starttime; } public date getendtime() { return endtime; } public void setendtime(date endtime) { this.endtime = endtime; } public string gettimestyle() { return timestyle; } public void settimestyle(string timestyle) { this.timestyle = timestyle; } public list getids() { return ids; } public void setids(list ids) { this.ids = ids; }} |
数据返回vo
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
public class definedreportformvo implements serializable { private string time; private list<map<string, object>> arr = new arraylist<>(); public string gettime() { return time; } public void settime(string time) { this.time = time; } public list<map<string, object>> getarr() { return arr; } public void setarr(list<map<string, object>> arr) { this.arr = arr; }} |
控制器controller
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
@getmapping("/report/defindreport") public jsonresponseext defindreport(definedreportformdto definedreportformdto){ //测试数据 list list1 = new arraylist<>(); list1.add("111"); definedreportformdto.setids(list1); definedreportformdto.settimestyle("month"); definedreportformdto.setavg(true); calendar instance = calendar.getinstance(); instance.set(2018,1,11); definedreportformdto.setstarttime(instance.gettime()); instance.settime(new date()); definedreportformdto.setendtime(instance.gettime()); return jsonresponseext.success(dataacquisitionfileinfoservice.defindquery(definedreportformdto)); } |
服务类service
|
1
2
3
4
5
|
public interface dataacquisitionfileinfoservice { list<definedreportformvo> defindquery(definedreportformdto parameter); } |
实现类serviceimpl
|
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
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
|
@suppresswarnings("unchecked") @override public list<definedreportformvo> defindquery(definedreportformdto parameter) { /** * 定义五张表的查询字符串，年月，和机构id默认查询 */ stringbuilder orginformationcbrc = new stringbuilder("select reporting_year as reportingyear,reporting_month as reportingmonth, company_id ,"); stringbuilder orgbasicinformation = new stringbuilder("select reporting_year as reportingyear,reporting_month as reportingmonth, company_id,"); stringbuilder orgbusinessstructure = new stringbuilder("select reporting_year as reportingyear,reporting_month as reportingmonth, company_id,"); stringbuilder orgprofit = new stringbuilder("select reporting_year as reportingyear,reporting_month as reportingmonth, company_id,"); stringbuilder orgbalancesheets = new stringbuilder("select reporting_year as reportingyear,reporting_month as reportingmonth, company_id,"); //定义机构的字符串 stringbuilder companyids = new stringbuilder(""); //查询所有机构 list<company> orglist = orgservice.getorglist(); //拼接所有机构的字符串（如果需要求平均数的话） for (company company : orglist) { companyids.append(company.getid()+","); } companyids.deletecharat(companyids.length()-1); //定义每个表的字符串判断 map<string ,string> bool = new hashmap<>(); //指标名 list<string> fieldnames = new arraylist(); //返回结果 list<map<string,object>> result = new arraylist<>(); //指标名默认添加年月机构id fieldnames.add("reportingyear"); fieldnames.add("reportingmonth"); fieldnames.add("companyid"); //定义指标id集合 list ids = parameter.getids(); //循环所有的指标 for (object id : ids) { //如果指标为空 if (!"".equals(id) && id != null) { //根据指标id查询指标 orgstatisticalindicators orgstatisticalindicators = orgstatisticalindicatorsrespository.findbyidandanddelflag(long.parselong(id.tostring())); if(("year".equals(parameter.gettimestyle()) && "0".equals(orgstatisticalindicators.getyearquery())) || ("month".equals(parameter.gettimestyle()) && "0".equals(orgstatisticalindicators.getmonthquery()))){ /** * 判断指标所在的表，然后为各自的表拼接上表的字段 */ if ("org_information_cbrc".equals(orgstatisticalindicators.gettablename())) { orginformationcbrc.append("ifnull("+orgstatisticalindicators.gettablefield()+",0) as "+orgstatisticalindicators.getfield()+" ,"); // if (bool.get("org_information_cbrc") == null) { bool.put("org_information_cbrc", orgstatisticalindicators.gettablefield()); } //如果其他表不存在这个属性则为其他表拼接null orgbasicinformation.append("null as " + orgstatisticalindicators.getfield() + ","); orgbalancesheets.append("null as " + orgstatisticalindicators.getfield() + ","); orgbusinessstructure.append("null as " + orgstatisticalindicators.getfield() + ","); orgprofit.append("null as " + orgstatisticalindicators.getfield() + ","); //行业平均 if (parameter.isavg()) { if("year".equals(parameter.gettimestyle())){ orginformationcbrc.append("(select avg("+orgstatisticalindicators.gettablefield()+") from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = '12' ) as "+orgstatisticalindicators.getfield()+"avg,"); }else{ orginformationcbrc.append("(select avg("+orgstatisticalindicators.gettablefield()+") from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = reportingmonth) as "+orgstatisticalindicators.getfield()+"avg,"); } orgbalancesheets.append("(select avg(null) from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = reportingmonth) as "+orgstatisticalindicators.getfield()+"avg,"); orgbasicinformation.append("(select avg(null) from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = reportingmonth) as "+orgstatisticalindicators.getfield()+"avg,"); orgbusinessstructure.append("(select avg(null) from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = reportingmonth) as "+orgstatisticalindicators.getfield()+"avg,"); orgprofit.append("(select avg(null) from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = reportingmonth) as "+orgstatisticalindicators.getfield()+"avg,"); } } else if ("org_basic_information".equals(orgstatisticalindicators.gettablename())) { if (bool.get("org_basic_information") == null) { bool.put("org_basic_information", orgstatisticalindicators.gettablefield()); } orgbasicinformation.append("ifnull("+orgstatisticalindicators.gettablefield()+",0) as "+orgstatisticalindicators.getfield()+" ,"); orginformationcbrc.append("null as " + orgstatisticalindicators.getfield() + ","); orgbalancesheets.append("null as " + orgstatisticalindicators.getfield() + ","); orgbusinessstructure.append("null as " + orgstatisticalindicators.getfield() + ","); orgprofit.append("null as " + orgstatisticalindicators.getfield() + ","); //行业平均 if (parameter.isavg()) { if("year".equals(parameter.gettimestyle())){ orgbasicinformation.append("(select avg("+orgstatisticalindicators.gettablefield()+") from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = '12' ) as "+orgstatisticalindicators.getfield()+"avg,"); }else{ orgbasicinformation.append("(select avg("+orgstatisticalindicators.gettablefield()+") from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = reportingmonth) as "+orgstatisticalindicators.getfield()+"avg,"); } orgprofit.append("(select avg(null) from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = reportingmonth) as "+orgstatisticalindicators.getfield()+"avg,"); orginformationcbrc.append("(select avg(null) from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = reportingmonth) as "+orgstatisticalindicators.getfield()+"avg,"); orgbalancesheets.append("(select avg(null) from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = reportingmonth) as "+orgstatisticalindicators.getfield()+"avg,"); orgbusinessstructure.append("(select avg(null) from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = reportingmonth) as "+orgstatisticalindicators.getfield()+"avg,"); } } else if ("org_business_structure".equals(orgstatisticalindicators.gettablename())) { orgbusinessstructure.append("ifnull("+orgstatisticalindicators.gettablefield()+",0) as "+orgstatisticalindicators.getfield()+" ,"); if (bool.get("org_business_structure") == null) { bool.put("org_business_structure", orgstatisticalindicators.gettablefield()); } orgbasicinformation.append("null as " + orgstatisticalindicators.getfield() + ","); orginformationcbrc.append("null as " + orgstatisticalindicators.getfield() + ","); orgbalancesheets.append("null as " + orgstatisticalindicators.getfield() + ","); orgprofit.append("null as " + orgstatisticalindicators.getfield() + ","); //行业平均 if (parameter.isavg()) { if("year".equals(parameter.gettimestyle())){ orgbusinessstructure.append("(select avg("+orgstatisticalindicators.gettablefield()+") from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = '12' ) as "+orgstatisticalindicators.getfield()+"avg,"); }else{ orgbusinessstructure.append("(select avg("+orgstatisticalindicators.gettablefield()+") from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = reportingmonth) as "+orgstatisticalindicators.getfield()+"avg,"); } orgprofit.append("(select avg(null) from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = reportingmonth) as "+orgstatisticalindicators.getfield()+"avg,"); orginformationcbrc.append("(select avg(null) from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = reportingmonth) as "+orgstatisticalindicators.getfield()+"avg,"); orgbalancesheets.append("(select avg(null) from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = reportingmonth) as "+orgstatisticalindicators.getfield()+"avg,"); orgbasicinformation.append("(select avg(null) from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = reportingmonth) as "+orgstatisticalindicators.getfield()+"avg,"); } } else if ("org_profit".equals(orgstatisticalindicators.gettablename())) { orgprofit.append("ifnull("+orgstatisticalindicators.gettablefield()+",0) as "+orgstatisticalindicators.getfield()+" ,"); if (bool.get("org_profit") == null) { bool.put("org_profit", orgstatisticalindicators.gettablefield()); } orgbasicinformation.append("null as " + orgstatisticalindicators.getfield() + ","); orginformationcbrc.append("null as " + orgstatisticalindicators.getfield() + ","); orgbalancesheets.append("null as " + orgstatisticalindicators.getfield() + ","); orgbusinessstructure.append("null as " + orgstatisticalindicators.getfield() + ","); //行业平均 if (parameter.isavg()) { if("year".equals(parameter.gettimestyle())){ orgprofit.append("(select avg("+orgstatisticalindicators.gettablefield()+") from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = '12' ) as "+orgstatisticalindicators.getfield()+"avg,"); }else{ orgprofit.append("(select avg("+orgstatisticalindicators.gettablefield()+") from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = reportingmonth) as "+orgstatisticalindicators.getfield()+"avg,"); } orgbasicinformation.append("(select avg(null) from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = reportingmonth) as "+orgstatisticalindicators.getfield()+"avg,"); orginformationcbrc.append("(select avg(null) from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = reportingmonth) as "+orgstatisticalindicators.getfield()+"avg,"); orgbalancesheets.append("(select avg(null) from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = reportingmonth) as "+orgstatisticalindicators.getfield()+"avg,"); orgbusinessstructure.append("(select avg(null) from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = reportingmonth) as "+orgstatisticalindicators.getfield()+"avg,"); } } else if ("org_balance_sheets".equals(orgstatisticalindicators.gettablename())) { orgbalancesheets.append("ifnull("+orgstatisticalindicators.gettablefield()+",0) as "+orgstatisticalindicators.getfield()+" ,"); if (bool.get("org_balance_sheets") == null) { bool.put("org_balance_sheets", orgstatisticalindicators.gettablefield()); } orgbasicinformation.append("null as " + orgstatisticalindicators.getfield() + ","); orginformationcbrc.append("null as " + orgstatisticalindicators.getfield() + ","); orgbusinessstructure.append("null as " + orgstatisticalindicators.getfield() + ","); orgprofit.append("null as " + orgstatisticalindicators.getfield() + ","); //行业平均 if (parameter.isavg()) { if("year".equals(parameter.gettimestyle())){ orgbalancesheets.append("(select avg("+orgstatisticalindicators.gettablefield()+") from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = '12' ) as "+orgstatisticalindicators.getfield()+"avg,"); }else{ orgbalancesheets.append("(select avg("+orgstatisticalindicators.gettablefield()+") from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = reportingmonth) as "+orgstatisticalindicators.getfield()+"avg,"); } orgprofit.append("(select avg(null) from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = reportingmonth) as "+orgstatisticalindicators.getfield()+"avg,"); orginformationcbrc.append("(select avg(null) from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = reportingmonth) as "+orgstatisticalindicators.getfield()+"avg,"); orgbalancesheets.append("(select avg(null) from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = reportingmonth) as "+orgstatisticalindicators.getfield()+"avg,"); orgbusinessstructure.append("(select avg(null) from "+orgstatisticalindicators.gettablename()+" where reporting_year = reportingyear and reporting_month = reportingmonth) as "+orgstatisticalindicators.getfield()+"avg,"); } } if (parameter.isavg()==true) { fieldnames.add(orgstatisticalindicators.getfield()); fieldnames.add(orgstatisticalindicators.getfield()+"avg"); } else { fieldnames.add(orgstatisticalindicators.getfield()); } } } } //拼接where条件 stringbuilder wheresql = new stringbuilder(" where 1 = 1"); if("year".equals(parameter.gettimestyle())){ wheresql.append(" and reporting_year >= :startyear and reporting_year <= :endyear and reporting_month = '12' "); }else{ 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"); } //获取所有机构id list parametercompanyids = parameter.getcompanyids(); //如果机构id不为空 if (parametercompanyids.size()>0) { wheresql.append(" and company_id in ( "); for (int i = 0; i < parametercompanyids.size(); i++) { wheresql.append(":s"+i+" ,"); } wheresql.deletecharat(wheresql.length()-1); wheresql.append(" )"); } //定义query query orgbalancesheetsquery = null; //拼接五张表和条件 orgbalancesheets.deletecharat(orgbalancesheets.length()-1); orgbalancesheets.append(" from org_balance_sheets "); orgbalancesheets.append(wheresql); orgbasicinformation.deletecharat(orgbasicinformation.length()-1); orgbasicinformation.append(" from org_basic_information "); orgbasicinformation.append(wheresql); orgbusinessstructure.deletecharat(orgbusinessstructure.length()-1); orgbusinessstructure.append(" from org_business_structure "); orgbusinessstructure.append(wheresql); orginformationcbrc.deletecharat(orginformationcbrc.length()-1); orginformationcbrc.append(" from org_information_cbrc "); orginformationcbrc.append(wheresql); orgprofit.deletecharat(orgprofit.length()-1); orgprofit.append(" from org_profit "); orgprofit.append(wheresql); //关联五张表 orgbalancesheets.append(" union "); orgbalancesheets.append(orgbasicinformation.tostring()); orgbalancesheets.append(" union "); orgbalancesheets.append(orgbusinessstructure.tostring()); orgbalancesheets.append(" union "); orgbalancesheets.append(orginformationcbrc.tostring()); orgbalancesheets.append(" union "); orgbalancesheets.append(orgprofit.tostring()); system.out.println(">>"+orgbalancesheets.tostring()); //创建本地sql查询实例 orgbalancesheetsquery = entitymanager.createnativequery(orgbalancesheets.tostring()); //如果时间为空那就获取现在的时间 if(parameter.getendtime() == null){ parameter.setendtime(new date()); } if(parameter.getstarttime() == null){ parameter.setstarttime(new date()); } if("year".equals(parameter.gettimestyle())){ orgbalancesheetsquery.setparameter("startyear", com.honebay.spv.core.utils.dateutil.formatdate(parameter.getstarttime(),"yyyy")); orgbalancesheetsquery.setparameter("endyear", com.honebay.spv.core.utils.dateutil.formatdate(parameter.getendtime(),"yyyy")); }else if("month".equals(parameter.gettimestyle())){ orgbalancesheetsquery.setparameter("startyear", com.honebay.spv.core.utils.dateutil.formatdate(parameter.getstarttime(),"yyyy-mm")); orgbalancesheetsquery.setparameter("endyear", com.honebay.spv.core.utils.dateutil.formatdate(parameter.getendtime(),"yyyy-mm")); } if (parametercompanyids.size()>0) { for (int i = 0; i < parametercompanyids.size(); i++) { orgbalancesheetsquery.setparameter("s"+i, parametercompanyids.get(i)); } } //获取数据 list resultlist = orgbalancesheetsquery.getresultlist(); system.out.println("resultlist==="+resultlist); //给数据设置属性 for (int i = 0; i < resultlist.size(); i++) { object o = resultlist.get(i); object[] cells = (object[]) o; map<string,object> map = new hashmap<>(); if(cells.length == 3){ continue; } for (int j = 0; j<cells.length; j++) { if (cells[j] != null && !"".equals(cells[j].tostring())) { map.put((string) fieldnames.get(j),cells[j]); }else{ setfield(resultlist,fieldnames,map,i,j); } } result.add(map); } system.out.println("result == "+result); list<definedreportformvo> definedreportformvolist = new arraylist<>(); map<string,list> stringlistmap = new hashmap<>(); //定义返回的格式 for (map<string, object> map : result) { string reportingyear = (string) map.get("reportingyear"); string reportingmonth = (string) map.get("reportingmonth"); string reportingdate = reportingyear+"-"+reportingmonth; //如果时间类型是年 if ("year".equals(parameter.gettimestyle())) { list list = stringlistmap.get(reportingyear); if (list != null) { list.add(map); stringlistmap.put(reportingyear,list); }else{ list inner =new arraylist(); inner.add(map); stringlistmap.put(reportingyear,inner); } }else{//如果为月 list list = stringlistmap.get(reportingdate); if (list != null) { list.add(map); stringlistmap.put(reportingdate,list); }else{ list inner =new arraylist(); inner.add(map); stringlistmap.put(reportingdate,inner); } } } system.out.println("stringlistmap == "+stringlistmap); for (map.entry<string,list> entry : stringlistmap.entryset()) { definedreportformvo formvo = new definedreportformvo(); formvo.settime(entry.getkey()); if(parameter.isavg()==true){ formvo.setarr(setavg(entry.getvalue(),fieldnames)); }else{ formvo.setarr(entry.getvalue()); } definedreportformvolist.add(formvo); } return definedreportformvolist; } |
指标实体
|
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
|
/** * 统计指标 */@entity@table(name = "org_statistical_indicators", catalog = "zhsupervision")public class orgstatisticalindicators { @id @generatedvalue private long id; /** * 前端显示名 */ private string name; /** * 表属性 */ private string tablefield; /** * 表名称 */ private string tablename; /** * 创建时间 */ private date createtime; /** * 更新时间 */ private date updatetime; /** * 删除标识 */ private string delflag; //父节点 private long pid; //属性 private string field; //该指标查询月的时候是否查询 private string monthquery; //该指标查询年的时候是否查询 private string yearquery; public string getmonthquery() { return monthquery; } public void setmonthquery(string monthquery) { this.monthquery = monthquery; } public string getyearquery() { return yearquery; } public void setyearquery(string yearquery) { this.yearquery = yearquery; } public string getfield() { return field; } public void setfield(string field) { this.field = field; } public long getid() { return id; } public void setid(long id) { this.id = id; } public long getpid() { return pid; } public void setpid(long pid) { this.pid = pid; } public string getname() { return name; } public void setname(string name) { this.name = name; } public string gettablefield() { return tablefield; } public void settablefield(string tablefield) { this.tablefield = tablefield; } public string gettablename() { return tablename; } public void settablename(string tablename) { this.tablename = tablename; } public date getcreatetime() { return createtime; } public void setcreatetime(date createtime) { this.createtime = createtime; } public date getupdatetime() { return updatetime; } public void setupdatetime(date updatetime) { this.updatetime = updatetime; } public string getdelflag() { return delflag; } public void setdelflag(string delflag) { this.delflag = delflag; }} |
指标service
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
/** * 统计指标服务类 */public interface orgstatisticalindicatorsservice { /** * 根据id获取 * @param id * @return */ orgstatisticalindicators findorgstatisticalindicatorsbyid(long id); /** * 根据表名查询 */ list<orgstatisticalindicators> findorgstatisticalindicatorsbytablename(string name);} |
指标serviceimpl
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
@servicepublic class orgstatisticalindicatorsserviceimpl extends baseserviceimpl<orgstatisticalindicators, string> implements orgstatisticalindicatorsservice { @autowired private orgstatisticalindicatorsrespository respository; @override public orgstatisticalindicators findorgstatisticalindicatorsbyid(long id) { return respository.findbyidandanddelflag(id); } @override public list<orgstatisticalindicators> findorgstatisticalindicatorsbytablename(string name) { return respository.findorgstatisticalindicatorsbytablename(name); }} |
指标repository
|
1
2
3
4
5
6
7
8
|
public interface orgstatisticalindicatorsrespository extends jpaspecificationexecutor { @query(value = "select * from org_statistical_indicators where id=?1 and del_flag = '0'",nativequery = true) orgstatisticalindicators findbyidandanddelflag(long id); @query(value = "select * from org_statistical_indicators where del_flag = '0' and name =?1",nativequery = true) orgstatisticalindicators findorgstatisticalindicatorsbyname(string name);} |
这个repository要继承 extends jparepository<t, id> 才可以，写漏了。
上面使用了union 进行表之间的关联查询，关联的表有点多，所以代码有些长，同时因为表多，指标（表的属性）有500多个，无法确定查询的返回实体，所以只能自己根据数据的返回给数据绑定属性。
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对服务器之家的支持。
原文链接：https://www.cnblogs.com/xiluonanfeng/p/10245974.html
