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