DashboardDAO.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.dashboard;

  36. import fr.paris.lutece.portal.service.dashboard.IDashboardComponent;
  37. import fr.paris.lutece.portal.service.util.AppLogService;
  38. import fr.paris.lutece.util.sql.DAOUtil;

  40. import java.util.ArrayList;
  41. import java.util.List;

  43. /**
  44.  *
  45.  * DashboardDAO
  46.  *
  47.  */
  48. public class DashboardDAO implements IDashboardDAO
  49. {
  50.     private static final String LOG_ERROR_NOT_FOUND = "Dashboard named {} not found";
  51.     private static final String SQL_QUERY_MAX_ORDER = "SELECT max(dashboard_order) FROM core_dashboard";
  52.     private static final String SQL_QUERY_MAX_ORDER_COLUMN = SQL_QUERY_MAX_ORDER + " WHERE dashboard_column = ? ";
  53.     private static final String SQL_QUERY_DELETE = "DELETE FROM core_dashboard ";
  54.     private static final String SQL_QUERY_DELETE_BY_NAME = SQL_QUERY_DELETE + " WHERE dashboard_name = ? ";
  55.     private static final String SQL_QUERY_SELECT = "SELECT dashboard_name, dashboard_order, dashboard_column  FROM core_dashboard ";
  56.     private static final String SQL_QUERY_ORDER_BY_COLUMN_AND_ORDER = " ORDER BY dashboard_column, dashboard_order";
  57.     private static final String SQL_QUERY_SELECT_ALL = SQL_QUERY_SELECT + " WHERE dashboard_column != - 1 " + SQL_QUERY_ORDER_BY_COLUMN_AND_ORDER;
  58.     private static final String SQL_QUERY_SELECT_COLUMNS = "SELECT dashboard_column FROM core_dashboard GROUP BY dashboard_column";
  59.     private static final String SQL_QUERY_FILTER_COLUMN = " dashboard_column = ? ";
  60.     private static final String SQL_QUERY_FILTER_ORDER = " dashboard_order = ? ";
  61.     private static final String SQL_QUERY_FILTER_NAME = " dashboard_name = ? ";
  62.     private static final String SQL_QUERY_SELECT_BY_PRIMARY_KEY = SQL_QUERY_SELECT + " WHERE " + SQL_QUERY_FILTER_NAME;
  63.     private static final String SQL_QUERY_INSERT = "INSERT INTO core_dashboard( dashboard_name, dashboard_order, dashboard_column ) " + " VALUES(?,?,?)";
  64.     private static final String SQL_QUERY_UPDATE = "UPDATE core_dashboard " + " SET dashboard_order = ?, dashboard_column = ? WHERE dashboard_name = ?";
  65.     private static final String SQL_QUERY_KEYWORD_WHERE = "  WHERE ";
  66.     private static final String SQL_QUERY_KEYWORD_AND = " AND ";

  68.     /**
  69.      * {@inheritDoc}
  70.      */
  71.     @Override
  72.     public void delete( String strBeanName )
  73.     {
  74.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_NAME ) )
  75.         {

  77.             daoUtil.setString( 1, strBeanName );

  79.             daoUtil.executeUpdate( );

  81.         }
  82.     }

  84.     /**
  85.      * {@inheritDoc}
  86.      */
  87.     @Override
  88.     public void deleteAll( )
  89.     {
  90.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE ) )
  91.         {

  93.             daoUtil.executeUpdate( );

  95.         }
  96.     }

  98.     /**
  99.      * {@inheritDoc}
  100.      */
  101.     @Override
  102.     public void insert( IDashboardComponent dashboardComponent )
  103.     {
  104.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT ) )
  105.         {

  107.             daoUtil.setString( 1, dashboardComponent.getName( ) );
  108.             setInsertOrUpdateValues( 2, dashboardComponent, daoUtil );

  110.             daoUtil.executeUpdate( );

  112.         }
  113.     }

  115.     /**
  116.      * Loads the dashboard from the factory
  117.      * 
  118.      * @param strBeanName
  119.      *            the dashboard name
  120.      * @return the dashboard found
  121.      * @see DashboardFactory#getDashboardComponent(String)
  122.      */
  123.     private IDashboardComponent findDashboardFromFactory( String strBeanName )
  124.     {
  125.         return DashboardFactory.getDashboardComponent( strBeanName );
  126.     }

  128.     /**
  129.      * {@inheritDoc}
  130.      */
  131.     @Override
  132.     public IDashboardComponent load( String strClassName )
  133.     {
  134.         IDashboardComponent dashboardComponent = null;

  136.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_PRIMARY_KEY ) )
  137.         {

  139.             daoUtil.setString( 1, strClassName );

  141.             daoUtil.executeQuery( );

  143.             if ( daoUtil.next( ) )
  144.             {
  145.                 String strBeanName = daoUtil.getString( 1 );

  147.                 dashboardComponent = findDashboardFromFactory( strBeanName );

  149.                 if ( dashboardComponent != null )
  150.                 {
  151.                     load( dashboardComponent, daoUtil );
  152.                 }
  153.                 else
  154.                 {
  155.                     AppLogService.error( LOG_ERROR_NOT_FOUND, strBeanName );
  156.                 }
  157.             }

  159.         }

  161.         return dashboardComponent;
  162.     }

  164.     /**
  165.      * {@inheritDoc}
  166.      */
  167.     @Override
  168.     public List<IDashboardComponent> selectAllDashboardComponents( )
  169.     {
  170.         List<IDashboardComponent> listDashboards = new ArrayList<>( );

  172.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL ) )
  173.         {

  175.             daoUtil.executeQuery( );

  177.             while ( daoUtil.next( ) )
  178.             {
  179.                 IDashboardComponent dashboardComponent = null;

  181.                 String strBeanName = daoUtil.getString( 1 );

  183.                 dashboardComponent = findDashboardFromFactory( strBeanName );

  185.                 if ( dashboardComponent != null )
  186.                 {
  187.                     load( dashboardComponent, daoUtil );
  188.                     listDashboards.add( dashboardComponent );
  189.                 }
  190.                 else
  191.                 {
  192.                     AppLogService.error( LOG_ERROR_NOT_FOUND, strBeanName );
  193.                 }
  194.             }

  196.         }

  198.         return listDashboards;
  199.     }

  201.     /**
  202.      * {@inheritDoc}
  203.      */
  204.     @Override
  205.     public int selectMaxOrder( )
  206.     {
  207.         int nMaxOrder = 0;
  208.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_MAX_ORDER ) )
  209.         {
  210.             daoUtil.executeQuery( );

  212.             if ( daoUtil.next( ) )
  213.             {
  214.                 nMaxOrder = daoUtil.getInt( 1 );
  215.             }

  217.         }

  219.         return nMaxOrder;
  220.     }

  222.     /**
  223.      * {@inheritDoc}
  224.      */
  225.     @Override
  226.     public int selectMaxOrder( int nColumn )
  227.     {
  228.         int nMaxOrder = 0;
  229.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_MAX_ORDER_COLUMN ) )
  230.         {
  231.             daoUtil.setInt( 1, nColumn );

  233.             daoUtil.executeQuery( );

  235.             if ( daoUtil.next( ) )
  236.             {
  237.                 nMaxOrder = daoUtil.getInt( 1 );
  238.             }

  240.         }

  242.         return nMaxOrder;
  243.     }

  245.     /**
  246.      * {@inheritDoc}
  247.      */
  248.     @Override
  249.     public List<IDashboardComponent> selectDashboardComponents( DashboardFilter filter )
  250.     {
  251.         List<IDashboardComponent> listDashboards = new ArrayList<>( );
  252.         StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT );
  253.         buildSQLFilter( sbSQL, filter );
  254.         sbSQL.append( SQL_QUERY_ORDER_BY_COLUMN_AND_ORDER );

  256.         try ( DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ) ) )
  257.         {

  259.             applySQLFilter( daoUtil, 1, filter );

  261.             daoUtil.executeQuery( );

  263.             while ( daoUtil.next( ) )
  264.             {
  265.                 IDashboardComponent dashboardComponent = null;

  267.                 String strBeanName = daoUtil.getString( 1 );

  269.                 dashboardComponent = findDashboardFromFactory( strBeanName );

  271.                 if ( dashboardComponent != null )
  272.                 {
  273.                     load( dashboardComponent, daoUtil );
  274.                     listDashboards.add( dashboardComponent );
  275.                 }
  276.                 else
  277.                 {
  278.                     AppLogService.error( LOG_ERROR_NOT_FOUND, strBeanName );
  279.                 }
  280.             }

  282.         }

  284.         return listDashboards;
  285.     }

  287.     /**
  288.      * {@inheritDoc}
  289.      */
  290.     @Override
  291.     public void store( IDashboardComponent dashboardComponent )
  292.     {
  293.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE ) )
  294.         {

  296.             int nIndex = setInsertOrUpdateValues( 1, dashboardComponent, daoUtil );
  297.             daoUtil.setString( nIndex, dashboardComponent.getName( ) );

  299.             daoUtil.executeUpdate( );

  301.         }
  302.     }

  304.     /**
  305.      * {@inheritDoc}
  306.      */
  307.     @Override
  308.     public List<Integer> selectColumns( )
  309.     {
  310.         List<Integer> listColumns = new ArrayList<>( );

  312.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_COLUMNS ) )
  313.         {

  315.             daoUtil.executeQuery( );

  317.             while ( daoUtil.next( ) )
  318.             {
  319.                 listColumns.add( daoUtil.getInt( 1 ) );
  320.             }

  322.         }

  324.         return listColumns;
  325.     }

  327.     /**
  328.      * Loads compenent data from daoUtil
  329.      * 
  330.      * @param component
  331.      *            the component
  332.      * @param daoUtil
  333.      *            the daoutil
  334.      */
  335.     private void load( IDashboardComponent component, DAOUtil daoUtil )
  336.     {
  337.         int nIndex = 1;
  338.         component.setName( daoUtil.getString( nIndex++ ) );
  339.         component.setOrder( daoUtil.getInt( nIndex++ ) );
  340.         component.setZone( daoUtil.getInt( nIndex++ ) );
  341.     }

  343.     /**
  344.      * Sets daoUtil values from componnet
  345.      * 
  346.      * @param nStartIndex
  347.      *            the start index
  348.      * @param component
  349.      *            the component
  350.      * @param daoUtil
  351.      *            daoutil
  352.      * @return the end index
  353.      */
  354.     private int setInsertOrUpdateValues( int nStartIndex, IDashboardComponent component, DAOUtil daoUtil )
  355.     {
  356.         int nIndex = nStartIndex;
  357.         daoUtil.setInt( nIndex++, component.getOrder( ) );
  358.         daoUtil.setInt( nIndex++, component.getZone( ) );

  360.         return nIndex;
  361.     }

  363.     /**
  364.      * Builds sql filter
  365.      * 
  366.      * @param sbSQL
  367.      *            the buffer
  368.      * @param filter
  369.      *            the filter
  370.      */
  371.     private void buildSQLFilter( StringBuilder sbSQL, DashboardFilter filter )
  372.     {
  373.         List<String> listFilters = new ArrayList<>( );

  375.         if ( filter.containsFilterOrder( ) )
  376.         {
  377.             listFilters.add( SQL_QUERY_FILTER_ORDER );
  378.         }

  380.         if ( filter.containsFilterColumn( ) )
  381.         {
  382.             listFilters.add( SQL_QUERY_FILTER_COLUMN );
  383.         }

  385.         if ( !listFilters.isEmpty( ) )
  386.         {
  387.             sbSQL.append( SQL_QUERY_KEYWORD_WHERE );

  389.             boolean bFirstFilter = true;

  391.             for ( String strFilter : listFilters )
  392.             {
  393.                 sbSQL.append( strFilter );

  395.                 if ( !bFirstFilter )
  396.                 {
  397.                     sbSQL.append( SQL_QUERY_KEYWORD_AND );
  398.                 }
  399.                 else
  400.                 {
  401.                     bFirstFilter = false;
  402.                 }
  403.             }
  404.         }
  405.     }

  407.     /**
  408.      * Add daoUtil parameters
  409.      * 
  410.      * @param daoUtil
  411.      *            daoUtil
  412.      * @param nStartIndex
  413.      *            start index
  414.      * @param filter
  415.      *            the filter to apply
  416.      * @return end index
  417.      */
  418.     private int applySQLFilter( DAOUtil daoUtil, int nStartIndex, DashboardFilter filter )
  419.     {
  420.         int nIndex = nStartIndex;

  422.         if ( filter.containsFilterOrder( ) )
  423.         {
  424.             daoUtil.setInt( nIndex++, filter.getFilterOrder( ) );
  425.         }

  427.         if ( filter.containsFilterColumn( ) )
  428.         {
  429.             daoUtil.setInt( nIndex++, filter.getFilterColumn( ) );
  430.         }

  432.         return nIndex;
  433.     }
  434. }
Created with JaCoCo 0.8.4.201905082037