PortletDAO.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.portlet;
import fr.paris.lutece.portal.business.stylesheet.StyleSheet;
import fr.paris.lutece.util.ReferenceList;
import fr.paris.lutece.util.sql.DAOUtil;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
/**
* This class provides Data Access methods for Portlet objects
*/
public final class PortletDAO implements IPortletDAO
{
// queries
private static final String SQL_QUERY_UPDATE = " UPDATE core_portlet SET name = ?, date_update = ?, column_no = ?, "
+ " portlet_order = ? , id_style = ? , id_page = ?, accept_alias = ? , display_portlet_title = ?, role = ?, device_display_flags = ? "
+ " WHERE id_portlet = ?";
private static final String SQL_QUERY_SELECT = " SELECT b.id_portlet_type, a.id_page, a.id_style, a.name , b.name, "
+ " b.url_creation, b.url_update, a.date_update, a.column_no, a.portlet_order, "
+ " b.home_class, a.accept_alias , a.role , b.plugin_name , a.display_portlet_title, a.status, a.device_display_flags "
+ " FROM core_portlet a , core_portlet_type b WHERE a.id_portlet_type = b.id_portlet_type AND a.id_portlet = ?";
private static final String SQL_QUERY_SELECT_ALIAS = " SELECT a.id_portlet FROM core_portlet a, core_portlet_alias b "
+ " WHERE a.id_portlet = b.id_portlet AND b.id_alias= ? ";
private static final String SQL_QUERY_DELETE = "DELETE FROM core_portlet WHERE id_portlet = ?";
private static final String SQL_QUERY_UPDATE_STATUS = " UPDATE core_portlet SET status = ?, date_update = ? WHERE id_portlet = ? ";
private static final String SQL_QUERY_UPDATE_POSITION = " UPDATE core_portlet SET column_no = ?, portlet_order = ? WHERE id_portlet = ? ";
private static final String SQL_QUERY_INSERT = " INSERT INTO core_portlet ( id_portlet_type, id_page, id_style, name, "
+ " date_creation, date_update, status, column_no, portlet_order, accept_alias, display_portlet_title, role, device_display_flags ) "
+ " VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? , ?, ?, ?)";
private static final String SQL_QUERY_SELECT_PORTLET_LIST_BY_STYLE = "SELECT id_portlet, name, id_page FROM core_portlet WHERE id_style=?";
private static final String SQL_QUERY_SELECT_PORTLET_LIST_BY_ROLE = "SELECT id_portlet, name, id_page FROM core_portlet WHERE role=?";
private static final String SQL_QUERY_SELECT_XSL_FILE = " SELECT a.id_stylesheet , a.description , a.file_name, a.source "
+ " FROM core_stylesheet a, core_portlet b, core_style_mode_stylesheet c " + " WHERE a.id_stylesheet = c.id_stylesheet "
+ " AND b.id_style = c.id_style AND b.id_portlet = ? AND c.id_mode = ? ";
private static final String SQL_QUERY_SELECT_STYLE_LIST = " SELECT distinct a.id_style , a.description_style "
+ " FROM core_style a , core_style_mode_stylesheet b " + " WHERE a.id_style = b.id_style "
+ " AND a.id_portlet_type = ? ORDER BY a.description_style";
private static final String SQL_QUERY_SELECT_PORTLET_TYPE = " SELECT id_portlet_type , name , url_creation, url_update, plugin_name "
+ " FROM core_portlet_type WHERE id_portlet_type = ? ORDER BY id_portlet_type ";
private static final String SQL_QUERY_SELECT_PORTLET_ALIAS = " SELECT a.id_portlet FROM core_portlet a , core_portlet_alias b"
+ " WHERE a.id_portlet = b.id_portlet " + " AND b.id_alias= ? ";
private static final String SQL_QUERY_SELECT_ALIASES_FOR_PORTLET = "SELECT p.id_portlet, p.id_page, p.name "
+ "FROM core_portlet_alias a JOIN core_portlet p ON p.id_portlet = a.id_portlet WHERE a.id_alias = ? ";
private static final String SQL_QUERY_SELECT_PORTLET_LIST_BY_NAME = " SELECT id_portlet , id_page , name FROM core_portlet WHERE name LIKE ? ";
private static final String SQL_QUERY_SELECT_PORTLET_LIST_BY_TYPE = " SELECT a.id_portlet, a.id_portlet_type, a.id_page, a.name, "
+ "a.date_update, a.status, a.portlet_order, a.column_no, a.id_style, a.accept_alias, a.date_creation, a.display_portlet_title, a.role, a.device_display_flags "
+ " FROM core_portlet a, core_page b WHERE a.id_page = b.id_page " + " AND a.id_portlet_type = ? ";
private static final String SQL_QUERY_SELECT_LAST_MODIFIED_PORTLET = " SELECT a.id_portlet, b.id_portlet_type, a.id_page, a.id_style, a.name , b.name, "
+ " b.url_creation, b.url_update, a.date_update, a.column_no, a.portlet_order, "
+ " b.home_class, a.accept_alias , a.role , b.plugin_name , a.display_portlet_title, a.status , a.device_display_flags "
+ " FROM core_portlet a , core_portlet_type b WHERE a.id_portlet_type = b.id_portlet_type ORDER BY a.date_update DESC LIMIT 1 ";
private static final String SQL_QUERY_SELECT_ORDER_FROM_PAGE_AND_COLUMN = " SELECT portlet_order FROM core_portlet WHERE column_no = ? AND id_page = ? ORDER BY portlet_order";
// /////////////////////////////////////////////////////////////////////////////////////
// Access methods to data
/**
* {@inheritDoc}
*/
public void insert( Portlet portlet )
{
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS ) )
{
int nIndex = 1;
daoUtil.setString( nIndex++, portlet.getPortletTypeId( ) );
daoUtil.setInt( nIndex++, portlet.getPageId( ) );
daoUtil.setInt( nIndex++, portlet.getStyleId( ) );
daoUtil.setString( nIndex++, portlet.getName( ) );
daoUtil.setTimestamp( nIndex++, new Timestamp( new java.util.Date( ).getTime( ) ) );
daoUtil.setTimestamp( nIndex++, new Timestamp( new java.util.Date( ).getTime( ) ) );
daoUtil.setInt( nIndex++, portlet.getStatus( ) );
daoUtil.setInt( nIndex++, portlet.getColumn( ) );
daoUtil.setInt( nIndex++, portlet.getOrder( ) );
daoUtil.setInt( nIndex++, portlet.getAcceptAlias( ) );
daoUtil.setInt( nIndex++, portlet.getDisplayPortletTitle( ) );
daoUtil.setString( nIndex++, portlet.getRole( ) );
daoUtil.setInt( nIndex, portlet.getDeviceDisplayFlags( ) );
daoUtil.executeUpdate( );
if ( daoUtil.nextGeneratedKey( ) )
{
portlet.setId( daoUtil.getGeneratedKeyInt( 1 ) );
}
}
}
/**
* {@inheritDoc}
*/
public void delete( int nPortletId )
{
// we recover the alias of the portlet parent to delete
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALIAS ) )
{
daoUtil.setInt( 1, nPortletId );
daoUtil.executeQuery( );
while ( daoUtil.next( ) )
{
AliasPortletHome.getInstance( ).remove( PortletHome.findByPrimaryKey( daoUtil.getInt( 1 ) ) );
}
}
// we delete the portlet
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE ) )
{
daoUtil.setInt( 1, nPortletId );
daoUtil.executeUpdate( );
}
}
/**
* {@inheritDoc}
*/
public Portlet load( int nPortletId )
{
PortletImpl portlet = new PortletImpl( );
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT ) )
{
daoUtil.setInt( 1, nPortletId );
daoUtil.executeQuery( );
if ( daoUtil.next( ) )
{
portlet.setId( nPortletId );
portlet.setPortletTypeId( daoUtil.getString( 1 ) );
portlet.setPageId( daoUtil.getInt( 2 ) );
portlet.setStyleId( daoUtil.getInt( 3 ) );
portlet.setName( daoUtil.getString( 4 ) );
portlet.setPortletTypeName( daoUtil.getString( 5 ) );
portlet.setUrlCreation( daoUtil.getString( 6 ) );
portlet.setUrlUpdate( daoUtil.getString( 7 ) );
portlet.setDateUpdate( daoUtil.getTimestamp( 8 ) );
portlet.setColumn( daoUtil.getInt( 9 ) );
portlet.setOrder( daoUtil.getInt( 10 ) );
portlet.setHomeClassName( daoUtil.getString( 11 ) );
portlet.setAcceptAlias( daoUtil.getInt( 12 ) );
portlet.setRole( daoUtil.getString( 13 ) );
portlet.setPluginName( daoUtil.getString( 14 ) );
portlet.setDisplayPortletTitle( daoUtil.getInt( 15 ) );
portlet.setStatus( daoUtil.getInt( 16 ) );
portlet.setDeviceDisplayFlags( daoUtil.getInt( 17 ) );
}
}
return portlet;
}
/**
* {@inheritDoc}
*/
public void store( Portlet portlet )
{
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE ) )
{
daoUtil.setString( 1, portlet.getName( ) );
daoUtil.setTimestamp( 2, new Timestamp( new java.util.Date( ).getTime( ) ) );
daoUtil.setInt( 3, portlet.getColumn( ) );
daoUtil.setInt( 4, portlet.getOrder( ) );
daoUtil.setInt( 5, portlet.getStyleId( ) );
daoUtil.setInt( 6, portlet.getPageId( ) );
daoUtil.setInt( 7, portlet.getAcceptAlias( ) );
daoUtil.setInt( 8, portlet.getDisplayPortletTitle( ) );
daoUtil.setString( 9, portlet.getRole( ) );
daoUtil.setInt( 10, portlet.getDeviceDisplayFlags( ) );
daoUtil.setInt( 11, portlet.getId( ) );
daoUtil.executeUpdate( );
}
}
/**
* {@inheritDoc}
*/
public void updateStatus( Portlet portlet, int nStatus )
{
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_STATUS ) )
{
daoUtil.setInt( 1, nStatus );
daoUtil.setTimestamp( 2, new Timestamp( new java.util.Date( ).getTime( ) ) );
daoUtil.setInt( 3, portlet.getId( ) );
daoUtil.executeUpdate( );
}
}
/**
* {@inheritDoc}
*/
public void updatePosition( Portlet portlet, int nColumn, int nOrder )
{
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_POSITION ) )
{
daoUtil.setInt( 1, nColumn );
daoUtil.setInt( 2, nOrder );
daoUtil.setInt( 3, portlet.getId( ) );
daoUtil.executeUpdate( );
}
}
/**
* {@inheritDoc}
*/
public StyleSheet selectXslFile( int nPortletId, int nIdMode )
{
StyleSheet stylesheet = new StyleSheet( );
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_XSL_FILE ) )
{
daoUtil.setInt( 1, nPortletId );
daoUtil.setInt( 2, nIdMode );
daoUtil.executeQuery( );
if ( daoUtil.next( ) )
{
stylesheet.setId( daoUtil.getInt( 1 ) );
stylesheet.setDescription( daoUtil.getString( 2 ) );
stylesheet.setFile( daoUtil.getString( 3 ) );
stylesheet.setSource( daoUtil.getBytes( 4 ) );
}
}
return stylesheet;
}
/**
* {@inheritDoc}
*/
public Collection<PortletImpl> selectPortletsListbyName( String strPortletName )
{
List<PortletImpl> list = new ArrayList<>( );
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PORTLET_LIST_BY_NAME ) )
{
daoUtil.setString( 1, '%' + strPortletName + '%' );
daoUtil.executeQuery( );
while ( daoUtil.next( ) )
{
PortletImpl portlet = new PortletImpl( );
portlet.setId( daoUtil.getInt( 1 ) );
portlet.setPageId( daoUtil.getInt( 2 ) );
portlet.setName( daoUtil.getString( 3 ) );
list.add( portlet );
}
}
return list;
}
/**
* {@inheritDoc}
*/
public Collection<Portlet> selectAliasesForPortlet( int nPortletId )
{
List<Portlet> list = new ArrayList<>( );
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALIASES_FOR_PORTLET ) )
{
daoUtil.setInt( 1, nPortletId );
daoUtil.executeQuery( );
while ( daoUtil.next( ) )
{
PortletImpl portlet = new PortletImpl( );
portlet.setId( daoUtil.getInt( 1 ) );
portlet.setPageId( daoUtil.getInt( 2 ) );
portlet.setName( daoUtil.getString( 3 ) );
list.add( portlet );
}
}
return list;
}
/**
* {@inheritDoc}
*/
public List<Portlet> selectPortletsByType( String strPortletTypeId )
{
List<Portlet> list = new ArrayList<>( );
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PORTLET_LIST_BY_TYPE ) )
{
daoUtil.setString( 1, strPortletTypeId );
daoUtil.executeQuery( );
while ( daoUtil.next( ) )
{
PortletImpl portlet = new PortletImpl( );
portlet.setId( daoUtil.getInt( 1 ) );
portlet.setPortletTypeId( daoUtil.getString( 2 ) );
portlet.setPageId( daoUtil.getInt( 3 ) );
portlet.setName( daoUtil.getString( 4 ) );
portlet.setDateUpdate( daoUtil.getTimestamp( 5 ) );
portlet.setStatus( daoUtil.getInt( 6 ) );
portlet.setOrder( daoUtil.getInt( 7 ) );
portlet.setColumn( daoUtil.getInt( 8 ) );
portlet.setStyleId( daoUtil.getInt( 9 ) );
portlet.setAcceptAlias( daoUtil.getInt( 10 ) );
portlet.setDateUpdate( daoUtil.getTimestamp( 11 ) );
portlet.setDisplayPortletTitle( daoUtil.getInt( 12 ) );
portlet.setRole( daoUtil.getString( 13 ) );
portlet.setDeviceDisplayFlags( daoUtil.getInt( 14 ) );
list.add( portlet );
}
}
return list;
}
/**
* {@inheritDoc}
*/
public ReferenceList selectStylesList( String strPortletTypeId )
{
ReferenceList list = new ReferenceList( );
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_STYLE_LIST ) )
{
daoUtil.setString( 1, strPortletTypeId );
daoUtil.executeQuery( );
while ( daoUtil.next( ) )
{
list.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
}
}
return list;
}
/**
* {@inheritDoc}
*/
public boolean hasAlias( int nPortletId )
{
boolean bHasAlias = false;
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PORTLET_ALIAS ) )
{
daoUtil.setInt( 1, nPortletId );
daoUtil.executeQuery( );
if ( daoUtil.next( ) )
{
bHasAlias = true;
}
}
return bHasAlias;
}
/**
* {@inheritDoc}
*/
public PortletType selectPortletType( String strPortletTypeId )
{
PortletType portletType = new PortletType( );
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PORTLET_TYPE ) )
{
daoUtil.setString( 1, strPortletTypeId );
daoUtil.executeQuery( );
if ( daoUtil.next( ) )
{
portletType.setId( daoUtil.getString( 1 ) );
portletType.setNameKey( daoUtil.getString( 2 ) );
portletType.setUrlCreation( daoUtil.getString( 3 ) );
portletType.setUrlUpdate( daoUtil.getString( 4 ) );
portletType.setPluginName( daoUtil.getString( 5 ) );
}
}
return portletType;
}
/**
* {@inheritDoc}
*/
public Collection<PortletImpl> selectPortletListByStyle( int nStyleId )
{
Collection<PortletImpl> portletList = new ArrayList<>( );
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PORTLET_LIST_BY_STYLE ) )
{
daoUtil.setInt( 1, nStyleId );
daoUtil.executeQuery( );
while ( daoUtil.next( ) )
{
PortletImpl portlet = new PortletImpl( );
portlet.setId( daoUtil.getInt( 1 ) );
portlet.setName( daoUtil.getString( 2 ) );
portlet.setPageId( daoUtil.getInt( 3 ) );
portletList.add( portlet );
}
}
return portletList;
}
/**
* {@inheritDoc }
*/
public Collection<Portlet> selectPortletsByRole( String strRole )
{
List<Portlet> list = new ArrayList<>( );
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PORTLET_LIST_BY_ROLE ) )
{
daoUtil.setString( 1, strRole );
daoUtil.executeQuery( );
while ( daoUtil.next( ) )
{
PortletImpl portlet = new PortletImpl( );
portlet.setId( daoUtil.getInt( 1 ) );
portlet.setPortletTypeId( daoUtil.getString( 2 ) );
portlet.setPageId( daoUtil.getInt( 3 ) );
list.add( portlet );
}
}
return list;
}
/**
* {@inheritDoc}
*/
public Portlet loadLastModifiedPortlet( )
{
PortletImpl portlet = null;
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_LAST_MODIFIED_PORTLET ) )
{
daoUtil.executeQuery( );
if ( daoUtil.next( ) )
{
portlet = new PortletImpl( );
int nIndex = 1;
portlet.setId( daoUtil.getInt( nIndex++ ) );
portlet.setPortletTypeId( daoUtil.getString( nIndex++ ) );
portlet.setPageId( daoUtil.getInt( nIndex++ ) );
portlet.setStyleId( daoUtil.getInt( nIndex++ ) );
portlet.setName( daoUtil.getString( nIndex++ ) );
portlet.setPortletTypeName( daoUtil.getString( nIndex++ ) );
portlet.setUrlCreation( daoUtil.getString( nIndex++ ) );
portlet.setUrlUpdate( daoUtil.getString( nIndex++ ) );
portlet.setDateUpdate( daoUtil.getTimestamp( nIndex++ ) );
portlet.setColumn( daoUtil.getInt( nIndex++ ) );
portlet.setOrder( daoUtil.getInt( nIndex++ ) );
portlet.setHomeClassName( daoUtil.getString( nIndex++ ) );
portlet.setAcceptAlias( daoUtil.getInt( nIndex++ ) );
portlet.setRole( daoUtil.getString( nIndex++ ) );
portlet.setPluginName( daoUtil.getString( nIndex++ ) );
portlet.setDisplayPortletTitle( daoUtil.getInt( nIndex++ ) );
portlet.setStatus( daoUtil.getInt( nIndex++ ) );
portlet.setDeviceDisplayFlags( daoUtil.getInt( nIndex++ ) );
}
}
return portlet;
}
/**
* {@inheritDoc}
*/
@Override
public List<Integer> getUsedOrdersForColumns( int pageId, int columnId )
{
List<Integer> result = new ArrayList<>( );
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ORDER_FROM_PAGE_AND_COLUMN ) )
{
daoUtil.setInt( 1, columnId );
daoUtil.setInt( 2, pageId );
daoUtil.executeQuery( );
while ( daoUtil.next( ) )
{
result.add( daoUtil.getInt( 1 ) );
}
}
return result;
}
}