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.calendar.business.category;
35
36 import fr.paris.lutece.portal.service.image.ImageResource;
37 import fr.paris.lutece.portal.service.plugin.Plugin;
38 import fr.paris.lutece.util.sql.DAOUtil;
39
40 import java.util.ArrayList;
41 import java.util.Collection;
42
43
44
45
46
47 public final class CategoryDAO implements ICategoryDAO
48 {
49
50 private static final String SQL_QUERY_MAX_PK = " SELECT MAX(id_category) FROM calendar_category ";
51 private static final String SQL_QUERY_SELECT_BY_NAME = " SELECT id_category, description, icon_content, icon_mime_type, workgroup_key FROM calendar_category WHERE calendar_category_name = ? ";
52 private static final String SQL_QUERY_SELECTALL = " SELECT id_category, calendar_category_name, description, icon_content, icon_mime_type, workgroup_key FROM calendar_category ORDER BY calendar_category_name";
53 private static final String SQL_QUERY_INSERT = " INSERT INTO calendar_category ( id_category, calendar_category_name, description, icon_content, icon_mime_type, workgroup_key ) VALUES ( ?, ?, ?, ?, ?, ? )";
54 private static final String SQL_QUERY_SELECT = " SELECT calendar_category_name, description, icon_content, icon_mime_type ,workgroup_key FROM calendar_category WHERE id_category = ? ";
55 private static final String SQL_QUERY_DELETE = " DELETE FROM calendar_category WHERE id_category = ? ";
56 private static final String SQL_QUERY_UPDATE = " UPDATE calendar_category SET calendar_category_name = ?, description = ?, icon_content = ?, icon_mime_type = ?, workgroup_key= ? WHERE id_category = ?";
57 private static final String SQL_QUERY_DELETE_LINK_CATEGORY_CALENDAR = " DELETE FROM calendar_category WHERE id_category = ? ";
58 private static final String SQL_QUERY_SELECTALL_ID_EVENT = " SELECT a.id_event FROM calendar_category_link a WHERE a.id_category = ? ";
59 private static final String SQL_QUERY_DELETE_LINKS_CATEGORY = " DELETE FROM calendar_category_link WHERE id_category = ? ";
60 private static final String SQL_QUERY_SELECT_COUNT_OF_EVENT_ID = " SELECT COUNT(*) FROM calendar_category_link WHERE id_category = ?";
61 private static final String SQL_QUERY_SELECT_EVENT_CATEGORIES = " SELECT a.id_category, a.calendar_category_name, a.description, a.icon_content, a.icon_mime_type , a.workgroup_key" +
62 " FROM calendar_category a, calendar_category_link b" +
63 " WHERE a.id_category = b.id_category AND b.id_event = ?";
64
65
66 private static final String SQL_QUERY_SELECT_RESOURCE_IMAGE = " SELECT icon_content, icon_mime_type FROM calendar_category WHERE id_category = ? ";
67
68
69
70
71
72
73
74
75
76 public Collection<Category> selectAll( Plugin plugin )
77 {
78 int nParam;
79 Collection<Category> listCategory = new ArrayList<Category>( );
80 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
81 daoUtil.executeQuery( );
82
83 while ( daoUtil.next( ) )
84 {
85 nParam = 0;
86
87 Category category = new Category( );
88 category.setId( daoUtil.getInt( ++nParam ) );
89 category.setName( daoUtil.getString( ++nParam ) );
90 category.setDescription( daoUtil.getString( ++nParam ) );
91 category.setIconContent( daoUtil.getBytes( ++nParam ) );
92 category.setIconMimeType( daoUtil.getString( ++nParam ) );
93 category.setWorkgroup( daoUtil.getString( ++nParam ) );
94
95 listCategory.add( category );
96 }
97
98 daoUtil.free( );
99
100 return listCategory;
101 }
102
103
104
105
106
107
108 public void insert( Category category, Plugin plugin )
109 {
110 int nParam = 0;
111 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
112
113 daoUtil.setInt( ++nParam, getNewPrimaryKey( plugin ) );
114 daoUtil.setString( ++nParam, category.getName( ) );
115 daoUtil.setString( ++nParam, category.getDescription( ) );
116 daoUtil.setBytes( ++nParam, category.getIconContent( ) );
117 daoUtil.setString( ++nParam, category.getIconMimeType( ) );
118 daoUtil.setString( ++nParam, category.getWorkgroup( ) );
119
120 daoUtil.executeUpdate( );
121 daoUtil.free( );
122 }
123
124
125
126
127
128
129 private int getNewPrimaryKey( Plugin plugin )
130 {
131 int nNewPrimaryKey = -1;
132 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_MAX_PK, plugin );
133 daoUtil.executeQuery( );
134
135 if ( daoUtil.next( ) )
136 {
137 nNewPrimaryKey = daoUtil.getInt( 1 );
138 }
139
140 daoUtil.free( );
141
142 return ++nNewPrimaryKey;
143 }
144
145
146
147
148
149
150
151 public Category load( int nIdCategory, Plugin plugin )
152 {
153 int nParam;
154 Category category = null;
155 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
156 daoUtil.setInt( 1, nIdCategory );
157
158 daoUtil.executeQuery( );
159
160 if ( daoUtil.next( ) )
161 {
162 nParam = 0;
163 category = new Category( );
164 category.setId( nIdCategory );
165 category.setName( daoUtil.getString( ++nParam ) );
166 category.setDescription( daoUtil.getString( ++nParam ) );
167 category.setIconContent( daoUtil.getBytes( ++nParam ) );
168 category.setIconMimeType( daoUtil.getString( ++nParam ) );
169 category.setWorkgroup( daoUtil.getString( ++nParam ) );
170 }
171
172 daoUtil.free( );
173
174 return category;
175 }
176
177
178
179
180
181
182
183 public Collection<Category> selectByEvent( int nIdEvent, Plugin plugin )
184 {
185 int nParam;
186 Collection<Category> listCategory = new ArrayList<Category>( );
187 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_EVENT_CATEGORIES, plugin );
188 daoUtil.setInt( 1, nIdEvent );
189 daoUtil.executeQuery( );
190
191 while ( daoUtil.next( ) )
192 {
193 nParam = 0;
194
195 Category category = new Category( );
196 category.setId( daoUtil.getInt( ++nParam ) );
197 category.setName( daoUtil.getString( ++nParam ) );
198 category.setDescription( daoUtil.getString( ++nParam ) );
199 category.setIconContent( daoUtil.getBytes( ++nParam ) );
200 category.setIconMimeType( daoUtil.getString( ++nParam ) );
201 category.setWorkgroup( daoUtil.getString( ++nParam ) );
202
203 listCategory.add( category );
204 }
205
206 daoUtil.free( );
207
208 return listCategory;
209 }
210
211
212
213
214
215
216 public void delete( int nIdCategory, Plugin plugin )
217 {
218 int nParam = 0;
219 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
220 daoUtil.setInt( ++nParam, nIdCategory );
221 daoUtil.executeUpdate( );
222 daoUtil.free( );
223 }
224
225
226
227
228
229
230 public void store( Category category, Plugin plugin )
231 {
232 int nParam = 0;
233 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
234 daoUtil.setString( ++nParam, category.getName( ) );
235 daoUtil.setString( ++nParam, category.getDescription( ) );
236 daoUtil.setBytes( ++nParam, category.getIconContent( ) );
237 daoUtil.setString( ++nParam, category.getIconMimeType( ) );
238 daoUtil.setString( ++nParam, category.getWorkgroup( ) );
239 daoUtil.setInt( ++nParam, category.getId( ) );
240
241 daoUtil.executeUpdate( );
242 daoUtil.free( );
243 }
244
245
246
247
248
249
250
251 public Collection<Category> selectByName( String strCategoryName, Plugin plugin )
252 {
253 int nParam;
254 Collection<Category> listCategory = new ArrayList<Category>( );
255 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_NAME, plugin );
256 daoUtil.setString( 1, strCategoryName );
257 daoUtil.executeQuery( );
258
259 while ( daoUtil.next( ) )
260 {
261 nParam = 0;
262
263 Category category = new Category( );
264 category.setId( daoUtil.getInt( ++nParam ) );
265 category.setName( strCategoryName );
266 category.setDescription( daoUtil.getString( ++nParam ) );
267 category.setIconContent( daoUtil.getBytes( ++nParam ) );
268 category.setIconMimeType( daoUtil.getString( ++nParam ) );
269 category.setWorkgroup( daoUtil.getString( ++nParam ) );
270
271 listCategory.add( category );
272 }
273
274 daoUtil.free( );
275
276 return listCategory;
277 }
278
279
280
281
282
283
284
285 public void deleteLinkCategoryEvent( int nIdCategory, int nIdEvent, Plugin plugin )
286 {
287 int nParam = 0;
288 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_LINK_CATEGORY_CALENDAR, plugin );
289 daoUtil.setInt( ++nParam, nIdEvent );
290 daoUtil.setInt( ++nParam, nIdCategory );
291 daoUtil.executeUpdate( );
292 daoUtil.free( );
293 }
294
295
296
297
298
299
300
301 public int[] selectAllIdEvent( int nIdCategory, Plugin plugin )
302 {
303 Collection<Integer> listIdEvent = new ArrayList<Integer>( );
304 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ID_EVENT, plugin );
305 daoUtil.setInt( 1, nIdCategory );
306 daoUtil.executeQuery( );
307
308 while ( daoUtil.next( ) )
309 {
310 listIdEvent.add( daoUtil.getInt( 1 ) );
311 }
312
313 daoUtil.free( );
314
315
316 int[] arrayIdEvent = new int[listIdEvent.size( )];
317 int i = 0;
318
319 for ( Integer nIdDocument : listIdEvent )
320 {
321 arrayIdEvent[i++] = nIdDocument.intValue( );
322 }
323
324 return arrayIdEvent;
325 }
326
327
328
329
330
331
332 public void deleteLinksCategory( int nIdCategory, Plugin plugin )
333 {
334 int nParam = 0;
335 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_LINKS_CATEGORY, plugin );
336 daoUtil.setInt( ++nParam, nIdCategory );
337 daoUtil.executeUpdate( );
338 daoUtil.free( );
339 }
340
341
342
343
344
345
346
347 public int selectCountIdEvents( int nIdCategory, Plugin plugin )
348 {
349 int nCountEventsId = -1;
350 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_COUNT_OF_EVENT_ID, plugin );
351 daoUtil.setInt( 1, nIdCategory );
352 daoUtil.executeQuery( );
353
354 while ( daoUtil.next( ) )
355 {
356 nCountEventsId = daoUtil.getInt( 1 );
357 }
358
359 daoUtil.free( );
360
361 return nCountEventsId;
362 }
363
364
365
366
367
368
369
370 public ImageResource loadImageResource( int nCategoryId, Plugin plugin )
371 {
372 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_RESOURCE_IMAGE, plugin );
373 daoUtil.setInt( 1, nCategoryId );
374 daoUtil.executeQuery( );
375
376 ImageResource image = null;
377
378 if ( daoUtil.next( ) )
379 {
380 image = new ImageResource( );
381 image.setImage( daoUtil.getBytes( 1 ) );
382 image.setMimeType( daoUtil.getString( 2 ) );
383 }
384
385 daoUtil.free( );
386
387 return image;
388 }
389 }