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.whatsnew.business.portlet;
35
36 import fr.paris.lutece.plugins.whatsnew.business.PortletDocumentLink;
37 import fr.paris.lutece.portal.business.portlet.Portlet;
38 import fr.paris.lutece.portal.service.plugin.Plugin;
39 import fr.paris.lutece.util.sql.DAOUtil;
40
41 import java.util.ArrayList;
42 import java.util.List;
43
44
45
46
47
48
49
50 public final class WhatsNewPortletDAO implements IWhatsNewPortletDAO
51 {
52
53 private static final String SQL_QUERY_SELECT_ALL = " SELECT id_portlet, show_documents, show_portlets, show_pages, period, nb_elements_max, elements_order, is_asc_sort, is_dynamic FROM whatsnew_portlet ";
54 private static final String SQL_QUERY_INSERT = " INSERT INTO whatsnew_portlet (id_portlet, show_documents, show_portlets, show_pages, period, nb_elements_max, elements_order, is_asc_sort, is_dynamic) VALUES ( ?,?,?,?,?,?,?,?,? ) ";
55 private static final String SQL_QUERY_DELETE = " DELETE FROM whatsnew_portlet WHERE id_portlet = ? ";
56 private static final String SQL_QUERY_SELECT = " SELECT id_portlet, show_documents, show_portlets, show_pages, period, nb_elements_max, elements_order, is_asc_sort, is_dynamic FROM whatsnew_portlet WHERE id_portlet = ? ";
57 private static final String SQL_QUERY_UPDATE = " UPDATE whatsnew_portlet SET show_documents = ?, show_portlets = ?, show_pages = ?, period = ?, nb_elements_max = ?, elements_order = ?, is_asc_sort = ?, is_dynamic = ? WHERE id_portlet = ? ";
58
59
60 private static final String SQL_QUERY_INSERT_PAGE_WHATSNEW = " INSERT INTO whatsnew_page_whatsnew (id_whatsnew_portlet, id_page) VALUES ( ?,? ) ";
61 private static final String SQL_QUERY_SELECT_PAGE_WHATSNEW = " SELECT id_page FROM whatsnew_page_whatsnew WHERE id_whatsnew_portlet = ? ";
62 private static final String SQL_QUERY_DELETE_PAGE_WHATSNEW_FROM_ID_WHATSNEW_PORTLET = " DELETE FROM whatsnew_page_whatsnew WHERE id_whatsnew_portlet = ? ";
63 private static final String SQL_QUERY_DELETE_PAGE_WHATSNEW_FROM_ID_PAGE = " DELETE FROM whatsnew_page_whatsnew WHERE id_page = ? ";
64 private static final String SQL_QUERY_DELETE_LINK_WHATSNEW_PORTLET_TO_PAGE = " DELETE FROM whatsnew_page_whatsnew WHERE id_whatsnew_portlet = ? AND id_page = ? ";
65
66
67 private static final String SQL_QUERY_INSERT_PORTLET_WHATSNEW = " INSERT INTO whatsnew_portlet_whatsnew (id_whatsnew_portlet, id_portlet) VALUES ( ?,? ) ";
68 private static final String SQL_QUERY_SELECT_PORTLET_WHATSNEW = " SELECT id_portlet FROM whatsnew_portlet_whatsnew WHERE id_whatsnew_portlet = ? ";
69 private static final String SQL_QUERY_DELETE_PORTLET_WHATSNEW_FROM_ID_WHATSNEW_PORTLET = " DELETE FROM whatsnew_portlet_whatsnew WHERE id_whatsnew_portlet = ? ";
70 private static final String SQL_QUERY_DELETE_PORTLET_WHATSNEW_FROM_ID_PORTLET = " DELETE FROM whatsnew_portlet_whatsnew WHERE id_portlet = ? ";
71 private static final String SQL_QUERY_DELETE_LINK_WHATSNEW_PORTLET_TO_PORTLET = " DELETE FROM whatsnew_portlet_whatsnew WHERE id_whatsnew_portlet = ? AND id_portlet = ? ";
72
73
74 private static final String SQL_QUERY_INSERT_DOCUMENT_WHATSNEW = " INSERT INTO whatsnew_document_whatsnew (id_whatsnew_portlet, id_portlet, id_document) VALUES ( ?,?,? ) ";
75 private static final String SQL_QUERY_SELECT_DOCUMENT_WHATSNEW = " SELECT id_portlet, id_document FROM whatsnew_document_whatsnew WHERE id_whatsnew_portlet = ? ";
76 private static final String SQL_QUERY_DELETE_DOCUMENT_WHATSNEW_FROM_ID_WHATSNEW_PORTLET = " DELETE FROM whatsnew_document_whatsnew WHERE id_whatsnew_portlet = ? ";
77 private static final String SQL_QUERY_DELETE_DOCUMENT_WHATSNEW_FROM_ID_DOCUMENT_ID_PORTLET = " DELETE FROM whatsnew_document_whatsnew WHERE id_portlet = ? AND id_document = ? ";
78 private static final String SQL_QUERY_DELETE_LINK_WHATSNEW_PORTLET_TO_DOCUMENT = " DELETE FROM whatsnew_document_whatsnew WHERE id_whatsnew_portlet = ? AND id_portlet = ? AND id_document = ?";
79
80
81
82
83
84
85
86 public void insert( Portlet portlet )
87 {
88 WhatsNewPortlet p = (WhatsNewPortlet) portlet;
89
90 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT );
91 int nIndex = 1;
92
93 daoUtil.setInt( nIndex++, p.getId( ) );
94 daoUtil.setBoolean( nIndex++, p.getShowDocuments( ) );
95 daoUtil.setBoolean( nIndex++, p.getShowPortlets( ) );
96 daoUtil.setBoolean( nIndex++, p.getShowPages( ) );
97 daoUtil.setInt( nIndex++, p.getPeriod( ) );
98 daoUtil.setInt( nIndex++, p.getNbElementsMax( ) );
99 daoUtil.setInt( nIndex++, p.getElementsOrder( ) );
100 daoUtil.setBoolean( nIndex++, p.getAscSort( ) );
101 daoUtil.setBoolean( nIndex++, p.getDynamic( ) );
102
103 daoUtil.executeUpdate( );
104 daoUtil.free( );
105 }
106
107
108
109
110 public void delete( int nPortletId )
111 {
112 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE );
113 daoUtil.setInt( 1, nPortletId );
114
115 daoUtil.executeUpdate( );
116 daoUtil.free( );
117 }
118
119
120
121
122 public Portlet load( int nPortletId )
123 {
124 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT );
125 daoUtil.setInt( 1, nPortletId );
126 daoUtil.executeQuery( );
127
128 WhatsNewPortlet portlet = new WhatsNewPortlet( );
129
130 int nIndex = 1;
131
132 if ( daoUtil.next( ) )
133 {
134 portlet.setId( daoUtil.getInt( nIndex++ ) );
135 portlet.setShowDocuments( daoUtil.getBoolean( nIndex++ ) );
136 portlet.setShowPortlets( daoUtil.getBoolean( nIndex++ ) );
137 portlet.setShowPages( daoUtil.getBoolean( nIndex++ ) );
138 portlet.setPeriod( daoUtil.getInt( nIndex++ ) );
139 portlet.setNbElementsMax( daoUtil.getInt( nIndex++ ) );
140 portlet.setElementsOrder( daoUtil.getInt( nIndex++ ) );
141 portlet.setAscSort( daoUtil.getBoolean( nIndex++ ) );
142 portlet.setDynamic( daoUtil.getBoolean( nIndex++ ) );
143 }
144
145 daoUtil.free( );
146
147 return portlet;
148 }
149
150
151
152
153 public void store( Portlet portlet )
154 {
155 WhatsNewPortlet p = (WhatsNewPortlet) portlet;
156
157 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE );
158 int nIndex = 1;
159
160 daoUtil.setBoolean( nIndex++, p.getShowDocuments( ) );
161 daoUtil.setBoolean( nIndex++, p.getShowPortlets( ) );
162 daoUtil.setBoolean( nIndex++, p.getShowPages( ) );
163 daoUtil.setInt( nIndex++, p.getPeriod( ) );
164 daoUtil.setInt( nIndex++, p.getNbElementsMax( ) );
165 daoUtil.setInt( nIndex++, p.getElementsOrder( ) );
166 daoUtil.setBoolean( nIndex++, p.getAscSort( ) );
167 daoUtil.setBoolean( nIndex++, p.getDynamic( ) );
168
169 daoUtil.setInt( nIndex++, p.getId( ) );
170
171 daoUtil.executeUpdate( );
172 daoUtil.free( );
173 }
174
175
176
177
178 public List<WhatsNewPortlet> findAll( )
179 {
180 List<WhatsNewPortlet> listPortlets = new ArrayList<WhatsNewPortlet>( );
181 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL );
182 daoUtil.executeQuery( );
183
184 while ( daoUtil.next( ) )
185 {
186 int nIndex = 1;
187 WhatsNewPortlet portlet = new WhatsNewPortlet( );
188 portlet.setId( daoUtil.getInt( nIndex++ ) );
189 portlet.setShowDocuments( daoUtil.getBoolean( nIndex++ ) );
190 portlet.setShowPortlets( daoUtil.getBoolean( nIndex++ ) );
191 portlet.setShowPages( daoUtil.getBoolean( nIndex++ ) );
192 portlet.setPeriod( daoUtil.getInt( nIndex++ ) );
193 portlet.setNbElementsMax( daoUtil.getInt( nIndex++ ) );
194 portlet.setElementsOrder( daoUtil.getInt( nIndex++ ) );
195 portlet.setAscSort( daoUtil.getBoolean( nIndex++ ) );
196 portlet.setDynamic( daoUtil.getBoolean( nIndex++ ) );
197 listPortlets.add( portlet );
198 }
199
200 daoUtil.free( );
201
202 return listPortlets;
203 }
204
205
206
207
208
209
210 public List<Integer> loadPageIdsFromWhatsNewPortletId( int nWhatsNewPortletId, Plugin plugin )
211 {
212 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PAGE_WHATSNEW, plugin );
213 daoUtil.setInt( 1, nWhatsNewPortletId );
214 daoUtil.executeQuery( );
215
216 List<Integer> listPageIds = new ArrayList<Integer>( );
217
218 while ( daoUtil.next( ) )
219 {
220 listPageIds.add( daoUtil.getInt( 1 ) );
221 }
222
223 daoUtil.free( );
224
225 return listPageIds;
226 }
227
228
229
230
231 public void insertPageForWhatsNew( int nWhatsNewPortletId, int nPageId, Plugin plugin )
232 {
233 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_PAGE_WHATSNEW, plugin );
234 int nIndex = 1;
235
236 daoUtil.setInt( nIndex++, nWhatsNewPortletId );
237 daoUtil.setInt( nIndex++, nPageId );
238
239 daoUtil.executeUpdate( );
240 daoUtil.free( );
241 }
242
243
244
245
246 public void deletePagesFromWhatsNew( int nWhatsNewPortletId, Plugin plugin )
247 {
248 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_PAGE_WHATSNEW_FROM_ID_WHATSNEW_PORTLET, plugin );
249
250 daoUtil.setInt( 1, nWhatsNewPortletId );
251
252 daoUtil.executeUpdate( );
253 daoUtil.free( );
254 }
255
256
257
258
259 public void deleteWhatsNewFromPage( int nPageId, Plugin plugin )
260 {
261 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_PAGE_WHATSNEW_FROM_ID_PAGE, plugin );
262
263 daoUtil.setInt( 1, nPageId );
264
265 daoUtil.executeUpdate( );
266 daoUtil.free( );
267 }
268
269
270
271
272 public void deleteLinkWhatsNewPortletToPage( int nWhatsNewPortletId, int nPageId, Plugin plugin )
273 {
274 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_LINK_WHATSNEW_PORTLET_TO_PAGE, plugin );
275
276 int nIndex = 1;
277 daoUtil.setInt( nIndex++, nWhatsNewPortletId );
278 daoUtil.setInt( nIndex++, nPageId );
279
280 daoUtil.executeUpdate( );
281 daoUtil.free( );
282 }
283
284
285
286
287
288
289 public List<Integer> loadPortletIdsFromWhatsNewPortletId( int nWhatsNewPortletId, Plugin plugin )
290 {
291 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PORTLET_WHATSNEW, plugin );
292 daoUtil.setInt( 1, nWhatsNewPortletId );
293 daoUtil.executeQuery( );
294
295 List<Integer> listPortletIds = new ArrayList<Integer>( );
296
297 while ( daoUtil.next( ) )
298 {
299 listPortletIds.add( daoUtil.getInt( 1 ) );
300 }
301
302 daoUtil.free( );
303
304 return listPortletIds;
305 }
306
307
308
309
310 public void insertPortletForWhatsNew( int nWhatsNewPortletId, int nPortletId, Plugin plugin )
311 {
312 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_PORTLET_WHATSNEW, plugin );
313 int nIndex = 1;
314
315 daoUtil.setInt( nIndex++, nWhatsNewPortletId );
316 daoUtil.setInt( nIndex++, nPortletId );
317
318 daoUtil.executeUpdate( );
319 daoUtil.free( );
320 }
321
322
323
324
325 public void deletePortletsFromWhatsNew( int nWhatsNewPortletId, Plugin plugin )
326 {
327 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_PORTLET_WHATSNEW_FROM_ID_WHATSNEW_PORTLET, plugin );
328
329 daoUtil.setInt( 1, nWhatsNewPortletId );
330
331 daoUtil.executeUpdate( );
332 daoUtil.free( );
333 }
334
335
336
337
338 public void deleteWhatsNewFromPortlet( int nPortletId, Plugin plugin )
339 {
340 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_PORTLET_WHATSNEW_FROM_ID_PORTLET, plugin );
341
342 daoUtil.setInt( 1, nPortletId );
343
344 daoUtil.executeUpdate( );
345 daoUtil.free( );
346 }
347
348
349
350
351 public void deleteLinkWhatsNewPortletToPortlet( int nWhatsNewPortletId, int nPortletId, Plugin plugin )
352 {
353 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_LINK_WHATSNEW_PORTLET_TO_PORTLET, plugin );
354
355 int nIndex = 1;
356 daoUtil.setInt( nIndex++, nWhatsNewPortletId );
357 daoUtil.setInt( nIndex++, nPortletId );
358
359 daoUtil.executeUpdate( );
360 daoUtil.free( );
361 }
362
363
364
365
366
367
368 public List<PortletDocumentLink> loadDocumentFromWhatsNewPortletId( int nWhatsNewPortletId, Plugin plugin )
369 {
370 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_DOCUMENT_WHATSNEW, plugin );
371 daoUtil.setInt( 1, nWhatsNewPortletId );
372 daoUtil.executeQuery( );
373
374 List<PortletDocumentLink> listPortletDocumentLink = new ArrayList<PortletDocumentLink>( );
375
376 while ( daoUtil.next( ) )
377 {
378 int nIndex = 1;
379 int nPortletId = daoUtil.getInt( nIndex++ );
380 int nDocumentId = daoUtil.getInt( nIndex++ );
381 PortletDocumentLink pdLink = new PortletDocumentLink( nPortletId, nDocumentId );
382 listPortletDocumentLink.add( pdLink );
383 }
384
385 daoUtil.free( );
386
387 return listPortletDocumentLink;
388 }
389
390
391
392
393 public void insertDocumentForWhatsNew( int nWhatsNewPortletId, PortletDocumentLink pdLink, Plugin plugin )
394 {
395 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_DOCUMENT_WHATSNEW, plugin );
396 int nIndex = 1;
397
398 daoUtil.setInt( nIndex++, nWhatsNewPortletId );
399 daoUtil.setInt( nIndex++, pdLink.getPortletId( ) );
400 daoUtil.setInt( nIndex++, pdLink.getDocumentId( ) );
401
402 daoUtil.executeUpdate( );
403 daoUtil.free( );
404 }
405
406
407
408
409 public void deleteDocumentsFromWhatsNew( int nWhatsNewPortletId, Plugin plugin )
410 {
411 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_DOCUMENT_WHATSNEW_FROM_ID_WHATSNEW_PORTLET, plugin );
412
413 daoUtil.setInt( 1, nWhatsNewPortletId );
414
415 daoUtil.executeUpdate( );
416 daoUtil.free( );
417 }
418
419
420
421
422 public void deleteWhatsNewFromDocument( PortletDocumentLink pdLink, Plugin plugin )
423 {
424 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_DOCUMENT_WHATSNEW_FROM_ID_DOCUMENT_ID_PORTLET, plugin );
425
426 int nIndex = 1;
427 daoUtil.setInt( nIndex++, pdLink.getPortletId( ) );
428 daoUtil.setInt( nIndex++, pdLink.getDocumentId( ) );
429
430 daoUtil.executeUpdate( );
431 daoUtil.free( );
432 }
433
434
435
436
437 public void deleteLinkWhatsNewPortletToDocument( int nWhatsNewPortletId, PortletDocumentLink pdLink, Plugin plugin )
438 {
439 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_LINK_WHATSNEW_PORTLET_TO_DOCUMENT, plugin );
440
441 int nIndex = 1;
442 daoUtil.setInt( nIndex++, nWhatsNewPortletId );
443 daoUtil.setInt( nIndex++, pdLink.getPortletId( ) );
444 daoUtil.setInt( nIndex++, pdLink.getDocumentId( ) );
445
446 daoUtil.executeUpdate( );
447 daoUtil.free( );
448 }
449 }