DashboardDAO.java
/*
* Copyright (c) 2002-2022, City of Paris
* All rights reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions
* are met:
*
* 1. Redistributions of source code must retain the above copyright notice
* and the following disclaimer.
*
* 2. Redistributions in binary form must reproduce the above copyright notice
* and the following disclaimer in the documentation and/or other materials
* provided with the distribution.
*
* 3. Neither the name of 'Mairie de Paris' nor 'Lutece' nor the names of its
* contributors may be used to endorse or promote products derived from
* this software without specific prior written permission.
*
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
* IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
* ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDERS OR CONTRIBUTORS BE
* LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
* CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
* SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
* INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
* CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
* ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
* POSSIBILITY OF SUCH DAMAGE.
*
* License 1.0
*/
package fr.paris.lutece.portal.business.dashboard;
import fr.paris.lutece.portal.service.dashboard.IDashboardComponent;
import fr.paris.lutece.portal.service.util.AppLogService;
import fr.paris.lutece.util.sql.DAOUtil;
import java.util.ArrayList;
import java.util.List;
/**
*
* DashboardDAO
*
*/
public class DashboardDAO implements IDashboardDAO
{
private static final String LOG_ERROR_NOT_FOUND = "Dashboard named {} not found";
private static final String SQL_QUERY_MAX_ORDER = "SELECT max(dashboard_order) FROM core_dashboard";
private static final String SQL_QUERY_MAX_ORDER_COLUMN = SQL_QUERY_MAX_ORDER + " WHERE dashboard_column = ? ";
private static final String SQL_QUERY_DELETE = "DELETE FROM core_dashboard ";
private static final String SQL_QUERY_DELETE_BY_NAME = SQL_QUERY_DELETE + " WHERE dashboard_name = ? ";
private static final String SQL_QUERY_SELECT = "SELECT dashboard_name, dashboard_order, dashboard_column FROM core_dashboard ";
private static final String SQL_QUERY_ORDER_BY_COLUMN_AND_ORDER = " ORDER BY dashboard_column, dashboard_order";
private static final String SQL_QUERY_SELECT_ALL = SQL_QUERY_SELECT + " WHERE dashboard_column != - 1 " + SQL_QUERY_ORDER_BY_COLUMN_AND_ORDER;
private static final String SQL_QUERY_SELECT_COLUMNS = "SELECT dashboard_column FROM core_dashboard GROUP BY dashboard_column";
private static final String SQL_QUERY_FILTER_COLUMN = " dashboard_column = ? ";
private static final String SQL_QUERY_FILTER_ORDER = " dashboard_order = ? ";
private static final String SQL_QUERY_FILTER_NAME = " dashboard_name = ? ";
private static final String SQL_QUERY_SELECT_BY_PRIMARY_KEY = SQL_QUERY_SELECT + " WHERE " + SQL_QUERY_FILTER_NAME;
private static final String SQL_QUERY_INSERT = "INSERT INTO core_dashboard( dashboard_name, dashboard_order, dashboard_column ) " + " VALUES(?,?,?)";
private static final String SQL_QUERY_UPDATE = "UPDATE core_dashboard " + " SET dashboard_order = ?, dashboard_column = ? WHERE dashboard_name = ?";
private static final String SQL_QUERY_KEYWORD_WHERE = " WHERE ";
private static final String SQL_QUERY_KEYWORD_AND = " AND ";
/**
* {@inheritDoc}
*/
@Override
public void delete( String strBeanName )
{
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_NAME ) )
{
daoUtil.setString( 1, strBeanName );
daoUtil.executeUpdate( );
}
}
/**
* {@inheritDoc}
*/
@Override
public void deleteAll( )
{
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE ) )
{
daoUtil.executeUpdate( );
}
}
/**
* {@inheritDoc}
*/
@Override
public void insert( IDashboardComponent dashboardComponent )
{
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT ) )
{
daoUtil.setString( 1, dashboardComponent.getName( ) );
setInsertOrUpdateValues( 2, dashboardComponent, daoUtil );
daoUtil.executeUpdate( );
}
}
/**
* Loads the dashboard from the factory
*
* @param strBeanName
* the dashboard name
* @return the dashboard found
* @see DashboardFactory#getDashboardComponent(String)
*/
private IDashboardComponent findDashboardFromFactory( String strBeanName )
{
return DashboardFactory.getDashboardComponent( strBeanName );
}
/**
* {@inheritDoc}
*/
@Override
public IDashboardComponent load( String strClassName )
{
IDashboardComponent dashboardComponent = null;
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_PRIMARY_KEY ) )
{
daoUtil.setString( 1, strClassName );
daoUtil.executeQuery( );
if ( daoUtil.next( ) )
{
String strBeanName = daoUtil.getString( 1 );
dashboardComponent = findDashboardFromFactory( strBeanName );
if ( dashboardComponent != null )
{
load( dashboardComponent, daoUtil );
}
else
{
AppLogService.error( LOG_ERROR_NOT_FOUND, strBeanName );
}
}
}
return dashboardComponent;
}
/**
* {@inheritDoc}
*/
@Override
public List<IDashboardComponent> selectAllDashboardComponents( )
{
List<IDashboardComponent> listDashboards = new ArrayList<>( );
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL ) )
{
daoUtil.executeQuery( );
while ( daoUtil.next( ) )
{
IDashboardComponent dashboardComponent = null;
String strBeanName = daoUtil.getString( 1 );
dashboardComponent = findDashboardFromFactory( strBeanName );
if ( dashboardComponent != null )
{
load( dashboardComponent, daoUtil );
listDashboards.add( dashboardComponent );
}
else
{
AppLogService.error( LOG_ERROR_NOT_FOUND, strBeanName );
}
}
}
return listDashboards;
}
/**
* {@inheritDoc}
*/
@Override
public int selectMaxOrder( )
{
int nMaxOrder = 0;
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_MAX_ORDER ) )
{
daoUtil.executeQuery( );
if ( daoUtil.next( ) )
{
nMaxOrder = daoUtil.getInt( 1 );
}
}
return nMaxOrder;
}
/**
* {@inheritDoc}
*/
@Override
public int selectMaxOrder( int nColumn )
{
int nMaxOrder = 0;
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_MAX_ORDER_COLUMN ) )
{
daoUtil.setInt( 1, nColumn );
daoUtil.executeQuery( );
if ( daoUtil.next( ) )
{
nMaxOrder = daoUtil.getInt( 1 );
}
}
return nMaxOrder;
}
/**
* {@inheritDoc}
*/
@Override
public List<IDashboardComponent> selectDashboardComponents( DashboardFilter filter )
{
List<IDashboardComponent> listDashboards = new ArrayList<>( );
StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT );
buildSQLFilter( sbSQL, filter );
sbSQL.append( SQL_QUERY_ORDER_BY_COLUMN_AND_ORDER );
try ( DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ) ) )
{
applySQLFilter( daoUtil, 1, filter );
daoUtil.executeQuery( );
while ( daoUtil.next( ) )
{
IDashboardComponent dashboardComponent = null;
String strBeanName = daoUtil.getString( 1 );
dashboardComponent = findDashboardFromFactory( strBeanName );
if ( dashboardComponent != null )
{
load( dashboardComponent, daoUtil );
listDashboards.add( dashboardComponent );
}
else
{
AppLogService.error( LOG_ERROR_NOT_FOUND, strBeanName );
}
}
}
return listDashboards;
}
/**
* {@inheritDoc}
*/
@Override
public void store( IDashboardComponent dashboardComponent )
{
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE ) )
{
int nIndex = setInsertOrUpdateValues( 1, dashboardComponent, daoUtil );
daoUtil.setString( nIndex, dashboardComponent.getName( ) );
daoUtil.executeUpdate( );
}
}
/**
* {@inheritDoc}
*/
@Override
public List<Integer> selectColumns( )
{
List<Integer> listColumns = new ArrayList<>( );
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_COLUMNS ) )
{
daoUtil.executeQuery( );
while ( daoUtil.next( ) )
{
listColumns.add( daoUtil.getInt( 1 ) );
}
}
return listColumns;
}
/**
* Loads compenent data from daoUtil
*
* @param component
* the component
* @param daoUtil
* the daoutil
*/
private void load( IDashboardComponent component, DAOUtil daoUtil )
{
int nIndex = 1;
component.setName( daoUtil.getString( nIndex++ ) );
component.setOrder( daoUtil.getInt( nIndex++ ) );
component.setZone( daoUtil.getInt( nIndex++ ) );
}
/**
* Sets daoUtil values from componnet
*
* @param nStartIndex
* the start index
* @param component
* the component
* @param daoUtil
* daoutil
* @return the end index
*/
private int setInsertOrUpdateValues( int nStartIndex, IDashboardComponent component, DAOUtil daoUtil )
{
int nIndex = nStartIndex;
daoUtil.setInt( nIndex++, component.getOrder( ) );
daoUtil.setInt( nIndex++, component.getZone( ) );
return nIndex;
}
/**
* Builds sql filter
*
* @param sbSQL
* the buffer
* @param filter
* the filter
*/
private void buildSQLFilter( StringBuilder sbSQL, DashboardFilter filter )
{
List<String> listFilters = new ArrayList<>( );
if ( filter.containsFilterOrder( ) )
{
listFilters.add( SQL_QUERY_FILTER_ORDER );
}
if ( filter.containsFilterColumn( ) )
{
listFilters.add( SQL_QUERY_FILTER_COLUMN );
}
if ( !listFilters.isEmpty( ) )
{
sbSQL.append( SQL_QUERY_KEYWORD_WHERE );
boolean bFirstFilter = true;
for ( String strFilter : listFilters )
{
sbSQL.append( strFilter );
if ( !bFirstFilter )
{
sbSQL.append( SQL_QUERY_KEYWORD_AND );
}
else
{
bFirstFilter = false;
}
}
}
}
/**
* Add daoUtil parameters
*
* @param daoUtil
* daoUtil
* @param nStartIndex
* start index
* @param filter
* the filter to apply
* @return end index
*/
private int applySQLFilter( DAOUtil daoUtil, int nStartIndex, DashboardFilter filter )
{
int nIndex = nStartIndex;
if ( filter.containsFilterOrder( ) )
{
daoUtil.setInt( nIndex++, filter.getFilterOrder( ) );
}
if ( filter.containsFilterColumn( ) )
{
daoUtil.setInt( nIndex++, filter.getFilterColumn( ) );
}
return nIndex;
}
}