1 /*
2 * PROJECT : DAR Runtime and Tools
3 * COPYRIGHT : Copyright (C) 1999-2004 tim.stephenson@enableit.org
4 * LICENSE : GNU LESSER GENERAL PUBLIC LICENSE
5 * Version 2.1, February 1999
6 *
7 * This program is free software; you can redistribute it and/or modify
8 * it under the terms of the GNU General Public License as published by
9 * the Free Software Foundation; either version 2 of the License, or
10 * (at your option) any later version.
11 *
12 * This program is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 * GNU General Public License for more details.
16 *
17 * You should have received a copy of the GNU General Public License
18 * along with this program; if not, write to the Free Software
19 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
20 */
21 package org.enableit.db;
22
23 import java.beans.PropertyDescriptor;
24 import java.math.BigDecimal;
25 import java.sql.DatabaseMetaData;
26 import java.sql.PreparedStatement;
27 import java.sql.ResultSetMetaData;
28 import java.sql.Types;
29 import java.util.ArrayList;
30 import java.util.HashMap;
31 import java.util.Hashtable;
32 import java.util.Iterator;
33 import java.util.List;
34 import java.util.Map;
35 import java.util.TreeMap;
36
37 import org.apache.commons.beanutils.PropertyUtils;
38 import org.apache.log4j.Logger;
39
40
41 /***
42 * Provides utilities to be used in conjunction with
43 * the database proxies.
44 *
45 * @version v1.1
46 * Extracted the ResultSet conversion routines from the database proxies.
47 *
48 * @author __AUTHOR__
49 *
50 * @see org.enableit.db.DatabaseProxy DatabaseProxy<br/>
51 * @see org.enableit.db.GenericDBProxy GenericDBProxy<br/>
52 */
53 public class DBUtils {
54 /*
55 * Properties
56 */
57
58 /***
59 * The Log4J <code>Category</code> doing the logging.
60 */
61 protected static Logger logger = Logger.getLogger(DBUtils.class);
62
63 /***
64 * Name of stored procedure used for getNextId
65 */
66 private static final String PROC_GET_NEXT_ID = "GetNextId";
67
68 /***
69 * Dynamic SQL used for getNextIdUsingSQL to update the id to the next one
70 */
71 private static final String SQL_SET_NEXT_ID = "UPDATE SystemIdentity SET siNextId = siNextId + 1 WHERE siName=";
72
73 /***
74 * Dynamic SQL used for getNextId
75 */
76 private static final String SQL_GET_NEXT_ID = "SELECT siNextId FROM SystemIdentity WHERE siName=";
77
78 /***
79 * Dynamic SQL used for getNextId, initialises the table if no previous query
80 */
81 private static final String SQL_INIT_ID = "INSERT INTO SystemIdentity ("
82 + "siNextId ," + "siDesc ,"
83 + "siCreationUser ," + "siUpdateUser ,"
84 + "siCreationDate ," + "siUpdateDate ," + "siName )"
85 + "VALUES ( " + "1 ,"
86 + "'description here' ," + "'sysuser' ,"
87 + "'sysuser' ,";
88 public static final int DB_UNKNOWN = 0;
89 public static final int DB_SYBASE_ASA = 1;
90 public static final int DB_SYBASE_ASE = 2;
91 public static final int DB_ORACLE = 3;
92 public static final int DB_MSSQLSERVER = 4;
93 public static final String DB_NAME_SYBASE_ASA = "Adaptive Server Anywhere";
94 public static final String DB_NAME_SYBASE_ASE = "Adaptive Server Enterprise";
95 public static final String DB_NAME_ORACLE = "Oracle";
96 public static final String DB_NAME_MSSQLSERVER = "Microsoft SQL Server";
97
98 /***
99 * Information on the exact CVS version accessible after compilation
100 */
101 public static final String ABOUT = "$Revision: 1.17 $";
102
103 /*
104 * Constructors
105 */
106
107 /***
108 * Default Constructor
109 */
110 public DBUtils() {
111 // No implementation
112 }
113
114 /*
115 * Methods
116 */
117
118 /***
119 * Converts a set of name-value pairs stored in a
120 * <CODE>java.util.Hashtable</CODE>
121 * to values only stored in a <CODE>java.util.ArrayList</CODE>
122 */
123 public static ArrayList convertHashtableToArrayList(Hashtable attributes) {
124 logger.info("METHOD_ENTRY convertHashtableToArrayList");
125
126 ArrayList al = new ArrayList(attributes.size());
127
128 for (Iterator i = attributes.keySet().iterator(); i.hasNext();) {
129 // Beware of this, there may be a cast needed on i.next()
130 al.add(attributes.get(i.next()));
131 }
132
133 logger.info("METHOD_EXIT convertHashtableToArrayList");
134
135 return al;
136 }
137
138 /***
139 * Converts a set of name-value pairs stored in a
140 * <CODE>java.util.HashMap</CODE>
141 * to values only stored in a <CODE>java.util.ArrayList</CODE>
142 */
143 public static ArrayList convertHashMapToArrayList(HashMap attributes) {
144 logger.info("METHOD_ENTRY convertHashMapToArrayList");
145
146 ArrayList al = new ArrayList(attributes.size());
147
148 for (Iterator i = attributes.keySet().iterator(); i.hasNext();) {
149 // Beware of this, there may be a cast needed on i.next()
150 al.add(attributes.get(i.next()));
151 }
152
153 logger.info("METHOD_EXIT convertHashMapToArrayList");
154
155 return al;
156 }
157
158 /***
159 * Turns a <code>ResultSet</code> into an <code>ArrayList</code>
160 * of <code>TreeMaps</code>
161 *
162 * @return list
163 * An <CODE>ArrayList</CODE> of <CODE>TreeMap</CODE>s. Each
164 * map contains the column heading value pairs for a single result set
165 * row.
166 */
167 public static List convertResultToList(java.sql.ResultSet sqlResults)
168 throws DBException {
169 logger.info("METHOD_ENTRY convertResultToList");
170
171 Map map = null;
172 List list = new ArrayList();
173 StringBuffer sbuff = new StringBuffer();
174
175 try {
176 // get meta data
177 ResultSetMetaData meta = sqlResults.getMetaData();
178 int colCount = meta.getColumnCount();
179 int rowCount = 0;
180
181 // get column headings
182 String[] headings = new String[colCount];
183
184 for (int i = 0; i < colCount; i++) {
185 headings[i] = meta.getColumnLabel(i + 1);
186
187 if (headings[i] == null) {
188 headings[i] = "column " + String.valueOf(i);
189 }
190 }
191
192 //Process all results in set
193 while (sqlResults.next() == true) {
194 map = new HashMap(colCount);
195
196 // Processes a single row of the result set.
197 // Each column becomes an element in the Map keyed
198 // by the column heading
199 for (int i = 0; i < colCount; i++) {
200 // Get column value
201 // Remember column numbers start from 1 !!!!
202 Object resObj = sqlResults.getObject(i + 1);
203
204 // Print out the retrieved info for verbose logging
205 logger.debug("Column " + String.valueOf(i + 1) + ": "
206 + headings[i] + " : "
207 + ((resObj == null) ? "null" : resObj.toString()));
208
209 // Append value to string for SQL logging
210 sbuff.append(String.valueOf(resObj));
211 sbuff.append("|");
212
213 // Store in map
214 //if(! sqlResults.wasNull()) {
215 if (resObj instanceof String) {
216 map.put(headings[i], ((String) resObj).trim());
217 } else {
218 map.put(headings[i], resObj);
219 }
220
221 //}
222 // send result trace to log
223 logger.debug(sbuff.toString());
224 sbuff.setLength(0);
225 }
226
227 TreeMap treeMap = new TreeMap(map);
228
229 list.add(treeMap);
230 }
231
232 // send result information to the log
233 logger.info("<SQL_RESULT> Got results set with "
234 + String.valueOf(list.size()) + " rows and "
235 + String.valueOf(colCount) + " columns");
236 } catch (Exception e) {
237 logger.error(e.getMessage());
238 throw new DBException(e.getMessage());
239 }
240
241 logger.info("METHOD_EXIT convertResultToList");
242
243 return list;
244 }
245
246 /***
247 * <p>Returns the next available id for the specified table</p>
248 *
249 * <p>The implementation makes use of a number fountain in the database
250 * to avoid the chance of two EJB onstances attempting to use the same key</p>
251 *
252 * @param tableName
253 */
254 public static int getNextIdUsingSQL(String tableName)
255 throws DBException {
256 logger.info("METHOD_ENTRY");
257
258 // Msg to report in the event of failure
259 String msg = "Unable to find next id: ";
260
261 int id = 0;
262
263 try {
264 // Update the next id to use first
265 try {
266 int rowsAffected = GenericDBProxy.executeUpdate(SQL_SET_NEXT_ID
267 + "'" + tableName + "'");
268
269 if (rowsAffected == 0) {
270 // May be first time, try an insert then
271 logger.info("STAGE 2: INIT NEXT ID");
272
273 java.sql.Date now = new java.sql.Date(new java.util.Date()
274 .getTime());
275 String sql = SQL_INIT_ID + "'" + now + "'," + "'" + now
276 + "'," + "'" + tableName + "')";
277
278 GenericDBProxy.executeUpdate(sql);
279 }
280 } catch (Exception e) {
281 // May be first time, try an insert then
282 java.sql.Date now = new java.sql.Date(new java.util.Date()
283 .getTime());
284 String sql = SQL_INIT_ID + "'" + now + "'," + "'" + now + "',"
285 + "'" + tableName + "')";
286
287 GenericDBProxy.executeUpdate(sql);
288 }
289
290 // Now select the next id out
291 ArrayList results = (ArrayList) GenericDBProxy.executeQuery(SQL_GET_NEXT_ID
292 + "'" + tableName + "'", DatabaseProxy.LIST);
293
294 logger.debug(results.toString());
295
296 if (results.size() > 0) {
297 // Got my id
298 TreeMap tm = (TreeMap) results.get(0);
299
300 id = Integer.parseInt(tm.get("siNextId").toString());
301 } else {
302 // Log an exception.
303 DBException dbe = new DBException(msg
304 + "no id returned from select");
305
306 logger.error(msg);
307 throw dbe;
308 }
309 } catch (DBException dbe) {
310 // Should already be logged at point raised, rethrow
311 throw dbe;
312 } catch (Exception e) {
313 /*
314 * Log an exception.
315 */
316 logger.error(e.getClass().getName(), e);
317
318 DBException dbe = new DBException(msg + e.getMessage());
319
320 logger.error(msg + e.getMessage());
321 throw dbe;
322 }
323
324 logger.info("METHOD_EXIT");
325
326 return id;
327 }
328
329 /***
330 * <p>Returns the next available id for the specified table</p>
331 *
332 * <p>The implementation makes use of a number fountain in the database
333 * to avoid the chance of two EJB onstances attempting to use the same key</p>
334 *
335 * @param tableName
336 *
337 * @param spSupported
338 * true - if stored procedures are to be used to execute the queries
339 * false - to use dynamic SQL statements
340 */
341 public static int getNextId(String tableName, boolean spSupported)
342 throws DBException {
343 logger.info("METHOD_ENTRY");
344
345 // Msg to report in the event of failure
346 String msg = "Unable to find next id";
347
348 if (!spSupported) {
349 return getNextIdUsingSQL(tableName);
350 }
351
352 int id = 0;
353
354 /*
355 * Single Result Set.
356 */
357 ArrayList parameterList = new ArrayList();
358
359 parameterList.add(tableName);
360
361 try {
362 ArrayList results = (ArrayList) GenericDBProxy
363 .executeQueryProcedure(PROC_GET_NEXT_ID, parameterList,
364 DatabaseProxy.LIST);
365
366 if (results.size() > 0) {
367 // Store the reference for faster access.
368 TreeMap tm = (TreeMap) results.get(0);
369
370 logger.debug(tm.toString());
371 id = Integer.parseInt(tm.get("nextId").toString());
372 } else {
373 /*
374 * Log an exception.
375 */
376 DBException re = new DBException();
377
378 logger.error(msg);
379 throw re;
380 }
381 } catch (DBException e) {
382 // Should already be logged at point raised, rethrow
383 throw e;
384 } catch (Exception e) {
385 /*
386 * Log an exception.
387 */
388 DBException dbe = new DBException(e.getMessage(), e);
389
390 logger.error(e);
391 throw dbe;
392 }
393
394 logger.info("METHOD_EXIT");
395
396 return id;
397 }
398
399 /***
400 * <p>Adds a 'where' or 'and' clause as appropriate to join on the
401 * supplied column and value</p>
402 *
403 * <p>If filterCol and / or filterVal are null then no filter is added.
404 * This could be modified to allow joins to null values, but presents
405 * issues of what datatype to send to the database</p>
406 *
407 * @param sql
408 * The base query to which we wish to add a filter
409 *
410 * @param filterCol
411 * The column to filter on
412 *
413 * @param filterVal
414 * The value to filter on
415 */
416 public static String addFilter(String sql, String filterCol,
417 String filterVal) {
418 logger.info("METHOD_ENTRY sql: " + sql);
419
420 if ((filterCol != null) && (filterVal != null)) {
421 // Determine whether filter value needs to be encased in quotes
422 boolean numeric = true;
423
424 for (int i = 0; i < filterVal.length(); i++) {
425 if (!Character.isDigit(filterVal.charAt(i))) {
426 numeric = false;
427 }
428 }
429
430 if (!numeric) {
431 filterVal = "'" + filterVal + "'";
432 }
433
434 // Determine whether WHERE or AND
435 if ((sql.toUpperCase().indexOf("WHERE")) != -1) {
436 // An AND clause required
437 sql += ("\nAND " + filterCol + "=" + filterVal);
438 } else {
439 // A WHERE clause required
440 sql += ("\nWHERE " + filterCol + "=" + filterVal);
441 }
442 }
443
444 logger.info("METHOD_EXIT");
445
446 return sql;
447 }
448
449 /***
450 * <p>Adds a 'where' or 'and' clause as appropriate to join on the
451 * supplied column and value</p>
452 *
453 * <p>If filter name and / or filter value are null then no filter is added.
454 * This could be modified to allow joins to null values, but presents
455 * issues of what datatype to send to the database</p>
456 *
457 * @param sql
458 * The base query to which we wish to add a filter
459 *
460 * @param filter
461 * All the encapsulated filter data
462 */
463 public static String addFilter(String sql, DBFilter filter) {
464 logger.info("METHOD_ENTRY sql: " + sql);
465
466 if ((filter.getName() != null) && (filter.getValue() != null)) {
467 // Determine whether filter value needs to be encased in quotes
468 boolean numeric = true;
469
470 // TODO: The value is no longer a string but an object
471 for (int i = 0; i < filter.getValue().toString().length(); i++) {
472 if (!Character.isDigit(filter.getValue().toString().charAt(i))) {
473 numeric = false;
474 }
475 }
476
477 if (filter.getValue().equals("null")) {
478 filter.setMatch(DBFilter.IS_NULL);
479 } else if (!numeric) {
480 filter.setValue("'" + filter.getValue() + "'");
481 }
482
483 // Log the clause to be added
484 logger.info("Adding filter: " + filter);
485
486 // Determine whether WHERE or AND
487 if ((sql.toUpperCase().indexOf("WHERE")) != -1) {
488 // An AND clause required
489 sql += ("\nAND " + filter.getName() + filter.getMatch()
490 + filter.getValue());
491 } else {
492 // A WHERE clause required
493 sql += ("\nWHERE " + filter.getName() + filter.getMatch()
494 + filter.getValue());
495 }
496 }
497
498 logger.info("METHOD_EXIT");
499
500 return sql;
501 }
502
503 /***
504 * <p>Adds a 'where' or 'and' clause as appropriate to join on the
505 * supplied column and value</p>
506 *
507 * <p>If filter name and / or filter value are null then no filter is added.
508 * This could be modified to allow joins to null values, but presents
509 * issues of what datatype to send to the database</p>
510 *
511 * @param sql
512 * The base query to which we wish to add a filter
513 *
514 * @param filter
515 * A java.util.List of DBFilter objects containing the encapsulated filter data
516 */
517 public static String addFilters(String sql, List filters) {
518 logger.info("METHOD_ENTRY sql: " + sql);
519
520 for (Iterator i = filters.iterator(); i.hasNext();) {
521 sql = addFilter(sql, (DBFilter) i.next());
522 }
523
524 logger.info("METHOD_EXIT");
525
526 return sql;
527 }
528
529 /***
530 * <p>Adds a 'where' or 'and' clause as appropriate to join on the
531 * supplied column and value</p>
532 *
533 * <p>If filter name and / or filter value are null then no filter is added.
534 * This could be modified to allow joins to null values, but presents
535 * issues of what datatype to send to the database</p>
536 *
537 * @param sql
538 * The base query to which we wish to add a filter
539 *
540 * @param filter
541 * A java.util.List of DBFilter objects containing the encapsulated filter data
542 */
543 public static StringBuffer addFilters(StringBuffer sql, List filters) {
544 logger.info("METHOD_ENTRY sql: " + sql);
545
546 for (Iterator i = filters.iterator(); i.hasNext();) {
547 sql = new StringBuffer(addFilter(sql.toString(), (DBFilter) i.next()));
548 }
549
550 logger.info("METHOD_EXIT");
551
552 return sql;
553 }
554
555 /***
556 * Read data from a Map containing data according to the convention in
557 * <code>convertResultToList</code> and populate a JavaBean object with it.
558 * <p>NB Due to a problem in the beanutils library this will not work with
559 * DynaBeans.</p>
560 * @param bean The JavaBean to populate
561 * @param dataRecord Map of property data to populate into JavaBean.
562 * @return bean The JavaBean now populated with the data
563 */
564 public static Object populateBean(Object bean, Map dataRecord)
565 throws DBException {
566 logger.info("METHOD_ENTRY: populateBean, dataRecord: " + dataRecord);
567
568 // add the column values
569 PropertyDescriptor[] descriptors = PropertyUtils.getPropertyDescriptors(bean);
570
571 for (int i = 0; i < descriptors.length; i++) {
572 String key = (String) descriptors[i].getName();
573 Object value = null;
574
575 try {
576 logger.debug("Attempt to populate bean property: " + key);
577
578 if (dataRecord.containsKey(key)) {
579 value = dataRecord.get(key);
580
581 Class targetType = descriptors[i].getPropertyType();
582
583 logger.debug("... found property in data record. value="
584 + value + ", type=" + value.getClass().getName()
585 + ", targetType=" + targetType.getClass().getName()
586 + ", key=" + key);
587
588 if (!value.getClass().getName().equals(targetType.getName())) {
589 // Handle type conversion
590 value = ConvertUtils.convert(value, targetType);
591 }
592
593 if (!value.getClass().getName().equals(targetType.getName())) {
594 logger.debug("STILL HAVE TYPE PROBLEM!! TYPE, VALUE"
595 + targetType + "," + value);
596
597 // Handle type conversion
598 //value = ConvertUtils.convert(value, targetType) ;
599 }
600
601 PropertyUtils.setProperty(bean, descriptors[i].getName(),
602 value);
603 } else {
604 logger.debug("Cannot find value for key=" + key);
605 }
606 } catch (java.lang.IllegalAccessException e) {
607 logger.error(e.getClass().getName() + ":" + e.getMessage());
608 throw new DBException(e.getMessage(), e);
609 } catch (java.lang.IllegalArgumentException e) {
610 logger.error(e.getClass().getName() + ":" + e.getMessage());
611 throw new DBException(e.getMessage(), e);
612 } catch (java.lang.reflect.InvocationTargetException e) {
613 String msg = e.getClass().getName() + ":" + e.getMessage()
614 + ":" + key + "=" + value;
615
616 logger.error(msg);
617 throw new DBException(e.getMessage(), e);
618 } catch (java.lang.NoSuchMethodException e) {
619 logger.error(e.getClass().getName() + ":" + e.getMessage());
620 throw new DBException(e.getMessage(), e);
621 }
622 }
623
624 logger.info("METHOD_EXIT: populateBean");
625
626 return bean;
627 }
628
629 /***
630 * Binds a List of varables into a SQL statement.
631 */
632 public static PreparedStatement bindVars(PreparedStatement ps, List parms)
633 throws DBException {
634 logger.info("METHOD_ENTRY: bindVars, parms=" + parms);
635
636 try {
637 // Set the parameters
638 int j = 1;
639
640 for (Iterator i = parms.iterator(); i.hasNext(); j++) {
641 Object obj = i.next();
642
643 logger.debug("Found obj=" + obj + ", type="
644 + obj.getClass().getName());
645
646 if (obj == null) {
647 // TODO 15/08/2002 This may well not be supported!!
648 ps.setNull(j, Types.NULL);
649 } else if (obj instanceof SqlType) {
650 ps.setNull(j, ((SqlType) obj).getDataType());
651 } else if (obj instanceof BigDecimal) {
652 ps.setBigDecimal(j, (BigDecimal) obj);
653 } else if (obj instanceof Number) {
654 // TODO: what about float or other real numbers!?
655 ps.setLong(j, ((Number) obj).longValue());
656 } else if (obj instanceof String) {
657 ps.setString(j, (String) obj);
658 } else if (obj instanceof java.sql.Timestamp) {
659 ps.setTimestamp(j, (java.sql.Timestamp) obj);
660 } else if (obj instanceof java.sql.Date) {
661 ps.setDate(j, (java.sql.Date) obj);
662 } else if (obj instanceof java.sql.Time) {
663 ps.setTime(j, (java.sql.Time) obj);
664 } else if (obj instanceof java.util.Date) {
665 ps.setDate(j,
666 new java.sql.Date(((java.util.Date) obj).getTime()));
667 } else if (obj instanceof Object) {
668 // This may not work depending on the RDBMS handling of Objects
669 logger.debug("Using setObject for: " + obj);
670 ps.setObject(j, obj);
671 } else {
672 String type = obj.getClass().getName();
673
674 logger.debug("Unknown parameter type supplied for UPDATE: "
675 + type);
676 throw new DBException(
677 "Unknown parameter type supplied for UPDATE: " + type);
678 }
679 }
680 } catch (java.sql.SQLException e) {
681 if (logger.isDebugEnabled()) {
682 logger.error(e.getMessage(), e);
683 }
684
685 throw new DBException(e.getMessage());
686 }
687
688 logger.info("METHOD_EXIT: bindVars");
689
690 return ps;
691 }
692
693 /***
694 * Return one of the enumerated database products.
695 */
696 public static int getDatabaseProduct(DatabaseMetaData dmd) {
697 logger.info("METHOD_ENTRY: getDatabaseProduct");
698
699 int product = DB_UNKNOWN;
700
701 try {
702 String prodName = dmd.getDatabaseProductName();
703
704 if (DB_NAME_SYBASE_ASA.equals(prodName)) {
705 product = DB_SYBASE_ASA;
706 } else if (DB_NAME_SYBASE_ASE.equals(prodName)) {
707 product = DB_SYBASE_ASE;
708 } else if (DB_NAME_ORACLE.equals(prodName)) {
709 product = DB_ORACLE;
710 } else if (DB_NAME_MSSQLSERVER.equals(prodName)) {
711 product = DB_MSSQLSERVER;
712 }
713 } catch (java.sql.SQLException e) {
714 logger.error(e.getMessage());
715 }
716
717 logger.info("METHOD_ENTRY: getDatabaseProduct");
718
719 return product;
720 }
721 }
This page was automatically generated by Maven