View Javadoc
1   /*
2    * Copyright (c) 2002-2020, City of Paris
3    * All rights reserved.
4    *
5    * Redistribution and use in source and binary forms, with or without
6    * modification, are permitted provided that the following conditions
7    * are met:
8    *
9    *  1. Redistributions of source code must retain the above copyright notice
10   *     and the following disclaimer.
11   *
12   *  2. Redistributions in binary form must reproduce the above copyright notice
13   *     and the following disclaimer in the documentation and/or other materials
14   *     provided with the distribution.
15   *
16   *  3. Neither the name of 'Mairie de Paris' nor 'Lutece' nor the names of its
17   *     contributors may be used to endorse or promote products derived from
18   *     this software without specific prior written permission.
19   *
20   * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
21   * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
22   * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
23   * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDERS OR CONTRIBUTORS BE
24   * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
25   * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
26   * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
27   * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
28   * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
29   * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
30   * POSSIBILITY OF SUCH DAMAGE.
31   *
32   * License 1.0
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   * This class provides Data Access methods for Category objects
45   */
46  public final class CategoryDAO implements ICategoryDAO
47  {
48      // Constants
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      // ImageResource queries
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      //Access methods to data
66  
67      /**
68      * Load the list of Category
69      * @return The Collection of Category (empty collection is no result)
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      * Insert a new Category
100     * @param category The object category to insert
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      * Auto increment the primary key for the new category
120      * @return the new primary key for category
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     * Load the data of Category from the table
140     * @param nIdCategory The identifier of the category
141     * @return The Instance of the object Category
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      * Delete a record from the table
171      * @param nIdCategory The identifier of the object Category
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      * Update the record in the table
184      * @param category The instance of the Category to update
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      * Load the data of Category from the table
203      * @param strCategoryName The name of the category
204      * @return The Collection of Category (empty collection is no result)
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      * Delete a link between category and document
236      * @param nIdCategory The identifier of the object Category
237      * @param nIdDocument The id of document
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      * Select a list of Id Documents for a specified category
251      * @param nIdCategory The category name
252      * @return The array of Id Document
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         // Convert ArrayList to Int[]
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      * Delete all links for a category
282      * @param nIdCategory The identifier of the object Category
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      * Return the number of documents linked to a category
295      * @param nIdCategory The category name
296      * @return count of id document
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      * Return the image resource corresponding to the category id
317      * @param nCategoryId The identifier of Category object
318      * @return The image resource
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 }