View Javadoc
1   /*
2    * Copyright (c) 2002-2014, Mairie de 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.dashboard;
35  
36  import fr.paris.lutece.portal.service.dashboard.IDashboardComponent;
37  import fr.paris.lutece.portal.service.util.AppLogService;
38  import fr.paris.lutece.util.sql.DAOUtil;
39  
40  import java.util.ArrayList;
41  import java.util.List;
42  
43  
44  /**
45   *
46   * DashboardDAO
47   *
48   */
49  public class DashboardDAO implements IDashboardDAO
50  {
51      private static final String SQL_QUERY_MAX_ORDER = "SELECT max(dashboard_order) FROM core_dashboard";
52      private static final String SQL_QUERY_MAX_ORDER_COLUMN = SQL_QUERY_MAX_ORDER + " WHERE dashboard_column = ? ";
53      private static final String SQL_QUERY_DELETE = "DELETE FROM core_dashboard ";
54      private static final String SQL_QUERY_DELETE_BY_NAME = SQL_QUERY_DELETE + " WHERE dashboard_name = ? ";
55      private static final String SQL_QUERY_SELECT = "SELECT dashboard_name, dashboard_order, dashboard_column  FROM core_dashboard ";
56      private static final String SQL_QUERY_ORDER_BY_COLUMN_AND_ORDER = " ORDER BY dashboard_column, dashboard_order";
57      private static final String SQL_QUERY_SELECT_ALL = SQL_QUERY_SELECT + " WHERE dashboard_column != - 1 " +
58          SQL_QUERY_ORDER_BY_COLUMN_AND_ORDER;
59      private static final String SQL_QUERY_SELECT_COLUMNS = "SELECT dashboard_column FROM core_dashboard GROUP BY dashboard_column";
60      private static final String SQL_QUERY_FILTER_COLUMN = " dashboard_column = ? ";
61      private static final String SQL_QUERY_FILTER_ORDER = " dashboard_order = ? ";
62      private static final String SQL_QUERY_FILTER_NAME = " dashboard_name = ? ";
63      private static final String SQL_QUERY_SELECT_BY_PRIMARY_KEY = SQL_QUERY_SELECT + " WHERE " + SQL_QUERY_FILTER_NAME;
64      private static final String SQL_QUERY_INSERT = "INSERT INTO core_dashboard( dashboard_name, dashboard_order, dashboard_column ) " +
65          " VALUES(?,?,?)";
66      private static final String SQL_QUERY_UPDATE = "UPDATE core_dashboard " +
67          " SET dashboard_order = ?, dashboard_column = ? WHERE dashboard_name = ?";
68      private static final String SQL_QUERY_KEYWORD_WHERE = "  WHERE ";
69      private static final String SQL_QUERY_KEYWORD_AND = " AND ";
70  
71      /**
72       * {@inheritDoc}
73       */
74      @Override
75      public void delete( String strBeanName )
76      {
77          DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_NAME );
78  
79          daoUtil.setString( 1, strBeanName );
80  
81          daoUtil.executeUpdate(  );
82  
83          daoUtil.free(  );
84      }
85  
86      /**
87       * {@inheritDoc}
88       */
89      @Override
90      public void deleteAll(  )
91      {
92          DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE );
93  
94          daoUtil.executeUpdate(  );
95  
96          daoUtil.free(  );
97      }
98  
99      /**
100      * {@inheritDoc}
101      */
102     @Override
103     public void insert( IDashboardComponent dashboardComponent )
104     {
105         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT );
106 
107         daoUtil.setString( 1, dashboardComponent.getName(  ) );
108         setInsertOrUpdateValues( 2, dashboardComponent, daoUtil );
109 
110         daoUtil.executeUpdate(  );
111 
112         daoUtil.free(  );
113     }
114 
115     /**
116      * Loads the dashboard from the factory
117      * @param strBeanName the dashboard name
118      * @return the dashboard found
119      * @see DashboardFactory#getDashboardComponent(String)
120      */
121     private IDashboardComponent findDashboardFromFactory( String strBeanName )
122     {
123         return DashboardFactory.getDashboardComponent( strBeanName );
124     }
125 
126     /**
127      * {@inheritDoc}
128      */
129     @Override
130     public IDashboardComponent load( String strClassName )
131     {
132         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_PRIMARY_KEY );
133 
134         daoUtil.setString( 1, strClassName );
135 
136         daoUtil.executeQuery(  );
137 
138         IDashboardComponent dashboardComponent = null;
139 
140         if ( daoUtil.next(  ) )
141         {
142             String strBeanName = daoUtil.getString( 1 );
143 
144             dashboardComponent = findDashboardFromFactory( strBeanName );
145 
146             if ( dashboardComponent != null )
147             {
148                 load( dashboardComponent, daoUtil );
149             }
150             else
151             {
152                 AppLogService.error( "Dashboard named " + strBeanName + " not found" );
153             }
154         }
155 
156         daoUtil.free(  );
157 
158         return dashboardComponent;
159     }
160 
161     /**
162      * {@inheritDoc}
163      */
164     @Override
165     public List<IDashboardComponent> selectAllDashboardComponents(  )
166     {
167         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL );
168 
169         daoUtil.executeQuery(  );
170 
171         List<IDashboardComponent> listDashboards = new ArrayList<IDashboardComponent>(  );
172 
173         while ( daoUtil.next(  ) )
174         {
175             IDashboardComponent dashboardComponent = null;
176 
177             String strBeanName = daoUtil.getString( 1 );
178 
179             dashboardComponent = findDashboardFromFactory( strBeanName );
180 
181             if ( dashboardComponent != null )
182             {
183                 load( dashboardComponent, daoUtil );
184                 listDashboards.add( dashboardComponent );
185             }
186             else
187             {
188                 AppLogService.error( "Dashboard named " + strBeanName + " not found" );
189             }
190         }
191 
192         daoUtil.free(  );
193 
194         return listDashboards;
195     }
196 
197     /**
198      * {@inheritDoc}
199      */
200     @Override
201     public int selectMaxOrder(  )
202     {
203         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_MAX_ORDER );
204 
205         int nMaxOrder = 0;
206 
207         daoUtil.executeQuery(  );
208 
209         if ( daoUtil.next(  ) )
210         {
211             nMaxOrder = daoUtil.getInt( 1 );
212         }
213 
214         daoUtil.free(  );
215 
216         return nMaxOrder;
217     }
218 
219     /**
220      * {@inheritDoc}
221      */
222     @Override
223     public int selectMaxOrder( int nColumn )
224     {
225         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_MAX_ORDER_COLUMN );
226 
227         int nMaxOrder = 0;
228 
229         daoUtil.setInt( 1, nColumn );
230 
231         daoUtil.executeQuery(  );
232 
233         if ( daoUtil.next(  ) )
234         {
235             nMaxOrder = daoUtil.getInt( 1 );
236         }
237 
238         daoUtil.free(  );
239 
240         return nMaxOrder;
241     }
242 
243     /**
244      * {@inheritDoc}
245      */
246     @Override
247     public List<IDashboardComponent> selectDashboardComponents( DashboardFilter filter )
248     {
249         StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT );
250         buildSQLFilter( sbSQL, filter );
251         sbSQL.append( SQL_QUERY_ORDER_BY_COLUMN_AND_ORDER );
252 
253         DAOUtil daoUtil = new DAOUtil( sbSQL.toString(  ) );
254 
255         applySQLFilter( daoUtil, 1, filter );
256 
257         daoUtil.executeQuery(  );
258 
259         List<IDashboardComponent> listDashboards = new ArrayList<IDashboardComponent>(  );
260 
261         while ( daoUtil.next(  ) )
262         {
263             IDashboardComponent dashboardComponent = null;
264 
265             String strBeanName = daoUtil.getString( 1 );
266 
267             dashboardComponent = findDashboardFromFactory( strBeanName );
268 
269             if ( dashboardComponent != null )
270             {
271                 load( dashboardComponent, daoUtil );
272                 listDashboards.add( dashboardComponent );
273             }
274             else
275             {
276                 AppLogService.error( "dashboard named " + strBeanName + " not found" );
277             }
278         }
279 
280         daoUtil.free(  );
281 
282         return listDashboards;
283     }
284 
285     /**
286      * {@inheritDoc}
287      */
288     @Override
289     public void store( IDashboardComponent dashboardComponent )
290     {
291         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE );
292 
293         int nIndex = setInsertOrUpdateValues( 1, dashboardComponent, daoUtil );
294         daoUtil.setString( nIndex, dashboardComponent.getName(  ) );
295 
296         daoUtil.executeUpdate(  );
297 
298         daoUtil.free(  );
299     }
300 
301     /**
302      * {@inheritDoc}
303      */
304     @Override
305     public List<Integer> selectColumns(  )
306     {
307         List<Integer> listColumns = new ArrayList<Integer>(  );
308 
309         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_COLUMNS );
310 
311         daoUtil.executeQuery(  );
312 
313         while ( daoUtil.next(  ) )
314         {
315             listColumns.add( daoUtil.getInt( 1 ) );
316         }
317 
318         daoUtil.free(  );
319 
320         return listColumns;
321     }
322 
323     /**
324      * Loads compenent data from daoUtil
325      * @param component the component
326      * @param daoUtil the daoutil
327      */
328     private void load( IDashboardComponent component, DAOUtil daoUtil )
329     {
330         int nIndex = 1;
331         component.setName( daoUtil.getString( nIndex++ ) );
332         component.setOrder( daoUtil.getInt( nIndex++ ) );
333         component.setZone( daoUtil.getInt( nIndex++ ) );
334     }
335 
336     /**
337      * Sets daoUtil values from componnet
338      * @param nStartIndex the start index
339      * @param component the component
340      * @param daoUtil daoutil
341      * @return the end index
342      */
343     private int setInsertOrUpdateValues( int nStartIndex, IDashboardComponent component, DAOUtil daoUtil )
344     {
345         int nIndex = nStartIndex;
346         daoUtil.setInt( nIndex++, component.getOrder(  ) );
347         daoUtil.setInt( nIndex++, component.getZone(  ) );
348 
349         return nIndex;
350     }
351 
352     /**
353      * Builds sql filter
354      * @param sbSQL the buffer
355      * @param filter the filter
356      */
357     private void buildSQLFilter( StringBuilder sbSQL, DashboardFilter filter )
358     {
359         List<String> listFilters = new ArrayList<String>(  );
360 
361         if ( filter.containsFilterOrder(  ) )
362         {
363             listFilters.add( SQL_QUERY_FILTER_ORDER );
364         }
365 
366         if ( filter.containsFilterColumn(  ) )
367         {
368             listFilters.add( SQL_QUERY_FILTER_COLUMN );
369         }
370 
371         if ( !listFilters.isEmpty(  ) )
372         {
373             sbSQL.append( SQL_QUERY_KEYWORD_WHERE );
374 
375             boolean bFirstFilter = true;
376 
377             for ( String strFilter : listFilters )
378             {
379                 sbSQL.append( strFilter );
380 
381                 if ( !bFirstFilter )
382                 {
383                     sbSQL.append( SQL_QUERY_KEYWORD_AND );
384                 }
385                 else
386                 {
387                     bFirstFilter = false;
388                 }
389             }
390         }
391     }
392 
393     /**
394      * Add daoUtil parameters
395      * @param daoUtil daoUtil
396      * @param nStartIndex start index
397      * @param filter the filter to apply
398      * @return end index
399      */
400     private int applySQLFilter( DAOUtil daoUtil, int nStartIndex, DashboardFilter filter )
401     {
402         int nIndex = nStartIndex;
403 
404         if ( filter.containsFilterOrder(  ) )
405         {
406             daoUtil.setInt( nIndex++, filter.getFilterOrder(  ) );
407         }
408 
409         if ( filter.containsFilterColumn(  ) )
410         {
411             daoUtil.setInt( nIndex++, filter.getFilterColumn(  ) );
412         }
413 
414         return nIndex;
415     }
416 }