MailItemQueueDAO.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.mail;
import fr.paris.lutece.portal.service.mail.MailItem;
import fr.paris.lutece.portal.service.util.AppLogService;
import fr.paris.lutece.util.mail.FileAttachment;
import fr.paris.lutece.util.mail.UrlAttachment;
import fr.paris.lutece.util.sql.DAOUtil;
import fr.paris.lutece.util.sql.Transaction;
import fr.paris.lutece.util.sql.TransactionManager;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectOutputStream;
import java.net.URL;
import java.sql.Statement;
import java.util.ArrayList;
import org.apache.commons.io.serialization.ValidatingObjectInputStream;
/**
* This class provides Data Access methods for MailItemQueue objects
*/
public class MailItemQueueDAO implements IMailItemQueueDAO
{
private static final String SQL_QUERY_SELECT_NEXT_MAIL_ITEM_QUEUE_ID = "SELECT min(id_mail_queue) FROM core_mail_queue WHERE is_locked=0";
private static final String SQL_QUERY_SELECT_COUNT = "SELECT COUNT(id_mail_queue) FROM core_mail_queue";
private static final String SQL_QUERY_LOAD_MAIL_ITEM = "SELECT id_mail_queue,mail_item FROM core_mail_item WHERE id_mail_queue=? ";
private static final String SQL_QUERY_INSERT = " INSERT INTO core_mail_queue( id_mail_queue ) VALUES( DEFAULT ) ";
private static final String SQL_QUERY_INSERT_MAIL_ITEM = " INSERT INTO core_mail_item(id_mail_queue,mail_item) VALUES(?,?) ";
private static final String SQL_QUERY_LOCK_MAIL_ITEM = " UPDATE core_mail_queue SET is_locked=1 WHERE id_mail_queue= ? ";
private static final String SQL_QUERY_DELETE = " DELETE FROM core_mail_queue WHERE id_mail_queue = ?";
private static final String SQL_QUERY_DELETE_MAIL_ITEM = " DELETE FROM core_mail_item WHERE id_mail_queue = ?";
/**
* return the next mail item queue id
*
* @return the next mail item queue id
*/
@Override
public int nextMailItemQueueId( )
{
int nIdMailItemQueue = -1;
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_NEXT_MAIL_ITEM_QUEUE_ID ) )
{
daoUtil.executeQuery( );
if ( daoUtil.next( ) )
{
nIdMailItemQueue = daoUtil.getInt( 1 );
}
}
return nIdMailItemQueue;
}
/**
* Lock the mail item
*
* @param nIdMailItemQueue
* the id of the mail item to lock
*/
@Override
public void lockMailItemQueue( int nIdMailItemQueue )
{
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_LOCK_MAIL_ITEM ) )
{
daoUtil.setInt( 1, nIdMailItemQueue );
daoUtil.executeUpdate( );
}
}
/**
* Insert a new mail item in the table.
*
* @param mailItemQueue
* the mail item
*/
@Override
public synchronized void insert( MailItemQueue mailItemQueue )
{
try
{
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream( );
ObjectOutputStream objectOutputStream;
objectOutputStream = new ObjectOutputStream( byteArrayOutputStream );
objectOutputStream.writeObject( mailItemQueue.getMailItem( ) );
objectOutputStream.close( );
byteArrayOutputStream.close( );
doInsertMail( mailItemQueue, byteArrayOutputStream );
}
catch( Exception e )
{
AppLogService.error( e );
}
}
private void doInsertMail( MailItemQueue mailItemQueue, ByteArrayOutputStream byteArrayOutputStream )
{
TransactionManager.beginTransaction( null );
try ( DAOUtil daoUtilKey = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS ) ;
DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_MAIL_ITEM ) )
{
daoUtilKey.executeUpdate( );
daoUtilKey.nextGeneratedKey( );
int nNewPrimaryKey = daoUtilKey.getGeneratedKeyInt( 1 );
mailItemQueue.setIdMailItemQueue( nNewPrimaryKey );
daoUtil.setInt( 1, nNewPrimaryKey );
daoUtil.setBytes( 2, byteArrayOutputStream.toByteArray( ) );
daoUtil.executeUpdate( );
TransactionManager.commitTransaction( null );
}
catch( Exception e )
{
TransactionManager.rollBack( null );
AppLogService.error( e );
}
}
/**
* return the first mail item in the table
*
* @param nIdMailItemQueue
* the id of the mail item
* @return the first mail item in the table
*/
@Override
public MailItemQueue load( int nIdMailItemQueue )
{
MailItemQueue mailItemQueue = null;
MailItem mailItem = null;
InputStream inputStream;
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_LOAD_MAIL_ITEM ) )
{
daoUtil.setInt( 1, nIdMailItemQueue );
daoUtil.executeQuery( );
if ( daoUtil.next( ) )
{
mailItemQueue = new MailItemQueue( );
mailItemQueue.setIdMailItemQueue( daoUtil.getInt( 1 ) );
inputStream = daoUtil.getBinaryStream( 2 );
try ( ValidatingObjectInputStream objectInputStream = new ValidatingObjectInputStream( inputStream ) )
{
objectInputStream.accept( MailItem.class, ArrayList.class, byte [ ].class, FileAttachment.class, UrlAttachment.class,
FileAttachment [ ].class, UrlAttachment [ ].class, URL.class );
mailItem = (MailItem) objectInputStream.readObject( );
}
catch( ClassNotFoundException | IOException e )
{
AppLogService.error( e.getMessage( ), e );
}
finally
{
try
{
inputStream.close( );
}
catch( IOException e )
{
AppLogService.error( e.getMessage( ), e );
}
}
mailItemQueue.setMailItem( mailItem );
}
}
return mailItemQueue;
}
/**
* Delete the mail item record in the table
*
* @param nIdMailItemQueue
* The identifier of the mail item to remove
*/
@Override
public void delete( int nIdMailItemQueue )
{
Transaction transaction = new Transaction( );
try
{
transaction.prepareStatement( SQL_QUERY_DELETE_MAIL_ITEM );
transaction.getStatement( ).setInt( 1, nIdMailItemQueue );
transaction.executeStatement( );
transaction.prepareStatement( SQL_QUERY_DELETE );
transaction.getStatement( ).setInt( 1, nIdMailItemQueue );
transaction.executeStatement( );
transaction.commit( );
}
catch( Exception e )
{
transaction.rollback( e );
AppLogService.error( e );
}
}
/**
* @return the number of mail item present in the core_mail_queue
*/
@Override
public int getCountMailItem( )
{
int nCount = 0;
try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_COUNT ) )
{
daoUtil.executeQuery( );
if ( daoUtil.next( ) )
{
nCount = daoUtil.getInt( 1 );
}
}
return nCount;
}
}