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.portlet;
35
36 import fr.paris.lutece.portal.business.stylesheet.StyleSheet;
37 import fr.paris.lutece.util.ReferenceList;
38 import fr.paris.lutece.util.sql.DAOUtil;
39
40 import java.sql.Statement;
41 import java.sql.Timestamp;
42
43 import java.util.ArrayList;
44 import java.util.Collection;
45 import java.util.List;
46
47
48
49
50 public final class PortletDAO implements IPortletDAO
51 {
52
53 private static final String SQL_QUERY_UPDATE = " UPDATE core_portlet SET name = ?, date_update = ?, column_no = ?, "
54 + " portlet_order = ? , id_style = ? , id_page = ?, accept_alias = ? , display_portlet_title = ?, role = ?, device_display_flags = ? "
55 + " WHERE id_portlet = ?";
56 private static final String SQL_QUERY_SELECT = " SELECT b.id_portlet_type, a.id_page, a.id_style, a.name , b.name, "
57 + " b.url_creation, b.url_update, a.date_update, a.column_no, a.portlet_order, "
58 + " b.home_class, a.accept_alias , a.role , b.plugin_name , a.display_portlet_title, a.status, a.device_display_flags "
59 + " FROM core_portlet a , core_portlet_type b WHERE a.id_portlet_type = b.id_portlet_type AND a.id_portlet = ?";
60 private static final String SQL_QUERY_SELECT_ALIAS = " SELECT a.id_portlet FROM core_portlet a, core_portlet_alias b "
61 + " WHERE a.id_portlet = b.id_portlet AND b.id_alias= ? ";
62 private static final String SQL_QUERY_DELETE = "DELETE FROM core_portlet WHERE id_portlet = ?";
63 private static final String SQL_QUERY_UPDATE_STATUS = " UPDATE core_portlet SET status = ?, date_update = ? WHERE id_portlet = ? ";
64 private static final String SQL_QUERY_UPDATE_POSITION = " UPDATE core_portlet SET column_no = ?, portlet_order = ? WHERE id_portlet = ? ";
65 private static final String SQL_QUERY_INSERT = " INSERT INTO core_portlet ( id_portlet_type, id_page, id_style, name, "
66 + " date_creation, date_update, status, column_no, portlet_order, accept_alias, display_portlet_title, role, device_display_flags ) "
67 + " VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? , ?, ?, ?)";
68 private static final String SQL_QUERY_SELECT_PORTLET_LIST_BY_STYLE = "SELECT id_portlet, name, id_page FROM core_portlet WHERE id_style=?";
69 private static final String SQL_QUERY_SELECT_PORTLET_LIST_BY_ROLE = "SELECT id_portlet, name, id_page FROM core_portlet WHERE role=?";
70 private static final String SQL_QUERY_SELECT_XSL_FILE = " SELECT a.id_stylesheet , a.description , a.file_name, a.source "
71 + " FROM core_stylesheet a, core_portlet b, core_style_mode_stylesheet c " + " WHERE a.id_stylesheet = c.id_stylesheet "
72 + " AND b.id_style = c.id_style AND b.id_portlet = ? AND c.id_mode = ? ";
73 private static final String SQL_QUERY_SELECT_STYLE_LIST = " SELECT distinct a.id_style , a.description_style "
74 + " FROM core_style a , core_style_mode_stylesheet b " + " WHERE a.id_style = b.id_style "
75 + " AND a.id_portlet_type = ? ORDER BY a.description_style";
76 private static final String SQL_QUERY_SELECT_PORTLET_TYPE = " SELECT id_portlet_type , name , url_creation, url_update, plugin_name "
77 + " FROM core_portlet_type WHERE id_portlet_type = ? ORDER BY id_portlet_type ";
78 private static final String SQL_QUERY_SELECT_PORTLET_ALIAS = " SELECT a.id_portlet FROM core_portlet a , core_portlet_alias b"
79 + " WHERE a.id_portlet = b.id_portlet " + " AND b.id_alias= ? ";
80 private static final String SQL_QUERY_SELECT_ALIASES_FOR_PORTLET = "SELECT p.id_portlet, p.id_page, p.name "
81 + "FROM core_portlet_alias a JOIN core_portlet p ON p.id_portlet = a.id_portlet WHERE a.id_alias = ? ";
82 private static final String SQL_QUERY_SELECT_PORTLET_LIST_BY_NAME = " SELECT id_portlet , id_page , name FROM core_portlet WHERE name LIKE ? ";
83 private static final String SQL_QUERY_SELECT_PORTLET_LIST_BY_TYPE = " SELECT a.id_portlet, a.id_portlet_type, a.id_page, a.name, "
84 + "a.date_update, a.status, a.portlet_order, a.column_no, a.id_style, a.accept_alias, a.date_creation, a.display_portlet_title, a.role, a.device_display_flags "
85 + " FROM core_portlet a, core_page b WHERE a.id_page = b.id_page " + " AND a.id_portlet_type = ? ";
86 private static final String SQL_QUERY_SELECT_LAST_MODIFIED_PORTLET = " SELECT a.id_portlet, b.id_portlet_type, a.id_page, a.id_style, a.name , b.name, "
87 + " b.url_creation, b.url_update, a.date_update, a.column_no, a.portlet_order, "
88 + " b.home_class, a.accept_alias , a.role , b.plugin_name , a.display_portlet_title, a.status , a.device_display_flags "
89 + " FROM core_portlet a , core_portlet_type b WHERE a.id_portlet_type = b.id_portlet_type ORDER BY a.date_update DESC LIMIT 1 ";
90 private static final String SQL_QUERY_SELECT_ORDER_FROM_PAGE_AND_COLUMN = " SELECT portlet_order FROM core_portlet WHERE column_no = ? AND id_page = ? ORDER BY portlet_order";
91
92
93
94
95
96
97
98 public void insert( Portlet portlet )
99 {
100 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS ) )
101 {
102 int nIndex = 1;
103 daoUtil.setString( nIndex++, portlet.getPortletTypeId( ) );
104 daoUtil.setInt( nIndex++, portlet.getPageId( ) );
105 daoUtil.setInt( nIndex++, portlet.getStyleId( ) );
106 daoUtil.setString( nIndex++, portlet.getName( ) );
107 daoUtil.setTimestamp( nIndex++, new Timestamp( new java.util.Date( ).getTime( ) ) );
108 daoUtil.setTimestamp( nIndex++, new Timestamp( new java.util.Date( ).getTime( ) ) );
109 daoUtil.setInt( nIndex++, portlet.getStatus( ) );
110 daoUtil.setInt( nIndex++, portlet.getColumn( ) );
111 daoUtil.setInt( nIndex++, portlet.getOrder( ) );
112 daoUtil.setInt( nIndex++, portlet.getAcceptAlias( ) );
113 daoUtil.setInt( nIndex++, portlet.getDisplayPortletTitle( ) );
114 daoUtil.setString( nIndex++, portlet.getRole( ) );
115 daoUtil.setInt( nIndex, portlet.getDeviceDisplayFlags( ) );
116
117 daoUtil.executeUpdate( );
118
119 if ( daoUtil.nextGeneratedKey( ) )
120 {
121 portlet.setId( daoUtil.getGeneratedKeyInt( 1 ) );
122 }
123 }
124 }
125
126
127
128
129 public void delete( int nPortletId )
130 {
131
132 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALIAS ) )
133 {
134 daoUtil.setInt( 1, nPortletId );
135 daoUtil.executeQuery( );
136
137 while ( daoUtil.next( ) )
138 {
139 AliasPortletHome.getInstance( ).remove( PortletHome.findByPrimaryKey( daoUtil.getInt( 1 ) ) );
140 }
141
142 }
143
144
145 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE ) )
146 {
147 daoUtil.setInt( 1, nPortletId );
148
149 daoUtil.executeUpdate( );
150 }
151 }
152
153
154
155
156 public Portlet load( int nPortletId )
157 {
158 PortletImpless/portlet/PortletImpl.html#PortletImpl">PortletImpl portlet = new PortletImpl( );
159 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT ) )
160 {
161 daoUtil.setInt( 1, nPortletId );
162 daoUtil.executeQuery( );
163
164 if ( daoUtil.next( ) )
165 {
166 portlet.setId( nPortletId );
167 portlet.setPortletTypeId( daoUtil.getString( 1 ) );
168 portlet.setPageId( daoUtil.getInt( 2 ) );
169 portlet.setStyleId( daoUtil.getInt( 3 ) );
170 portlet.setName( daoUtil.getString( 4 ) );
171 portlet.setPortletTypeName( daoUtil.getString( 5 ) );
172 portlet.setUrlCreation( daoUtil.getString( 6 ) );
173 portlet.setUrlUpdate( daoUtil.getString( 7 ) );
174 portlet.setDateUpdate( daoUtil.getTimestamp( 8 ) );
175 portlet.setColumn( daoUtil.getInt( 9 ) );
176 portlet.setOrder( daoUtil.getInt( 10 ) );
177 portlet.setHomeClassName( daoUtil.getString( 11 ) );
178 portlet.setAcceptAlias( daoUtil.getInt( 12 ) );
179 portlet.setRole( daoUtil.getString( 13 ) );
180 portlet.setPluginName( daoUtil.getString( 14 ) );
181 portlet.setDisplayPortletTitle( daoUtil.getInt( 15 ) );
182 portlet.setStatus( daoUtil.getInt( 16 ) );
183 portlet.setDeviceDisplayFlags( daoUtil.getInt( 17 ) );
184 }
185
186 }
187
188 return portlet;
189 }
190
191
192
193
194 public void store( Portlet portlet )
195 {
196 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE ) )
197 {
198
199 daoUtil.setString( 1, portlet.getName( ) );
200 daoUtil.setTimestamp( 2, new Timestamp( new java.util.Date( ).getTime( ) ) );
201 daoUtil.setInt( 3, portlet.getColumn( ) );
202 daoUtil.setInt( 4, portlet.getOrder( ) );
203 daoUtil.setInt( 5, portlet.getStyleId( ) );
204 daoUtil.setInt( 6, portlet.getPageId( ) );
205 daoUtil.setInt( 7, portlet.getAcceptAlias( ) );
206 daoUtil.setInt( 8, portlet.getDisplayPortletTitle( ) );
207 daoUtil.setString( 9, portlet.getRole( ) );
208 daoUtil.setInt( 10, portlet.getDeviceDisplayFlags( ) );
209 daoUtil.setInt( 11, portlet.getId( ) );
210
211 daoUtil.executeUpdate( );
212 }
213 }
214
215
216
217
218 public void updateStatus( Portlet portlet, int nStatus )
219 {
220 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_STATUS ) )
221 {
222
223 daoUtil.setInt( 1, nStatus );
224 daoUtil.setTimestamp( 2, new Timestamp( new java.util.Date( ).getTime( ) ) );
225 daoUtil.setInt( 3, portlet.getId( ) );
226
227 daoUtil.executeUpdate( );
228 }
229 }
230
231
232
233
234 public void updatePosition( Portlet portlet, int nColumn, int nOrder )
235 {
236 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_POSITION ) )
237 {
238 daoUtil.setInt( 1, nColumn );
239 daoUtil.setInt( 2, nOrder );
240 daoUtil.setInt( 3, portlet.getId( ) );
241
242 daoUtil.executeUpdate( );
243 }
244 }
245
246
247
248
249 public StyleSheet selectXslFile( int nPortletId, int nIdMode )
250 {
251 StyleSheets/stylesheet/StyleSheet.html#StyleSheet">StyleSheet stylesheet = new StyleSheet( );
252 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_XSL_FILE ) )
253 {
254 daoUtil.setInt( 1, nPortletId );
255 daoUtil.setInt( 2, nIdMode );
256 daoUtil.executeQuery( );
257
258 if ( daoUtil.next( ) )
259 {
260 stylesheet.setId( daoUtil.getInt( 1 ) );
261 stylesheet.setDescription( daoUtil.getString( 2 ) );
262 stylesheet.setFile( daoUtil.getString( 3 ) );
263 stylesheet.setSource( daoUtil.getBytes( 4 ) );
264 }
265
266 }
267
268 return stylesheet;
269 }
270
271
272
273
274 public Collection<PortletImpl> selectPortletsListbyName( String strPortletName )
275 {
276 List<PortletImpl> list = new ArrayList<>( );
277 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PORTLET_LIST_BY_NAME ) )
278 {
279 daoUtil.setString( 1, '%' + strPortletName + '%' );
280 daoUtil.executeQuery( );
281
282 while ( daoUtil.next( ) )
283 {
284 PortletImpless/portlet/PortletImpl.html#PortletImpl">PortletImpl portlet = new PortletImpl( );
285 portlet.setId( daoUtil.getInt( 1 ) );
286 portlet.setPageId( daoUtil.getInt( 2 ) );
287 portlet.setName( daoUtil.getString( 3 ) );
288
289 list.add( portlet );
290 }
291
292 }
293
294 return list;
295 }
296
297
298
299
300 public Collection<Portlet> selectAliasesForPortlet( int nPortletId )
301 {
302 List<Portlet> list = new ArrayList<>( );
303 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALIASES_FOR_PORTLET ) )
304 {
305 daoUtil.setInt( 1, nPortletId );
306 daoUtil.executeQuery( );
307
308 while ( daoUtil.next( ) )
309 {
310 PortletImpless/portlet/PortletImpl.html#PortletImpl">PortletImpl portlet = new PortletImpl( );
311 portlet.setId( daoUtil.getInt( 1 ) );
312 portlet.setPageId( daoUtil.getInt( 2 ) );
313 portlet.setName( daoUtil.getString( 3 ) );
314
315 list.add( portlet );
316 }
317
318 }
319
320 return list;
321 }
322
323
324
325
326 public List<Portlet> selectPortletsByType( String strPortletTypeId )
327 {
328 List<Portlet> list = new ArrayList<>( );
329 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PORTLET_LIST_BY_TYPE ) )
330 {
331 daoUtil.setString( 1, strPortletTypeId );
332 daoUtil.executeQuery( );
333
334 while ( daoUtil.next( ) )
335 {
336 PortletImpless/portlet/PortletImpl.html#PortletImpl">PortletImpl portlet = new PortletImpl( );
337 portlet.setId( daoUtil.getInt( 1 ) );
338 portlet.setPortletTypeId( daoUtil.getString( 2 ) );
339 portlet.setPageId( daoUtil.getInt( 3 ) );
340 portlet.setName( daoUtil.getString( 4 ) );
341 portlet.setDateUpdate( daoUtil.getTimestamp( 5 ) );
342 portlet.setStatus( daoUtil.getInt( 6 ) );
343 portlet.setOrder( daoUtil.getInt( 7 ) );
344 portlet.setColumn( daoUtil.getInt( 8 ) );
345 portlet.setStyleId( daoUtil.getInt( 9 ) );
346 portlet.setAcceptAlias( daoUtil.getInt( 10 ) );
347 portlet.setDateUpdate( daoUtil.getTimestamp( 11 ) );
348 portlet.setDisplayPortletTitle( daoUtil.getInt( 12 ) );
349 portlet.setRole( daoUtil.getString( 13 ) );
350 portlet.setDeviceDisplayFlags( daoUtil.getInt( 14 ) );
351
352 list.add( portlet );
353 }
354
355 }
356
357 return list;
358 }
359
360
361
362
363 public ReferenceList selectStylesList( String strPortletTypeId )
364 {
365 ReferenceListnceList.html#ReferenceList">ReferenceList list = new ReferenceList( );
366 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_STYLE_LIST ) )
367 {
368 daoUtil.setString( 1, strPortletTypeId );
369 daoUtil.executeQuery( );
370
371 while ( daoUtil.next( ) )
372 {
373 list.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
374 }
375
376 }
377
378 return list;
379 }
380
381
382
383
384 public boolean hasAlias( int nPortletId )
385 {
386 boolean bHasAlias = false;
387 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PORTLET_ALIAS ) )
388 {
389 daoUtil.setInt( 1, nPortletId );
390
391 daoUtil.executeQuery( );
392
393 if ( daoUtil.next( ) )
394 {
395 bHasAlias = true;
396 }
397
398 }
399
400 return bHasAlias;
401 }
402
403
404
405
406 public PortletType selectPortletType( String strPortletTypeId )
407 {
408 PortletTypeportlet/PortletType.html#PortletType">PortletType portletType = new PortletType( );
409 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PORTLET_TYPE ) )
410 {
411
412 daoUtil.setString( 1, strPortletTypeId );
413 daoUtil.executeQuery( );
414
415 if ( daoUtil.next( ) )
416 {
417 portletType.setId( daoUtil.getString( 1 ) );
418 portletType.setNameKey( daoUtil.getString( 2 ) );
419 portletType.setUrlCreation( daoUtil.getString( 3 ) );
420 portletType.setUrlUpdate( daoUtil.getString( 4 ) );
421 portletType.setPluginName( daoUtil.getString( 5 ) );
422 }
423
424 }
425
426 return portletType;
427 }
428
429
430
431
432 public Collection<PortletImpl> selectPortletListByStyle( int nStyleId )
433 {
434 Collection<PortletImpl> portletList = new ArrayList<>( );
435 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PORTLET_LIST_BY_STYLE ) )
436 {
437
438 daoUtil.setInt( 1, nStyleId );
439 daoUtil.executeQuery( );
440
441 while ( daoUtil.next( ) )
442 {
443 PortletImpless/portlet/PortletImpl.html#PortletImpl">PortletImpl portlet = new PortletImpl( );
444
445 portlet.setId( daoUtil.getInt( 1 ) );
446 portlet.setName( daoUtil.getString( 2 ) );
447 portlet.setPageId( daoUtil.getInt( 3 ) );
448
449 portletList.add( portlet );
450 }
451
452 }
453
454 return portletList;
455 }
456
457
458
459
460 public Collection<Portlet> selectPortletsByRole( String strRole )
461 {
462 List<Portlet> list = new ArrayList<>( );
463 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PORTLET_LIST_BY_ROLE ) )
464 {
465 daoUtil.setString( 1, strRole );
466 daoUtil.executeQuery( );
467
468 while ( daoUtil.next( ) )
469 {
470 PortletImpless/portlet/PortletImpl.html#PortletImpl">PortletImpl portlet = new PortletImpl( );
471 portlet.setId( daoUtil.getInt( 1 ) );
472 portlet.setPortletTypeId( daoUtil.getString( 2 ) );
473 portlet.setPageId( daoUtil.getInt( 3 ) );
474
475 list.add( portlet );
476 }
477
478 }
479
480 return list;
481 }
482
483
484
485
486 public Portlet loadLastModifiedPortlet( )
487 {
488 PortletImpl portlet = null;
489 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_LAST_MODIFIED_PORTLET ) )
490 {
491 daoUtil.executeQuery( );
492
493 if ( daoUtil.next( ) )
494 {
495 portlet = new PortletImpl( );
496
497 int nIndex = 1;
498 portlet.setId( daoUtil.getInt( nIndex++ ) );
499 portlet.setPortletTypeId( daoUtil.getString( nIndex++ ) );
500 portlet.setPageId( daoUtil.getInt( nIndex++ ) );
501 portlet.setStyleId( daoUtil.getInt( nIndex++ ) );
502 portlet.setName( daoUtil.getString( nIndex++ ) );
503 portlet.setPortletTypeName( daoUtil.getString( nIndex++ ) );
504 portlet.setUrlCreation( daoUtil.getString( nIndex++ ) );
505 portlet.setUrlUpdate( daoUtil.getString( nIndex++ ) );
506 portlet.setDateUpdate( daoUtil.getTimestamp( nIndex++ ) );
507 portlet.setColumn( daoUtil.getInt( nIndex++ ) );
508 portlet.setOrder( daoUtil.getInt( nIndex++ ) );
509 portlet.setHomeClassName( daoUtil.getString( nIndex++ ) );
510 portlet.setAcceptAlias( daoUtil.getInt( nIndex++ ) );
511 portlet.setRole( daoUtil.getString( nIndex++ ) );
512 portlet.setPluginName( daoUtil.getString( nIndex++ ) );
513 portlet.setDisplayPortletTitle( daoUtil.getInt( nIndex++ ) );
514 portlet.setStatus( daoUtil.getInt( nIndex++ ) );
515 portlet.setDeviceDisplayFlags( daoUtil.getInt( nIndex++ ) );
516 }
517
518 }
519
520 return portlet;
521 }
522
523
524
525
526 @Override
527 public List<Integer> getUsedOrdersForColumns( int pageId, int columnId )
528 {
529 List<Integer> result = new ArrayList<>( );
530 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ORDER_FROM_PAGE_AND_COLUMN ) )
531 {
532 daoUtil.setInt( 1, columnId );
533 daoUtil.setInt( 2, pageId );
534
535 daoUtil.executeQuery( );
536
537 while ( daoUtil.next( ) )
538 {
539 result.add( daoUtil.getInt( 1 ) );
540 }
541
542 }
543
544 return result;
545 }
546 }