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.Date;
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 class AnnounceSearchFilterDAO implements IAnnounceSearchFilterDAO
50 {
51 private static final String SQL_QUERY_NEW_PRIMARY_KEY = " SELECT max(id_filter) FROM announce_search_filters ";
52 private static final String SQL_QUERY_SELECT = " SELECT id_filter, id_category, keywords, date_min, date_max, price_min, price_max FROM announce_search_filters ";
53 private static final String SQL_QUERY_SELECT_LIST_ID = SQL_QUERY_SELECT + " WHERE id_filter IN ( ";
54 private static final String SQL_QUERY_SELECT_BY_PRIMARY_KEY = SQL_QUERY_SELECT + " WHERE id_filter = ? ";
55 private static final String SQL_QUERY_INSERT = " INSERT INTO announce_search_filters ( id_filter, id_category, keywords, date_min, date_max, price_min, price_max ) VALUES (?,?,?,?,?,?,?) ";
56 private static final String SQL_QUERY_UPDATE = " UPDATE announce_search_filters SET id_category = ?, keywords = ?, date_min = ?, date_max = ?, price_min = ?, price_max = ? WHERE id_filter = ? ";
57 private static final String SQL_QUERY_DELETE = " DELETE FROM announce_search_filters WHERE id_filter = ? ";
58 private static final String SQL_QUERY_DELETE_BY_ID_CATEGORY = " DELETE FROM announce_search_filters WHERE id_category = ? ";
59 private static final String CONSTANT_COMA = ",";
60 private static final String CONSTANT_CLOSE_PARENTHESIS = ")";
61
62
63
64
65
66
67
68
69 private int getNewPrimaryKey( Plugin plugin )
70 {
71 int nRes = 1;
72 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PRIMARY_KEY, plugin ) )
73 {
74 daoUtil.executeQuery( );
75
76 if ( daoUtil.next( ) )
77 {
78 nRes = daoUtil.getInt( 1 ) + 1;
79 }
80 }
81 return nRes;
82 }
83
84
85
86
87 @Override
88 public AnnounceSearchFilter findByPrimaryKey( int nIdFilter, Plugin plugin )
89 {
90 AnnounceSearchFilter filter = null;
91 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_PRIMARY_KEY, plugin ) )
92 {
93 daoUtil.setInt( 1, nIdFilter );
94 daoUtil.executeQuery( );
95
96 if ( daoUtil.next( ) )
97 {
98 filter = getFilterFromDAO( daoUtil );
99 }
100 }
101 return filter;
102 }
103
104
105
106
107 @Override
108 public synchronized void create( AnnounceSearchFilter filter, Plugin plugin )
109 {
110 filter.setIdFilter( getNewPrimaryKey( plugin ) );
111
112 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin ) )
113 {
114 int nIndex = 1;
115 daoUtil.setInt( nIndex++, filter.getIdFilter( ) );
116 daoUtil.setInt( nIndex++, filter.getIdCategory( ) );
117 daoUtil.setString( nIndex++, filter.getKeywords( ) );
118 daoUtil.setDate( nIndex++, ( filter.getDateMin( ) == null ) ? null : new Date( filter.getDateMin( ).getTime( ) ) );
119 daoUtil.setDate( nIndex++, ( filter.getDateMax( ) == null ) ? null : new Date( filter.getDateMax( ).getTime( ) ) );
120 daoUtil.setInt( nIndex++, filter.getPriceMin( ) );
121 daoUtil.setInt( nIndex, filter.getPriceMax( ) );
122 daoUtil.executeUpdate( );
123 }
124 }
125
126
127
128
129 @Override
130 public void update( AnnounceSearchFilter filter, Plugin plugin )
131 {
132 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
133 {
134 int nIndex = 1;
135 daoUtil.setInt( nIndex++, filter.getIdCategory( ) );
136 daoUtil.setString( nIndex++, filter.getKeywords( ) );
137 daoUtil.setDate( nIndex++, new Date( filter.getDateMin( ).getTime( ) ) );
138 daoUtil.setDate( nIndex++, new Date( filter.getDateMax( ).getTime( ) ) );
139 daoUtil.setInt( nIndex++, filter.getPriceMin( ) );
140 daoUtil.setInt( nIndex++, filter.getPriceMax( ) );
141 daoUtil.setInt( nIndex, filter.getIdFilter( ) );
142
143 daoUtil.executeUpdate( );
144 }
145 }
146
147
148
149
150 @Override
151 public void delete( int nIdFilter, Plugin plugin )
152 {
153 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
154 {
155 daoUtil.setInt( 1, nIdFilter );
156 daoUtil.executeUpdate( );
157 }
158 }
159
160
161
162
163 @Override
164 public void deleteByIdCategory( int nIdCategory, Plugin plugin )
165 {
166 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_ID_CATEGORY, plugin ) )
167 {
168 daoUtil.setInt( 1, nIdCategory );
169 daoUtil.executeUpdate( );
170 }
171 }
172
173
174
175
176 @Override
177 public List<AnnounceSearchFilter> findAll( Plugin plugin )
178 {
179 List<AnnounceSearchFilter> listFilters = new ArrayList<>( );
180 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
181 {
182 daoUtil.executeQuery( );
183
184 while ( daoUtil.next( ) )
185 {
186 listFilters.add( getFilterFromDAO( daoUtil ) );
187 }
188 }
189 return listFilters;
190 }
191
192
193
194
195 @Override
196 public List<AnnounceSearchFilter> findByListId( List<Integer> listIdFilters, Plugin plugin )
197 {
198 if ( CollectionUtils.isEmpty( listIdFilters ) )
199 {
200 return null;
201 }
202
203 StringBuilder sbSql = new StringBuilder( SQL_QUERY_SELECT_LIST_ID );
204 boolean bHasContent = false;
205
206 for ( int nIdFilters : listIdFilters )
207 {
208 if ( bHasContent )
209 {
210 sbSql.append( CONSTANT_COMA );
211 }
212
213 sbSql.append( nIdFilters );
214 }
215
216 sbSql.append( CONSTANT_CLOSE_PARENTHESIS );
217 List<AnnounceSearchFilter> listFilters = new ArrayList<>( );
218 try ( DAOUtil daoUtil = new DAOUtil( sbSql.toString( ), plugin ) )
219 {
220 daoUtil.executeQuery( );
221
222 while ( daoUtil.next( ) )
223 {
224 listFilters.add( getFilterFromDAO( daoUtil ) );
225 }
226 }
227 return listFilters;
228 }
229
230
231
232
233
234
235
236
237
238 private AnnounceSearchFilter getFilterFromDAO( DAOUtil daoUtil )
239 {
240 int nIndex = 1;
241 AnnounceSearchFilteriness/AnnounceSearchFilter.html#AnnounceSearchFilter">AnnounceSearchFilter filter = new AnnounceSearchFilter( );
242 filter.setIdFilter( daoUtil.getInt( nIndex++ ) );
243 filter.setIdCategory( daoUtil.getInt( nIndex++ ) );
244 filter.setKeywords( daoUtil.getString( nIndex++ ) );
245 filter.setDateMin( daoUtil.getDate( nIndex++ ) );
246 filter.setDateMax( daoUtil.getDate( nIndex++ ) );
247 filter.setPriceMin( daoUtil.getInt( nIndex++ ) );
248 filter.setPriceMax( daoUtil.getInt( nIndex ) );
249
250 return filter;
251 }
252 }