Source code: org/enableit/db/DatabaseProxy.java
1 /*
2 * PROJECT : __PROJECT_NAME__
3 *
4 * COPYRIGHT : Copyright (C) 1999,2000,2001,2002 enableIT.org
5 *
6 * contact us at gpl@enableit.org.
7 *
8 * This program is free software; you can redistribute it and/or modify
9 * it under the terms of the GNU General Public License as published by
10 * the Free Software Foundation; either version 2 of the License, or
11 * (at your option) any later version.
12 *
13 * This program is distributed in the hope that it will be useful,
14 * but WITHOUT ANY WARRANTY; without even the implied warranty of
15 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 * GNU General Public License for more details.
17 *
18 * You should have received a copy of the GNU General Public License
19 * along with this program; if not, write to the Free Software
20 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
21 */
22 package org.enableit.db;
23
24 // Java Imports
25 import java.util.*;
26 import java.sql.*;
27
28 // Log4J Imports
29 import org.apache.log4j.Category;
30
31 /**
32 * Proxy class to wrap database access in a simple form. The user is required to
33 * provide their own <CODE>Connection</CODE>
34 *
35 * @version v1.0
36 *
37 * @author __AUTHOR__
38 *
39 */
40 public class DatabaseProxy
41 {
42
43 /**
44 * Execute the supplied SQL query against the supplied database
45 * connection.
46 *
47 * @param conn
48 * The database connection to use
49 *
50 * @param sql
51 * The SQL query to execute
52 *
53 * @return
54 * The SQL results stored as an <code>java.util.ArrayList</code>, each
55 * element of which is a <code>java.util.TreeMap</code> holding a
56 * single row of results
57 *
58 * @throws DBException
59 * If execution of the query failed.
60 */
61 public static java.util.ArrayList executeQuery(Connection conn, String sql)
62 throws DBException
63 {
64 return (java.util.ArrayList)executeQuery(conn, sql, 1) ;
65 }
66
67 /**
68 * Execute the supplied SQL query against the supplied database
69 * connection.
70 *
71 * @param conn
72 * The database connection to use
73 *
74 * @param sql
75 * The SQL query to execute
76 *
77 * @param parms
78 * <code>List</code> containing parameters to insert in the sql statement.
79 *
80 * @return
81 * The SQL results stored as an <code>java.util.ArrayList</code>, each
82 * element of which is a <code>java.util.TreeMap</code> holding a
83 * single row of results
84 *
85 * @throws DBException
86 * If execution of the query failed.
87 */
88 public static java.util.List executeQuery(Connection conn, String sql, List parms)
89 throws DBException {
90 logger.info("METHOD_ENTRY: executeQuery");
91
92 List resultList = null;
93 PreparedStatement ps = null;
94 java.sql.ResultSet rs = null ;
95
96 try {
97 // if appropriate write out trace information before executing SQL
98 logger.debug("<SQL_SUMMARY> About to execute SQL query: " + sql);
99
100 // create a statement on the connection
101 ps = conn.prepareStatement(sql);
102
103 ps = DBUtils.bindVars(ps, parms);
104
105 // Do the query
106 boolean results = ps.execute();
107
108 logger.debug("<SQL_RESULT> Results found ?: " + String.valueOf(results));
109
110 // If one exists get the result set
111 if (results) {
112 rs = ps.getResultSet() ;
113 resultList = DBUtils.convertResultToList(rs) ;
114 }
115
116 // Print any warnings chained to statement object
117 SQLWarning warning = ps.getWarnings();
118 while(warning != null) {
119 logger.warn("<SQL Warning>" + warning.toString());
120 warning = warning.getNextWarning() ;
121 }
122 // Print any warnings chained to ResultSet object
123 warning = rs.getWarnings();
124 while(warning != null){
125 logger.debug("<SQL Warning>" + warning.toString());
126 warning = warning.getNextWarning() ;
127 }
128 } catch (SQLException sqle) {
129 int errCode = sqle.getErrorCode();
130 logger.error("\n ========== Database error encountered code = " + String.valueOf(errCode) + "==========");
131 logger.error(sqle);
132 logger.error("SQL executed was: " + sql);
133 throw new DBException(sqle.getMessage());
134 } catch (Exception e) {
135 logger.fatal(e.getMessage());
136 throw new DBException(e.getMessage());
137 } finally {
138 try {
139 rs.close() ;
140 rs = null ;
141 } catch (Exception e) {
142 ;
143 }
144 try {
145 ps.close();
146 ps=null;
147 } catch (Exception e) {
148 ;
149 }
150 }
151
152 logger.info("METHOD_ENTRY: executeQuery");
153 return resultList ;
154 }
155
156 /**
157 * Execute the supplied SQL query against the supplied database
158 * connection.
159 *
160 * @param conn
161 * The database connection to use
162 *
163 * @param sql
164 * The SQL query to execute
165 *
166 * @param returnType
167 * The object type to convert the SQL results into
168 *
169 * @return
170 * The SQL results as the object type requested
171 *
172 * @throws DBException
173 * If execution of the query failed.
174 *
175 * @deprecated <br/>
176 * use <code>executeQuery(conn:Connection, sql:String):java.util.ArrayList</code> instead
177 */
178 public static Object executeQuery(Connection conn, String sql, int returnType)
179 throws DBException
180 {
181 logger.debug("METHOD_ENTRY");
182
183 Object o = null;
184 Statement stmt = null;
185 java.sql.ResultSet rs = null ;
186
187 try {
188 // create a statement on the connection
189 stmt=conn.createStatement();
190
191 // if appropriate write out trace information before executing SQL
192 logger.debug("<SQL_SUMMARY> About to execute SQL query: " + sql);
193
194 // Do the query
195 boolean results=stmt.execute(sql);
196
197 logger.debug("<SQL_RESULT> Results found ?: " + String.valueOf(results));
198
199 // If one exists get the result set
200 if (results) {
201 rs = stmt.getResultSet() ;
202 // Do any object conversions required
203 switch (returnType) {
204 case 1:
205 List resultList = DBUtils.convertResultToList(rs) ;
206 o = resultList;
207 break;
208 case 2:
209 throw new DBException("Attempt to use deprecated functionality that has been compiled out of this version") ;
210 default :
211 o = rs ;
212 }
213 }
214
215 // Print any warnings chained to statement object
216 SQLWarning warning = stmt.getWarnings();
217 while(warning != null){
218 logger.warn("<SQL Warning>" + warning.toString());
219 warning = warning.getNextWarning() ;
220 }
221 // Print any warnings chained to ResultSet object
222 warning = rs.getWarnings();
223 while(warning != null){
224 logger.debug("<SQL Warning>" + warning.toString());
225 warning = warning.getNextWarning() ;
226 }
227 } catch (SQLException sqle) {
228 int errCode = sqle.getErrorCode();
229 logger.error("\n ========== Database error encountered code = " + String.valueOf(errCode) + "==========");
230 logger.error(sqle);
231 logger.error("SQL executed was: " + sql);
232 throw new DBException(sqle.getMessage());
233 } catch (Exception e) {
234 logger.fatal(e.getMessage());
235 throw new DBException(e.getMessage());
236 } finally {
237 try {
238 stmt.close();
239 stmt=null;
240 } catch (Exception e) {
241 logger.fatal(e.getMessage());
242 }
243 }
244
245 logger.debug("<EXIT>");
246 return o ;
247 }
248
249 /**
250 * Execute the supplied SQL update query against a the supplied
251 * database connection.
252 *
253 * @param sql
254 * The SQL update to execute
255 *
256 * @param conn
257 * The database connection to use
258 *
259 * @return
260 * The number of rows affected by the update.
261 *
262 * @throws DBException
263 * If execution of the update failed.
264 */
265 public static int executeUpdate(Connection conn, String sql)
266 throws DBException
267 {
268 logger.debug("METHOD_ENTRY");
269
270 int rowsAffected = 0;
271 Statement stmt = null;
272
273 try {
274 // Create a JDBC connection and execute the String
275 stmt = conn.createStatement();
276
277 // If appropriate write out trace information before executing
278 logger.debug("<SQL_SUMMARY> About to execute SQL: \n" + sql);
279 rowsAffected = stmt.executeUpdate(sql);
280 //stmt.executeUpdate(sql);
281
282 // Print any warnings chained to statement object
283 SQLWarning warning = stmt.getWarnings();
284 while(warning != null){
285 logger.debug("<SQL Warning :>" + warning.toString());
286 warning = warning.getNextWarning() ;
287 }
288
289 } catch (SQLException sqle) {
290 int errCode = sqle.getErrorCode();
291 logger.error("========== Database error encountered code = " + errCode + "==========");
292 logger.error(sqle);
293 logger.error("SQL executed was: " + sql);
294 throw new DBException(sqle.getMessage());
295 } catch (Exception e) {
296 logger.fatal(e.getMessage());
297 throw new DBException(e.getMessage());
298 } finally {
299 try {
300 stmt.close();
301 stmt = null ;
302 } catch (Exception e) {}
303 }
304
305 logger.debug("<EXIT>");
306 return rowsAffected;
307 }
308
309 /**
310 * Execute the supplied SQL update query against a the supplied
311 * database connection.
312 *
313 * @param conn
314 * The database connection to use
315 *
316 * @param sql
317 * The SQL update to execute with parameters represented by ?
318 *
319 * @param parms
320 * <CODE>List</CODE> containing parameters to insert in the sql statement
321 *
322 * @return
323 * The number of rows affected by the update.
324 *
325 * @throws DBException
326 * If execution of the update failed.
327 */
328 public static int executeUpdate(Connection conn, String sql, List parms)
329 throws SQLException, DBException
330 {
331 logger.debug("METHOD_ENTRY executeUpdate, parms=" + parms);
332
333 int rowsAffected = 0;
334 PreparedStatement ps = null;
335
336 try {
337 // Create a JDBC connection and execute the String
338 ps = conn.prepareStatement(sql);
339 if (parms == null) {
340 parms = new ArrayList();
341 }
342 // Set the parameters
343 for (int j = 0 ; j < parms.size() ;j++) {
344 if (parms.get(j) == null) {
345 // TODO 15/08/2002 This may well not be supported!!
346 ps.setNull(j+1, Types.NULL);
347 } else if (parms.get(j) instanceof SqlType) {
348 ps.setNull(j+1, ((SqlType)parms.get(j)).getDataType());
349 } else if (parms.get(j) instanceof java.lang.Number) {
350 ps.setLong(j+1, ((java.lang.Number)parms.get(j)).longValue());
351 } else if (parms.get(j) instanceof java.lang.String) {
352 ps.setString(j+1, (java.lang.String)parms.get(j));
353 } else if (parms.get(j) instanceof java.sql.Date) {
354 ps.setDate(j+1, (java.sql.Date)parms.get(j));
355 } else if (parms.get(j) instanceof java.util.Date) {
356 ps.setDate(j+1, new java.sql.Date(((java.util.Date)parms.get(j)).getTime()));
357 } else if (parms.get(j) instanceof Object) {
358 // This may not work depending on the RDBMS handling of Objects
359 ps.setObject(j+1, parms.get(j));
360 } else {
361 String type = parms.get(j).getClass().getName() ;
362 logger.debug("Unknown parameter type supplied for UPDATE: " + type) ;
363 throw new DBException ("Unknown parameter type supplied for UPDATE: " + type) ;
364 }
365 }
366
367 // If appropriate write out trace information before executing
368 logger.debug("<SQL_SUMMARY> About to execute SQL: \n" + sql);
369 logger.debug("<SQL_VERBOSE> ... with parameters: ") ;
370 for (int j = 0 ; j < parms.size() ;j++) {
371 logger.debug("<SQL_VERBOSE> ... parm" + String.valueOf(j+1) + ": " + parms.get(j)) ;
372 }
373 rowsAffected = ps.executeUpdate();
374
375 // Print any warnings chained to statement object
376 SQLWarning warning = ps.getWarnings();
377 while(warning != null){
378 logger.warn("<SQL Warning :>" + warning.toString());
379 warning = warning.getNextWarning() ;
380 }
381
382 }
383 catch (SQLException sqle) {
384 int errCode = sqle.getErrorCode();
385 logger.error("\n ========== Database error encountered code = " + String.valueOf(errCode) + "==========");
386 logger.error(sqle);
387 logger.error("SQL executed was: " + sql);
388 throw new DBException(sqle.getMessage());
389 }
390 catch (Exception e) {
391 logger.fatal(e.getClass().getName() + ":" + e.getMessage());
392 throw new DBException(e.getMessage());
393 }
394 finally {
395 try { ps.close(); } catch (Exception e) {}
396 }
397
398 logger.debug("<EXIT>");
399 return rowsAffected;
400 }
401 /**
402 * Execute the supplied stored procedure against the supplied
403 * database connection.
404 *
405 * @param conn
406 * The database connection to use
407 *
408 * @param sp
409 * A string containing the stored procedure name
410 *
411 * @param parms
412 * A <code>java.util.List</code> of String parameters for the stored procedure
413 *
414 * @return
415 * The number of rows affected by the stored procedure executed
416 *
417 * @throws DBException
418 * If execution of the stored procedure failed.
419 */
420 public static int executeDmlProcedure(Connection conn, String sp, List parms)
421 throws DBException
422 {
423 logger.debug("METHOD_ENTRY");
424
425 List resultList = null;
426 CallableStatement cs = null;
427 int i ;
428
429 try {
430 int j ;
431
432 StringBuffer spCall = new StringBuffer("{call " + sp + "(") ;
433
434 if (parms == null) {
435 parms = new ArrayList();
436 }
437
438 for (j = 0 ; j < parms.size() ;j++) {
439 spCall.append("?,") ;
440 }
441 // remove the trailing comma
442 StringBuffer spCall2 = new StringBuffer(spCall.substring(0, spCall.length()-1)) ;
443 spCall2.append(")}") ;
444 cs = conn.prepareCall(spCall2.toString());
445
446 for (j = 0 ; j < parms.size() ;j++) {
447 if (parms.get(j) instanceof java.lang.Long) {
448 cs.setLong(j+1, ((java.lang.Long)parms.get(j)).longValue());
449 } else if (parms.get(j) instanceof java.lang.String) {
450 cs.setString(j+1, (java.lang.String)parms.get(j));
451 } else if (parms.get(j) instanceof Integer) {
452 cs.setInt(j+1, ((Integer)parms.get(j)).intValue());
453 } else if (parms.get(j) instanceof Object) {
454 // This may not work depending on the RDBMS handling of Objects
455 cs.setObject(j+1, parms.get(j));
456 } else {
457 logger.debug("Unknown parameter type supplied for stored procedure") ;
458 throw new DBException ("Unknown parameter type supplied for stored procedure") ;
459 }
460 }
461
462 // if appropriate write out trace information before executing SQL
463 logger.debug("<SQL_SUMMARY> About to execute stored procedure: " + sp);
464 logger.debug("<SQL_VERBOSE> ... with parameters: ") ;
465 for (j = 0 ; j < parms.size() ;j++) {
466 if (parms.get(j)!=null) {
467 logger.debug("<SQL_VERBOSE> ... " + parms.get(j).getClass().getName() + " parm" + String.valueOf(j+1) + ": " +
468 parms.get(j).toString()) ;
469 } else {
470 logger.debug("<SQL_VERBOSE> ... parm" + String.valueOf(j+1) + ": null") ;
471 }
472 }
473
474 i=cs.executeUpdate();
475
476 // Print any warnings chained to statement object
477 SQLWarning warning = cs.getWarnings();
478 while(warning != null) {
479 logger.warn("<SQL Warning>" + warning.toString());
480 warning = warning.getNextWarning() ;
481 }
482
483 }
484 catch (SQLException sqle) {
485 int errCode = sqle.getErrorCode();
486 logger.error("========== Database error encountered: code = " + String.valueOf(errCode) + " ==========");
487 logger.error(sqle);
488 logger.error("Stored procedure executed was: " + sp);
489 throw new DBException(sqle.getMessage());
490 } catch (Exception e) {
491 logger.fatal(e.getMessage());
492 throw new DBException(e.getMessage());
493 } finally {
494 try {
495 cs.close() ;
496 cs = null ;
497 } catch (Exception e) {}
498 }
499
500 logger.debug("<EXIT>");
501 return i ;
502 }
503
504 /**
505 * Execute the supplied stored procedure against the supplied
506 * database connection.
507 *
508 * @param conn
509 * The database connection to use
510 *
511 * @param sp
512 * A string containing the stored procedure name
513 *
514 * @param parms
515 * A <code>java.util.List</code> of String parameters for the stored procedure
516 *
517 * @param returnType
518 * One of the defined return types defined as constants within this class.
519 *
520 * @return
521 * <code>java.lang.Object</code> containing any rows found.
522 * The <code>Object</code> type is defined by the return type parameter
523 *
524 * @throws DBException
525 * If execution of the stored procedure failed.
526 */
527 public static java.lang.Object executeQueryProcedure(Connection conn, String sp, List parms, int returnType) throws DBException
528 {
529 logger.debug("METHOD_ENTRY");
530
531 java.lang.Object o = null ;
532 //List resultList = null;
533 CallableStatement cs = null;
534 boolean results = false ;
535 java.sql.ResultSet rs = null ;
536
537 try {
538 int j ;
539
540 StringBuffer spCall = new StringBuffer("{?=call " + sp + "(") ;
541 if (parms == null) {
542 parms = new ArrayList();
543 }
544 for (j = 0 ; j < parms.size() ;j++) {
545 spCall.append("?,") ;
546 }
547 // remove the trailing comma
548 StringBuffer spCall2 = new StringBuffer(spCall.substring(0, spCall.length()-1)) ;
549 spCall2.append(")}") ;
550
551 logger.debug(spCall2.toString()) ;
552 cs = conn.prepareCall(spCall2.toString());
553
554 for (j = 0 ; j < parms.size() ;j++) {
555 if (parms.get(j) instanceof java.lang.String) {
556 cs.setString(j+2, (java.lang.String)parms.get(j));
557 } else if (parms.get(j) instanceof java.lang.Long) {
558 cs.setLong(j+2, ((java.lang.Long)parms.get(j)).longValue());
559 } else {
560 logger.debug("Unknown parameter type supplied for stored procedure") ;
561 throw new DBException ("Unknown parameter type supplied for stored procedure") ;
562 }
563 }
564
565 // if appropriate write out trace information before executing SQL
566 logger.debug("<SQL_SUMMARY> About to execute stored procedure: " + sp);
567
568 logger.debug("<SQL_VERBOSE> ... with parameters: ") ;
569 for (j = 0 ; j < parms.size() ;j++) {
570 logger.debug("<SQL_VERBOSE> ... parm" + String.valueOf(j+1) + ": " + parms.get(j).toString()) ;
571 }
572
573 rs=cs.executeQuery();
574
575 if (rs == null) {
576 logger.debug("<SQL_VERBOSE> failed to get result set object ");
577 }
578 // Do any object conversions required
579 switch (returnType) {
580 case 1:
581 List resultList = DBUtils.convertResultToList(rs) ;
582 o = resultList;
583 break;
584 case 2:
585 throw new DBException("Attempt to use deprecated functionality that has been compiled out of this version") ;
586 default :
587 o = rs ;
588 }
589
590 // Print any warnings chained to statement object
591 SQLWarning warning = cs.getWarnings();
592 while(warning != null) {
593 logger.warn("<SQL Warning>" + warning.toString());
594 warning = warning.getNextWarning() ;
595 }
596
597 }
598 catch (SQLException sqle) {
599 int errCode = sqle.getErrorCode();
600 logger.error("========== Database error encountered: code = " + String.valueOf(errCode) + " ==========");
601 logger.error(sqle);
602 logger.error("Stored procedure executed was: " + sp);
603 throw new DBException(sqle.getMessage());
604 }
605 catch (Exception e) {
606 logger.fatal(e.getMessage());
607 throw new DBException(e.getMessage());
608 }
609 finally {
610 try { cs.close(); } catch (Exception e) {}
611 }
612
613 logger.debug("<EXIT>");
614 return o ;
615 }
616
617 /*
618 * Return types that may be used by the query methods
619 */
620 /** Valid Object type for result set returns */
621 public static final int LIST = 1;
622
623 /**
624 * Valid Object type for result set returns.
625 * When returning references to a <code>java.sql.ResultSet</code> the developer
626 * must take care to ensure that the reference and the <code>Connection</code>
627 * that created it are correctly closed to avoid a memory leak.
628 */
629 public static final int SQL_RESULT_SET = 3;
630
631 /**
632 * The Log4J <code>Category</code> doing the logging.
633 * Same <code>Category</code> is used throughout the library.
634 */
635 protected static Category logger = Category.getInstance(DatabaseProxy.class);
636
637 /**
638 * CVS info about this class and its current version
639 */
640 public static final String about = "$Id: DatabaseProxy.java,v 1.4 2003/02/02 07:39:42 TimAndVicky Exp $" ;
641
642 }
643