CategoryPropDB


1   package org.jahia.services.categories;
2   
3   import org.jahia.exceptions.JahiaException;
4   import org.jahia.services.database.ConnectionDispenser;
5   
6   import java.sql.*;
7   import java.util.ArrayList  ;
8   import java.util.Enumeration  ;
9   import java.util.Properties  ;
10  
11  /**
12   * <p>Title: Persistance class for category properties</p>
13   * <p>Description: </p>
14   * <p>Copyright: Copyright (c) 2002</p>
15   * <p>Company: Jahia Ltd</p>
16   *
17   * @author Serge Huber
18   * @version 1.0
19   */
20  
21  public class CategoryPropDB {
22  
23      private static org.apache.log4j.Logger logger =
24              org.apache.log4j.Logger.getLogger (CategoryPropDB.class);
25  
26      private static final String   GET_CATEGORY_PROPS_QUERY =
27              "SELECT * FROM jahia_category_prop WHERE id_category=?";
28      private static final String   GET_CATEGORY_PROP_QUERY =
29              "SELECT value_category_prop FROM jahia_category_prop WHERE id_category=? AND name_category_prop=?";
30  
31      private static final String   FIND_CATEGORYIDS_BYPROPNAMEANDVALUE_QUERY =
32              "SELECT id_category FROM jahia_category_prop WHERE name_category_prop LIKE ? AND value_category_prop LIKE ?";
33  
34      private static final String   SET_CATEGORY_PROPS_QUERY =
35              "INSERT INTO jahia_category_prop VALUES(?,?,?)";
36      private static final String   SET_CATEGORY_PROP_QUERY =
37              "INSERT INTO jahia_category_prop VALUES(?,?,?)";
38      private static final String   UPDATE_CATEGORY_PROP_QUERY =
39          "UPDATE jahia_category_prop SET value_category_prop = ? WHERE id_category=? AND name_category_prop=?";
40  
41      private static final String   REMOVE_CATEGORY_PROPS_QUERY =
42              "DELETE FROM jahia_category_prop WHERE id_category=?";
43      private static final String   REMOVE_CATEGORY_PROP_QUERY =
44              "DELETE FROM jahia_category_prop WHERE id_category=? AND name_category_prop=?";
45  
46      private static CategoryPropDB singletonInstance = null;
47  
48      /**
49       * Default constructor, not much to say here...
50       */
51      private CategoryPropDB () {
52      }
53  
54      /**
55       * @return an instance of this class
56       */
57      protected static synchronized CategoryPropDB getInstance () {
58          if (singletonInstance == null) {
59              singletonInstance = new CategoryPropDB ();
60          }
61          return singletonInstance;
62      }
63  
64      /**
65       * Retrieves all the properties for a given category
66       *
67       * @param categoryID the identifier of the category whose properties
68       *                   we want to retrieve from the database
69       *
70       * @return a Properties object that contains all the properties that are
71       *         available for this category in the database. This method will never
72       *         return null, but it will return an empty property set if no properties
73       *         exist for this category in the database
74       *
75       * @throws JahiaException generated if there were problems executing the
76       *                        query or communicating with the database.
77       */
78      protected Properties   getProperties (int categoryID)
79              throws JahiaException {
80          Properties   result = new Properties   ();
81          Connection dbConn = null;
82          PreparedStatement stmt = null;
83          ResultSet rs = null;
84          try {
85  
86              dbConn = ConnectionDispenser.getConnection ();
87              stmt = dbConn.prepareStatement (GET_CATEGORY_PROPS_QUERY);
88              stmt.setInt (1, categoryID);
89              rs = stmt.executeQuery ();
90  
91              while (rs.next ()) {
92                  String   name = rs.getString ("name_category_prop");
93                  String   value = rs.getString ("value_category_prop");
94                  result.setProperty (name, value);
95              }
96  
97          } catch (SQLException se) {
98              String   errorMsg = "Error while retrieving category " + categoryID +
99                      " properties : ";
100             logger.error (errorMsg, se);
101             throw new JahiaException (
102                     "Cannot load category " + categoryID +
103                     " properties from the database",
104                     errorMsg, JahiaException.DATABASE_ERROR,
105                     JahiaException.CRITICAL_SEVERITY, se);
106         } finally {
107             closeStatement (stmt);
108         }
109         return result;
110 
111     }
112 
113     /**
114      * Saves a whole set of properties in the database for the specified
115      * category. WARNING : the way this is implemented (for speed reasons)
116      * is that first all the existing properties for a category are
117      * DELETED from the database. Remember to always load the full set of
118      * properties before calling this method or this will result in dataloss.
119      * Also if the operation with the database fails during the delete operation
120      * or the insertion of the new elements this will also occur in data loss.
121      * A safer way would be to test the existence of each property in the database
122      * and then INSERT or UPDATE the value, but that would take forever.
123      *
124      * @param categoryID         the category whose properties we are serializing
125      *                           in the database.
126      * @param categoryProperties the Properties object that contains all
127      *                           the properties to save in the database. Only what is passed here will
128      *                           exist in the database. If the database contained properties that aren't
129      *                           in this hashtable they will be deleted.
130      *
131      * @throws JahiaException generated if there were problems executing the
132      *                        query or communicating with the database.
133      */
134     protected void setProperties (int categoryID,
135                                   Properties   categoryProperties)
136             throws JahiaException {
137 
138         // First we clear all the existing properties in the database...
139         // Warning this is dangerous if the operation is interrupted in the
140         // middle.
141         removeProperties (categoryID);
142 
143         Connection dbConn = null;
144         PreparedStatement pstmt = null;
145 
146         try {
147             // opens connection
148             dbConn = ConnectionDispenser.getConnection ();
149 
150             Enumeration   propNames = categoryProperties.keys ();
151             while (propNames.hasMoreElements ()) {
152                 String   curPropName = (String  ) propNames.nextElement ();
153                 String   curPropValue = categoryProperties.getProperty (
154                         curPropName);
155 
156                 pstmt = dbConn.prepareStatement (SET_CATEGORY_PROPS_QUERY);
157                 pstmt.setInt (1, categoryID);
158                 pstmt.setString (2, curPropName);
159                 pstmt.setString (3, curPropValue);
160                 pstmt.executeUpdate ();
161             }
162 
163         } catch (SQLException se) {
164             String   errorMsg = "Error while storing category " + categoryID +
165                     " properties in database:";
166             logger.error (errorMsg, se);
167             throw new JahiaException (
168                     "Cannot create category " + categoryID +
169                     " properties in the database",
170                     errorMsg, JahiaException.DATABASE_ERROR,
171                     JahiaException.CRITICAL_SEVERITY, se);
172         } finally {
173             closeStatement (pstmt);
174         }
175     }
176 
177     /**
178      * Removes all the properties for the specified category ID.
179      *
180      * @param categoryID idenfitifer of the category to delete all the
181      *                   properties
182      *
183      * @throws JahiaException generated if there were problems executing the
184      *                        query or communicating with the database.
185      */
186     protected void removeProperties (int categoryID)
187             throws JahiaException {
188         Connection dbConn = null;
189         PreparedStatement stmt = null;
190         try {
191 
192             // executes the query
193             dbConn = ConnectionDispenser.getConnection ();
194             stmt = dbConn.prepareStatement (REMOVE_CATEGORY_PROPS_QUERY);
195             stmt.setInt (1, categoryID);
196             stmt.executeUpdate ();
197 
198         } catch (SQLException se) {
199             String   errorMsg = "Error while removing properties for category " +
200                     categoryID + " : ";
201             logger.error (errorMsg, se);
202             throw new JahiaException (
203                     "Cannot delete category " + categoryID +
204                     " properties in the database",
205                     errorMsg, JahiaException.DATABASE_ERROR,
206                     JahiaException.CRITICAL_SEVERITY, se);
207         } finally {
208             closeStatement (stmt);
209         }
210     }
211 
212     /**
213      * Retrieves the category property from the database. This method may
214      * also be used as a test for existence of a property in the database.
215      *
216      * @param categoryID   identifier of the category
217      * @param propertyName name of the property to retrieve
218      *
219      * @return a String containing the value of the property, or null if the
220      *         property doesn't have a value in the database (ie if it doesn't exist)
221      *
222      * @throws JahiaException generated if there were problems executing the
223      *                        query or communicating with the database.
224      */
225     protected String   getProperty (int categoryID, String   propertyName)
226             throws JahiaException {
227         String   result = null;
228         Connection dbConn = null;
229         PreparedStatement pstmt = null;
230         ResultSet rs = null;
231         try {
232             dbConn = ConnectionDispenser.getConnection ();
233 
234             pstmt = dbConn.prepareStatement (GET_CATEGORY_PROP_QUERY);
235 
236             pstmt.setInt (1, categoryID);
237             pstmt.setString (2, propertyName);
238 
239             rs = pstmt.executeQuery ();
240 
241             if (rs.next ()) {
242                 String   value = rs.getString ("value_category_prop");
243                 result = value;
244             }
245 
246         } catch (SQLException se) {
247             String   errorMsg = "Error while retrieving property [" +
248                     propertyName + "] for category " + categoryID +
249                     ": ";
250             logger.error (errorMsg, se);
251             throw new JahiaException (
252                     "Cannot load category " + categoryID + " property [" +
253                     propertyName + "] from the database",
254                     errorMsg, JahiaException.DATABASE_ERROR,
255                     JahiaException.CRITICAL_SEVERITY, se);
256         } finally {
257             closeStatement (pstmt);
258         }
259         return result;
260     }
261 
262     /**
263      * Saves a single property in the database for a given category. This
264      * operation starts by deleting any existing entry and then inserting a
265      * new value.
266      *
267      * @param categoryID    identifier of the category
268      * @param propertyName  name of the property to add in the database
269      * @param propertyValue name of the property to add in the database
270      *
271      * @throws JahiaException generated if there were problems executing the
272      *                        query or communicating with the database.
273      */
274     protected void setProperty (int categoryID,
275                                 String   propertyName,
276                                 String   propertyValue)
277             throws JahiaException {
278 
279         Connection dbConn = null;
280         PreparedStatement pstmt = null;
281 
282         try {
283             // opens connection
284             dbConn = ConnectionDispenser.getConnection ();
285 
286             pstmt = dbConn.prepareStatement (UPDATE_CATEGORY_PROP_QUERY);            
287             pstmt.setString (1, propertyValue);
288             pstmt.setInt (2, categoryID);
289             pstmt.setString (3, propertyName);
290             
291             int rowCount = pstmt.executeUpdate ();
292             if (rowCount == 0) {
293                 closeStatement (pstmt);
294                 
295                 pstmt = dbConn.prepareStatement (SET_CATEGORY_PROP_QUERY);
296                 pstmt.setInt (1, categoryID);
297                 pstmt.setString (2, propertyName);
298                 pstmt.setString (3, propertyValue);                
299                 pstmt.executeUpdate ();
300             }
301         } catch (SQLException se) {
302             String   errorMsg = "Error while storing property [" + propertyName +
303                     "] with value [" + propertyValue +
304                     "] for category " + categoryID + ":";
305             logger.error (errorMsg, se);
306             throw new JahiaException (
307                     "Cannot create category " + categoryID + " property [" +
308                     propertyName + "] in the database",
309                     errorMsg, JahiaException.DATABASE_ERROR,
310                     JahiaException.CRITICAL_SEVERITY, se);
311         } finally {
312             closeStatement (pstmt);
313         }
314     }
315 
316     /**
317      * Removes a single property for the given category.
318      *
319      * @param categoryID   identifer of the category
320      * @param propertyName name of the property to be deleted.
321      *
322      * @throws JahiaException generated if there were problems executing the
323      *                        query or communicating with the database.
324      */
325     protected void removeProperty (int categoryID,
326                                    String   propertyName)
327             throws JahiaException {
328         Connection dbConn = null;
329         PreparedStatement pstmt = null;
330         try {
331 
332             dbConn = ConnectionDispenser.getConnection ();
333 
334             // executes the query
335             pstmt = dbConn.prepareStatement (REMOVE_CATEGORY_PROP_QUERY);
336             pstmt.setInt (1, categoryID);
337             pstmt.setString (2, propertyName);
338             pstmt.executeUpdate ();
339         } catch (SQLException se) {
340             String   errorMsg = "Error while removing property [" + propertyName +
341                     "] for category " + categoryID + ": ";
342             logger.error (errorMsg, se);
343             throw new JahiaException (
344                     "Cannot delete category " + categoryID + " property [" +
345                     propertyName + "] in the database",
346                     errorMsg, JahiaException.DATABASE_ERROR,
347                     JahiaException.CRITICAL_SEVERITY, se);
348         } finally {
349             closeStatement (pstmt);
350         }
351     }
352 
353     /**
354      * Finds category identifier by specifying property name and
355      * property value. These will be used to build LIKE SQL queries to find
356      * all the categories that contain strings that match the properties. It
357      * is allowed to use "%" characters here to perform partial String matching
358      * instead of full String matching.
359      *
360      * @param propName  a property name that will be used to
361      *                  retrieve categories by their ID. Use "%" character for partial matching.
362      * @param propValue a partial property value that will be used to
363      *                  retrieve categories by their ID. Use "%" character for partial matching.
364      *
365      * @return an ArrayList containing Integer objects that are the category
366      *         IDs that correspond to the matched properties.
367      *
368      * @throws JahiaException generated if there were problems executing the
369      *                        query or communicating with the database.
370      */
371     protected ArrayList   findCategoryIDsByPropNameAndValue (String   propName,
372                                                            String   propValue)
373             throws JahiaException {
374 
375         ArrayList   result = new ArrayList   ();
376         Connection dbConn = null;
377         PreparedStatement stmt = null;
378         ResultSet rs = null;
379         try {
380 
381             dbConn = ConnectionDispenser.getConnection ();
382             stmt = dbConn.prepareStatement (FIND_CATEGORYIDS_BYPROPNAMEANDVALUE_QUERY);
383             stmt.setString (1, propName);
384             stmt.setString (2, propValue);
385             rs = stmt.executeQuery ();
386 
387             while (rs.next ()) {
388                 int id = rs.getInt ("id_category");
389                 result.add (new Integer   (id));
390             }
391 
392         } catch (SQLException se) {
393             String   errorMsg = "Error while retrieving category by partial property name " + propName +
394                     " and partial property value " + propValue + " : ";
395             logger.error (errorMsg, se);
396             throw new JahiaException (
397                     "Cannot find categories by partial property name and value from the database",
398                     errorMsg, JahiaException.DATABASE_ERROR,
399                     JahiaException.CRITICAL_SEVERITY, se);
400         } finally {
401             closeStatement (stmt);
402         }
403         return result;
404 
405     }
406 
407 
408     //-------------------------------------------------------------------------
409     private void closeStatement (Statement statement) {
410         // Close the opened statement
411         try {
412             if (statement != null) {
413                 statement.close ();
414             }
415         } catch (SQLException sqlEx) {
416             logger.error ("Error while closing a statement", sqlEx);
417         }
418     }
419 
420 }
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags