JahiaPageTemplateDB


1   //
2   //                                   ____.
3   //                       __/\ ______|    |__/\.     _______
4   //            __   .____|    |       \   |    +----+       \
5   //    _______|  /--|    |    |    -   \  _    |    :    -   \_________
6   //   \\______: :---|    :    :           |    :    |         \________>
7   //           |__\---\_____________:______:    :____|____:_____\
8   //                                      /_____|
9   //
10  //                 . . . i n   j a h i a   w e   t r u s t . . .
11  //
12  
13  package org.jahia.services.pages;
14  
15  import org.apache.log4j.Logger;
16  import org.jahia.data.JahiaDBDOMObject;
17  import org.jahia.data.JahiaDOMObject;
18  import org.jahia.exceptions.JahiaException;
19  import org.jahia.exceptions.database.JahiaDatabaseException;
20  import org.jahia.services.database.ConnectionDispenser;
21  import org.jahia.utils.JahiaTools;
22  
23  import java.sql.Connection  ;
24  import java.sql.PreparedStatement  ;
25  import java.sql.ResultSet  ;
26  import java.sql.SQLException  ;
27  import java.sql.Statement  ;
28  import java.util.Vector  ;
29  
30  public class JahiaPageTemplateDB {
31  
32      /** The logging reference. */
33      private static Logger logger = Logger.getLogger(JahiaPageTemplateDB.class);
34  
35      /** The singleton unique instance. */
36      private static JahiaPageTemplateDB mObject = null;
37  
38  
39      /**
40       * Default constructor
41       */
42      private JahiaPageTemplateDB() {
43      }
44  
45  
46      public static JahiaPageTemplateDB getInstance() {
47          if (mObject == null) {
48              mObject = new JahiaPageTemplateDB();
49          }
50          return mObject;
51      }
52  
53      /**
54       * load a page template by its template id
55       *
56       * @param templateID the template id
57       */
58      public synchronized JahiaPageDefinition loadPageTemplate(int templateID)
59              throws JahiaException {
60          Connection   dbConn = null;
61          PreparedStatement   statement = null;
62          JahiaPageDefinition theTemplate = null;
63          try {
64              String   sqlQuery = "SELECT * FROM jahia_pages_def WHERE id_jahia_pages_def=?";
65  
66              dbConn = ConnectionDispenser.getConnection();
67              statement = dbConn.prepareStatement(sqlQuery);
68              if (statement != null) {
69                  statement.setInt(1, templateID);
70                  ResultSet   rs = statement.executeQuery();
71                  if (rs != null) {
72                      if (rs.next()) {
73                          theTemplate = readTemplateFromResultSet(rs);
74                      }
75                  }
76              }
77  
78          } catch (SQLException   se) {
79              String   errorMsg = "Error in db_load_page_definition : " + se.getMessage();
80              logger.error(errorMsg + " -> BAILING OUT");
81              throw new JahiaException("Cannot load page data from the database",
82                      errorMsg, JahiaException.DATABASE_ERROR, JahiaException.CRITICAL_SEVERITY);
83          } finally {
84              closeStatement(statement);
85          }
86  
87          // load the properties
88          if (theTemplate != null) {
89              theTemplate.setProperties(
90                      JahiaPageDefinitionPropDB.getProperties(theTemplate.getID()));
91          }
92  
93          return theTemplate;
94      }
95  
96  
97      /**
98       * Create a new page template entry in the table. The passed page
99       * template will receive a new unique identification number.
100      *
101      * @param thePageTemplate The JahiaPageDefinition object to create.
102      * @throws JahiaException Throws this object if any error occurs while accessing the database.
103      */
104     public synchronized boolean insertPageTemplate(JahiaPageDefinition thePageTemplate)
105             throws JahiaException {
106         // Get the database connection.
107         Connection   dbConn = ConnectionDispenser.getConnection();
108         if (dbConn == null) {
109             throw new JahiaException(
110                     "Page template creation error on database access",
111                     "Cannot insert the new page template in the database, could not obtain a DB connection.",
112                     JahiaException.DATABASE_ERROR, JahiaException.CRITICAL_SEVERITY);
113         }
114 
115         PreparedStatement   statement = null;
116         boolean result = false;
117 
118         try {
119             // creates empty line
120             String   sqlQuery = "INSERT INTO jahia_pages_def(id_jahia_pages_def) VALUES(?)";
121 
122             statement = dbConn.prepareStatement(sqlQuery);
123             if (statement != null) {
124                 statement.setInt(1, thePageTemplate.getID());
125                 // executes the query
126                 statement.execute();
127 
128                 result = true;
129             }
130         } catch (SQLException   se) {
131 
132             String   errorMsg = "Error in insertPageTemplate : " + se.getMessage();
133             logger.error(errorMsg + " -> BAILING OUT");
134             throw new JahiaException("Cannot insert new page template in the database",
135                     errorMsg, JahiaException.DATABASE_ERROR, JahiaException.CRITICAL_SEVERITY);
136 
137         } finally {
138 
139             closeStatement(statement);
140         }
141 
142         if (result) {
143             // enters values with db_update_jahia_field
144             updatePageTemplate(thePageTemplate);
145         }
146 
147         // save properties
148         JahiaPageDefinitionPropDB.setProperties(thePageTemplate,
149                 thePageTemplate.getProperties());
150 
151         return result;
152     }
153 
154 
155     /**
156      * updates a page template
157      *
158      * @param thePageTemplate the JahiaPageDefinition to update
159      */
160     public synchronized void updatePageTemplate(JahiaPageDefinition thePageTemplate)
161             throws JahiaException {
162         Connection   dbConn = null;
163         Statement   statement = null;
164         try {
165             // checks that there is no null string
166             String   errorMsg = "";
167             if (thePageTemplate.getName().equals("")) {
168                 errorMsg =
169                         "Error in db_update_page_definition : page name value is an empty string";
170             }
171             if (!errorMsg.equals("")) {
172                 logger.error(errorMsg + " -> BAILING OUT");
173                 throw new JahiaException(
174                         "Cannot update pages template in the database : page name value is an empty string",
175                         errorMsg, JahiaException.DATABASE_ERROR,
176                         JahiaException.CRITICAL_SEVERITY);
177             }
178 
179             // composes the query
180             StringBuffer   sqlQuery = new StringBuffer  ("UPDATE jahia_pages_def SET ");
181             sqlQuery.append("jahiaid_jahia_pages_def = " + thePageTemplate.getJahiaID() + ",");
182             sqlQuery.append(
183                     "name_jahia_pages_def = '" + JahiaTools.quote(thePageTemplate.getName()) + "',");
184             sqlQuery.append(
185                     "sourcepath_jahia_pages_def = '" + JahiaTools.quote(
186                             thePageTemplate.getSourcePath()) + "', ");
187 
188 
189             if (thePageTemplate.isAvailable()) {
190                 sqlQuery.append("visible_jahia_pages_def = 1, ");
191             } else {
192                 sqlQuery.append("visible_jahia_pages_def = 0, ");
193             }
194 
195             // dummy values, no used anymore
196             sqlQuery.append("browsable_jahia_pages_def = 1 ,");
197             sqlQuery.append("warning_msg_jahia_pages_def = '', ");
198 
199             sqlQuery.append(
200                     "img_jahia_pages_def = '" + JahiaTools.quote(thePageTemplate.getImage()) + "' ");
201             sqlQuery.append("WHERE id_jahia_pages_def=" + thePageTemplate.getID());
202 
203             // executes the query
204             dbConn = ConnectionDispenser.getConnection();
205             statement = dbConn.createStatement();
206             if (statement != null) {
207                 //System.out.println(" query is " + sqlQuery.toString());
208                 statement.executeUpdate(sqlQuery.toString());
209             }
210         }
211                 // catches error if cannot execute update query
212         catch (SQLException   se) {
213 
214             String   errorMsg = "Error in db_update_page_definition : " + se.getMessage();
215             logger.error(errorMsg + " -> BAILING OUT");
216             throw new JahiaException("Cannot update page template in the database",
217                     errorMsg, JahiaException.DATABASE_ERROR, JahiaException.CRITICAL_SEVERITY);
218         } finally {
219 
220             closeStatement(statement);
221         }
222 
223         // save properties
224         JahiaPageDefinitionPropDB.setProperties(thePageTemplate,
225                 thePageTemplate.getProperties());
226 
227     }
228 
229 
230     /**
231      * deletes a page template
232      *
233      * @param templateID the page template ID to delete
234      */
235     public synchronized void deletePageTemplate(int templateID)
236             throws JahiaException {
237         Connection   dbConn = null;
238         PreparedStatement   statement = null;
239         try {
240 
241             // composes the query
242             String   sqlQuery = "DELETE from jahia_pages_def " +
243                     "WHERE id_jahia_pages_def=?";
244 
245             // executes the query
246             dbConn = ConnectionDispenser.getConnection();
247             statement = dbConn.prepareStatement(sqlQuery);
248             if (statement != null) {
249                 statement.setInt(1, templateID);
250                 statement.executeUpdate();
251             }
252         }
253                 // catches error if cannot execute update query
254         catch (SQLException   se) {
255             String   errorMsg = "Error in db_delete_page_definition : " + se.getMessage();
256             logger.error(errorMsg + " -> BAILING OUT");
257             throw new JahiaException("Cannot delete page template in the database",
258                     errorMsg, JahiaException.DATABASE_ERROR, JahiaException.CRITICAL_SEVERITY);
259         } finally {
260 
261             closeStatement(statement);
262         }
263 
264         // delete properties
265         JahiaPageDefinitionPropDB.removeProperties(templateID);
266 
267     }
268 
269 
270     /**
271      * Check if a page has a same location as the gived path
272      *
273      * @param siteID the site identification number
274      * @param path   the full path including the filename to the template file.
275      * @return (JahiaPageDefinition) a JahiaPageDefinition with same source path
276      *         or null if not found
277      */
278     public synchronized int getPageTemplateIDMatchingSourcePath(int siteID, String   path)
279             throws JahiaException {
280 
281         Connection   dbConn = null;
282         PreparedStatement   statement = null;
283         int templateID = -1;
284 
285         try {
286             // composes the query
287             String   sqlQuery = "SELECT id_jahia_pages_def FROM " +
288                     "jahia_pages_def WHERE sourcepath_jahia_pages_def = ? and jahiaid_jahia_pages_def=?";
289 
290             // executes the query
291             dbConn = ConnectionDispenser.getConnection();
292             statement = dbConn.prepareStatement(sqlQuery);
293             if (statement != null) {
294                 statement.setString(1, JahiaTools.quote(path));
295                 statement.setInt(2, siteID);
296                 ResultSet   rs = statement.executeQuery();
297                 if (rs != null) {
298                     if (rs.next()) {
299                         templateID = rs.getInt("id_jahia_pages_def");
300                     }
301                 }
302             }
303 
304         }
305                 // catches error if cannot execute update query
306         catch (SQLException   se) {
307             String   errorMsg = "Error in db_check_template_sourcepath : " + se.getMessage();
308             logger.error(errorMsg + " -> BAILING OUT");
309             throw new JahiaException("db_check_template_sourcepath",
310                     errorMsg, JahiaException.DATABASE_ERROR, JahiaException.ERROR_SEVERITY);
311         } finally {
312 
313             closeStatement(statement);
314         }
315 
316         return templateID;
317     }
318 
319 
320     private JahiaPageDefinition readTemplateFromResultSet(ResultSet   rs)
321             throws SQLException   {
322 
323         int ID = rs.getInt("id_jahia_pages_def");
324         int siteID = rs.getInt("jahiaid_jahia_pages_def");
325         String   name = rs.getString("name_jahia_pages_def");
326         String   sourcePath = rs.getString("sourcepath_jahia_pages_def");
327         int visible = rs.getInt("visible_jahia_pages_def");
328         String   img = rs.getString("img_jahia_pages_def");
329 
330         return new JahiaPageDefinition(ID, siteID, name, sourcePath, (visible == 1), img);
331     }
332 
333 
334     /**
335      * return a DOM document of all page def of a site
336      *
337      * @param siteID the site identification number
338      * @return JahiaDOMObject a DOM representation of this object
339      */
340     public JahiaDOMObject getPageDefsAsDOM(int siteID) throws JahiaException {
341 
342         Connection   dbConn = null;
343         PreparedStatement   statement = null;
344 
345         JahiaDBDOMObject dom = null;
346 
347         try {
348             String   sqlQuery = "SELECT * FROM jahia_pages_def where jahiaid_jahia_pages_def=?";
349 
350             dbConn = ConnectionDispenser.getConnection();
351             statement = dbConn.prepareStatement(sqlQuery);
352             if (statement != null) {
353                 statement.setInt(1, siteID);
354                 ResultSet   rs = statement.executeQuery();
355                 if (rs != null) {
356                     dom = new JahiaDBDOMObject();
357                     dom.addTable("jahia_pages_def", rs);
358                     return dom;
359                 }
360             }
361         } catch (SQLException   se) {
362             String   errorMsg = "Error in getPagesDefAsDOM(int siteID) : " + se.getMessage();
363             logger.error(errorMsg + " -> BAILING OUT");
364             throw new JahiaException("Cannot load pages def from the database",
365                     errorMsg, JahiaException.DATABASE_ERROR,
366                     JahiaException.CRITICAL_SEVERITY);
367         } finally {
368             closeStatement(statement);
369         }
370 
371         return dom;
372     }
373 
374 
375     //-------------------------------------------------------------------------
376     public synchronized int getNextID() {
377         int counter = -1;
378 
379         Connection   dbConn = ConnectionDispenser.getConnection();
380         if (dbConn != null) {
381             PreparedStatement   statement = null;
382 
383             try {
384                 String   query = "SELECT MAX(id_jahia_pages_def) as MaxID FROM jahia_pages_def";
385                 statement = dbConn.prepareStatement(query);
386                 if (statement != null) {
387                     ResultSet   rs = statement.executeQuery();
388                     if (rs != null) {
389                         if (rs.next()) {
390                             // get the highest used page ID
391                             counter = rs.getInt("MaxID");
392 
393                             // increment by one to get the next counter
394                             counter++;
395                         }
396                     }
397                 }
398             } catch (SQLException   ex) {
399                 logger.error(ex);
400             } finally {
401 
402                 closeStatement(statement);
403             }
404         }
405         return counter;
406     }
407 
408 
409     //-------------------------------------------------------------------------
410     // FH   2 May 2001
411     /**
412      * Return the amount of pages templates in the database.
413      *
414      * @return The amount of page templates.
415      */
416     public synchronized int getNbPageTemplates()
417             throws JahiaDatabaseException {
418 
419         return getNbPageTemplates(-1);
420     }
421 
422 
423     // NK   16 May 2001
424     /**
425      * Return the amount of pages templates in the database.
426      *
427      * @param siteID the site identification number
428      * @return The amount of page templates.
429      */
430     public synchronized int getNbPageTemplates(int siteID)
431             throws JahiaDatabaseException {
432         int counter = 0;
433 
434         Connection   dbConn = ConnectionDispenser.getConnection();
435         if (dbConn != null) {
436             String   query = "";
437             PreparedStatement   statement = null;
438 
439             try {
440                 query = "SELECT COUNT(id_jahia_pages_def) as nbItems FROM jahia_pages_def";
441 
442                 if (siteID != -1) {
443                     query += " WHERE jahiaid_jahia_pages_def=?";
444                 }
445 
446                 statement = dbConn.prepareStatement(query.toString());
447                 if (statement != null) {
448                     if (siteID != -1) {                    
449                         statement.setInt(1, siteID);
450                     }    
451                     ResultSet   rs = statement.executeQuery();
452 
453                     if (rs != null) {
454                         if (rs.next()) {
455                             counter = rs.getInt("nbItems");
456                         }
457                     }
458 
459                     // Result set not needed anymore.
460                     rs = null;
461                 }
462             } catch (SQLException   ex) {
463                 throw new JahiaDatabaseException("Database error.", ex,
464                         JahiaDatabaseException.ERROR_SEVERITY);
465             } finally {
466                 query = null;
467 
468                 closeStatement(statement);
469             }
470         }
471         return counter;
472     }
473 
474 
475     private void closeStatement(Statement   statement) {
476         // Close the opened statement
477         try {
478             if (statement != null) {
479                 statement.close();
480             }
481 
482         } catch (SQLException   sqlEx) {
483             logger.warn("Cannot close a statement", sqlEx);
484         }
485     }
486 
487     /**
488      * gets all the page template ids
489      *
490      * @return a Vector of page template ids
491      */
492     public Vector   getAllPageTemplateIDs()
493             throws JahiaException {
494         Connection   dbConn = null;
495         PreparedStatement   statement = null;
496         ResultSet   rs = null;
497         Vector   theList = new Vector  ();
498         try {
499             String   sqlQuery = "SELECT id_jahia_pages_def FROM jahia_pages_def ORDER BY name_jahia_pages_def";
500 
501             dbConn = ConnectionDispenser.getConnection();
502             statement = dbConn.prepareStatement(sqlQuery);
503             rs = statement.executeQuery();
504             while (rs.next()) {
505                 theList.add(new Integer  (rs.getInt("id_jahia_pages_def")));
506             }
507 
508         } catch (SQLException   se) {
509             String   errorMsg = "Error in db_get_all_page_definition_ids : " + se.getMessage();
510             logger.error(errorMsg + " -> BAILING OUT");
511             throw new JahiaException("Cannot load page data from the database",
512                     errorMsg, JahiaException.DATABASE_ERROR, JahiaException.CRITICAL_SEVERITY,
513                     se);
514         } finally {
515 
516             closeStatement(statement);
517         }
518         return theList;
519     }
520 
521 }
522
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags