前言
之前有做個一個自定義報表的查詢，這里使用的是一個動態的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
