JahiaContainerPropDB


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