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