|
1
23
24 package org.infoglue.cms.util.workflow;
25
26 import java.sql.Connection  ;
27 import java.sql.DriverManager ;
28 import java.sql.PreparedStatement ;
29 import java.sql.ResultSet ;
30 import java.sql.SQLException ;
31 import java.sql.Timestamp ;
32 import java.sql.Types ;
33 import java.util.ArrayList ;
34 import java.util.Collection ;
35 import java.util.Date ;
36 import java.util.Map ;
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
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 String colData;
110 String colDate;
111 String colFloat;
112 String colGlobalKey;
113 String colItemKey;
114 String colItemType;
115 String colNumber;
116 String colString;
117
118 private String userName;
119 private String password;
120 private String driverClassName;
121 private String url;
122
123 String globalKey;
125 String tableName;
126
127
129 public Collection getKeys(String prefix, int type) throws PropertyException {
130 if (prefix == null) {
131 prefix = "";
132 }
133
134 Connection conn = null;
135
136 try {
137 conn = getConnection();
138
139 PreparedStatement ps = null;
140 String 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 list = new ArrayList ();
155 ResultSet 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 e) {
166 throw new PropertyException(e.getMessage());
167 } finally {
168 closeConnection(conn);
169 }
170 }
171
172 public int getType(String key) throws PropertyException {
173 Connection conn = null;
174
175 try {
176 conn = getConnection();
177
178 String sql = "SELECT " + colItemType + " FROM " + tableName + " WHERE " + colGlobalKey + " = ? AND " + colItemKey + " = ?";
179 PreparedStatement ps = conn.prepareStatement(sql);
180 ps.setString(1, globalKey);
181 ps.setString(2, key);
182
183 ResultSet 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 e) {
195 throw new PropertyException(e.getMessage());
196 } finally {
197 closeConnection(conn);
198 }
199 }
200
201 public boolean exists(String key) throws PropertyException {
202 return getType(key) != 0;
203 }
204
205 public void init(Map config, Map args) {
206 globalKey = (String ) args.get("globalKey");
208
209
218
219 tableName = (String ) config.get("table.name");
220 colGlobalKey = (String ) config.get("col.globalKey");
221 colItemKey = (String ) config.get("col.itemKey");
222 colItemType = (String ) config.get("col.itemType");
223 colString = (String ) config.get("col.string");
224 colDate = (String ) config.get("col.date");
225 colData = (String ) config.get("col.data");
226 colFloat = (String ) config.get("col.float");
227 colNumber = (String ) config.get("col.number");
228
229 this.userName = (String ) config.get("username");
230 this.password = (String ) config.get("password");
231 this.driverClassName = (String ) config.get("driverClassName");
232 this.url = (String ) config.get("url");
233 }
234
235 public void remove(String key) throws PropertyException {
236 Connection conn = null;
237
238 try {
239 conn = getConnection();
240
241 String sql = "DELETE FROM " + tableName + " WHERE " + colGlobalKey + " = ? AND " + colItemKey + " = ?";
242 PreparedStatement ps = conn.prepareStatement(sql);
243 ps.setString(1, globalKey);
244 ps.setString(2, key);
245 ps.executeUpdate();
246 ps.close();
247 } catch (SQLException e) {
248 throw new PropertyException(e.getMessage());
249 } finally {
250 closeConnection(conn);
251 }
252 }
253
254 protected void setImpl(int type, String key, Object 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 conn = null;
260
261 try {
262 conn = getConnection();
263
264 String sql = "UPDATE " + tableName + " SET " + colString + " = ?, " + colDate + " = ?, " + colData + " = ?, " + colFloat + " = ?, " + colNumber + " = ?, " + colItemType + " = ? " + " WHERE " + colGlobalKey + " = ? AND " + colItemKey + " = ?";
265 PreparedStatement 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 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 e) {
280 throw new PropertyException(e.getMessage());
281 } finally {
282 closeConnection(conn);
283 }
284 }
285
286 protected Object get(int type, String key) throws PropertyException {
287 String sql = "SELECT " + colItemType + ", " + colString + ", " + colDate + ", " + colData + ", " + colFloat + ", " + colNumber + " FROM " + tableName + " WHERE " + colItemKey + " = ? AND " + colGlobalKey + " = ?";
288
289 Object o = null;
290 Connection conn = null;
291
292 try {
293 conn = getConnection();
294
295 PreparedStatement ps = conn.prepareStatement(sql);
296 ps.setString(1, key);
297 ps.setString(2, globalKey);
298
299 int propertyType;
300 ResultSet 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 (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 (rs.getDouble(colFloat));
329
330 break;
331
332 case PropertySet.INT:
333 o = new Integer (rs.getInt(colNumber));
334
335 break;
336
337 case PropertySet.LONG:
338 o = new Long (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 e) {
355 throw new PropertyException(e.getMessage());
356 } catch (NumberFormatException e) {
357 throw new PropertyException(e.getMessage());
358 } finally {
359 closeConnection(conn);
360 }
361
362 return o;
363 }
364
365 private void setValues(PreparedStatement ps, int type, String key, Object value) throws SQLException , PropertyException {
366 String driverName;
368
369 try {
370 driverName = ps.getConnection().getMetaData().getDriverName().toUpperCase();
371 } catch (Exception e) {
372 driverName = "";
373 }
374
375 ps.setNull(1, Types.VARCHAR);
376 ps.setNull(2, Types.TIMESTAMP);
377
378 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 boolVal = (Boolean ) 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 date = (Date ) value;
410 ps.setTimestamp(2, new Timestamp (date.getTime()));
411
412 break;
413
414 case PropertySet.DOUBLE:
415
416 Double d = (Double ) value;
417 ps.setDouble(4, d.doubleValue());
418
419 break;
420
421 case PropertySet.INT:
422
423 Integer i = (Integer ) value;
424 ps.setInt(5, i.intValue());
425
426 break;
427
428 case PropertySet.LONG:
429
430 Long l = (Long ) value;
431 ps.setLong(5, l.longValue());
432
433 break;
434
435 case PropertySet.STRING:
436 ps.setString(1, (String ) value);
437
438 break;
439
440 default:
441 throw new PropertyException("This type isn't supported!");
442 }
443 }
444
445 protected Connection getConnection() throws SQLException
446 {
447 Connection 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 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 }
473 catch (Exception ex)
474 {
475 ex.printStackTrace();
476 }
477
478 return conn;
479 }
480
481 private void closeConnection(Connection conn) {
482 try {
483 if ((conn != null) && !conn.isClosed()) {
484 conn.close();
485 }
486 } catch (SQLException e) {
487 logger.error("Could not close connection");
488 }
489 }
490
491 public void setupDriver(String connectURI, String userName, String password) throws Exception
492 {
493 String 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
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
518 {
519 PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
520 driver.closePool("infoGlueJDBCPropertySet");
521 }
522
523 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.