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