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.blog.business;
35  
36  import java.util.ArrayList;
37  import java.util.List;
38  
39  import fr.paris.lutece.portal.service.plugin.Plugin;
40  import fr.paris.lutece.util.ReferenceList;
41  import fr.paris.lutece.util.sql.DAOUtil;
42  
43  /**
44   * This class provides Data Access methods for TagDAO objects
45   */
46  public final class TagDAO implements ITagDAO
47  {
48  
49      // Constants
50      private static final String SQL_QUERY_NEW_PK = "SELECT max( id_tag ) FROM blog_tag";
51      private static final String SQL_QUERY_INSERT_TAG = "INSERT INTO blog_tag ( id_tag, name ) VALUES ( ? , ? )";
52      private static final String SQL_QUERY_SELECT_TAG = "SELECT  id_tag, name FROM blog_tag WHERE id_tag = ? ";
53      private static final String SQL_QUERY_SELECT_ALL_TAG = "SELECT  id_tag, name FROM blog_tag ";
54      private static final String SQL_QUERY_SELECT_TAG_BY_NAME = "SELECT  id_tag, name FROM blog_tag WHERE name = ? ";
55  
56      private static final String SQL_QUERY_DELETE = "DELETE FROM blog_tag WHERE id_tag = ? ";
57      private static final String SQL_QUERY_UPDATE = "UPDATE blog_tag SET  id_tag = ?, name = ? WHERE id_tag = ?";
58  
59      private static final String SQL_QUERY_INSERT_TAG_DOC = "INSERT INTO blog_tag_document ( id_tag, id_blog, priority ) VALUES ( ? , ?, ? )";
60      private static final String SQL_QUERY_SELECT_TAG_DOC = " SELECT  a.id_tag, t.name, a.priority FROM blog_tag_document a Inner join blog_tag t on (t.id_tag= a.id_tag) WHERE a.id_blog = ? ORDER BY priority";
61      private static final String SQL_QUERY_DELETE_BY_TAG = "DELETE FROM blog_tag_document WHERE id_tag = ? AND id_blog = ?";
62      private static final String SQL_QUERY_DELETE_BY_DOC = "DELETE FROM blog_tag_document WHERE id_blog = ? ";
63  
64      /**
65       * Generates a new primary key
66       * 
67       * @param plugin
68       *            The Plugin
69       * @return The new primary key
70       */
71      public int newPrimaryKey( Plugin plugin )
72      {
73          int nKey = 1;
74          try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin ) )
75          {
76              daoUtil.executeQuery( );
77  
78              if ( daoUtil.next( ) )
79              {
80                  nKey = daoUtil.getInt( 1 ) + 1;
81              }
82          }
83          return nKey;
84      }
85  
86      /**
87       * {@inheritDoc }
88       */
89      @Override
90      public void insert( Tag tag, Plugin plugin )
91      {
92          try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_TAG, plugin ) )
93          {
94              tag.setIdTag( newPrimaryKey( plugin ) );
95  
96              daoUtil.setInt( 1, tag.getIdTag( ) );
97              daoUtil.setString( 2, tag.getName( ) );
98  
99              daoUtil.executeUpdate( );
100         }
101     }
102 
103     /**
104      * {@inheritDoc }
105      */
106     @Override
107     public Tag load( int idTag, Plugin plugin )
108     {
109         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_TAG, plugin ) )
110         {
111             daoUtil.setInt( 1, idTag );
112             daoUtil.executeQuery( );
113 
114             if ( daoUtil.next( ) )
115             {
116                 Tagplugins/blog/business/Tag.html#Tag">Tag tag = new Tag( );
117 
118                 tag.setIdTag( daoUtil.getInt( 1 ) );
119                 tag.setName( daoUtil.getString( 2 ) );
120 
121                 daoUtil.free( );
122 
123                 return tag;
124             }
125         }
126         return null;
127     }
128 
129     /**
130      * {@inheritDoc }
131      */
132     @Override
133     public List<Tag> loadAllTag( Plugin plugin )
134     {
135         List<Tag> listTag = new ArrayList<>( );
136         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL_TAG, plugin ) )
137         {
138             daoUtil.executeQuery( );
139 
140             while ( daoUtil.next( ) )
141             {
142                 Tagplugins/blog/business/Tag.html#Tag">Tag tag = new Tag( );
143 
144                 tag.setIdTag( daoUtil.getInt( 1 ) );
145                 tag.setName( daoUtil.getString( 2 ) );
146 
147                 listTag.add( tag );
148 
149             }
150         }
151 
152         return listTag;
153     }
154 
155     /**
156      * {@inheritDoc }
157      */
158     @Override
159     public void delete( int idTag, Plugin plugin )
160     {
161         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
162         {
163             daoUtil.setInt( 1, idTag );
164             daoUtil.executeUpdate( );
165         }
166     }
167 
168     /**
169      * {@inheritDoc }
170      */
171     @Override
172     public void store( Tag tag, Plugin plugin )
173     {
174         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
175         {
176 
177             daoUtil.setInt( 1, tag.getIdTag( ) );
178             daoUtil.setString( 2, tag.getName( ) );
179             daoUtil.setInt( 3, tag.getIdTag( ) );
180 
181             daoUtil.executeUpdate( );
182         }
183     }
184 
185     /**
186      * {@inheritDoc }
187      */
188     @Override
189     public void insert( int nIdTag, int nIdDoc, int nPriority, Plugin plugin )
190     {
191         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_TAG_DOC, plugin ) )
192         {
193             daoUtil.setInt( 1, nIdTag );
194             daoUtil.setInt( 2, nIdDoc );
195             daoUtil.setInt( 3, nPriority );
196 
197             daoUtil.executeUpdate( );
198         }
199     }
200 
201     /**
202      * {@inheritDoc }
203      */
204     @Override
205     public void deleteByTAG( int idTag, int idDoc, Plugin plugin )
206     {
207         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_TAG, plugin ) )
208         {
209             daoUtil.setInt( 1, idTag );
210             daoUtil.setInt( 2, idDoc );
211 
212             daoUtil.executeUpdate( );
213         }
214 
215     }
216 
217     /**
218      * {@inheritDoc }
219      */
220     @Override
221     public void deleteByDoc( int idDoc, Plugin plugin )
222     {
223         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_DOC, plugin ) )
224         {
225             daoUtil.setInt( 1, idDoc );
226             daoUtil.executeUpdate( );
227         }
228     }
229 
230     /**
231      * {@inheritDoc }
232      */
233     @Override
234     public List<Tag> loadByDoc( int idDoc, Plugin plugin )
235     {
236         List<Tag> list = new ArrayList<>( );
237         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_TAG_DOC, plugin ) )
238         {
239             daoUtil.setInt( 1, idDoc );
240             daoUtil.executeQuery( );
241 
242             while ( daoUtil.next( ) )
243             {
244                 Tagplugins/blog/business/Tag.html#Tag">Tag tag = new Tag( );
245                 tag.setIdTag( daoUtil.getInt( 1 ) );
246                 tag.setName( daoUtil.getString( 2 ) );
247                 tag.setPriority( daoUtil.getInt( 3 ) );
248                 list.add( tag );
249             }
250         }
251 
252         return list;
253     }
254 
255     /**
256      * {@inheritDoc }
257      */
258     @Override
259     public ReferenceList selectTagsReferenceList( Plugin plugin )
260     {
261         ReferenceList blogList = new ReferenceList( );
262         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL_TAG, plugin ) )
263         {
264             daoUtil.executeQuery( );
265 
266             while ( daoUtil.next( ) )
267             {
268                 blogList.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
269             }
270         }
271         return blogList;
272     }
273 
274     /**
275      * {@inheritDoc }
276      */
277     @Override
278     public List<Tag> loadListTagByIdDoc( int idDoc, Plugin plugin )
279     {
280         List<Tag> listTag = new ArrayList<>( );
281         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_TAG_DOC, plugin ) )
282         {
283             daoUtil.setInt( 1, idDoc );
284             daoUtil.executeQuery( );
285 
286             while ( daoUtil.next( ) )
287             {
288                 Tagplugins/blog/business/Tag.html#Tag">Tag tag = new Tag( );
289                 tag.setIdTag( daoUtil.getInt( 1 ) );
290                 tag.setName( daoUtil.getString( 2 ) );
291                 tag.setPriority( daoUtil.getInt( 3 ) );
292 
293                 listTag.add( tag );
294 
295             }
296         }
297 
298         return listTag;
299     }
300 
301     /**
302      * {@inheritDoc }
303      */
304     @Override
305     public Tag loadByName( String strName, Plugin plugin )
306     {
307         try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_TAG_BY_NAME, plugin ) )
308         {
309             daoUtil.setString( 1, strName );
310             daoUtil.executeQuery( );
311 
312             if ( daoUtil.next( ) )
313             {
314                 Tagplugins/blog/business/Tag.html#Tag">Tag tag = new Tag( );
315 
316                 tag.setIdTag( daoUtil.getInt( 1 ) );
317                 tag.setName( daoUtil.getString( 2 ) );
318 
319                 daoUtil.free( );
320 
321                 return tag;
322             }
323         }
324         return null;
325     }
326 }