MailingListDAO.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.mailinglist;

  36. import fr.paris.lutece.util.sql.DAOUtil;

  38. import java.sql.Statement;
  39. import java.util.ArrayList;
  40. import java.util.Collection;
  41. import java.util.List;

  43. /**
  44.  * This class provides Data Access methods for MailingList objects
  45.  */
  46. public final class MailingListDAO implements IMailingListDAO
  47. {
  48.     // Constants
  49.     private static final String SQL_QUERY_SELECT = "SELECT id_mailinglist, name, description, workgroup FROM core_admin_mailinglist WHERE id_mailinglist = ?";
  50.     private static final String SQL_QUERY_INSERT = "INSERT INTO core_admin_mailinglist ( name, description, workgroup ) VALUES ( ?, ?, ? ) ";
  51.     private static final String SQL_QUERY_DELETE = "DELETE FROM core_admin_mailinglist WHERE id_mailinglist = ? ";
  52.     private static final String SQL_QUERY_UPDATE = "UPDATE core_admin_mailinglist SET id_mailinglist = ?, name = ?, description = ?, workgroup = ? WHERE id_mailinglist = ?";
  53.     private static final String SQL_QUERY_SELECTALL = "SELECT id_mailinglist, name, description, workgroup FROM core_admin_mailinglist";
  54.     private static final String SQL_QUERY_SELECT_BY_WORKGROUP = "SELECT id_mailinglist, name, description, workgroup FROM core_admin_mailinglist WHERE workgroup = ? ";

  56.     // filters
  57.     private static final String SQL_QUERY_FILTERS_INSERT = "INSERT INTO core_admin_mailinglist_filter ( id_mailinglist, workgroup, role ) VALUES ( ?, ?, ? ) ";
  58.     private static final String SQL_QUERY_FILTERS_DELETE = "DELETE FROM core_admin_mailinglist_filter WHERE id_mailinglist = ? ";
  59.     private static final String SQL_QUERY_FILTERS_DELETE_FILTER = "DELETE FROM core_admin_mailinglist_filter WHERE id_mailinglist = ? AND workgroup = ? AND role = ? ";
  60.     private static final String SQL_QUERY_FILTERS_SELECTALL = "SELECT id_mailinglist, workgroup, role FROM core_admin_mailinglist_filter WHERE id_mailinglist = ?";
  61.     private static final String SQL_QUERY_FILTERS_SELECT = "SELECT id_mailinglist, workgroup, role FROM core_admin_mailinglist_filter WHERE id_mailinglist = ? AND workgroup = ? AND role = ?";

  63.     /**
  64.      * Insert a new record in the table.
  65.      *
  66.      * @param mailingList
  67.      *            instance of the MailingList object to insert
  68.      */
  69.     @Override
  70.     public void insert( MailingList mailingList )
  71.     {
  72.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS ) )
  73.         {
  74.             int nIndex = 1;
  75.             daoUtil.setString( nIndex++, mailingList.getName( ) );
  76.             daoUtil.setString( nIndex++, mailingList.getDescription( ) );
  77.             daoUtil.setString( nIndex, mailingList.getWorkgroup( ) );

  79.             daoUtil.executeUpdate( );

  81.             if ( daoUtil.nextGeneratedKey( ) )
  82.             {
  83.                 mailingList.setId( daoUtil.getGeneratedKeyInt( 1 ) );
  84.             }
  85.         }
  86.     }

  88.     /**
  89.      * Load the data of the mailingList from the table
  90.      *
  91.      * @param nId
  92.      *            The identifier of the mailingList
  93.      * @return the instance of the MailingList
  94.      */
  95.     @Override
  96.     public MailingList load( int nId )
  97.     {
  98.         MailingList mailinglist = null;
  99.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT ) )
  100.         {
  101.             daoUtil.setInt( 1, nId );
  102.             daoUtil.executeQuery( );

  104.             if ( daoUtil.next( ) )
  105.             {
  106.                 mailinglist = new MailingList( );

  108.                 mailinglist.setId( daoUtil.getInt( 1 ) );
  109.                 mailinglist.setName( daoUtil.getString( 2 ) );
  110.                 mailinglist.setDescription( daoUtil.getString( 3 ) );
  111.                 mailinglist.setWorkgroup( daoUtil.getString( 4 ) );
  112.             }

  114.         }

  116.         // load filters
  117.         selectMailingListUsersFilters( mailinglist );

  119.         return mailinglist;
  120.     }

  122.     /**
  123.      * Delete a record from the table
  124.      *
  125.      * @param nMailingListId
  126.      *            The identifier of the mailingList
  127.      */
  128.     @Override
  129.     public void delete( int nMailingListId )
  130.     {
  131.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE ) )
  132.         {
  133.             daoUtil.setInt( 1, nMailingListId );
  134.             daoUtil.executeUpdate( );
  135.         }

  137.         // delete filters
  138.         deleteFilters( nMailingListId );
  139.     }

  141.     /**
  142.      * Update the record in the table
  143.      *
  144.      * @param mailingList
  145.      *            The reference of the mailingList
  146.      */
  147.     @Override
  148.     public void store( MailingList mailingList )
  149.     {
  150.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE ) )
  151.         {

  153.             daoUtil.setInt( 1, mailingList.getId( ) );
  154.             daoUtil.setString( 2, mailingList.getName( ) );
  155.             daoUtil.setString( 3, mailingList.getDescription( ) );
  156.             daoUtil.setString( 4, mailingList.getWorkgroup( ) );
  157.             daoUtil.setInt( 5, mailingList.getId( ) );

  159.             daoUtil.executeUpdate( );
  160.         }
  161.     }

  163.     /**
  164.      * Load the data of all the mailingLists and returns them in form of a collection
  165.      *
  166.      * @return The Collection which contains the data of all the mailingLists
  167.      */
  168.     @Override
  169.     public Collection<MailingList> selectAll( )
  170.     {
  171.         Collection<MailingList> mailingListList = new ArrayList<>( );
  172.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL ) )
  173.         {
  174.             daoUtil.executeQuery( );

  176.             while ( daoUtil.next( ) )
  177.             {
  178.                 MailingList mailingList = new MailingList( );

  180.                 mailingList.setId( daoUtil.getInt( 1 ) );
  181.                 mailingList.setName( daoUtil.getString( 2 ) );
  182.                 mailingList.setDescription( daoUtil.getString( 3 ) );
  183.                 mailingList.setWorkgroup( daoUtil.getString( 4 ) );

  185.                 mailingListList.add( mailingList );
  186.             }

  188.         }

  190.         return mailingListList;
  191.     }

  193.     /**
  194.      * Returns all mailing lists having a scope restricted to a given workgroup
  195.      *
  196.      * @param strWorkgroup
  197.      *            The workgroup
  198.      * @return the collection which contains the data of all the mailingLists
  199.      */
  200.     @Override
  201.     public Collection<MailingList> selectByWorkgroup( String strWorkgroup )
  202.     {
  203.         Collection<MailingList> mailingListList = new ArrayList<>( );
  204.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_WORKGROUP ) )
  205.         {
  206.             daoUtil.setString( 1, strWorkgroup );
  207.             daoUtil.executeQuery( );

  209.             while ( daoUtil.next( ) )
  210.             {
  211.                 MailingList mailingList = new MailingList( );

  213.                 mailingList.setId( daoUtil.getInt( 1 ) );
  214.                 mailingList.setName( daoUtil.getString( 2 ) );
  215.                 mailingList.setDescription( daoUtil.getString( 3 ) );
  216.                 mailingList.setWorkgroup( daoUtil.getString( 4 ) );

  218.                 mailingListList.add( mailingList );
  219.             }

  221.         }

  223.         return mailingListList;
  224.     }

  226.     /**
  227.      * Insert a new record in the table.
  228.      *
  229.      * @param nMailingListId
  230.      *            The mailing list Id
  231.      * @param mailingListUsersFilter
  232.      *            instance of the MailingListUsersFilter object to insert
  233.      */
  234.     @Override
  235.     public void insertFilter( MailingListUsersFilter mailingListUsersFilter, int nMailingListId )
  236.     {
  237.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FILTERS_INSERT ) )
  238.         {

  240.             daoUtil.setInt( 1, nMailingListId );
  241.             daoUtil.setString( 2, mailingListUsersFilter.getWorkgroup( ) );
  242.             daoUtil.setString( 3, mailingListUsersFilter.getRole( ) );

  244.             daoUtil.executeUpdate( );
  245.         }
  246.     }

  248.     /**
  249.      * Remove an users filter from the mailing list
  250.      * 
  251.      * @param nMailingListId
  252.      *            The Id of the mailing list
  253.      * @param filter
  254.      *            the filter to remove
  255.      */
  256.     @Override
  257.     public void deleteFilter( MailingListUsersFilter filter, int nMailingListId )
  258.     {
  259.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FILTERS_DELETE_FILTER ) )
  260.         {
  261.             daoUtil.setInt( 1, nMailingListId );
  262.             daoUtil.setString( 2, filter.getWorkgroup( ) );
  263.             daoUtil.setString( 3, filter.getRole( ) );
  264.             daoUtil.executeUpdate( );
  265.         }
  266.     }

  268.     /**
  269.      * Delete a record from the table
  270.      *
  271.      * @param nMailingListUsersFilterId
  272.      *            The identifier of the mailingListUsersFilter
  273.      */
  274.     public void deleteFilters( int nMailingListUsersFilterId )
  275.     {
  276.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FILTERS_DELETE ) )
  277.         {
  278.             daoUtil.setInt( 1, nMailingListUsersFilterId );
  279.             daoUtil.executeUpdate( );
  280.         }
  281.     }

  283.     /**
  284.      * Load the data of all the mailingListUsersFilters and returns them in form of a collection
  285.      * 
  286.      * @param mailinglist
  287.      *            The mailing list
  288.      */
  289.     public void selectMailingListUsersFilters( MailingList mailinglist )
  290.     {
  291.         if ( mailinglist != null )
  292.         {
  293.             Collection<MailingListUsersFilter> mailingListUsersFilterList = mailinglist.getFilters( );
  294.             try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FILTERS_SELECTALL ) )
  295.             {
  296.                 daoUtil.setInt( 1, mailinglist.getId( ) );
  297.                 daoUtil.executeQuery( );

  299.                 while ( daoUtil.next( ) )
  300.                 {
  301.                     MailingListUsersFilter mailingListUsersFilter = new MailingListUsersFilter( );

  303.                     mailingListUsersFilter.setWorkgroup( daoUtil.getString( 2 ) );
  304.                     mailingListUsersFilter.setRole( daoUtil.getString( 3 ) );

  306.                     mailingListUsersFilterList.add( mailingListUsersFilter );
  307.                 }

  309.             }
  310.         }
  311.     }

  313.     /**
  314.      * {@inheritDoc}
  315.      */
  316.     @Override
  317.     public boolean checkFilter( MailingListUsersFilter filter, int nId )
  318.     {
  319.         boolean bExists = false;
  320.         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FILTERS_SELECT ) )
  321.         {
  322.             daoUtil.setInt( 1, nId );
  323.             daoUtil.setString( 2, filter.getWorkgroup( ) );
  324.             daoUtil.setString( 3, filter.getRole( ) );
  325.             daoUtil.executeQuery( );

  327.             if ( daoUtil.next( ) )
  328.             {
  329.                 bExists = true;
  330.             }

  332.         }

  334.         return bExists;
  335.     }

  337.     /**
  338.      * {@inheritDoc}
  339.      */
  340.     @Override
  341.     public List<MailingList> selectByFilter( MailingListFilter filter )
  342.     {
  343.         List<MailingList> mailingListList = new ArrayList<>( );
  344.         try ( DAOUtil daoUtil = new DAOUtil( filter.buildSQLQuery( SQL_QUERY_SELECTALL ) ) )
  345.         {
  346.             filter.setFilterValues( daoUtil );
  347.             daoUtil.executeQuery( );

  349.             while ( daoUtil.next( ) )
  350.             {
  351.                 int nIndex = 1;
  352.                 MailingList mailingList = new MailingList( );

  354.                 mailingList.setId( daoUtil.getInt( nIndex++ ) );
  355.                 mailingList.setName( daoUtil.getString( nIndex++ ) );
  356.                 mailingList.setDescription( daoUtil.getString( nIndex++ ) );
  357.                 mailingList.setWorkgroup( daoUtil.getString( nIndex ) );

  359.                 mailingListList.add( mailingList );
  360.             }

  362.         }

  364.         return mailingListList;
  365.     }
  366. }
Created with JaCoCo 0.8.4.201905082037