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.announce.business;
35
36 import fr.paris.lutece.portal.service.plugin.Plugin;
37 import fr.paris.lutece.util.sql.DAOUtil;
38
39 import java.sql.Timestamp;
40
41 import java.util.ArrayList;
42 import java.util.List;
43
44 import org.apache.commons.collections.CollectionUtils;
45
46
47
48
49 public final class AnnounceDAO implements IAnnounceDAO
50 {
51 private static final String ORDER_BY = " ORDER BY ";
52 private static final String ORDER_BY_ASCENDING = " ASC ";
53 private static final String ORDER_BY_DESCENDING = " DESC ";
54
55
56 private static final String SQL_QUERY_SELECT_ID = "SELECT a.id_announce FROM announce_announce a, announce_category b WHERE a.id_category = b.id_category";
57 private static final String SQL_QUERY_SELECTALL_ID_PUBLISHED = SQL_QUERY_SELECT_ID
58 + " AND a.published = 1 AND a.suspended = 0 AND a.suspended_by_user = 0 ";
59 private static final String SQL_QUERY_SELECTALL = SQL_QUERY_SELECT_ID;
60 private static final String SQL_QUERY_SELECTALL_PUBLISHED_FOR_CATEGORY = "SELECT a.id_announce FROM announce_announce a WHERE a.id_category = ? AND a.published = 1 AND a.suspended = 0 AND a.suspended_by_user = 0 ";
61 private static final String SQL_QUERY_SELECT_ID_BY_DATE_CREATION = "SELECT id_announce FROM announce_announce WHERE date_creation < ?";
62 private static final String SQL_QUERY_SELECT_ID_BY_TIME_PUBLICATION = "SELECT id_announce FROM announce_announce WHERE publication_time > ? ";
63
64
65 private static final String SQL_QUERY_NEW_PK = "SELECT max( id_announce ) FROM announce_announce";
66
67
68 private static final String SQL_QUERY_SELECT_FIELD_LIST_WITH_CATEGORY = "SELECT a.id_announce, a.title_announce, a.description_announce, a.price_announce, a.date_creation, a.date_modification, a.user_name, a.user_lastname, a.user_secondname, a.contact_information, a.published, a.suspended, a.suspended_by_user, a.tags, a.has_pictures, a.publication_time, a.has_notified, a.id_category, b.label_category, b.display_price, b.id_sector FROM announce_announce a, announce_category b WHERE a.id_category = b.id_category ";
69 private static final String SQL_QUERY_SELECT = SQL_QUERY_SELECT_FIELD_LIST_WITH_CATEGORY + " AND a.id_announce = ? ";
70 private static final String SQL_QUERY_SELECTALL_PUBLISHED = SQL_QUERY_SELECT_FIELD_LIST_WITH_CATEGORY
71 + "AND a.published = 1 AND a.suspended = 0 AND a.suspended_by_user = 0 ";
72 private static final String SQL_QEURY_SELECT_BY_LIST_ID = SQL_QUERY_SELECT_FIELD_LIST_WITH_CATEGORY + " AND a.id_announce IN (";
73 private static final String SQL_QUERY_SELECTALL_ANNOUNCES_FOR_USER = SQL_QUERY_SELECT_FIELD_LIST_WITH_CATEGORY + " AND a.user_name = ? ";
74
75
76 private static final String SQL_QUERY_INSERT = "INSERT INTO announce_announce ( id_announce, user_name, user_lastname, user_secondname, contact_information, id_category, title_announce, description_announce, price_announce, date_creation, date_modification, published, tags, has_pictures, publication_time, has_notified) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
77 private static final String SQL_QUERY_DELETE = "DELETE FROM announce_announce WHERE id_announce = ? ";
78
79
80 private static final String SQL_QUERY_UPDATE = "UPDATE announce_announce SET title_announce = ?, description_announce = ?, price_announce = ?, contact_information = ?, published = ?, tags = ?, has_pictures = ?, date_modification = ?, has_notified = ? WHERE id_announce = ?";
81
82 private static final String SQL_QUERY_SET_PUBLISHED = "UPDATE announce_announce SET published = ?, publication_time = ? WHERE id_announce = ?";
83 private static final String SQL_QUERY_SET_HASNOTIFED = "UPDATE announce_announce SET has_notified = ? WHERE id_announce = ?";
84 private static final String SQL_QUERY_SET_SUSPENDED = "UPDATE announce_announce SET suspended = ?, publication_time = ? WHERE id_announce = ?";
85 private static final String SQL_QUERY_SET_SUSPENDED_BY_USER = "UPDATE announce_announce SET suspended_by_user = ?, publication_time = ? WHERE id_announce = ?";
86
87
88 private static final String SQL_QUERY_INSERT_ANNOUNCE_RESPONSE = "INSERT INTO announce_announce_response (id_announce, id_response, is_image) VALUES (?,?,?)";
89 private static final String SQL_FRAGMENT_AND_IS_IMAGE = " AND is_image = ?";
90 private static final String SQL_QUERY_SELECT_ANNOUNCE_RESPONSE_LIST = "SELECT id_response FROM announce_announce_response WHERE id_announce = ?";
91 private static final String SQL_QUERY_SELECT_ANNOUNCE_IMAGE_RESPONSE_LIST = SQL_QUERY_SELECT_ANNOUNCE_RESPONSE_LIST + SQL_FRAGMENT_AND_IS_IMAGE;
92 private static final String SQL_QUERY_SELECT_ANNOUNCE_BY_RESPONSE = "SELECT id_announce FROM announce_announce_response WHERE id_response = ?";
93 private static final String SQL_QUERY_SELECT_ANNOUNCE_BY_IMAGE_RESPONSE = SQL_QUERY_SELECT_ANNOUNCE_BY_RESPONSE + SQL_FRAGMENT_AND_IS_IMAGE;
94 private static final String SQL_QUERY_DELETE_ANNOUNCE_RESPONSE = "DELETE FROM announce_announce_response WHERE id_announce = ?";
95
96
97 private static final String CONSTANT_COMA = ",";
98 private static final String CONSTANT_CLOSE_PARENTHESIS = ")";
99 private static final String CONSTANT_SPACE = " ";
100
101
102
103
104
105
106
107
108 public int newPrimaryKey( Plugin plugin )
109 {
110 int nKey = 1;
111 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin ) )
112 {
113 daoUtil.executeQuery( );
114 if ( daoUtil.next( ) )
115 {
116 nKey = daoUtil.getInt( 1 ) + 1;
117 }
118 }
119
120 return nKey;
121 }
122
123
124
125
126 @Override
127 public synchronized void insert( Announce announce, Plugin plugin )
128 {
129 announce.setId( newPrimaryKey( plugin ) );
130
131 int nIndex = 1;
132 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin ) )
133 {
134
135 daoUtil.setInt( nIndex++, announce.getId( ) );
136 daoUtil.setString( nIndex++, announce.getUserName( ) );
137 daoUtil.setString( nIndex++, announce.getUserLastName( ) );
138 daoUtil.setString( nIndex++, announce.getUserSecondName( ) );
139 daoUtil.setString( nIndex++, announce.getContactInformation( ) );
140 daoUtil.setInt( nIndex++, announce.getCategory( ).getId( ) );
141 daoUtil.setString( nIndex++, announce.getTitle( ) );
142 daoUtil.setString( nIndex++, announce.getDescription( ) );
143 daoUtil.setDouble( nIndex++, announce.getPrice( ) );
144 daoUtil.setTimestamp( nIndex++, announce.getDateCreation( ) );
145 daoUtil.setTimestamp( nIndex++, announce.getDateModification( ) );
146 daoUtil.setBoolean( nIndex++, announce.getPublished( ) );
147 daoUtil.setString( nIndex++, announce.getTags( ) );
148 daoUtil.setBoolean( nIndex++, announce.getHasPictures( ) );
149 daoUtil.setLong( nIndex++, announce.getTimePublication( ) );
150 daoUtil.setInt( nIndex, announce.getHasNotify( ) );
151
152 daoUtil.executeUpdate( );
153 }
154 }
155
156
157
158
159 @Override
160 public Announce load( int nId, Plugin plugin )
161 {
162 Announce announce = null;
163 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
164 {
165 daoUtil.setInt( 1, nId );
166 daoUtil.executeQuery( );
167
168 if ( daoUtil.next( ) )
169 {
170 announce = getAnnounceWithCategory( daoUtil );
171 }
172 }
173 return announce;
174 }
175
176
177
178
179 @Override
180 public void delete( int nAnnounceId, Plugin plugin )
181 {
182 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
183 {
184 daoUtil.setInt( 1, nAnnounceId );
185 daoUtil.executeUpdate( );
186 }
187 }
188
189
190
191
192 @Override
193 public void store( Announce announce, Plugin plugin )
194 {
195 int nIndex = 1;
196 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
197 {
198 daoUtil.setString( nIndex++, announce.getTitle( ) );
199 daoUtil.setString( nIndex++, announce.getDescription( ) );
200 daoUtil.setDouble( nIndex++, announce.getPrice( ) );
201 daoUtil.setString( nIndex++, announce.getContactInformation( ) );
202 daoUtil.setBoolean( nIndex++, announce.getPublished( ) );
203 daoUtil.setString( nIndex++, announce.getTags( ) );
204 daoUtil.setBoolean( nIndex++, announce.getHasPictures( ) );
205 daoUtil.setTimestamp( nIndex++, announce.getDateModification( ) );
206 daoUtil.setInt( nIndex++, announce.getHasNotify( ) );
207
208 daoUtil.setInt( nIndex, announce.getId( ) );
209
210 daoUtil.executeUpdate( );
211 }
212 }
213
214
215
216
217 @Override
218 public List<Integer> selectAll( AnnounceSort announceSort, Plugin plugin )
219 {
220 List<Integer> announceList = new ArrayList<>( );
221 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL + getOrderBy( announceSort ), plugin ) )
222 {
223 daoUtil.executeQuery( );
224
225 while ( daoUtil.next( ) )
226 {
227 announceList.add( daoUtil.getInt( 1 ) );
228 }
229 }
230 return announceList;
231 }
232
233
234
235
236 @Override
237 public List<Integer> selectAllPublishedId( AnnounceSort announceSort, Plugin plugin )
238 {
239 List<Integer> listIdAnnounce = new ArrayList<>( );
240 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ID_PUBLISHED + getOrderBy( announceSort ), plugin ) )
241 {
242 daoUtil.executeQuery( );
243
244 while ( daoUtil.next( ) )
245 {
246 listIdAnnounce.add( daoUtil.getInt( 1 ) );
247 }
248 }
249 return listIdAnnounce;
250 }
251
252
253
254
255 @Override
256 public List<Announce> selectAllPublished( AnnounceSort announceSort, Plugin plugin )
257 {
258 List<Announce> announceList = new ArrayList<>( );
259 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_PUBLISHED + getOrderBy( announceSort ), plugin ) )
260 {
261 daoUtil.executeQuery( );
262 while ( daoUtil.next( ) )
263 {
264 announceList.add( getAnnounceWithCategory( daoUtil ) );
265 }
266 }
267 return announceList;
268 }
269
270
271
272
273 @Override
274 public List<Announce> findByListId( List<Integer> listIdAnnounces, AnnounceSort announceSort, Plugin plugin )
275 {
276 List<Announce> announceList = new ArrayList<>( );
277
278 if ( CollectionUtils.isEmpty( listIdAnnounces ) )
279 {
280 return announceList;
281 }
282
283 StringBuilder sbSql = new StringBuilder( SQL_QEURY_SELECT_BY_LIST_ID );
284 boolean bIsFirst = true;
285
286 for ( Integer nId : listIdAnnounces )
287 {
288 if ( !bIsFirst )
289 {
290 sbSql.append( CONSTANT_COMA );
291 }
292 else
293 {
294 bIsFirst = false;
295 }
296
297 sbSql.append( nId );
298 }
299
300 sbSql.append( CONSTANT_CLOSE_PARENTHESIS );
301 sbSql.append( getOrderBy( announceSort ) );
302
303 try ( DAOUtil daoUtil = new DAOUtil( sbSql.toString( ), plugin ) )
304 {
305 daoUtil.executeQuery( );
306
307 while ( daoUtil.next( ) )
308 {
309 announceList.add( getAnnounceWithCategory( daoUtil ) );
310 }
311 }
312 return announceList;
313 }
314
315
316
317
318 @Override
319 public List<Integer> selectAllPublishedForCategory( Category category, AnnounceSort announceSort, Plugin plugin )
320 {
321 List<Integer> announceList = new ArrayList<>( );
322 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_PUBLISHED_FOR_CATEGORY + getOrderBy( announceSort ), plugin ) )
323 {
324 daoUtil.setInt( 1, category.getId( ) );
325 daoUtil.executeQuery( );
326
327 while ( daoUtil.next( ) )
328 {
329 announceList.add( daoUtil.getInt( 1 ) );
330 }
331 }
332 return announceList;
333 }
334
335
336
337
338 @Override
339 public List<Announce> selectAllForUser( String strUsername, AnnounceSort announceSort, Plugin plugin )
340 {
341 List<Announce> announceList = new ArrayList<>( );
342 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ANNOUNCES_FOR_USER + getOrderBy( announceSort ), plugin ) )
343 {
344 daoUtil.setString( 1, strUsername );
345 daoUtil.executeQuery( );
346
347 while ( daoUtil.next( ) )
348 {
349 announceList.add( getAnnounceWithCategory( daoUtil ) );
350 }
351 }
352 return announceList;
353 }
354
355
356
357
358 @Override
359 public void setPublished( Announce announce, Plugin plugin )
360 {
361 int nParam = 1;
362 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SET_PUBLISHED, plugin ) )
363 {
364 daoUtil.setBoolean( nParam++, announce.getPublished( ) );
365 daoUtil.setLong( nParam++, announce.getTimePublication( ) );
366 daoUtil.setInt( nParam, announce.getId( ) );
367 daoUtil.executeUpdate( );
368 }
369 }
370
371
372
373
374 @Override
375 public void setHasNotifed( Announce announce, Plugin plugin )
376 {
377 int nParam = 1;
378 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SET_HASNOTIFED, plugin ) )
379 {
380 daoUtil.setInt( nParam++, announce.getHasNotify( ) );
381 daoUtil.setInt( nParam, announce.getId( ) );
382 daoUtil.executeUpdate( );
383 }
384 }
385
386
387
388
389 @Override
390 public void setSuspended( Announce announce, Plugin plugin )
391 {
392 int nParam = 1;
393 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SET_SUSPENDED, plugin ) )
394 {
395 daoUtil.setBoolean( nParam++, announce.getSuspended( ) );
396 daoUtil.setLong( nParam++, announce.getTimePublication( ) );
397 daoUtil.setInt( nParam, announce.getId( ) );
398 daoUtil.executeUpdate( );
399 }
400 }
401
402
403
404
405 @Override
406 public void setSuspendedByUser( Announce announce, Plugin plugin )
407 {
408 int nParam = 1;
409 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SET_SUSPENDED_BY_USER, plugin ) )
410 {
411 daoUtil.setBoolean( nParam++, announce.getSuspendedByUser( ) );
412 daoUtil.setLong( nParam++, announce.getTimePublication( ) );
413 daoUtil.setInt( nParam, announce.getId( ) );
414 daoUtil.executeUpdate( );
415 }
416 }
417
418
419
420
421 @Override
422 public List<Integer> findIdAnnouncesByDateCreation( Timestamp timestamp, Plugin plugin )
423 {
424 List<Integer> announceIdList = new ArrayList<>( );
425 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ID_BY_DATE_CREATION, plugin ) )
426 {
427 daoUtil.setTimestamp( 1, timestamp );
428 daoUtil.executeQuery( );
429
430 while ( daoUtil.next( ) )
431 {
432 announceIdList.add( daoUtil.getInt( 1 ) );
433 }
434 }
435 return announceIdList;
436 }
437
438
439
440
441 @Override
442 public List<Integer> findIdAnnouncesByDatePublication( long lMinPublicationTime, Plugin plugin )
443 {
444 List<Integer> announceIdList = new ArrayList<>( );
445 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ID_BY_TIME_PUBLICATION, plugin ) )
446 {
447 daoUtil.setLong( 1, lMinPublicationTime );
448 daoUtil.executeQuery( );
449
450 while ( daoUtil.next( ) )
451 {
452 announceIdList.add( daoUtil.getInt( 1 ) );
453 }
454 }
455 return announceIdList;
456 }
457
458
459
460
461
462
463
464
465 @Override
466 public void insertAnnounceResponse( int nIdAnnounce, int nIdResponse, boolean bIsImage, Plugin plugin )
467 {
468 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_ANNOUNCE_RESPONSE, plugin ) )
469 {
470 daoUtil.setInt( 1, nIdAnnounce );
471 daoUtil.setInt( 2, nIdResponse );
472 daoUtil.setBoolean( 3, bIsImage );
473 daoUtil.executeUpdate( );
474 }
475 }
476
477
478
479
480 @Override
481 public List<Integer> findListIdResponse( int nIdAnnounce, Plugin plugin )
482 {
483
484 List<Integer> listIdResponse = new ArrayList<>( );
485 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ANNOUNCE_RESPONSE_LIST, plugin ) )
486 {
487 daoUtil.setInt( 1, nIdAnnounce );
488 daoUtil.executeQuery( );
489 while ( daoUtil.next( ) )
490 {
491 listIdResponse.add( daoUtil.getInt( 1 ) );
492 }
493 }
494 return listIdResponse;
495 }
496
497
498
499
500 @Override
501 public List<Integer> findListIdImageResponse( int nIdAnnounce, Plugin plugin )
502 {
503 List<Integer> listIdResponse = new ArrayList<>( );
504 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ANNOUNCE_IMAGE_RESPONSE_LIST, plugin ) )
505 {
506 daoUtil.setInt( 1, nIdAnnounce );
507 daoUtil.setBoolean( 2, Boolean.TRUE );
508 daoUtil.executeQuery( );
509
510 while ( daoUtil.next( ) )
511 {
512 listIdResponse.add( daoUtil.getInt( 1 ) );
513 }
514 }
515
516 return listIdResponse;
517 }
518
519
520
521
522 @Override
523 public Integer findIdByImageResponse( int nIdResponse, Plugin plugin )
524 {
525 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ANNOUNCE_BY_IMAGE_RESPONSE, plugin ) )
526 {
527 daoUtil.setInt( 1, nIdResponse );
528 daoUtil.setBoolean( 2, Boolean.TRUE );
529 daoUtil.executeQuery( );
530
531 if ( daoUtil.next( ) )
532 {
533 return daoUtil.getInt( 1 );
534 }
535 }
536 return null;
537 }
538
539
540
541
542 @Override
543 public void deleteAnnounceResponse( int nIdAnnounce, Plugin plugin )
544 {
545 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_ANNOUNCE_RESPONSE, plugin ) )
546 {
547 daoUtil.setInt( 1, nIdAnnounce );
548 daoUtil.executeUpdate( );
549 }
550 }
551
552
553
554
555
556
557
558
559
560 private Announce getAnnounceWithCategory( DAOUtil daoUtil )
561 {
562 Announcenounce/business/Announce.html#Announce">Announce announce = new Announce( );
563 Categorynounce/business/Category.html#Category">Category category = new Category( );
564
565 int nIndex = 1;
566 announce.setId( daoUtil.getInt( nIndex++ ) );
567 announce.setTitle( daoUtil.getString( nIndex++ ) );
568 announce.setDescription( daoUtil.getString( nIndex++ ) );
569 announce.setPrice( daoUtil.getDouble( nIndex++ ) );
570 announce.setDateCreation( daoUtil.getTimestamp( nIndex++ ) );
571 announce.setDateModification( daoUtil.getTimestamp( nIndex++ ) );
572 announce.setUserName( daoUtil.getString( nIndex++ ) );
573 announce.setUserLastName( daoUtil.getString( nIndex++ ) );
574 announce.setUserSecondName( daoUtil.getString( nIndex++ ) );
575 announce.setContactInformation( daoUtil.getString( nIndex++ ) );
576 announce.setPublished( daoUtil.getBoolean( nIndex++ ) );
577 announce.setSuspended( daoUtil.getBoolean( nIndex++ ) );
578 announce.setSuspendedByUser( daoUtil.getBoolean( nIndex++ ) );
579 announce.setTags( daoUtil.getString( nIndex++ ) );
580 announce.setHasPictures( daoUtil.getBoolean( nIndex++ ) );
581 announce.setTimePublication( daoUtil.getLong( nIndex++ ) );
582 announce.setHasNotify( daoUtil.getInt( nIndex++ ) );
583
584 category.setId( daoUtil.getInt( nIndex++ ) );
585 category.setLabel( daoUtil.getString( nIndex++ ) );
586 category.setDisplayPrice( daoUtil.getBoolean( nIndex++ ) );
587 category.setIdSector( daoUtil.getInt( nIndex ) );
588
589 announce.setCategory( category );
590
591 return announce;
592 }
593
594
595
596
597
598
599
600
601 private String getOrderBy( AnnounceSort announceSort )
602 {
603 return ORDER_BY + announceSort.getSortColumn( ) + CONSTANT_SPACE + ( announceSort.getSortAsc( ) ? ORDER_BY_ASCENDING : ORDER_BY_DESCENDING );
604 }
605 }