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