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