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 package fr.paris.lutece.portal.business.dashboard;
35
36 import fr.paris.lutece.portal.service.dashboard.IDashboardComponent;
37 import fr.paris.lutece.portal.service.util.AppLogService;
38 import fr.paris.lutece.util.sql.DAOUtil;
39
40 import java.util.ArrayList;
41 import java.util.List;
42
43
44
45
46
47
48 public class DashboardDAO implements IDashboardDAO
49 {
50 private static final String LOG_ERROR_NOT_FOUND = "Dashboard named {} not found";
51 private static final String SQL_QUERY_MAX_ORDER = "SELECT max(dashboard_order) FROM core_dashboard";
52 private static final String SQL_QUERY_MAX_ORDER_COLUMN = SQL_QUERY_MAX_ORDER + " WHERE dashboard_column = ? ";
53 private static final String SQL_QUERY_DELETE = "DELETE FROM core_dashboard ";
54 private static final String SQL_QUERY_DELETE_BY_NAME = SQL_QUERY_DELETE + " WHERE dashboard_name = ? ";
55 private static final String SQL_QUERY_SELECT = "SELECT dashboard_name, dashboard_order, dashboard_column FROM core_dashboard ";
56 private static final String SQL_QUERY_ORDER_BY_COLUMN_AND_ORDER = " ORDER BY dashboard_column, dashboard_order";
57 private static final String SQL_QUERY_SELECT_ALL = SQL_QUERY_SELECT + " WHERE dashboard_column != - 1 " + SQL_QUERY_ORDER_BY_COLUMN_AND_ORDER;
58 private static final String SQL_QUERY_SELECT_COLUMNS = "SELECT dashboard_column FROM core_dashboard GROUP BY dashboard_column";
59 private static final String SQL_QUERY_FILTER_COLUMN = " dashboard_column = ? ";
60 private static final String SQL_QUERY_FILTER_ORDER = " dashboard_order = ? ";
61 private static final String SQL_QUERY_FILTER_NAME = " dashboard_name = ? ";
62 private static final String SQL_QUERY_SELECT_BY_PRIMARY_KEY = SQL_QUERY_SELECT + " WHERE " + SQL_QUERY_FILTER_NAME;
63 private static final String SQL_QUERY_INSERT = "INSERT INTO core_dashboard( dashboard_name, dashboard_order, dashboard_column ) " + " VALUES(?,?,?)";
64 private static final String SQL_QUERY_UPDATE = "UPDATE core_dashboard " + " SET dashboard_order = ?, dashboard_column = ? WHERE dashboard_name = ?";
65 private static final String SQL_QUERY_KEYWORD_WHERE = " WHERE ";
66 private static final String SQL_QUERY_KEYWORD_AND = " AND ";
67
68
69
70
71 @Override
72 public void delete( String strBeanName )
73 {
74 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_NAME ) )
75 {
76
77 daoUtil.setString( 1, strBeanName );
78
79 daoUtil.executeUpdate( );
80
81 }
82 }
83
84
85
86
87 @Override
88 public void deleteAll( )
89 {
90 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE ) )
91 {
92
93 daoUtil.executeUpdate( );
94
95 }
96 }
97
98
99
100
101 @Override
102 public void insert( IDashboardComponent dashboardComponent )
103 {
104 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT ) )
105 {
106
107 daoUtil.setString( 1, dashboardComponent.getName( ) );
108 setInsertOrUpdateValues( 2, dashboardComponent, daoUtil );
109
110 daoUtil.executeUpdate( );
111
112 }
113 }
114
115
116
117
118
119
120
121
122
123 private IDashboardComponent findDashboardFromFactory( String strBeanName )
124 {
125 return DashboardFactory.getDashboardComponent( strBeanName );
126 }
127
128
129
130
131 @Override
132 public IDashboardComponent load( String strClassName )
133 {
134 IDashboardComponent dashboardComponent = null;
135
136 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_PRIMARY_KEY ) )
137 {
138
139 daoUtil.setString( 1, strClassName );
140
141 daoUtil.executeQuery( );
142
143 if ( daoUtil.next( ) )
144 {
145 String strBeanName = daoUtil.getString( 1 );
146
147 dashboardComponent = findDashboardFromFactory( strBeanName );
148
149 if ( dashboardComponent != null )
150 {
151 load( dashboardComponent, daoUtil );
152 }
153 else
154 {
155 AppLogService.error( LOG_ERROR_NOT_FOUND, strBeanName );
156 }
157 }
158
159 }
160
161 return dashboardComponent;
162 }
163
164
165
166
167 @Override
168 public List<IDashboardComponent> selectAllDashboardComponents( )
169 {
170 List<IDashboardComponent> listDashboards = new ArrayList<>( );
171
172 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL ) )
173 {
174
175 daoUtil.executeQuery( );
176
177 while ( daoUtil.next( ) )
178 {
179 IDashboardComponent dashboardComponent = null;
180
181 String strBeanName = daoUtil.getString( 1 );
182
183 dashboardComponent = findDashboardFromFactory( strBeanName );
184
185 if ( dashboardComponent != null )
186 {
187 load( dashboardComponent, daoUtil );
188 listDashboards.add( dashboardComponent );
189 }
190 else
191 {
192 AppLogService.error( LOG_ERROR_NOT_FOUND, strBeanName );
193 }
194 }
195
196 }
197
198 return listDashboards;
199 }
200
201
202
203
204 @Override
205 public int selectMaxOrder( )
206 {
207 int nMaxOrder = 0;
208 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_MAX_ORDER ) )
209 {
210 daoUtil.executeQuery( );
211
212 if ( daoUtil.next( ) )
213 {
214 nMaxOrder = daoUtil.getInt( 1 );
215 }
216
217 }
218
219 return nMaxOrder;
220 }
221
222
223
224
225 @Override
226 public int selectMaxOrder( int nColumn )
227 {
228 int nMaxOrder = 0;
229 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_MAX_ORDER_COLUMN ) )
230 {
231 daoUtil.setInt( 1, nColumn );
232
233 daoUtil.executeQuery( );
234
235 if ( daoUtil.next( ) )
236 {
237 nMaxOrder = daoUtil.getInt( 1 );
238 }
239
240 }
241
242 return nMaxOrder;
243 }
244
245
246
247
248 @Override
249 public List<IDashboardComponent> selectDashboardComponents( DashboardFilter filter )
250 {
251 List<IDashboardComponent> listDashboards = new ArrayList<>( );
252 StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT );
253 buildSQLFilter( sbSQL, filter );
254 sbSQL.append( SQL_QUERY_ORDER_BY_COLUMN_AND_ORDER );
255
256 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ) ) )
257 {
258
259 applySQLFilter( daoUtil, 1, filter );
260
261 daoUtil.executeQuery( );
262
263 while ( daoUtil.next( ) )
264 {
265 IDashboardComponent dashboardComponent = null;
266
267 String strBeanName = daoUtil.getString( 1 );
268
269 dashboardComponent = findDashboardFromFactory( strBeanName );
270
271 if ( dashboardComponent != null )
272 {
273 load( dashboardComponent, daoUtil );
274 listDashboards.add( dashboardComponent );
275 }
276 else
277 {
278 AppLogService.error( LOG_ERROR_NOT_FOUND, strBeanName );
279 }
280 }
281
282 }
283
284 return listDashboards;
285 }
286
287
288
289
290 @Override
291 public void store( IDashboardComponent dashboardComponent )
292 {
293 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE ) )
294 {
295
296 int nIndex = setInsertOrUpdateValues( 1, dashboardComponent, daoUtil );
297 daoUtil.setString( nIndex, dashboardComponent.getName( ) );
298
299 daoUtil.executeUpdate( );
300
301 }
302 }
303
304
305
306
307 @Override
308 public List<Integer> selectColumns( )
309 {
310 List<Integer> listColumns = new ArrayList<>( );
311
312 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_COLUMNS ) )
313 {
314
315 daoUtil.executeQuery( );
316
317 while ( daoUtil.next( ) )
318 {
319 listColumns.add( daoUtil.getInt( 1 ) );
320 }
321
322 }
323
324 return listColumns;
325 }
326
327
328
329
330
331
332
333
334
335 private void load( IDashboardComponent component, DAOUtil daoUtil )
336 {
337 int nIndex = 1;
338 component.setName( daoUtil.getString( nIndex++ ) );
339 component.setOrder( daoUtil.getInt( nIndex++ ) );
340 component.setZone( daoUtil.getInt( nIndex++ ) );
341 }
342
343
344
345
346
347
348
349
350
351
352
353
354 private int setInsertOrUpdateValues( int nStartIndex, IDashboardComponent component, DAOUtil daoUtil )
355 {
356 int nIndex = nStartIndex;
357 daoUtil.setInt( nIndex++, component.getOrder( ) );
358 daoUtil.setInt( nIndex++, component.getZone( ) );
359
360 return nIndex;
361 }
362
363
364
365
366
367
368
369
370
371 private void buildSQLFilter( StringBuilder sbSQL, DashboardFilter filter )
372 {
373 List<String> listFilters = new ArrayList<>( );
374
375 if ( filter.containsFilterOrder( ) )
376 {
377 listFilters.add( SQL_QUERY_FILTER_ORDER );
378 }
379
380 if ( filter.containsFilterColumn( ) )
381 {
382 listFilters.add( SQL_QUERY_FILTER_COLUMN );
383 }
384
385 if ( !listFilters.isEmpty( ) )
386 {
387 sbSQL.append( SQL_QUERY_KEYWORD_WHERE );
388
389 boolean bFirstFilter = true;
390
391 for ( String strFilter : listFilters )
392 {
393 sbSQL.append( strFilter );
394
395 if ( !bFirstFilter )
396 {
397 sbSQL.append( SQL_QUERY_KEYWORD_AND );
398 }
399 else
400 {
401 bFirstFilter = false;
402 }
403 }
404 }
405 }
406
407
408
409
410
411
412
413
414
415
416
417
418 private int applySQLFilter( DAOUtil daoUtil, int nStartIndex, DashboardFilter filter )
419 {
420 int nIndex = nStartIndex;
421
422 if ( filter.containsFilterOrder( ) )
423 {
424 daoUtil.setInt( nIndex++, filter.getFilterOrder( ) );
425 }
426
427 if ( filter.containsFilterColumn( ) )
428 {
429 daoUtil.setInt( nIndex++, filter.getFilterColumn( ) );
430 }
431
432 return nIndex;
433 }
434 }