View Javadoc
1   /*
2    * Copyright (c) 2002-2017, Mairie de 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.directory.business;
35  
36  import fr.paris.lutece.portal.service.plugin.Plugin;
37  import fr.paris.lutece.util.sql.DAOUtil;
38  
39  import java.sql.Date;
40  
41  import java.util.ArrayList;
42  import java.util.List;
43  
44  /**
45   * This class provides Data Access methods for Field objects
46   */
47  public final class FieldDAO implements IFieldDAO
48  {
49      // Constants
50      private static final String SQL_QUERY_NEW_PK = "SELECT max( id_field ) FROM directory_field";
51      private static final String SQL_QUERY_FIND_BY_PRIMARY_KEY = "SELECT id_field,id_entry,title,default_value,height,width,"
52              + "is_default_value,max_size_enter,field_position,value_type_date,role_key,workgroup_key"
53              + ",is_shown_in_result_list,is_shown_in_result_record,image_type " + " FROM directory_field WHERE id_field = ? ";
54      private static final String SQL_QUERY_FIND_BY_VALUE = "SELECT id_field,id_entry,title,default_value,height,width,"
55              + "is_default_value,max_size_enter,field_position,value_type_date,role_key,workgroup_key"
56              + ",is_shown_in_result_list,is_shown_in_result_record, image_type" + " FROM directory_field WHERE id_entry=? and default_value = ? ";
57      private static final String SQL_QUERY_INSERT = "INSERT INTO directory_field(id_field,id_entry,title,default_value,height,"
58              + "width,is_default_value,max_size_enter,field_position,value_type_date,role_key,workgroup_key,is_shown_in_result_list,"
59              + " is_shown_in_result_record, image_type)" + " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
60      private static final String SQL_QUERY_DELETE = "DELETE FROM directory_field WHERE id_field = ? ";
61      private static final String SQL_QUERY_INSERT_VERIF_BY = "INSERT INTO directory_verify_by(id_field,id_expression) VALUES(?,?) ";
62      private static final String SQL_QUERY_DELETE_VERIF_BY = "DELETE FROM directory_verify_by WHERE id_field = ? and id_expression= ?";
63      private static final String SQL_QUERY_UPDATE = "UPDATE directory_field SET "
64              + "id_field=?,id_entry=?,title=?,default_value=?,height=?,width=?,is_default_value=?,max_size_enter=?, "
65              + "field_position=?,value_type_date=?,role_key=?,workgroup_key=?,is_shown_in_result_list=?,is_shown_in_result_record=?, image_type=?"
66              + "  WHERE id_field = ?";
67      private static final String SQL_QUERY_SELECT_FIELD_BY_ID_ENTRY = "SELECT id_field,id_entry,title,default_value,height,width,is_default_value,"
68              + "max_size_enter,field_position,value_type_date,role_key,workgroup_key,is_shown_in_result_list"
69              + ",is_shown_in_result_record, image_type FROM directory_field  WHERE id_entry = ? ORDER BY field_position";
70      private static final String SQL_QUERY_NEW_POSITION = "SELECT MAX(field_position)" + " FROM directory_field ";
71      private static final String SQL_QUERY_SELECT_REGULAR_EXPRESSION_BY_ID_FIELD = "SELECT id_expression " + " FROM directory_verify_by where id_field=?";
72      private static final String SQL_QUERY_COUNT_FIELD_BY_ID_REGULAR_EXPRESSION = "SELECT COUNT(id_field) "
73              + " FROM directory_verify_by where id_expression = ?";
74  
75      /**
76       * Generates a new primary key
77       *
78       * @param plugin
79       *            the plugin
80       * @return The new primary key
81       */
82      public int newPrimaryKey( Plugin plugin )
83      {
84          DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
85          daoUtil.executeQuery( );
86  
87          int nKey;
88  
89          if ( !daoUtil.next( ) )
90          {
91              // if the table is empty
92              nKey = 1;
93          }
94  
95          nKey = daoUtil.getInt( 1 ) + 1;
96          daoUtil.free( );
97  
98          return nKey;
99      }
100 
101     /**
102      * Generates a new field position
103      * 
104      * @param plugin
105      *            the plugin
106      * @return the new entry position
107      */
108     private int newPosition( Plugin plugin )
109     {
110         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_POSITION, plugin );
111         daoUtil.executeQuery( );
112 
113         int nPos;
114 
115         if ( !daoUtil.next( ) )
116         {
117             // if the table is empty
118             nPos = 1;
119         }
120 
121         nPos = daoUtil.getInt( 1 ) + 1;
122         daoUtil.free( );
123 
124         return nPos;
125     }
126 
127     /**
128      * {@inheritDoc}
129      */
130     @Override
131     public synchronized int insert( Field field, Plugin plugin )
132     {
133         field.setIdField( newPrimaryKey( plugin ) );
134         field.setPosition( newPosition( plugin ) );
135 
136         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
137         daoUtil.setInt( 1, field.getIdField( ) );
138         daoUtil.setInt( 2, field.getEntry( ).getIdEntry( ) );
139         daoUtil.setString( 3, field.getTitle( ) );
140         daoUtil.setString( 4, field.getValue( ) );
141         daoUtil.setInt( 5, field.getHeight( ) );
142         daoUtil.setInt( 6, field.getWidth( ) );
143         daoUtil.setBoolean( 7, field.isDefaultValue( ) );
144         daoUtil.setInt( 8, field.getMaxSizeEnter( ) );
145         daoUtil.setInt( 9, field.getPosition( ) );
146         daoUtil.setDate( 10, ( field.getValueTypeDate( ) == null ) ? null : new Date( field.getValueTypeDate( ).getTime( ) ) );
147 
148         daoUtil.setString( 11, field.getRoleKey( ) );
149         daoUtil.setString( 12, field.getWorkgroup( ) );
150         daoUtil.setBoolean( 13, field.isShownInResultList( ) );
151         daoUtil.setBoolean( 14, field.isShownInResultRecord( ) );
152         daoUtil.setString( 15, field.getImageType( ) );
153 
154         daoUtil.executeUpdate( );
155         daoUtil.free( );
156 
157         return field.getIdField( );
158     }
159 
160     /**
161      * {@inheritDoc}
162      */
163     @Override
164     public Field load( int nId, Plugin plugin )
165     {
166         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_PRIMARY_KEY, plugin );
167         daoUtil.setInt( 1, nId );
168         daoUtil.executeQuery( );
169 
170         Field field = null;
171         IEntry entry = null;
172 
173         if ( daoUtil.next( ) )
174         {
175             field = new Field( );
176             field.setIdField( daoUtil.getInt( 1 ) );
177             // parent entry
178             entry = new Entry( );
179             entry.setIdEntry( daoUtil.getInt( 2 ) );
180             field.setEntry( entry );
181             field.setTitle( daoUtil.getString( 3 ) );
182             field.setValue( daoUtil.getString( 4 ) );
183             field.setHeight( daoUtil.getInt( 5 ) );
184             field.setWidth( daoUtil.getInt( 6 ) );
185             field.setDefaultValue( daoUtil.getBoolean( 7 ) );
186             field.setMaxSizeEnter( daoUtil.getInt( 8 ) );
187             field.setPosition( daoUtil.getInt( 9 ) );
188             field.setValueTypeDate( daoUtil.getDate( 10 ) );
189             field.setRoleKey( daoUtil.getString( 11 ) );
190             field.setWorkgroup( daoUtil.getString( 12 ) );
191             field.setShownInResultList( daoUtil.getBoolean( 13 ) );
192             field.setShownInResultRecord( daoUtil.getBoolean( 14 ) );
193             field.setImageType( daoUtil.getString( 15 ) );
194         }
195 
196         daoUtil.free( );
197 
198         return field;
199     }
200 
201     /**
202      * {@inheritDoc}
203      */
204     @Override
205     public Field loadByValue( int nIdEntry, String strValue, Plugin plugin )
206     {
207         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_VALUE, plugin );
208         daoUtil.setInt( 1, nIdEntry );
209         daoUtil.setString( 2, strValue );
210         daoUtil.executeQuery( );
211 
212         Field field = null;
213         IEntry entry = null;
214 
215         if ( daoUtil.next( ) )
216         {
217             field = new Field( );
218             field.setIdField( daoUtil.getInt( 1 ) );
219             // parent entry
220             entry = new Entry( );
221             entry.setIdEntry( daoUtil.getInt( 2 ) );
222             field.setEntry( entry );
223             field.setTitle( daoUtil.getString( 3 ) );
224             field.setValue( daoUtil.getString( 4 ) );
225             field.setHeight( daoUtil.getInt( 5 ) );
226             field.setWidth( daoUtil.getInt( 6 ) );
227             field.setDefaultValue( daoUtil.getBoolean( 7 ) );
228             field.setMaxSizeEnter( daoUtil.getInt( 8 ) );
229             field.setPosition( daoUtil.getInt( 9 ) );
230             field.setValueTypeDate( daoUtil.getDate( 10 ) );
231             field.setRoleKey( daoUtil.getString( 11 ) );
232             field.setWorkgroup( daoUtil.getString( 12 ) );
233             field.setShownInResultList( daoUtil.getBoolean( 13 ) );
234             field.setShownInResultRecord( daoUtil.getBoolean( 14 ) );
235             field.setImageType( daoUtil.getString( 15 ) );
236         }
237 
238         daoUtil.free( );
239 
240         return field;
241     }
242 
243     /**
244      * {@inheritDoc}
245      */
246     @Override
247     public void delete( int nIdField, Plugin plugin )
248     {
249         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
250         daoUtil.setInt( 1, nIdField );
251         daoUtil.executeUpdate( );
252         daoUtil.free( );
253     }
254 
255     /**
256      * {@inheritDoc}
257      */
258     @Override
259     public void store( Field field, Plugin plugin )
260     {
261         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
262         daoUtil.setInt( 1, field.getIdField( ) );
263         daoUtil.setInt( 2, field.getEntry( ).getIdEntry( ) );
264         daoUtil.setString( 3, field.getTitle( ) );
265         daoUtil.setString( 4, field.getValue( ) );
266         daoUtil.setInt( 5, field.getHeight( ) );
267         daoUtil.setInt( 6, field.getWidth( ) );
268         daoUtil.setBoolean( 7, field.isDefaultValue( ) );
269         daoUtil.setInt( 8, field.getMaxSizeEnter( ) );
270         daoUtil.setInt( 9, field.getPosition( ) );
271         daoUtil.setDate( 10, ( field.getValueTypeDate( ) == null ) ? null : new Date( field.getValueTypeDate( ).getTime( ) ) );
272         daoUtil.setString( 11, field.getRoleKey( ) );
273         daoUtil.setString( 12, field.getWorkgroup( ) );
274         daoUtil.setBoolean( 13, field.isShownInResultList( ) );
275         daoUtil.setBoolean( 14, field.isShownInResultRecord( ) );
276         daoUtil.setString( 15, field.getImageType( ) );
277         daoUtil.setInt( 16, field.getIdField( ) );
278         daoUtil.executeUpdate( );
279         daoUtil.free( );
280     }
281 
282     /**
283      * {@inheritDoc}
284      */
285     @Override
286     public List<Field> selectFieldListByIdEntry( int nIdEntry, Plugin plugin )
287     {
288         List<Field> fieldList = new ArrayList<Field>( );
289         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_FIELD_BY_ID_ENTRY, plugin );
290         daoUtil.setInt( 1, nIdEntry );
291         daoUtil.executeQuery( );
292 
293         Field field = null;
294         IEntry entry = null;
295 
296         while ( daoUtil.next( ) )
297         {
298             field = new Field( );
299             field.setIdField( daoUtil.getInt( 1 ) );
300             // parent entry
301             entry = new Entry( );
302             entry.setIdEntry( daoUtil.getInt( 2 ) );
303             field.setEntry( entry );
304             field.setTitle( daoUtil.getString( 3 ) );
305             field.setValue( daoUtil.getString( 4 ) );
306             field.setHeight( daoUtil.getInt( 5 ) );
307             field.setWidth( daoUtil.getInt( 6 ) );
308             field.setDefaultValue( daoUtil.getBoolean( 7 ) );
309             field.setMaxSizeEnter( daoUtil.getInt( 8 ) );
310             field.setPosition( daoUtil.getInt( 9 ) );
311             field.setValueTypeDate( daoUtil.getDate( 10 ) );
312             field.setRoleKey( daoUtil.getString( 11 ) );
313             field.setWorkgroup( daoUtil.getString( 12 ) );
314             field.setShownInResultList( daoUtil.getBoolean( 13 ) );
315             field.setShownInResultRecord( daoUtil.getBoolean( 14 ) );
316             field.setImageType( daoUtil.getString( 15 ) );
317 
318             fieldList.add( field );
319         }
320 
321         daoUtil.free( );
322 
323         return fieldList;
324     }
325 
326     /**
327      * {@inheritDoc}
328      */
329     @Override
330     public void deleteVerifyBy( int nIdField, int nIdExpression, Plugin plugin )
331     {
332         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_VERIF_BY, plugin );
333         daoUtil.setInt( 1, nIdField );
334         daoUtil.setInt( 2, nIdExpression );
335         daoUtil.executeUpdate( );
336         daoUtil.free( );
337     }
338 
339     /**
340      * {@inheritDoc}
341      */
342     @Override
343     public void insertVerifyBy( int nIdField, int nIdExpression, Plugin plugin )
344     {
345         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_VERIF_BY, plugin );
346         daoUtil.setInt( 1, nIdField );
347         daoUtil.setInt( 2, nIdExpression );
348         daoUtil.executeUpdate( );
349         daoUtil.free( );
350     }
351 
352     /**
353      * {@inheritDoc}
354      */
355     @Override
356     public List<Integer> selectListRegularExpressionKeyByIdField( int nIdField, Plugin plugin )
357     {
358         List<Integer> regularExpressionList = new ArrayList<Integer>( );
359         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_REGULAR_EXPRESSION_BY_ID_FIELD, plugin );
360         daoUtil.setInt( 1, nIdField );
361         daoUtil.executeQuery( );
362 
363         while ( daoUtil.next( ) )
364         {
365             regularExpressionList.add( daoUtil.getInt( 1 ) );
366         }
367 
368         daoUtil.free( );
369 
370         return regularExpressionList;
371     }
372 
373     /**
374      * {@inheritDoc}
375      */
376     @Override
377     public boolean isRegularExpressionIsUse( int nIdExpression, Plugin plugin )
378     {
379         int nNumberEntry = 0;
380 
381         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_COUNT_FIELD_BY_ID_REGULAR_EXPRESSION, plugin );
382         daoUtil.setInt( 1, nIdExpression );
383         daoUtil.executeQuery( );
384 
385         if ( daoUtil.next( ) )
386         {
387             nNumberEntry = daoUtil.getInt( 1 );
388         }
389 
390         daoUtil.free( );
391 
392         return nNumberEntry != 0;
393     }
394 }