KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > infoglue > cms > util > workflow > InfoGlueJDBCPropertySet


1 /* ===============================================================================
2 *
3 * Part of the InfoGlue Content Management Platform (www.infoglue.org)
4 *
5 * ===============================================================================
6 *
7 * Copyright (C)
8 *
9 * This program is free software; you can redistribute it and/or modify it under
10 * the terms of the GNU General Public License version 2, as published by the
11 * Free Software Foundation. See the file LICENSE.html for more information.
12 *
13 * This program is distributed in the hope that it will be useful, but WITHOUT
14 * ANY WARRANTY, including the implied warranty of MERCHANTABILITY or FITNESS
15 * FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 *
17 * You should have received a copy of the GNU General Public License along with
18 * this program; if not, write to the Free Software Foundation, Inc. / 59 Temple
19 * Place, Suite 330 / Boston, MA 02111-1307 / USA.
20 *
21 * ===============================================================================
22 */

23
24 package org.infoglue.cms.util.workflow;
25
26 import java.sql.Connection JavaDoc;
27 import java.sql.DriverManager JavaDoc;
28 import java.sql.PreparedStatement JavaDoc;
29 import java.sql.ResultSet JavaDoc;
30 import java.sql.SQLException JavaDoc;
31 import java.sql.Timestamp JavaDoc;
32 import java.sql.Types JavaDoc;
33 import java.util.ArrayList JavaDoc;
34 import java.util.Collection JavaDoc;
35 import java.util.Date JavaDoc;
36 import java.util.Map JavaDoc;
37
38 import org.apache.commons.dbcp.ConnectionFactory;
39 import org.apache.commons.dbcp.DriverManagerConnectionFactory;
40 import org.apache.commons.dbcp.PoolableConnectionFactory;
41 import org.apache.commons.dbcp.PoolingDriver;
42 import org.apache.commons.pool.ObjectPool;
43 import org.apache.commons.pool.impl.GenericObjectPool;
44 import org.apache.log4j.Logger;
45
46 import com.opensymphony.module.propertyset.InvalidPropertyTypeException;
47 import com.opensymphony.module.propertyset.PropertyException;
48 import com.opensymphony.module.propertyset.PropertySet;
49 import com.opensymphony.module.propertyset.database.JDBCPropertySet;
50 import com.opensymphony.util.Data;
51
52
53
54 /**
55  * This is an implementation of a property set manager for JDBC. It relies on
56  * one table, called "os_propertyset" that has four columns: "type" (integer),
57  * "keyValue" (string), "globalKey" (string), and "value" (string). This is not
58  * likely to be enough for people who store BLOBS as properties. Of course,
59  * those people need to get a life.
60  * <p>
61  *
62  * For Postgres(?):<br>
63  * CREATE TABLE OS_PROPERTYENTRY (GLOBAL_KEY varchar(255), ITEM_KEY varchar(255), ITEM_TYPE smallint, STRING_VALUE varchar(255), DATE_VALUE timestamp, DATA_VALUE oid, FLOAT_VALUE float8, NUMBER_VALUE numeric, primary key (GLOBAL_KEY, ITEM_KEY));
64  * <p>
65  *
66  * For Oracle (Thanks to Michael G. Slack!):<br>
67  * CREATE TABLE OS_PROPERTYENTRY (GLOBAL_KEY varchar(255), ITEM_KEY varchar(255), ITEM_TYPE smallint, STRING_VALUE varchar(255), DATE_VALUE date, DATA_VALUE long raw, FLOAT_VALUE float, NUMBER_VALUE numeric, primary key (GLOBAL_KEY, ITEM_KEY));
68  * <p>
69  *
70  * Other databases may require small tweaks to the table creation scripts!
71  *
72  * <p>
73  *
74  * <b>Required Args</b>
75  * <ul>
76  * <li><b>globalKey</b> - the globalKey to use with this PropertySet</li>
77  * </ul>
78  * <p>
79  *
80  * <b>Required Configuration</b>
81  * <ul>
82  * <li><b>datasource</b> - JNDI path for the DataSource</li>
83  * <li><b>table.name</b> - the table name</li>
84  * <li><b>col.globalKey</b> - column name for the globalKey</li>
85  * <li><b>col.itemKey</b> - column name for the itemKey</li>
86  * <li><b>col.itemType</b> - column name for the itemType</li>
87  * <li><b>col.string</b> - column name for the string value</li>
88  * <li><b>col.date</b> - column name for the date value</li>
89  * <li><b>col.data</b> - column name for the data value</li>
90  * <li><b>col.float</b> - column name for the float value</li>
91  * <li><b>col.number</b> - column name for the number value</li>
92  * </ul>
93  *
94  * @version $Revision: 1.5 $
95  * @author <a HREF="mailto:epesh@hotmail.com">Joseph B. Ottinger</a>
96  * @author <a HREF="mailto:plightbo@hotmail.com">Pat Lightbody</a>
97  */

98 public class InfoGlueJDBCPropertySet extends JDBCPropertySet
99 {
100     private final static Logger logger = Logger.getLogger(InfoGlueJDBCPropertySet.class.getName());
101
102     private static ObjectPool connectionPool;
103     private static ConnectionFactory connectionFactory;
104     private static PoolableConnectionFactory poolableConnectionFactory;
105     private static PoolingDriver driver;
106
107     // config
108
//DataSource ds;
109
String JavaDoc colData;
110     String JavaDoc colDate;
111     String JavaDoc colFloat;
112     String JavaDoc colGlobalKey;
113     String JavaDoc colItemKey;
114     String JavaDoc colItemType;
115     String JavaDoc colNumber;
116     String JavaDoc colString;
117     
118     private String JavaDoc userName;
119     private String JavaDoc password;
120     private String JavaDoc driverClassName;
121     private String JavaDoc url;
122
123     // args
124
String JavaDoc globalKey;
125     String JavaDoc tableName;
126
127     //~ Methods ////////////////////////////////////////////////////////////////
128

129     public Collection JavaDoc getKeys(String JavaDoc prefix, int type) throws PropertyException {
130         if (prefix == null) {
131             prefix = "";
132         }
133
134         Connection JavaDoc conn = null;
135
136         try {
137             conn = getConnection();
138
139             PreparedStatement JavaDoc ps = null;
140             String JavaDoc sql = "SELECT " + colItemKey + " FROM " + tableName + " WHERE " + colItemKey + " LIKE ? AND " + colGlobalKey + " = ?";
141
142             if (type == 0) {
143                 ps = conn.prepareStatement(sql);
144                 ps.setString(1, prefix + "%");
145                 ps.setString(2, globalKey);
146             } else {
147                 sql = sql + " AND " + colItemType + " = ?";
148                 ps = conn.prepareStatement(sql);
149                 ps.setString(1, prefix + "%");
150                 ps.setString(2, globalKey);
151                 ps.setInt(3, type);
152             }
153
154             ArrayList JavaDoc list = new ArrayList JavaDoc();
155             ResultSet JavaDoc rs = ps.executeQuery();
156
157             while (rs.next()) {
158                 list.add(rs.getString(colItemKey));
159             }
160
161             rs.close();
162             ps.close();
163
164             return list;
165         } catch (SQLException JavaDoc e) {
166             throw new PropertyException(e.getMessage());
167         } finally {
168             closeConnection(conn);
169         }
170     }
171
172     public int getType(String JavaDoc key) throws PropertyException {
173         Connection JavaDoc conn = null;
174
175         try {
176             conn = getConnection();
177
178             String JavaDoc sql = "SELECT " + colItemType + " FROM " + tableName + " WHERE " + colGlobalKey + " = ? AND " + colItemKey + " = ?";
179             PreparedStatement JavaDoc ps = conn.prepareStatement(sql);
180             ps.setString(1, globalKey);
181             ps.setString(2, key);
182
183             ResultSet JavaDoc rs = ps.executeQuery();
184             int type = 0;
185
186             if (rs.next()) {
187                 type = rs.getInt(colItemType);
188             }
189
190             rs.close();
191             ps.close();
192
193             return type;
194         } catch (SQLException JavaDoc e) {
195             throw new PropertyException(e.getMessage());
196         } finally {
197             closeConnection(conn);
198         }
199     }
200
201     public boolean exists(String JavaDoc key) throws PropertyException {
202         return getType(key) != 0;
203     }
204
205     public void init(Map JavaDoc config, Map JavaDoc args) {
206         // args
207
globalKey = (String JavaDoc) args.get("globalKey");
208         
209         //super.init(Map config, Map args);
210
/*
211         // config
212         try {
213             ds = (DataSource) EJBUtils.lookup((String) config.get("datasource"));
214         } catch (Exception e) {
215             log.fatal("Could not get DataSource", e);
216         }
217         */

218
219         tableName = (String JavaDoc) config.get("table.name");
220         colGlobalKey = (String JavaDoc) config.get("col.globalKey");
221         colItemKey = (String JavaDoc) config.get("col.itemKey");
222         colItemType = (String JavaDoc) config.get("col.itemType");
223         colString = (String JavaDoc) config.get("col.string");
224         colDate = (String JavaDoc) config.get("col.date");
225         colData = (String JavaDoc) config.get("col.data");
226         colFloat = (String JavaDoc) config.get("col.float");
227         colNumber = (String JavaDoc) config.get("col.number");
228
229         this.userName = (String JavaDoc) config.get("username");
230         this.password = (String JavaDoc) config.get("password");
231         this.driverClassName = (String JavaDoc) config.get("driverClassName");
232         this.url = (String JavaDoc) config.get("url");
233     }
234     
235     public void remove(String JavaDoc key) throws PropertyException {
236         Connection JavaDoc conn = null;
237
238         try {
239             conn = getConnection();
240
241             String JavaDoc sql = "DELETE FROM " + tableName + " WHERE " + colGlobalKey + " = ? AND " + colItemKey + " = ?";
242             PreparedStatement JavaDoc ps = conn.prepareStatement(sql);
243             ps.setString(1, globalKey);
244             ps.setString(2, key);
245             ps.executeUpdate();
246             ps.close();
247         } catch (SQLException JavaDoc e) {
248             throw new PropertyException(e.getMessage());
249         } finally {
250             closeConnection(conn);
251         }
252     }
253
254     protected void setImpl(int type, String JavaDoc key, Object JavaDoc value) throws PropertyException {
255         if (value == null) {
256             throw new PropertyException("JDBCPropertySet does not allow for null values to be stored");
257         }
258
259         Connection JavaDoc conn = null;
260
261         try {
262             conn = getConnection();
263
264             String JavaDoc sql = "UPDATE " + tableName + " SET " + colString + " = ?, " + colDate + " = ?, " + colData + " = ?, " + colFloat + " = ?, " + colNumber + " = ?, " + colItemType + " = ? " + " WHERE " + colGlobalKey + " = ? AND " + colItemKey + " = ?";
265             PreparedStatement JavaDoc ps = conn.prepareStatement(sql);
266             setValues(ps, type, key, value);
267
268             int rows = ps.executeUpdate();
269             ps.close();
270
271             if (rows != 1) {
272                 // ok, this is a new value, insert it
273
sql = "INSERT INTO " + tableName + " (" + colString + ", " + colDate + ", " + colData + ", " + colFloat + ", " + colNumber + ", " + colItemType + ", " + colGlobalKey + ", " + colItemKey + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
274                 ps = conn.prepareStatement(sql);
275                 setValues(ps, type, key, value);
276                 ps.executeUpdate();
277                 ps.close();
278             }
279         } catch (SQLException JavaDoc e) {
280             throw new PropertyException(e.getMessage());
281         } finally {
282             closeConnection(conn);
283         }
284     }
285
286     protected Object JavaDoc get(int type, String JavaDoc key) throws PropertyException {
287         String JavaDoc sql = "SELECT " + colItemType + ", " + colString + ", " + colDate + ", " + colData + ", " + colFloat + ", " + colNumber + " FROM " + tableName + " WHERE " + colItemKey + " = ? AND " + colGlobalKey + " = ?";
288
289         Object JavaDoc o = null;
290         Connection JavaDoc conn = null;
291
292         try {
293             conn = getConnection();
294
295             PreparedStatement JavaDoc ps = conn.prepareStatement(sql);
296             ps.setString(1, key);
297             ps.setString(2, globalKey);
298
299             int propertyType;
300             ResultSet JavaDoc rs = ps.executeQuery();
301
302             if (rs.next()) {
303                 propertyType = rs.getInt(colItemType);
304
305                 if (propertyType != type) {
306                     throw new InvalidPropertyTypeException();
307                 }
308
309                 switch (type) {
310                 case PropertySet.BOOLEAN:
311
312                     int boolVal = rs.getInt(colNumber);
313                     o = new Boolean JavaDoc(boolVal == 1);
314
315                     break;
316
317                 case PropertySet.DATA:
318                     o = rs.getBytes(colData);
319
320                     break;
321
322                 case PropertySet.DATE:
323                     o = rs.getTimestamp(colDate);
324
325                     break;
326
327                 case PropertySet.DOUBLE:
328                     o = new Double JavaDoc(rs.getDouble(colFloat));
329
330                     break;
331
332                 case PropertySet.INT:
333                     o = new Integer JavaDoc(rs.getInt(colNumber));
334
335                     break;
336
337                 case PropertySet.LONG:
338                     o = new Long JavaDoc(rs.getLong(colNumber));
339
340                     break;
341
342                 case PropertySet.STRING:
343                     o = rs.getString(colString);
344
345                     break;
346
347                 default:
348                     throw new InvalidPropertyTypeException("JDBCPropertySet doesn't support this type yet.");
349                 }
350             }
351
352             rs.close();
353             ps.close();
354         } catch (SQLException JavaDoc e) {
355             throw new PropertyException(e.getMessage());
356         } catch (NumberFormatException JavaDoc e) {
357             throw new PropertyException(e.getMessage());
358         } finally {
359             closeConnection(conn);
360         }
361
362         return o;
363     }
364
365     private void setValues(PreparedStatement JavaDoc ps, int type, String JavaDoc key, Object JavaDoc value) throws SQLException JavaDoc, PropertyException {
366         // Patched by Edson Richter for MS SQL Server JDBC Support!
367
String JavaDoc driverName;
368
369         try {
370             driverName = ps.getConnection().getMetaData().getDriverName().toUpperCase();
371         } catch (Exception JavaDoc e) {
372             driverName = "";
373         }
374
375         ps.setNull(1, Types.VARCHAR);
376         ps.setNull(2, Types.TIMESTAMP);
377
378         // Patched by Edson Richter for MS SQL Server JDBC Support!
379
// Oracle support suggestion also Michael G. Slack
380
if ((driverName.indexOf("SQLSERVER") >= 0) || (driverName.indexOf("ORACLE") >= 0)) {
381             ps.setNull(3, Types.BINARY);
382         } else {
383             ps.setNull(3, Types.BLOB);
384         }
385
386         ps.setNull(4, Types.FLOAT);
387         ps.setNull(5, Types.NUMERIC);
388         ps.setInt(6, type);
389         ps.setString(7, globalKey);
390         ps.setString(8, key);
391
392         switch (type) {
393         case PropertySet.BOOLEAN:
394
395             Boolean JavaDoc boolVal = (Boolean JavaDoc) value;
396             ps.setInt(5, boolVal.booleanValue() ? 1 : 0);
397
398             break;
399
400         case PropertySet.DATA:
401
402             Data data = (Data) value;
403             ps.setBytes(3, data.getBytes());
404
405             break;
406
407         case PropertySet.DATE:
408
409             Date JavaDoc date = (Date JavaDoc) value;
410             ps.setTimestamp(2, new Timestamp JavaDoc(date.getTime()));
411
412             break;
413
414         case PropertySet.DOUBLE:
415
416             Double JavaDoc d = (Double JavaDoc) value;
417             ps.setDouble(4, d.doubleValue());
418
419             break;
420
421         case PropertySet.INT:
422
423             Integer JavaDoc i = (Integer JavaDoc) value;
424             ps.setInt(5, i.intValue());
425
426             break;
427
428         case PropertySet.LONG:
429
430             Long JavaDoc l = (Long JavaDoc) value;
431             ps.setLong(5, l.longValue());
432
433             break;
434
435         case PropertySet.STRING:
436             ps.setString(1, (String JavaDoc) value);
437
438             break;
439
440         default:
441             throw new PropertyException("This type isn't supported!");
442         }
443     }
444
445     protected Connection JavaDoc getConnection() throws SQLException JavaDoc
446     {
447         Connection JavaDoc conn = null;
448         
449         try
450         {
451             if(connectionPool == null)
452             {
453                 logger.info("Establishing connection to database '" + this.url + "'");
454                 
455                 try
456                 {
457                     setupDriver(url, this.userName, this.password);
458                 }
459                 catch (Exception JavaDoc e)
460                 {
461                     e.printStackTrace();
462                 }
463                 logger.info("Done.");
464             }
465
466             conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:infoGlueJDBCPropertySet");
467             logger.info("Fetched connection from pool...");
468             printDriverStats();
469             
470             //System.out.println("Creating connection.");
471
//conn = DriverManager.getConnection(url, this.userName, this.password);
472
}
473         catch (Exception JavaDoc ex)
474         {
475             ex.printStackTrace();
476         }
477         
478         return conn;
479     }
480     
481     private void closeConnection(Connection JavaDoc conn) {
482         try {
483             if ((conn != null) && !conn.isClosed()) {
484                 conn.close();
485             }
486         } catch (SQLException JavaDoc e) {
487            logger.error("Could not close connection");
488         }
489     }
490     
491     public void setupDriver(String JavaDoc connectURI, String JavaDoc userName, String JavaDoc password) throws Exception JavaDoc
492     {
493         String JavaDoc validationQuery = "SELECT count(*) FROM " + tableName;
494         
495         logger.info("Setting up driver.");
496         Class.forName(this.driverClassName).newInstance();
497
498         connectionPool = new GenericObjectPool(null);
499         connectionFactory = new DriverManagerConnectionFactory(connectURI, userName, password);
500         poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory,connectionPool,null,validationQuery,false,true);
501
502         Class.forName("org.apache.commons.dbcp.PoolingDriver");
503         driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
504
505         driver.registerPool("infoGlueJDBCPropertySet",connectionPool);
506     }
507
508     public void printDriverStats() throws Exception JavaDoc
509     {
510         PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
511         ObjectPool connectionPool = driver.getConnectionPool("infoGlueJDBCPropertySet");
512         
513         logger.info("NumActive: " + connectionPool.getNumActive());
514         logger.info("NumIdle: " + connectionPool.getNumIdle());
515     }
516
517     public void shutdownDriver() throws Exception JavaDoc
518     {
519         PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
520         driver.closePool("infoGlueJDBCPropertySet");
521     }
522  
523 }
Popular Tags