AttributeFieldDAO.java

  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.user.attribute;

  35. import fr.paris.lutece.portal.service.util.AppLogService;
  36. import fr.paris.lutece.util.sql.DAOUtil;

  37. import java.sql.Statement;
  38. import java.util.ArrayList;
  39. import java.util.List;

  40. /**
  41.  *
  42.  * AttributeFieldDAO
  43.  *
  44.  */
  45. public class AttributeFieldDAO implements IAttributeFieldDAO
  46. {

  47.     // NEW POSITION
  48.     private static final String SQL_QUERY_NEW_POSITION = "SELECT MAX(field_position)" + " FROM core_attribute_field ";

  49.     // SELECT
  50.     private static final String SQL_QUERY_SELECT = " SELECT id_field, id_attribute, title, DEFAULT_value, is_DEFAULT_value, height, width, max_size_enter, is_multiple, field_position "
  51.             + " FROM core_attribute_field WHERE id_field = ? ";
  52.     private static final String SQL_QUERY_SELECT_ATTRIBUTE_BY_ID_FIELD = " SELECT a.type_class_name, a.id_attribute, a.title, a.help_message, a.is_mandatory, a.attribute_position "
  53.             + " FROM core_attribute a INNER JOIN core_attribute_field af ON a.id_attribute = af.id_attribute " + " WHERE af.id_field = ? ";
  54.     private static final String SQL_QUERY_SELECT_ATTRIBUTE_FIELDS_BY_ID_ATTRIBUTE = " SELECT id_field, id_attribute, title, DEFAULT_value, is_DEFAULT_value, height, width, max_size_enter, is_multiple, field_position "
  55.             + " FROM core_attribute_field WHERE id_attribute = ? ORDER BY field_position ";

  56.     // INSERT
  57.     private static final String SQL_QUERY_INSERT = " INSERT INTO core_attribute_field (id_attribute, title, DEFAULT_value, is_DEFAULT_value, height, width, max_size_enter, is_multiple, field_position) "
  58.             + " VALUES(?,?,?,?,?,?,?,?,?) ";

  59.     // UPDATE
  60.     private static final String SQL_QUERY_UPDATE = " UPDATE core_attribute_field SET title = ?, DEFAULT_value = ?, is_DEFAULT_value = ?, height = ?, width = ?, max_size_enter = ?, is_multiple = ?, field_position = ? "
  61.             + " WHERE id_field = ? ";

  62.     // DELETE
  63.     private static final String SQL_QUERY_DELETE = " DELETE FROM core_attribute_field WHERE id_field = ? ";
  64.     private static final String SQL_QUERY_DELETE_BY_ID_ATTRIBUTE = " DELETE FROM core_attribute_field WHERE id_attribute = ? ";

  65.     /**
  66.      * Generates a new field position
  67.      *
  68.      * @return the new entry position
  69.      */
  70.     private int newPosition( )
  71.     {
  72.         int nPos;
  73.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_POSITION ) )
  74.         {
  75.             daoUtil.executeQuery( );

  76.             if ( !daoUtil.next( ) )
  77.             {
  78.                 // if the table is empty
  79.                 nPos = 1;
  80.             }

  81.             nPos = daoUtil.getInt( 1 ) + 1;
  82.         }

  83.         return nPos;
  84.     }

  85.     /**
  86.      * Load attribute field
  87.      *
  88.      * @param nIdField
  89.      *            ID Field
  90.      * @return Attribute Field
  91.      */
  92.     @Override
  93.     public AttributeField load( int nIdField )
  94.     {
  95.         AttributeField attributeField = null;
  96.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT ) )
  97.         {
  98.             daoUtil.setInt( 1, nIdField );
  99.             daoUtil.executeQuery( );

  100.             if ( daoUtil.next( ) )
  101.             {
  102.                 attributeField = new AttributeField( );
  103.                 attributeField.setIdField( daoUtil.getInt( 1 ) );

  104.                 IAttribute attribute = selectAttributeByIdField( nIdField );
  105.                 attributeField.setAttribute( attribute );
  106.                 attributeField.setTitle( daoUtil.getString( 3 ) );
  107.                 attributeField.setValue( daoUtil.getString( 4 ) );
  108.                 attributeField.setDefaultValue( daoUtil.getBoolean( 5 ) );
  109.                 attributeField.setHeight( daoUtil.getInt( 6 ) );
  110.                 attributeField.setWidth( daoUtil.getInt( 7 ) );
  111.                 attributeField.setMaxSizeEnter( daoUtil.getInt( 8 ) );
  112.                 attributeField.setMultiple( daoUtil.getBoolean( 9 ) );
  113.                 attributeField.setPosition( daoUtil.getInt( 10 ) );
  114.             }

  115.         }

  116.         return attributeField;
  117.     }

  118.     /**
  119.      * Select attribute by id field
  120.      *
  121.      * @param nIdField
  122.      *            id field
  123.      * @return user attribute
  124.      */
  125.     @Override
  126.     public IAttribute selectAttributeByIdField( int nIdField )
  127.     {
  128.         IAttribute attribute = null;
  129.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ATTRIBUTE_BY_ID_FIELD ) )
  130.         {
  131.             daoUtil.setInt( 1, nIdField );
  132.             daoUtil.executeQuery( );

  133.             if ( daoUtil.next( ) )
  134.             {
  135.                 try
  136.                 {
  137.                     attribute = (IAttribute) Class.forName( daoUtil.getString( 1 ) ).newInstance( );
  138.                 }
  139.                 catch( IllegalAccessException | InstantiationException | ClassNotFoundException e )
  140.                 {
  141.                     AppLogService.error( e.getMessage( ), e );
  142.                 }
  143.                 if ( attribute != null )
  144.                 {
  145.                     attribute.setIdAttribute( daoUtil.getInt( 2 ) );
  146.                     attribute.setTitle( daoUtil.getString( 3 ) );
  147.                     attribute.setHelpMessage( daoUtil.getString( 4 ) );
  148.                     attribute.setMandatory( daoUtil.getBoolean( 5 ) );
  149.                     attribute.setPosition( daoUtil.getInt( 6 ) );
  150.                 }
  151.             }

  152.         }

  153.         return attribute;
  154.     }

  155.     /**
  156.      * Load the lists of attribute field associated to an attribute
  157.      *
  158.      * @param nIdAttribute
  159.      *            the ID attribute
  160.      * @return the list of attribute fields
  161.      */
  162.     @Override
  163.     public List<AttributeField> selectAttributeFieldsByIdAttribute( int nIdAttribute )
  164.     {
  165.         List<AttributeField> listAttributeFields = new ArrayList<>( );
  166.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ATTRIBUTE_FIELDS_BY_ID_ATTRIBUTE ) )
  167.         {
  168.             daoUtil.setInt( 1, nIdAttribute );
  169.             daoUtil.executeQuery( );

  170.             while ( daoUtil.next( ) )
  171.             {
  172.                 AttributeField attributeField = new AttributeField( );
  173.                 attributeField.setIdField( daoUtil.getInt( 1 ) );

  174.                 IAttribute attribute = selectAttributeByIdField( attributeField.getIdField( ) );
  175.                 attributeField.setAttribute( attribute );
  176.                 attributeField.setTitle( daoUtil.getString( 3 ) );
  177.                 attributeField.setValue( daoUtil.getString( 4 ) );
  178.                 attributeField.setDefaultValue( daoUtil.getBoolean( 5 ) );
  179.                 attributeField.setHeight( daoUtil.getInt( 6 ) );
  180.                 attributeField.setWidth( daoUtil.getInt( 7 ) );
  181.                 attributeField.setMaxSizeEnter( daoUtil.getInt( 8 ) );
  182.                 attributeField.setMultiple( daoUtil.getBoolean( 9 ) );
  183.                 attributeField.setPosition( daoUtil.getInt( 10 ) );
  184.                 listAttributeFields.add( attributeField );
  185.             }

  186.         }

  187.         return listAttributeFields;
  188.     }

  189.     /**
  190.      * Insert a new attribute field
  191.      *
  192.      * @param attributeField
  193.      *            the attribute field
  194.      * @return new PK
  195.      */
  196.     @Override
  197.     public int insert( AttributeField attributeField )
  198.     {
  199.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS ) )
  200.         {
  201.             int nIndex = 1;
  202.             daoUtil.setInt( nIndex++, attributeField.getAttribute( ).getIdAttribute( ) );
  203.             daoUtil.setString( nIndex++, attributeField.getTitle( ) );
  204.             daoUtil.setString( nIndex++, attributeField.getValue( ) );
  205.             daoUtil.setBoolean( nIndex++, attributeField.isDefaultValue( ) );
  206.             daoUtil.setInt( nIndex++, attributeField.getHeight( ) );
  207.             daoUtil.setInt( nIndex++, attributeField.getWidth( ) );
  208.             daoUtil.setInt( nIndex++, attributeField.getMaxSizeEnter( ) );
  209.             daoUtil.setBoolean( nIndex++, attributeField.isMultiple( ) );
  210.             daoUtil.setInt( nIndex, newPosition( ) );

  211.             daoUtil.executeUpdate( );

  212.             if ( daoUtil.nextGeneratedKey( ) )
  213.             {
  214.                 attributeField.setIdField( daoUtil.getGeneratedKeyInt( 1 ) );
  215.             }
  216.         }

  217.         return attributeField.getIdField( );
  218.     }

  219.     /**
  220.      * Update an attribute field
  221.      *
  222.      * @param attributeField
  223.      *            the attribute field
  224.      */
  225.     @Override
  226.     public void store( AttributeField attributeField )
  227.     {
  228.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE ) )
  229.         {
  230.             daoUtil.setString( 1, attributeField.getTitle( ) );
  231.             daoUtil.setString( 2, attributeField.getValue( ) );
  232.             daoUtil.setBoolean( 3, attributeField.isDefaultValue( ) );
  233.             daoUtil.setInt( 4, attributeField.getHeight( ) );
  234.             daoUtil.setInt( 5, attributeField.getWidth( ) );
  235.             daoUtil.setInt( 6, attributeField.getMaxSizeEnter( ) );
  236.             daoUtil.setBoolean( 7, attributeField.isMultiple( ) );
  237.             daoUtil.setInt( 8, attributeField.getPosition( ) );
  238.             daoUtil.setInt( 9, attributeField.getIdField( ) );

  239.             daoUtil.executeUpdate( );
  240.         }
  241.     }

  242.     /**
  243.      * Delete an attribute field
  244.      *
  245.      * @param nIdField
  246.      *            The id field
  247.      */
  248.     @Override
  249.     public void delete( int nIdField )
  250.     {
  251.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE ) )
  252.         {
  253.             daoUtil.setInt( 1, nIdField );

  254.             daoUtil.executeUpdate( );
  255.         }
  256.     }

  257.     /**
  258.      * Delete all attribute field from an attribute id
  259.      *
  260.      * @param nIdAttribute
  261.      *            the ID attribute
  262.      */
  263.     @Override
  264.     public void deleteAttributeFieldsFromIdAttribute( int nIdAttribute )
  265.     {
  266.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_ID_ATTRIBUTE ) )
  267.         {
  268.             daoUtil.setInt( 1, nIdAttribute );

  269.             daoUtil.executeUpdate( );
  270.         }
  271.     }
  272. }