前言
之前有做個(gè)一個(gè)自定義報(bào)表的查詢，這里使用的是一個(gè)動(dòng)態(tài)的sql拼接，是前端選擇了什么指標(biāo)就查詢什么信息！（這里的指標(biāo)是多個(gè)表的字段，前端隨便選擇了這些指標(biāo)，然后后端根據(jù)這些指標(biāo)拼接sql，返回這些指標(biāo)的數(shù)據(jù)）。
參數(shù)接受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 { /** * 指標(biāo)id */ private list ids; /** * 開始時(shí)間 */ @datetimeformat(pattern = "yyyy-mm") private date starttime; /** * 結(jié)束時(shí)間 */ @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; }} |
數(shù)據(jù)返回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){ //測(cè)試數(shù)據(jù) 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)); } |
服務(wù)類service
|
1
2
3
4
5
|
public interface dataacquisitionfileinfoservice { list<definedreportformvo> defindquery(definedreportformdto parameter); } |
實(shí)現(xiàn)類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) { /** * 定義五張表的查詢字符串，年月，和機(jī)構(gòu)id默認(rèn)查詢 */ 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,"); //定義機(jī)構(gòu)的字符串 stringbuilder companyids = new stringbuilder(""); //查詢所有機(jī)構(gòu) list<company> orglist = orgservice.getorglist(); //拼接所有機(jī)構(gòu)的字符串（如果需要求平均數(shù)的話） for (company company : orglist) { companyids.append(company.getid()+","); } companyids.deletecharat(companyids.length()-1); //定義每個(gè)表的字符串判斷 map<string ,string> bool = new hashmap<>(); //指標(biāo)名 list<string> fieldnames = new arraylist(); //返回結(jié)果 list<map<string,object>> result = new arraylist<>(); //指標(biāo)名默認(rèn)添加年月機(jī)構(gòu)id fieldnames.add("reportingyear"); fieldnames.add("reportingmonth"); fieldnames.add("companyid"); //定義指標(biāo)id集合 list ids = parameter.getids(); //循環(huán)所有的指標(biāo) for (object id : ids) { //如果指標(biāo)為空 if (!"".equals(id) && id != null) { //根據(jù)指標(biāo)id查詢指標(biāo) 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()))){ /** * 判斷指標(biāo)所在的表，然后為各自的表拼接上表的字段 */ 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()); } //如果其他表不存在這個(gè)屬性則為其他表拼接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() + ","); //行業(yè)平均 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() + ","); //行業(yè)平均 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() + ","); //行業(yè)平均 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() + ","); //行業(yè)平均 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() + ","); //行業(yè)平均 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"); } //獲取所有機(jī)構(gòu)id list parametercompanyids = parameter.getcompanyids(); //如果機(jī)構(gòu)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); //關(guān)聯(lián)五張表 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()); //創(chuàng)建本地sql查詢實(shí)例 orgbalancesheetsquery = entitymanager.createnativequery(orgbalancesheets.tostring()); //如果時(shí)間為空那就獲取現(xiàn)在的時(shí)間 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)); } } //獲取數(shù)據(jù) list resultlist = orgbalancesheetsquery.getresultlist(); system.out.println("resultlist==="+resultlist); //給數(shù)據(jù)設(shè)置屬性 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; //如果時(shí)間類型是年 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; } |
指標(biāo)實(shí)體
|
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
|
/** * 統(tǒng)計(jì)指標(biāo) */@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; /** * 創(chuàng)建時(shí)間 */ private date createtime; /** * 更新時(shí)間 */ private date updatetime; /** * 刪除標(biāo)識(shí) */ private string delflag; //父節(jié)點(diǎn) private long pid; //屬性 private string field; //該指標(biāo)查詢?cè)碌臅r(shí)候是否查詢 private string monthquery; //該指標(biāo)查詢年的時(shí)候是否查詢 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; }} |
指標(biāo)service
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
/** * 統(tǒng)計(jì)指標(biāo)服務(wù)類 */public interface orgstatisticalindicatorsservice { /** * 根據(jù)id獲取 * @param id * @return */ orgstatisticalindicators findorgstatisticalindicatorsbyid(long id); /** * 根據(jù)表名查詢 */ list<orgstatisticalindicators> findorgstatisticalindicatorsbytablename(string name);} |
指標(biāo)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); }} |
指標(biāo)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);} |
這個(gè)repository要繼承 extends jparepository<t, id> 才可以，寫漏了。
上面使用了union 進(jìn)行表之間的關(guān)聯(lián)查詢，關(guān)聯(lián)的表有點(diǎn)多，所以代碼有些長(zhǎng)，同時(shí)因?yàn)楸矶啵笜?biāo)（表的屬性）有500多個(gè)，無法確定查詢的返回實(shí)體，所以只能自己根據(jù)數(shù)據(jù)的返回給數(shù)據(jù)綁定屬性。
總結(jié)
以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問大家可以留言交流，謝謝大家對(duì)服務(wù)器之家的支持。
原文鏈接：https://www.cnblogs.com/xiluonanfeng/p/10245974.html
