View Javadoc
1   /*
2    * Copyright (c) 2002-2022, 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.portal.business.stylesheet;
35  
36  import fr.paris.lutece.portal.service.util.AppException;
37  import fr.paris.lutece.util.sql.DAOUtil;
38  
39  import java.sql.Statement;
40  import java.util.ArrayList;
41  import java.util.Collection;
42  
43  /**
44   * This class provides Data Access methods for StyleSheet objects
45   */
46  public final class StyleSheetDAO implements IStyleSheetDAO
47  {
48      // Constants
49      private static final String SQL_QUERY_SELECT = " SELECT a.description , a.file_name , a.source , b.id_style , b.id_mode " + " FROM core_stylesheet a "
50              + " LEFT JOIN core_style_mode_stylesheet b ON a.id_stylesheet = b.id_stylesheet " + " WHERE a.id_stylesheet = ? ";
51      private static final String SQL_QUERY_INSERT = " INSERT INTO core_stylesheet ( description , file_name, source ) " + " VALUES ( ? ,?, ? )";
52      private static final String SQL_QUERY_DELETE = " DELETE FROM core_stylesheet WHERE id_stylesheet = ? ";
53      private static final String SQL_QUERY_UPDATE = " UPDATE core_stylesheet SET id_stylesheet = ?, description = ?, file_name = ?, source = ? WHERE id_stylesheet = ?  ";
54      private static final String SQL_QUERY_SELECT_MODEID = " SELECT a.id_mode FROM core_mode a , core_style_mode_stylesheet b  "
55              + " WHERE a.id_mode = b.id_mode AND b.id_stylesheet = ?";
56      private static final String SQL_QUERY_COUNT_STYLESHEET = " SELECT count(*) FROM core_style_mode_stylesheet WHERE id_style = ? AND id_mode = ? ";
57      private static final String SQL_QUERY_INSERT_STYLEMODESTYLESHEET = " INSERT INTO core_style_mode_stylesheet ( id_style , id_mode , id_stylesheet ) "
58              + " VALUES ( ?, ? ,? )";
59      private static final String SQL_QUERY_UPDATE_STYLEMODESTYLESHEET = " UPDATE core_style_mode_stylesheet SET id_style = ? , id_mode = ?  "
60              + " WHERE id_stylesheet = ? ";
61      private static final String SQL_QUERY_DELETEE_STYLEMODESTYLESHEET = " DELETE FROM core_style_mode_stylesheet WHERE id_stylesheet = ? ";
62  
63      // /////////////////////////////////////////////////////////////////////////////////////
64      // Access methods to data
65  
66      /**
67       * Insert a new record in the table.
68       * 
69       * @param stylesheet
70       *            The StyleSheet object
71       */
72      public void insert( StyleSheet stylesheet )
73      {
74          try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS ) )
75          {
76  
77              int nIndex = 1;
78              daoUtil.setString( nIndex++, stylesheet.getDescription( ) );
79              daoUtil.setString( nIndex++, stylesheet.getFile( ) );
80              daoUtil.setBytes( nIndex, stylesheet.getSource( ) );
81  
82              daoUtil.executeUpdate( );
83  
84              if ( daoUtil.nextGeneratedKey( ) )
85              {
86                  stylesheet.setId( daoUtil.getGeneratedKeyInt( 1 ) );
87              }
88  
89              // Update of the table style_mode_stylesheet in the database
90              insertStyleModeStyleSheet( stylesheet );
91          }
92      }
93  
94      /**
95       * Load the data of Stylesheet from the table
96       * 
97       * @param nIdStylesheet
98       *            the identifier of the Stylesheet to load
99       * @return stylesheet
100      */
101     public StyleSheet load( int nIdStylesheet )
102     {
103         StyleSheet stylesheet = null;
104         try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT ) )
105         {
106             daoUtil.setInt( 1, nIdStylesheet );
107             daoUtil.executeQuery( );
108 
109             if ( daoUtil.next( ) )
110             {
111                 stylesheet = new StyleSheet( );
112                 stylesheet.setId( nIdStylesheet );
113                 stylesheet.setDescription( daoUtil.getString( 1 ) );
114                 stylesheet.setFile( daoUtil.getString( 2 ) );
115                 stylesheet.setSource( daoUtil.getBytes( 3 ) );
116                 stylesheet.setStyleId( daoUtil.getInt( 4 ) );
117                 stylesheet.setModeId( daoUtil.getInt( 5 ) );
118             }
119 
120         }
121 
122         return stylesheet;
123     }
124 
125     /**
126      * Delete the StyleSheet from the database whose identifier is specified in parameter
127      * 
128      * @param nIdStylesheet
129      *            the identifier of the StyleSheet to delete
130      */
131     public void delete( int nIdStylesheet )
132     {
133         try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE ) )
134         {
135             daoUtil.setInt( 1, nIdStylesheet );
136             daoUtil.executeUpdate( );
137         }
138 
139         // delete also into style_mode_stylesheet
140         deleteStyleModeStyleSheet( nIdStylesheet );
141     }
142 
143     /**
144      * Load the list of stylesheet
145      * 
146      * @param nModeId
147      *            The Mode identifier
148      * @return the list of the StyleSheet in form of a collection of StyleSheet objects
149      */
150     public Collection<StyleSheet> selectStyleSheetList( int nModeId )
151     {
152         Collection<StyleSheet> stylesheetList = new ArrayList<>( );
153 
154         String strSelect = " SELECT a.id_stylesheet , a.description , a.file_name ";
155         String strFrom = " FROM core_stylesheet a ";
156 
157         if ( nModeId != -1 )
158         {
159             strFrom = " FROM  core_stylesheet a , core_style_mode_stylesheet b " + " WHERE a.id_stylesheet = b.id_stylesheet " + " AND b.id_mode = ? ";
160         }
161 
162         strFrom += " ORDER BY a.description ";
163 
164         String strSQL = strSelect + strFrom;
165 
166         try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( strSQL ) )
167         {
168 
169             if ( nModeId != -1 )
170             {
171                 daoUtil.setInt( 1, nModeId );
172             }
173 
174             daoUtil.executeQuery( );
175 
176             while ( daoUtil.next( ) )
177             {
178                 StyleSheets/stylesheet/StyleSheet.html#StyleSheet">StyleSheet stylesheet = new StyleSheet( );
179 
180                 stylesheet.setId( daoUtil.getInt( 1 ) );
181                 stylesheet.setDescription( daoUtil.getString( 2 ) );
182                 stylesheet.setFile( daoUtil.getString( 3 ) );
183                 stylesheetList.add( stylesheet );
184             }
185 
186         }
187 
188         return stylesheetList;
189     }
190 
191     /**
192      * Update the record in the table
193      * 
194      * @param stylesheet
195      *            The stylesheet
196      */
197     public void store( StyleSheet stylesheet )
198     {
199         try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE ) )
200         {
201             daoUtil.setInt( 1, stylesheet.getId( ) );
202             daoUtil.setString( 2, stylesheet.getDescription( ) );
203             daoUtil.setString( 3, stylesheet.getFile( ) );
204             daoUtil.setBytes( 4, stylesheet.getSource( ) );
205             daoUtil.setInt( 5, stylesheet.getId( ) );
206 
207             daoUtil.executeUpdate( );
208         }
209 
210         // update the table style_mode_stylesheet
211         updateStyleModeStyleSheet( stylesheet );
212     }
213 
214     /**
215      * Insert a new record in the table style_mode_stylesheet
216      * 
217      * @param stylesheet
218      *            the instance of StyleSheet to insert in the table
219      */
220     private void insertStyleModeStyleSheet( StyleSheet stylesheet )
221     {
222         try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_STYLEMODESTYLESHEET ) )
223         {
224 
225             daoUtil.setInt( 1, stylesheet.getStyleId( ) );
226             daoUtil.setInt( 2, stylesheet.getModeId( ) );
227             daoUtil.setInt( 3, stylesheet.getId( ) );
228 
229             daoUtil.executeUpdate( );
230         }
231     }
232 
233     /**
234      * Updates the table style_mode_stylesheet with the data of the StyleShhet instance specified in parameter
235      * 
236      * @param stylesheet
237      *            the instance of the stylesheet to update
238      */
239     private void updateStyleModeStyleSheet( StyleSheet stylesheet )
240     {
241         try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_STYLEMODESTYLESHEET ) )
242         {
243 
244             daoUtil.setInt( 1, stylesheet.getStyleId( ) );
245             daoUtil.setInt( 2, stylesheet.getModeId( ) );
246             daoUtil.setInt( 3, stylesheet.getId( ) );
247 
248             daoUtil.executeUpdate( );
249         }
250     }
251 
252     /**
253      * Deletes the data in the table style_mode_stylesheet
254      *
255      * @param nStyleSheetId
256      *            the identifier of the stylesheet
257      */
258     private void deleteStyleModeStyleSheet( int nStyleSheetId )
259     {
260         try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETEE_STYLEMODESTYLESHEET ) )
261         {
262             daoUtil.setInt( 1, nStyleSheetId );
263             daoUtil.executeUpdate( );
264         }
265     }
266 
267     /**
268      * Returns the number of stylesheets associated to the style and the mode specified in parameter
269      * 
270      * @param nStyleId
271      *            the style id
272      * @param nModeId
273      *            the mode id
274      * @return the number of stylesheet associated
275      */
276     public int selectStyleSheetNbPerStyleMode( int nStyleId, int nModeId )
277     {
278         int nCount;
279         try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_COUNT_STYLESHEET ) )
280         {
281 
282             daoUtil.setInt( 1, nStyleId );
283             daoUtil.setInt( 2, nModeId );
284 
285             daoUtil.executeQuery( );
286 
287             if ( !daoUtil.next( ) )
288             {
289                 daoUtil.free( );
290                 throw new AppException( DAOUtil.MSG_EXCEPTION_SELECT_ERROR + nModeId + " StyleId " + nStyleId );
291             }
292 
293             nCount = ( daoUtil.getInt( 1 ) );
294 
295         }
296 
297         return nCount;
298     }
299 
300     /**
301      * Returns the identifier of the mode of the stylesheet whose identifier is specified in parameter
302      * 
303      * @param nIdStylesheet
304      *            the identifier of the stylesheet
305      * @return the identifier of the mode
306      */
307     public int selectModeId( int nIdStylesheet )
308     {
309         int nModeId;
310         try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_MODEID ) )
311         {
312 
313             daoUtil.setInt( 1, nIdStylesheet );
314             daoUtil.executeQuery( );
315 
316             if ( !daoUtil.next( ) )
317             {
318                 daoUtil.free( );
319                 throw new AppException( DAOUtil.MSG_EXCEPTION_SELECT_ERROR + nIdStylesheet );
320             }
321 
322             nModeId = ( daoUtil.getInt( 1 ) );
323 
324         }
325 
326         return nModeId;
327     }
328 }