CmsSqlManager


1   /*
2    * File   : $Source: /usr/local/cvs/opencms/src/org/opencms/db/generic/CmsSqlManager.java,v $
3    * Date   : $Date: 2006/03/27 14:52:54 $
4    * Version: $Revision: 1.65 $
5    *
6    * This library is part of OpenCms -
7    * the Open Source Content Mananagement System
8    *
9    * Copyright (c) 2005 Alkacon Software GmbH (http://www.alkacon.com)
10   *
11   * This library is free software; you can redistribute it and/or
12   * modify it under the terms of the GNU Lesser General Public
13   * License as published by the Free Software Foundation; either
14   * version 2.1 of the License, or (at your option) any later version.
15   *
16   * This library is distributed in the hope that it will be useful,
17   * but WITHOUT ANY WARRANTY; without even the implied warranty of
18   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
19   * Lesser General Public License for more details.
20   *
21   * For further information about Alkacon Software GmbH, please see the
22   * company website: http://www.alkacon.com
23   *
24   * For further information about OpenCms, please see the
25   * project website: http://www.opencms.org
26   * 
27   * You should have received a copy of the GNU Lesser General Public
28   * License along with this library; if not, write to the Free Software
29   * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
30   */
31  
32  package org.opencms.db.generic;
33  
34  import org.opencms.db.CmsDbContext;
35  import org.opencms.db.CmsDbPool;
36  import org.opencms.file.CmsDataAccessException;
37  import org.opencms.file.CmsProject;
38  import org.opencms.main.CmsLog;
39  import org.opencms.util.CmsStringUtil;
40  
41  import java.io.ByteArrayInputStream  ;
42  import java.io.Serializable  ;
43  import java.sql.Connection  ;
44  import java.sql.PreparedStatement  ;
45  import java.sql.ResultSet  ;
46  import java.sql.SQLException  ;
47  import java.sql.Statement  ;
48  import java.util.HashMap  ;
49  import java.util.Iterator  ;
50  import java.util.Map  ;
51  import java.util.Properties  ;
52  
53  import org.apache.commons.logging.Log;
54  
55  /**
56   * Generic (ANSI-SQL) implementation of the SQL manager.<p>
57   * 
58   * @author Thomas Weckert 
59   * 
60   * @version $Revision: 1.65 $
61   * 
62   * @since 6.0.0 
63   */
64  public class CmsSqlManager extends org.opencms.db.CmsSqlManager implements Serializable  , Cloneable   {
65  
66      /** A pattern being replaced in SQL queries to generate SQL queries to access online/offline tables. */
67      protected static final String   QUERY_PROJECT_SEARCH_PATTERN = "_${PROJECT}_";
68  
69      /** The log object for this class. */
70      private static final Log LOG = CmsLog.getLog(CmsSqlManager.class);
71  
72      /** The filename/path of the SQL query properties. */
73      private static final String   QUERY_PROPERTIES = "org/opencms/db/generic/query.properties";
74  
75      /** Serial version UID required for safe serialization. */
76      private static final long serialVersionUID = -5994026786008303964L;
77  
78      /** A map to cache queries with replaced search patterns. */
79      protected Map   m_cachedQueries;
80  
81      /** The type ID of the driver (vfs, user, project, workflow or backup) from where this SQL manager is referenced. */
82      protected int m_driverType;
83  
84      /** The pool URL to get connections from the JDBC driver manager, including DBCP's pool URL prefix. */
85      protected String   m_poolUrl;
86  
87      /** A map holding all SQL queries. */
88      protected Map   m_queries;
89  
90      /**
91       * Creates a new, empty SQL manager.<p>
92       */
93      public CmsSqlManager() {
94  
95          m_cachedQueries = new HashMap  ();
96          m_queries = new HashMap  ();
97          loadQueryProperties(QUERY_PROPERTIES);
98      }
99  
100     /**
101      * Creates a new instance of a SQL manager.<p>
102      * 
103      * @param classname the classname of the SQL manager
104      * 
105      * @return a new instance of the SQL manager
106      */
107     public static org.opencms.db.generic.CmsSqlManager getInstance(String   classname) {
108 
109         org.opencms.db.generic.CmsSqlManager sqlManager;
110 
111         try {
112             Object   objectInstance = Class.forName(classname).newInstance();
113             sqlManager = (org.opencms.db.generic.CmsSqlManager)objectInstance;
114         } catch (Throwable   t) {
115             LOG.error(Messages.get().getBundle().key(Messages.LOG_SQL_MANAGER_INIT_FAILED_1, classname), t);
116             sqlManager = null;
117         }
118 
119         if (CmsLog.INIT.isInfoEnabled()) {
120             CmsLog.INIT.info(Messages.get().getBundle().key(Messages.INIT_DRIVER_SQL_MANAGER_1, classname));
121         }
122 
123         return sqlManager;
124 
125     }
126 
127     /**
128      * Replaces the project search pattern in SQL queries by the pattern _ONLINE_ or _OFFLINE_ depending on the 
129      * specified project ID.<p> 
130      * 
131      * @param projectId the ID of the current project
132      * @param query the SQL query
133      * @return String the SQL query with the table key search pattern replaced
134      */
135     protected static String   replaceProjectPattern(int projectId, String   query) {
136 
137         // make the statement project dependent
138         String   replacePattern = (projectId == CmsProject.ONLINE_PROJECT_ID || projectId < 0) ? "_ONLINE_" : "_OFFLINE_";
139         query = CmsStringUtil.substitute(query, QUERY_PROJECT_SEARCH_PATTERN, replacePattern);
140 
141         return query;
142     }
143 
144     /**
145      * Attemts to close the connection, statement and result set after a statement has been executed.<p>
146      * 
147      * @param dbc the current database context
148      * @param con the JDBC connection
149      * @param stmnt the statement
150      * @param res the result set
151      */
152     public void closeAll(CmsDbContext dbc, Connection   con, Statement   stmnt, ResultSet   res) {
153 
154         // NOTE: we have to close Connections/Statements that way, because a dbcp PoolablePreparedStatement
155         // is not a DelegatedStatement; for that reason its not removed from the trace of the connection when it is closed.
156         // So, the connection tries to close it again when the connection is closed itself; 
157         // as a result there is an error that forces the connection to be destroyed and not pooled
158 
159         if (dbc == null) {
160             LOG.error(Messages.get().getBundle().key(Messages.LOG_NULL_DB_CONTEXT_0));
161         }
162 
163         try {
164             // first, close the connection and (eventually) implicitly all assigned statements and result sets
165             if (con != null && !con.isClosed()) {
166                 con.close();
167             }
168         } catch (SQLException   e) {
169             // intentionally left blank
170         } finally {
171             con = null;
172         }
173 
174         try {
175             // close the statement and (normally) implicitly all assigned result sets
176             if (stmnt != null) {
177                 stmnt.close();
178             }
179         } catch (SQLException   e) {
180             // intentionally left blank
181         } finally {
182             stmnt = null;
183         }
184 
185         try {
186             // close the result set          
187             if (res != null) {
188                 res.close();
189             }
190         } catch (SQLException   e) {
191             // intentionally left blank
192         } finally {
193             res = null;
194         }
195 
196     }
197 
198     /**
199      * Retrieves the value of the designated column in the current row of this ResultSet object as 
200      * a byte array in the Java programming language.<p>
201      * 
202      * The bytes represent the raw values returned by the driver. Overwrite this method if another 
203      * database server requires a different handling of byte attributes in tables.
204      * 
205      * @param res the result set
206      * @param attributeName the name of the table attribute
207      * @return byte[] the column value; if the value is SQL NULL, the value returned is null 
208      * @throws SQLException if a database access error occurs
209      */
210     public byte[] getBytes(ResultSet   res, String   attributeName) throws SQLException   {
211 
212         return res.getBytes(attributeName);
213     }
214 
215     /**
216      * Returns a JDBC connection from the connection pool.<p>
217      * 
218      * Use this method to get a connection for reading/writing project independent data.<p>
219      * 
220      * @param dbc the current database context
221      * 
222      * @return a JDBC connection
223      * @throws SQLException if the project id is not supported
224      */
225     public Connection   getConnection(CmsDbContext dbc) throws SQLException   {
226 
227         return getConnection(dbc, 0);
228     }
229 
230     /**
231      * Returns a JDBC connection from the connection pool specified by the given CmsProject id.<p>
232      * 
233      * Use this method to get a connection for reading/writing data either in online or offline projects
234      * such as files, folders.<p>
235      * 
236      * @param dbc the current database context
237      * @param projectId the id of a project (to distinguish between online / offline tables)
238      * 
239      * @return a JDBC connection
240      * @throws SQLException if a database access error occurs
241      */
242     public Connection   getConnection(CmsDbContext dbc, int projectId) throws SQLException   {
243 
244         if (dbc == null) {
245             LOG.error(Messages.get().getBundle().key(Messages.LOG_NULL_DB_CONTEXT_0));
246         }
247         return getConnection(projectId);
248     }
249 
250     /**
251      * Returns a PreparedStatement for a JDBC connection specified by the key of a SQL query
252      * and the CmsProject.<p>
253      * 
254      * @param con the JDBC connection
255      * @param project the specified CmsProject
256      * @param queryKey the key of the SQL query
257      * @return PreparedStatement a new PreparedStatement containing the pre-compiled SQL statement 
258      * @throws SQLException if a database access error occurs
259      */
260     public PreparedStatement   getPreparedStatement(Connection   con, CmsProject project, String   queryKey)
261     throws SQLException   {
262 
263         return getPreparedStatement(con, project.getId(), queryKey);
264     }
265 
266     /**
267      * Returns a PreparedStatement for a JDBC connection specified by the key of a SQL query
268      * and the project-ID.<p>
269      * 
270      * @param con the JDBC connection
271      * @param projectId the ID of the specified CmsProject
272      * @param queryKey the key of the SQL query
273      * @return PreparedStatement a new PreparedStatement containing the pre-compiled SQL statement 
274      * @throws SQLException if a database access error occurs
275      */
276     public PreparedStatement   getPreparedStatement(Connection   con, int projectId, String   queryKey) throws SQLException   {
277 
278         String   rawSql = readQuery(projectId, queryKey);
279         return getPreparedStatementForSql(con, rawSql);
280     }
281 
282     /**
283      * Returns a PreparedStatement for a JDBC connection specified by the key of a SQL query.<p>
284      * 
285      * @param con the JDBC connection
286      * @param queryKey the key of the SQL query
287      * @return PreparedStatement a new PreparedStatement containing the pre-compiled SQL statement 
288      * @throws SQLException if a database access error occurs
289      */
290     public PreparedStatement   getPreparedStatement(Connection   con, String   queryKey) throws SQLException   {
291 
292         String   rawSql = readQuery(0, queryKey);
293         return getPreparedStatementForSql(con, rawSql);
294     }
295 
296     /**
297      * Returns a PreparedStatement for a JDBC connection specified by the SQL query.<p>
298      * 
299      * @param con the JDBC connection
300      * @param query the SQL query
301      * @return PreparedStatement a new PreparedStatement containing the pre-compiled SQL statement 
302      * @throws SQLException if a database access error occurs
303      */
304     public PreparedStatement   getPreparedStatementForSql(Connection   con, String   query) throws SQLException   {
305 
306         // unfortunately, this wrapper is essential, because some JDBC driver 
307         // implementations don't accept the delegated objects of DBCP's connection pool. 
308         return con.prepareStatement(query);
309     }
310 
311     /**
312      * Initializes this SQL manager.<p>
313      * 
314      * @param driverType the type ID of the driver (vfs,user,project,workflow or backup) from where this SQL manager is referenced
315      * @param poolUrl the pool URL to get connections from the JDBC driver manager
316      */
317     public void init(int driverType, String   poolUrl) {
318 
319         if (!poolUrl.startsWith(CmsDbPool.DBCP_JDBC_URL_PREFIX)) {
320             poolUrl = CmsDbPool.DBCP_JDBC_URL_PREFIX + poolUrl;
321         }
322 
323         m_driverType = driverType;
324         m_poolUrl = poolUrl;
325 
326     }
327 
328     /**
329      * Generates a new primary key for a given database table name.<p>
330      * 
331      * This method makes only sense for old-style tables where the primary key is NOT a CmsUUID!
332      * 
333      * @param tableName the table for which a new primary key should be generated.
334      * @return int the new primary key
335      * @throws CmsDataAccessException if an error occurs
336      */
337     public int nextId(String   tableName) throws CmsDataAccessException {
338 
339         return org.opencms.db.CmsDbUtil.nextId(m_poolUrl, tableName);
340     }
341 
342     /**
343      * Searches for the SQL query with the specified key and CmsProject.<p>
344      * 
345      * @param project the specified CmsProject
346      * @param queryKey the key of the SQL query
347      * @return the the SQL query in this property list with the specified key
348      */
349     public String   readQuery(CmsProject project, String   queryKey) {
350 
351         return readQuery(project.getId(), queryKey);
352     }
353 
354     /**
355      * Searches for the SQL query with the specified key and project-ID.<p>
356      * 
357      * For projectIds &ne; 0, the pattern {@link #QUERY_PROJECT_SEARCH_PATTERN} in table names of queries is 
358      * replaced with "_ONLINE_" or "_OFFLINE_" to choose the right database 
359      * tables for SQL queries that are project dependent!
360      * 
361      * @param projectId the ID of the specified CmsProject
362      * @param queryKey the key of the SQL query
363      * @return the the SQL query in this property list with the specified key
364      */
365     public String   readQuery(int projectId, String   queryKey) {
366 
367         String   key;
368         if (projectId != 0) {
369             // id 0 is special, please see below
370             StringBuffer   buffer = new StringBuffer  (128);
371             buffer.append(queryKey);
372             if (projectId == CmsProject.ONLINE_PROJECT_ID || projectId < 0) {
373                 buffer.append("_ONLINE");
374             } else {
375                 buffer.append("_OFFLINE");
376             }
377             key = buffer.toString();
378         } else {
379             key = queryKey;
380         }
381 
382         // look up the query in the cache
383         String   query = (String  )m_cachedQueries.get(key);
384 
385         if (query == null) {
386             // the query has not been cached yet
387             // get the SQL statement from the properties hash
388             query = readQuery(queryKey);
389 
390             // replace control chars.
391             query = CmsStringUtil.substitute(query, "\t", " ");
392             query = CmsStringUtil.substitute(query, "\n", " ");
393 
394             if (projectId != 0) {
395                 // a project ID = 0 is an internal indicator that a project-independent 
396                 // query was requested - further regex operations are not required then
397                 query = CmsSqlManager.replaceProjectPattern(projectId, query);
398             }
399 
400             // to minimize costs, all statements with replaced expressions are cached in a map
401             m_cachedQueries.put(key, query);
402         }
403 
404         return query;
405     }
406 
407     /**
408      * Searches for the SQL query with the specified key.<p>
409      * 
410      * @param queryKey the SQL query key
411      * @return the the SQL query in this property list with the specified key
412      */
413     public String   readQuery(String   queryKey) {
414 
415         String   value = (String  )m_queries.get(queryKey);
416         if (value == null) {
417             if (LOG.isErrorEnabled()) {
418                 LOG.error(Messages.get().getBundle().key(Messages.LOG_QUERY_NOT_FOUND_1, queryKey));
419             }
420         }
421         return value;
422     }
423 
424     /**
425      * Sets the designated parameter to the given Java array of bytes.<p>
426      * 
427      * The driver converts this to an SQL VARBINARY or LONGVARBINARY (depending on the argument's 
428      * size relative to the driver's limits on VARBINARY values) when it sends it to the database. 
429      * 
430      * @param statement the PreparedStatement where the content is set
431      * @param pos the first parameter is 1, the second is 2, ...
432      * @param content the parameter value 
433      * @throws SQLException if a database access error occurs
434      */
435     public void setBytes(PreparedStatement   statement, int pos, byte[] content) throws SQLException   {
436 
437         if (content.length < 2000) {
438             statement.setBytes(pos, content);
439         } else {
440             statement.setBinaryStream(pos, new ByteArrayInputStream  (content), content.length);
441         }
442     }
443 
444     /**
445      * Replaces null or empty Strings with a String with one space character <code>" "</code>.<p>
446      * 
447      * @param value the string to validate
448      * @return the validate string or a String with one space character if the validated string is null or empty
449      */
450     public String   validateEmpty(String   value) {
451 
452         if (CmsStringUtil.isNotEmpty(value)) {
453             return value;
454         }
455 
456         return " ";
457     }
458 
459     /**
460      * @see java.lang.Object#finalize()
461      */
462     protected void finalize() throws Throwable   {
463 
464         try {
465             if (m_cachedQueries != null) {
466                 m_cachedQueries.clear();
467             }
468 
469             if (m_queries != null) {
470                 m_queries.clear();
471             }
472         } catch (Throwable   t) {
473             // intentionally left blank
474         } finally {
475             m_cachedQueries = null;
476             m_queries = null;
477             m_poolUrl = null;
478         }
479 
480         super.finalize();
481     }
482 
483     /**
484      * Returns a JDBC connection from the connection pool specified by the given project ID.<p>
485      * 
486      * The project ID is (usually) the ID of the current project.<p>
487      * 
488      * Use this method to get a connection for reading/writing data either in online or offline projects
489      * such as files, folders.<p>
490      * 
491      * @param projectId the ID of a Cms project (e.g. the current project from the request context)
492      * @return a JDBC connection from the pool specified by the project-ID 
493      * @throws SQLException if a database access error occurs
494      */
495     protected Connection   getConnection(int projectId) throws SQLException   {
496 
497         // the specified project ID is not evaluated in this implementation.
498         // extensions of this object might evaluate the project ID to return
499         // different connections...        
500 
501         if (projectId < 0) {
502             throw new SQLException  (Messages.get().getBundle().key(
503                 Messages.ERR_JDBC_CONN_INVALID_PROJECT_ID_1,
504                 new Integer  (projectId)));
505         }
506 
507         // match the ID to a JDBC pool URL of the OpenCms JDBC pools {online|offline|backup}
508         return getConnectionByUrl(m_poolUrl);
509     }
510 
511     /**
512      * Loads a Java properties hash containing SQL queries.<p>
513      * 
514      * @param propertyFilename the package/filename of the properties hash
515      */
516     protected void loadQueryProperties(String   propertyFilename) {
517 
518         Properties   properties = new Properties  ();
519 
520         try {
521             properties.load(getClass().getClassLoader().getResourceAsStream(propertyFilename));
522             m_queries.putAll(properties);
523             replaceQuerySearchPatterns();
524         } catch (Throwable   t) {
525             if (LOG.isErrorEnabled()) {
526                 LOG.error(
527                     Messages.get().getBundle().key(Messages.LOG_LOAD_QUERY_PROP_FILE_FAILED_1, propertyFilename),
528                     t);
529             }
530 
531             properties = null;
532         }
533     }
534 
535     /**
536      * Replaces patterns ${XXX} by another property value, if XXX is a property key with a value.<p>
537      */
538     protected synchronized void replaceQuerySearchPatterns() {
539 
540         String   currentKey = null;
541         String   currentValue = null;
542         int startIndex = 0;
543         int endIndex = 0;
544         int lastIndex = 0;
545 
546         Iterator   allKeys = m_queries.keySet().iterator();
547         while (allKeys.hasNext()) {
548             currentKey = (String  )allKeys.next();
549             currentValue = (String  )m_queries.get(currentKey);
550             startIndex = 0;
551             endIndex = 0;
552             lastIndex = 0;
553 
554             while ((startIndex = currentValue.indexOf("${", lastIndex)) != -1) {
555                 endIndex = currentValue.indexOf('}', startIndex);
556                 if (endIndex != -1 && !currentValue.startsWith(QUERY_PROJECT_SEARCH_PATTERN, startIndex - 1)) {
557 
558                     String   replaceKey = currentValue.substring(startIndex + 2, endIndex);
559                     String   searchPattern = currentValue.substring(startIndex, endIndex + 1);
560                     String   replacePattern = this.readQuery(replaceKey);
561 
562                     if (replacePattern != null) {
563                         currentValue = CmsStringUtil.substitute(currentValue, searchPattern, replacePattern);
564                     }
565                 }
566 
567                 lastIndex = endIndex + 2;
568             }
569             m_queries.put(currentKey, currentValue);
570         }
571     }
572 }
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags