View Javadoc
1   /*
2    * Copyright (c) 2002-2020, 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.plugins.announce.business;
35  
36  import fr.paris.lutece.portal.service.plugin.Plugin;
37  import fr.paris.lutece.util.sql.DAOUtil;
38  
39  import java.sql.Date;
40  
41  import java.util.ArrayList;
42  import java.util.List;
43  
44  import org.apache.commons.collections.CollectionUtils;
45  
46  /**
47   * DAO for announce search filters
48   */
49  public class AnnounceSearchFilterDAO implements IAnnounceSearchFilterDAO
50  {
51      private static final String SQL_QUERY_NEW_PRIMARY_KEY = " SELECT max(id_filter) FROM announce_search_filters ";
52      private static final String SQL_QUERY_SELECT = " SELECT id_filter, id_category, keywords, date_min, date_max, price_min, price_max FROM announce_search_filters ";
53      private static final String SQL_QUERY_SELECT_LIST_ID = SQL_QUERY_SELECT + " WHERE id_filter IN ( ";
54      private static final String SQL_QUERY_SELECT_BY_PRIMARY_KEY = SQL_QUERY_SELECT + " WHERE id_filter = ? ";
55      private static final String SQL_QUERY_INSERT = " INSERT INTO announce_search_filters ( id_filter, id_category, keywords, date_min, date_max, price_min, price_max ) VALUES (?,?,?,?,?,?,?) ";
56      private static final String SQL_QUERY_UPDATE = " UPDATE announce_search_filters SET id_category = ?, keywords = ?, date_min = ?, date_max = ?, price_min = ?, price_max = ? WHERE id_filter = ? ";
57      private static final String SQL_QUERY_DELETE = " DELETE FROM announce_search_filters WHERE id_filter = ? ";
58      private static final String SQL_QUERY_DELETE_BY_ID_CATEGORY = " DELETE FROM announce_search_filters WHERE id_category = ? ";
59      private static final String CONSTANT_COMA = ",";
60      private static final String CONSTANT_CLOSE_PARENTHESIS = ")";
61  
62      /**
63       * Get a new primary key
64       * 
65       * @param plugin
66       *            The plugin
67       * @return the new value of the primary key
68       */
69      private int getNewPrimaryKey( Plugin plugin )
70      {
71          int nRes = 1;
72          try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PRIMARY_KEY, plugin ) )
73          {
74              daoUtil.executeQuery( );
75  
76              if ( daoUtil.next( ) )
77              {
78                  nRes = daoUtil.getInt( 1 ) + 1;
79              }
80          }
81          return nRes;
82      }
83  
84      /**
85       * {@inheritDoc}
86       */
87      @Override
88      public AnnounceSearchFilter findByPrimaryKey( int nIdFilter, Plugin plugin )
89      {
90          AnnounceSearchFilter filter = null;
91          try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_PRIMARY_KEY, plugin ) )
92          {
93              daoUtil.setInt( 1, nIdFilter );
94              daoUtil.executeQuery( );
95  
96              if ( daoUtil.next( ) )
97              {
98                  filter = getFilterFromDAO( daoUtil );
99              }
100         }
101         return filter;
102     }
103 
104     /**
105      * {@inheritDoc}
106      */
107     @Override
108     public synchronized void create( AnnounceSearchFilter filter, Plugin plugin )
109     {
110         filter.setIdFilter( getNewPrimaryKey( plugin ) );
111 
112         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin ) )
113         {
114             int nIndex = 1;
115             daoUtil.setInt( nIndex++, filter.getIdFilter( ) );
116             daoUtil.setInt( nIndex++, filter.getIdCategory( ) );
117             daoUtil.setString( nIndex++, filter.getKeywords( ) );
118             daoUtil.setDate( nIndex++, ( filter.getDateMin( ) == null ) ? null : new Date( filter.getDateMin( ).getTime( ) ) );
119             daoUtil.setDate( nIndex++, ( filter.getDateMax( ) == null ) ? null : new Date( filter.getDateMax( ).getTime( ) ) );
120             daoUtil.setInt( nIndex++, filter.getPriceMin( ) );
121             daoUtil.setInt( nIndex, filter.getPriceMax( ) );
122             daoUtil.executeUpdate( );
123         }
124     }
125 
126     /**
127      * {@inheritDoc}
128      */
129     @Override
130     public void update( AnnounceSearchFilter filter, Plugin plugin )
131     {
132         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
133         {
134             int nIndex = 1;
135             daoUtil.setInt( nIndex++, filter.getIdCategory( ) );
136             daoUtil.setString( nIndex++, filter.getKeywords( ) );
137             daoUtil.setDate( nIndex++, new Date( filter.getDateMin( ).getTime( ) ) );
138             daoUtil.setDate( nIndex++, new Date( filter.getDateMax( ).getTime( ) ) );
139             daoUtil.setInt( nIndex++, filter.getPriceMin( ) );
140             daoUtil.setInt( nIndex++, filter.getPriceMax( ) );
141             daoUtil.setInt( nIndex, filter.getIdFilter( ) );
142 
143             daoUtil.executeUpdate( );
144         }
145     }
146 
147     /**
148      * {@inheritDoc}
149      */
150     @Override
151     public void delete( int nIdFilter, Plugin plugin )
152     {
153         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
154         {
155             daoUtil.setInt( 1, nIdFilter );
156             daoUtil.executeUpdate( );
157         }
158     }
159 
160     /**
161      * {@inheritDoc}
162      */
163     @Override
164     public void deleteByIdCategory( int nIdCategory, Plugin plugin )
165     {
166         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_ID_CATEGORY, plugin ) )
167         {
168             daoUtil.setInt( 1, nIdCategory );
169             daoUtil.executeUpdate( );
170         }
171     }
172 
173     /**
174      * {@inheritDoc}
175      */
176     @Override
177     public List<AnnounceSearchFilter> findAll( Plugin plugin )
178     {
179         List<AnnounceSearchFilter> listFilters = new ArrayList<>( );
180         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
181         {
182             daoUtil.executeQuery( );
183 
184             while ( daoUtil.next( ) )
185             {
186                 listFilters.add( getFilterFromDAO( daoUtil ) );
187             }
188         }
189         return listFilters;
190     }
191 
192     /**
193      * {@inheritDoc}
194      */
195     @Override
196     public List<AnnounceSearchFilter> findByListId( List<Integer> listIdFilters, Plugin plugin )
197     {
198         if ( CollectionUtils.isEmpty( listIdFilters ) )
199         {
200             return null;
201         }
202 
203         StringBuilder sbSql = new StringBuilder( SQL_QUERY_SELECT_LIST_ID );
204         boolean bHasContent = false;
205 
206         for ( int nIdFilters : listIdFilters )
207         {
208             if ( bHasContent )
209             {
210                 sbSql.append( CONSTANT_COMA );
211             }
212 
213             sbSql.append( nIdFilters );
214         }
215 
216         sbSql.append( CONSTANT_CLOSE_PARENTHESIS );
217         List<AnnounceSearchFilter> listFilters = new ArrayList<>( );
218         try ( DAOUtil daoUtil = new DAOUtil( sbSql.toString( ), plugin ) )
219         {
220             daoUtil.executeQuery( );
221 
222             while ( daoUtil.next( ) )
223             {
224                 listFilters.add( getFilterFromDAO( daoUtil ) );
225             }
226         }
227         return listFilters;
228     }
229 
230     /**
231      * Get a filter from a DAOUtil
232      * 
233      * @param daoUtil
234      *            The daoUtil to read data from. Note that the daoUtil will NOT be freed by this method, and that the call to the daoUtil.next( ) must have been
235      *            done before calling this method.
236      * @return The filter
237      */
238     private AnnounceSearchFilter getFilterFromDAO( DAOUtil daoUtil )
239     {
240         int nIndex = 1;
241         AnnounceSearchFilteriness/AnnounceSearchFilter.html#AnnounceSearchFilter">AnnounceSearchFilter filter = new AnnounceSearchFilter( );
242         filter.setIdFilter( daoUtil.getInt( nIndex++ ) );
243         filter.setIdCategory( daoUtil.getInt( nIndex++ ) );
244         filter.setKeywords( daoUtil.getString( nIndex++ ) );
245         filter.setDateMin( daoUtil.getDate( nIndex++ ) );
246         filter.setDateMax( daoUtil.getDate( nIndex++ ) );
247         filter.setPriceMin( daoUtil.getInt( nIndex++ ) );
248         filter.setPriceMax( daoUtil.getInt( nIndex ) );
249 
250         return filter;
251     }
252 }