001 // Copyright (c) 2001 Hursh Jain (http://www.mollypages.org)
002 // The Molly framework is freely distributable under the terms of an
003 // MIT-style license. For details, see the molly pages web site at:
004 // http://www.mollypages.org/. Use, modify, have fun !
005
006 package fc.jdbc;
007
008 import java.io.*;
009 import java.util.*;
010 import java.sql.*;
011 import javax.servlet.*;
012
013 import fc.io.*;
014 import fc.web.*;
015 import fc.util.*;
016
017 /**
018 Various JDBC utility methods.
019 <p>
020 @author hursh jain
021 **/
022 public final class QueryUtil
023 {
024 private static long txID = 1;
025
026 private QueryUtil() { /*no construction*/ }
027
028 static Log log = Log.get("fc.web.servlet.QueryUtil");
029
030 /**
031 This method can be called to initialize a logger for this class (else
032 a default logger will used).
033
034 @param logger Sets the log to which methods in this class
035 will send logging output.
036 **/
037 public static final void init(SystemLog logger) {
038 log = logger;
039 }
040
041 /**
042 I don't think this is needed for anything.
043
044 Often foreign keys are cascaded to null when the referenced key is deleted.
045 Many times keys are integral types; the JDBC getInt(..) method returns a
046 integer with value 0 when the actual value is null (since primitive java
047 types cannot be null). When saving a previously retrieved record with some
048 key value of 0, we would like to save that key back as null in the
049 database.
050 <p>
051 This method examines the given keyvalue and if it is 0, sets the value in
052 the specified position as null (otherwise sets it to the specified value).
053 <p>
054 This does imply a record creation convention whereby keys never have the
055 value of 0 in normal circumstances.
056 <p>
057 We could alternatively use object types such as Integer (and not primitive
058 types). This would have the advantage of natively being capable of being
059 null. However, that has the big disadvantage of making html form/GUI code
060 more complicated.
061
062 public static final void setNullableKey(PreparedStatement pstmt,
063 int pos, int keyvalue)
064 throws SQLException
065 {
066 if (keyvalue == 0) {
067 pstmt.setObject(pos, null);
068 }
069 else {
070 pstmt.setInt(pos, keyvalue);
071 }
072 }
073 */
074
075 /**
076 Returns a montonically increasing number starting from 1. This is true for
077 a given JVM invocation, this value will start from 1 again the next time
078 the JVM is invoked.
079 **/
080 public static final long getNewTxID() {
081 synchronized (QueryUtil.class) {
082 return txID++;
083 }
084 }
085
086 /**
087 Returns the dbname corresponding that the database for the specified
088 jdbc connection. Useful for writing database specific code as/when applicable.
089 A similar method is also available in {@link ConnectionMgr}
090 */
091 public static DBName getDBName(final Connection con) throws SQLException
092 {
093 final DatabaseMetaData md = con.getMetaData();
094 return DBName.fromDriver(md.getDriverName());
095 }
096
097 /**
098 Checks to see if the specified result set is scrollable.
099
100 @throws SQLException if the specified result set is <b>not</b>
101 scrollable.
102 **/
103 public static final void ensureScrollable(ResultSet rs)
104 throws SQLException
105 {
106 if (rs.getType() == ResultSet.TYPE_FORWARD_ONLY) {
107 throw new SQLException("Specified ResultSet not scrollable; need scrollable to proceed. [ResultSet=" + rs + "]");
108 }
109 }
110 /**
111 Checks to see if actual row count was same as expected row count, for some
112 query. Takes 2 integers as parameters and simply sees if the first equals
113 the second.
114
115 @param rowcount the actual count (returned by executeUpdate() etc.)
116 @param expected the expected count
117 @throws SQLException if the actual and expected counts don't match
118 **/
119 public static final void ensureCount(int rowcount, int expected)
120 throws SQLException
121 {
122 if (rowcount != expected) {
123 throw new SQLException("row count mismatch, recieved [" + rowcount + "], expected [" + expected + "]");
124 }
125 }
126
127 /**
128 Returns the number of rows in the specified ResultSet. If the ResultSet is
129 of type {@link ResultSet#TYPE_SCROLL_INSENSITIVE}, then this method moves
130 the result set pointer back to the beginning, after it is finished. If the
131 result set is not scroll insensitive, then this method will still work
132 properly, but the contents of the result set will not be usable again
133 (since it cannot be rewinded).
134 <p>
135 <b>Note</b>: the count can also be retreived directly (and more
136 efficiently) for many queries by including the <tt>COUNT()</tt> SQL
137 function as part of the query (in which case one would read the returned
138 count column directly and NOT call this method)
139
140 @param rset the result set to examine
141 @return the number of rows in the rowset
142 **/
143 public static final long getRowCount(ResultSet rset)
144 throws SQLException
145 {
146 int rowcount = 0;
147
148 if (rset != null)
149 {
150 int type = rset.getType();
151 if (type == ResultSet.TYPE_FORWARD_ONLY)
152 {
153 while (rset.next()) {
154 rowcount++;
155 }
156 }
157 else { //scrollable
158 rset.last(); //note, afterlast() will return 0 for getRow
159 rowcount = rset.getRow();
160 rset.beforeFirst();
161 }
162 }
163 return rowcount;
164 }
165
166 /**
167 Gets the last inserted id, typically auto-increment(mysql) or serial
168 (postgresql) columns. Since auto increment values can be integers or longs,
169 this method always returns it's value as a long. The caller can narrow this
170 down to an int if that's what's defined in the database.
171
172 @param con the connection to get the last id for. Both
173 mysql and postgresql (and probably other db's) treat the
174 last inserted id as a per connection value (associated with
175 the last statement on that connection that returned such a
176 value).
177 @param dbname the name of the target database. Needed because
178 this must be implemented in a database specific
179 way.
180 @param info optional further info needed to implement this
181 method for some databases. This currently is:
182 <blockquote>
183 <ul>
184 <li>mysql: not needed, specify <tt>null</tt>
185 <li>postgresql: specify the name of the sequence, which
186 for serial columns is (by default)
187 <tt>tablename_colname_seq</tt>, for example
188 <tt>"tablefoo_myserialid_seq"</tt>
189 </blockquote>
190
191 @throws SQLException if the last insert id could not be found
192 or if some other datbase problem occurred.
193 */
194 public static final long getLastInsertID(
195 Connection con, DBName dbname, Object info)
196 throws SQLException
197 {
198 //at some point we can refactor this into separate classes
199 //for each db but that's overkill for us now
200
201 if (dbname == DBName.mysql) {
202 String query = "select last_insert_id()";
203 Statement stmt = con.createStatement();
204 ResultSet rs = stmt.executeQuery(query);
205 boolean found = rs.next();
206 if (! found) throw new SQLException("No last inserted id returned");
207 return rs.getLong(1);
208 }
209 if (dbname == DBName.postgres) {
210 if (! (info instanceof String)) throw new SQLException("postgres requires the info parameter as a String");
211 String query = "select currval('" + info + "')";
212 Statement stmt = con.createStatement();
213 ResultSet rs = stmt.executeQuery(query);
214 boolean found = rs.next();
215 if (! found) throw new SQLException("No last inserted id returned");
216 return rs.getLong(1);
217 }
218 else {
219 throw new SQLException("Method not yet implemented for database of type: " + dbname);
220 }
221 }
222
223 /**
224 Convenience method that calls {@link #executeQuery(Connection, String, boolean)}
225 specifying no header options (i.e., column headers are not returned as part
226 of the query results).
227
228 @param con the database connection to use.
229 @param query the query to be performed
230 @param headers if true, the first row contains the column headers
231 **/
232 public static final List executeQuery(
233 Connection con, String query) throws SQLException
234 {
235 return executeQuery(con, query, false);
236 }
237
238 /**
239 Performs the specified query and returns a <tt>List</tt> (the result of
240 converting the ResultSet via the {@link #rsToList} method).
241 <p>
242 <b>Important Note</b>: this method is useful for program generated queries,
243 but should not be used for queries where unknown data could be send by a
244 malicious user (since the query string is sent as-is to the server). For
245 secure queries, use PreparedStatements instead.
246
247 @param con the database connection to use.
248 @param query the query to be peformed
249 @param headers if true, the first row contains the column headers
250 **/
251 public static final List executeQuery(
252 Connection con, String query, boolean headers)
253 throws SQLException
254 {
255 Statement stmt = con.createStatement();
256 ResultSet rs = stmt.executeQuery(query);
257 List list = rsToList(rs, headers);
258 return list;
259 }
260
261 /**
262 Peforms the specified query and returns true if the query has
263 only one row of data.
264
265 @param con the database connection to use.
266 **/
267 public static final boolean hasExactlyOneRow(
268 Connection con, String query) throws SQLException
269 {
270 Statement stmt = con.createStatement();
271 ResultSet rs = stmt.executeQuery(query);
272 boolean valid = getRowCount(rs) == 1;
273 return valid;
274 }
275
276 /**
277 Peforms the specified query and returns true if the query returns
278 no data.
279
280 @param con the database connection to use.
281 **/
282 public static final boolean hasExactlyZeroRows(
283 Connection con, String query) throws SQLException
284 {
285 Statement stmt = con.createStatement();
286 ResultSet rs = stmt.executeQuery(query);
287 boolean valid = getRowCount(rs) == 0;
288 return valid;
289 }
290
291 /**
292 Creates a new connection that will return ResultSet's of
293 <tt>TYPE_SCROLL_INSENSITIVE</tt> and <tt>CONCUR_READ_ONLY</tt>.
294 **/
295 public static final Statement getRewindableStmt(Connection con)
296 throws SQLException
297 {
298 Statement stmt = con.createStatement(
299 ResultSet.TYPE_SCROLL_INSENSITIVE,
300 ResultSet.CONCUR_READ_ONLY);
301 return stmt;
302 }
303
304 /**
305 Creates a new connection that will return ResultSet's of
306 <tt>TYPE_SCROLL_INSENSITIVE</tt> and <tt>CONCUR_READ_ONLY</tt>. (i.e., the
307 PreparedStatement returned by this method should be rewindable).
308 <p>
309 Note, by default a java.sql.Connection.prepareStatment(String) method
310 returns statements that support ResultSet's of forward_only. This means we
311 will not be able to determine the row count via the {@link #getRowCount}
312 and <b>also</b> be able to then rewind the ResultSet and read it's
313 contents.
314 <p>
315 **/
316 public static final PreparedStatement
317 getRewindablePreparedStmt(Connection con, String sql)
318 throws SQLException
319 {
320 PreparedStatement stmt = con.prepareStatement(
321 sql,
322 ResultSet.TYPE_SCROLL_INSENSITIVE,
323 ResultSet.CONCUR_READ_ONLY);
324 return stmt;
325 }
326
327 /**
328 Starts a transaction on the specified connection. The
329 connection is set to <i>not</i> autocommit any statements from
330 here onwards and the {@link #endTransaction endTransaction}
331 method must be called to end this transaction.
332 <p>
333 If the transaction cannot be started, the connection is closed.
334
335 @param con the connection to commit
336 @param transactionIsolation the transaction isolation level
337 for this transaction
338 @param message commit description; shown when debug
339 logging is turned on
340
341 @return <tt>true</tt> if the transaction was started successfully,
342 <tt>false</tt> if the transaction could not start for some reason.
343
344 @throws IllegalArgumentException if the transaction isolation level is not a valid value (as defined in {@link java.sql.Connection}
345 */
346 public static final boolean startTransaction(Connection con, int transactionIsolation, String message)
347 {
348 switch (transactionIsolation)
349 {
350 case Connection.TRANSACTION_NONE:
351 case Connection.TRANSACTION_READ_COMMITTED:
352 case Connection.TRANSACTION_READ_UNCOMMITTED:
353 case Connection.TRANSACTION_REPEATABLE_READ:
354 case Connection.TRANSACTION_SERIALIZABLE:
355 break;
356 default:
357 throw new IllegalArgumentException("The specfied transaction isolation level " + transactionIsolation + " is not a valid value");
358 }
359 try {
360 con.setTransactionIsolation(transactionIsolation);
361 }
362 catch (Exception e) {
363 log.error("Could not set transaction isolation;", IOUtil.throwableToString(e));
364 return false;
365 }
366
367 return startTransaction(con, message);
368 }
369
370 /**
371 Starts a transaction on the specified connection. The connection is set to
372 not autocommit any statements from here onwards and the {@link
373 #endTransaction} method must be called to end this transaction. The
374 transaction isolation is whatever the default transaction isolation is for
375 this connection, driver or database (this method does not explicitly set
376 the isolation level). See {@link #startTransaction(Connection, int,
377 String)}.
378 <p>
379 If the transaction cannot be started, the connection is closed.
380
381 @param con the connection to commit
382 @param message commit description; shown if debug logging
383 is turned on
384
385 @return <tt>true</tt> if the transaction was started successfully,
386 <tt>false</tt> if the transaction could not start for some reason.
387 */
388 public static final boolean startTransaction(Connection con, String message)
389 {
390 boolean result = true;
391
392 try {
393 con.setAutoCommit(false);
394 }
395 catch (Exception e)
396 {
397 log.error(message, "Could not set autoCommit(false) on this connection; Transaction will not be started and the connection will be closed. ", con, IOUtil.throwableToString(e));
398 try {
399 con.close();
400 }
401 catch (Exception e2) {
402 log.error(message, "Connection does not allow autoCommit(false) and connetion cannot even be closed. ", con, IOUtil.throwableToString(e2));
403 }
404 result = false;
405 }
406
407 log.bug(message, "/START: Transaction [Isolation Level=", getTransactionLevelString(con), "];", con);
408 return result;
409 }
410
411
412 private static final String t_str = "*** ";
413
414 /**
415 Calls {@link startTransaction(Connection, String) startTransaction} with
416 a empty message string.
417 */
418 public static final boolean startTransaction(Connection con)
419 {
420 return startTransaction(con, t_str);
421 }
422
423 /**
424 Aborts (rolls back) the current transaction on the specified connection.
425 <p>
426 After the transaction is rolled back, the connection is set to
427 autoCommit(true).
428
429 @param con the connection to rollback
430 @param message a description; shown if logging is turned on
431
432 @return <tt>true</tt> if the transaction was rolledback successful,
433 <tt>false</tt> if the transaction rollback for some reason.
434 **/
435 public static final boolean abortTransaction(Connection con, String message)
436 {
437 boolean result = true;
438
439 try {
440 if (con.getAutoCommit() == true) //throws an Exception
441 {
442 log.error("Connection not in transaction, autoCommit is true. Did you call QueryUtil.startTransaction() on this connection ?", message, con);
443 return false;
444 }
445 }
446 catch (Exception e) {
447 log.error(IOUtil.throwableToString(e));
448 return false;
449 }
450
451 try {
452 con.rollback();
453 }
454 /*catch problems, exception, incl. all runtime related*/
455 catch (Throwable e)
456 {
457 result = false;
458 log.error(message, "*** Transaction could not be aborted/rolled back ***", IOUtil.throwableToString(e));
459 }
460 finally
461 {
462 try {
463 con.setAutoCommit(true);
464 }
465 catch (Exception e) {
466 log.error(message, "Could NOT reset connection to be autoCommit=true", con, IOUtil.throwableToString(e));
467 result = false;
468 }
469 }
470
471 if (log.canLog(Log.DEBUG))
472 log.bug(message, "/ABORT: Transaction rolled back [Time=", new java.util.Date(), "]; connection=", con);
473
474 return result;
475 } //~doCommit
476
477
478 /**
479 Calls {@link abortTransaction(Connection, String) abortTransaction} with
480 an empty message string.
481 */
482 public static final boolean abortTransaction(Connection con)
483 {
484 return abortTransaction(con, t_str);
485 }
486
487 /**
488 Commits the specified connection. Attemps a rollback if the
489 <tt>commit()</tt> call fails for some reason. This method should only be
490 called after all queries in the transaction have been sent to the
491 database (so if any of those fail, the entire transaction can be rolled
492 back).
493 <p>
494 After the transaction completes, the connection is set to
495 autoCommit(true).
496
497 @param con the connection to commit
498 @param message commit description; shown if logging is turned on
499
500 @return <tt>true</tt> if the transaction was committed successful,
501 <tt>false</tt> if the transaction failed for some reason.
502 **/
503 public static final boolean endTransaction(Connection con, String message)
504 {
505 boolean result = true;
506
507 try {
508 if (con.getAutoCommit() == true) //throws an Exception
509 {
510 log.error("Connection not in transaction, autoCommit is true. Did you prior call QueryUtil.startTransaction() on this connection ?", message, con);
511 return false;
512 }
513 }
514 catch (Exception e) {
515 log.error(IOUtil.throwableToString(e));
516 return false;
517 }
518
519 try {
520 con.commit();
521 }
522 /*catch problems, exception, incl. all runtime related*/
523 catch (Throwable e)
524 {
525 result = false;
526 log.error(message, "*** Transaction could not complete: attempting roll back ***", IOUtil.throwableToString(e));
527 try {
528 con.rollback();
529 }
530 catch (SQLException e2) {
531 log.error(message, "*** Transaction could not be rolled back ***", IOUtil.throwableToString(e2));
532 }
533 //throw e;
534 }
535 finally
536 {
537 try {
538 con.setAutoCommit(true);
539 }
540 catch (Exception e) {
541 log.error(message, "Could NOT reset connection to be autoCommit=true", con, IOUtil.throwableToString(e));
542 result = false;
543 }
544 }
545
546 log.bug(message, "/FINISH: Transaction completed. Connection=", con);
547
548 return result;
549 }
550
551 /**
552 Calls {@link endTransaction(Connection, String) endTransaction} with
553 an empty message string.
554 */
555 public static final boolean endTransaction(Connection con)
556 {
557 return endTransaction(con, t_str);
558 }
559
560 private static String getTransactionLevelString(Connection con)
561 {
562 try {
563 final int level = con.getTransactionIsolation();
564 switch (level)
565 {
566 case Connection.TRANSACTION_NONE:
567 return "TRANSACTION_NONE";
568 case Connection.TRANSACTION_READ_COMMITTED:
569 return "TRANSACTION_READ_COMMITTED";
570 case Connection.TRANSACTION_READ_UNCOMMITTED:
571 return "TRANSACTION_READ_UNCOMMITTED";
572 case Connection.TRANSACTION_REPEATABLE_READ:
573 return "TRANSACTION_REPEATABLE_READ";
574 case Connection.TRANSACTION_SERIALIZABLE:
575 return "TRANSACTION_SERIALIZABLE";
576 default:
577 return "Unknown level (not a legal value)";
578 }
579 }
580 catch (Exception e) {
581 log.error(IOUtil.throwableToString(e));
582 }
583 return "";
584 }
585
586
587 /**
588 Closes the specified connection, statement and resultset, logging any
589 errors to the stderr. Ignores any parameters with <tt>null</tt> values.
590 **/
591 public static final void close(ResultSet rs, Statement stmt, Connection con)
592 {
593 //objects _must_ be closed in the following order
594 if (rs != null)
595 try {
596 rs.close();
597 }
598 catch (Exception e) {
599 log.warn("", e);
600 }
601
602 if (stmt != null) try {
603 stmt.close();
604 }
605 catch (Exception e) {
606 log.warn("", e);
607 }
608
609 if (con != null) try {
610 con.close();
611 }
612 catch (Exception e) {
613 log.warn("", e);
614 }
615 }
616
617
618 /**
619 Closes the specified connection, ignoring any exceptions encountered
620 in the connection.close() method itself.
621 **/
622 public static final void close(Connection con)
623 {
624 close(null, null, con);
625 }
626
627
628 /**
629 Converts a java.sql.ResultSet into a List of Object[], where each Object[]
630 represents all the columns in one row. All column values are stored in the
631 Object[] via the getObject() method of the ResultSet. If the ResultSet is
632 empty, this method returns <tt>null</tt> (or returns only the headers if
633 headers are to be printed).
634
635 @param rs the ResultSet
636 @param headers if set to true, the first row of the returned List
637 will contain an Object[] of the column header names.
638 */
639 public static final List rsToList(
640 java.sql.ResultSet rs, boolean headers) throws SQLException
641 {
642 List list = new ArrayList();
643 ResultSetMetaData metadata = rs.getMetaData();
644 int numcols = metadata.getColumnCount();
645 Object[] arow = null;
646 int i;
647
648 if (headers)
649 {
650 // get column header info
651 arow = new Object[numcols];
652 for ( i = 1; i <= numcols; i++) {
653 arow[i-1] = metadata.getColumnLabel(i);
654 }
655 list.add(arow);
656 }
657
658 while (rs.next()) {
659 arow = new Object[numcols];
660 for (i=1; i <= numcols; i++) {
661 arow[i-1] = rs.getObject(i);
662 }
663 list.add(arow);
664 }
665
666 return list;
667 } //~rsToList()
668
669 /**
670 Converts the list returned by the {@link #rsToList method} to a String,
671 consisting of all the rows contained in the list. Each row is rendered
672 within brackets <tt>[..row1..]</tt>, with different rows seperated by
673 commas (<tt>[..row1..], [..row2..], ...</tt>) but this format may be
674 changed in the future.
675 **/
676 public static final String rsListToString(List list)
677 {
678 String result = "";
679 if (list == null) {
680 return result;
681 }
682 int size = list.size();
683 result += "Total records: " + size + IOUtil.LINE_SEP;
684 for (int n = 0; n < size; n++)
685 {
686 Object[] row = (Object[]) list.get(n);
687 int rowlen = row.length;
688 result += "[";
689 //1 row
690 for (int k= 0; k < rowlen; k++) {
691 result += row[k];
692 if ((k + 1) != rowlen) {
693 result += ", ";
694 }
695 }
696 //end 1 row
697 result += "]";
698 if ((n + 1) != size) {
699 result += ", ";
700 }
701 }
702 return result;
703 } //~rsListToString()
704
705
706 /**
707 Prints the given java.sql.ResultSet (including result set headers) in a
708 simple straightforward fashion to <tt>System.out</tt>
709
710 @param rs the result set to print
711 */
712 public static final void printRS(ResultSet rs)
713 throws SQLException
714 {
715 ResultSetMetaData metadata = rs.getMetaData();
716 int numcols = metadata.getColumnCount();
717
718 System.out.print("[Headers] ");
719 for (int i = 1; i <= numcols; i++) {
720 System.out.print(metadata.getColumnLabel(i));
721 if (i != numcols) System.out.print(", ");
722 }
723 System.out.println("");
724 int rowcount = 0;
725 while (rs.next())
726 {
727 System.out.print("[Row #" + ++rowcount + " ] ");
728 for(int n=1 ; n <= numcols; n++) {
729 Object obj = rs.getObject(n);
730 String str = (obj != null) ? obj.toString() : "null";
731 if (str.length() > 0)
732 System.out.print(str);
733 else
734 System.out.print("\"\"");
735 if (n != numcols) System.out.print(", ");
736 }
737 System.out.println("");
738 }
739 }
740
741
742 /**
743 Delegates to {@link printResultSetTable(ResultSet, PrintStream,
744 ResultSetPrintDirection, TablePrinter.PrintConfig, boolean)} so that the
745 table is printed {@link ResultSetPrintDirection.HORIZONTAL horizontally}
746 with the default table style and headers set to true.
747 **/
748 public static final void printResultSetTable(ResultSet rs, PrintStream ps)
749 throws SQLException
750 {
751 printResultSetTable(rs, ps, ResultSetPrintDirection.HORIZONTAL, null, true);
752 }
753
754 /**
755 Prints the specified result set in a tabular format. The printed table
756 style is according specified {@link TablePrinter.PrintConfig PrintConfig}
757 object.
758
759 @param rs the ResultSet
760 @param ps the destination print stream
761 @param direction the result set printing orientation.
762 @param config the printing configuration.
763 <b>Specify <tt>null</tt>for to use the
764 default style</b>.
765 @param headers <tt>true</tt> to print headers, <tt>false</tt> to omit
766 headers. Headers are obtained from the
767 ResultSet's Meta Data.
768 **/
769 public static final void printResultSetTable(
770 ResultSet rs, PrintStream ps, ResultSetPrintDirection direction,
771 TablePrinter.PrintConfig config, boolean headers)
772 throws SQLException
773 {
774 ResultSetMetaData metadata = rs.getMetaData();
775 int numcols = metadata.getColumnCount();
776 TablePrinter fprint = null;
777 if (config == null) {
778 config = new TablePrinter.PrintConfig();
779 config.setCellPadding(1);
780 }
781
782 if (direction == ResultSetPrintDirection.HORIZONTAL)
783 {
784 fprint = new TablePrinter(numcols, ps, config);
785 fprint.startTable();
786
787 //print headers
788 if (headers)
789 {
790 fprint.startRow();
791 for (int i = 1; i <= numcols; i++) {
792 fprint.printCell(metadata.getColumnLabel(i));
793 }
794 fprint.endRow();
795 }
796
797 while (rs.next()) {
798 fprint.startRow();
799 for (int i = 1; i <= numcols; i++) {
800 fprint.printCell(rs.getString(i));
801 }
802 fprint.endRow();
803 }
804 fprint.endTable();
805 }
806
807 else if (direction == ResultSetPrintDirection.VERTICAL)
808 {
809 if (rs.getType() == ResultSet.TYPE_FORWARD_ONLY) {
810 ps.println("QueryUtil.printResultSet(): Vertical print orientation requires a scrollable resultset");
811 return;
812 }
813
814 rs.last();
815 int rows = rs.getRow(); //number of row in rs == our columns
816 int printcols = (headers) ? rows + 1 : rows;
817
818 //we don't support this cause we don't know how many times
819 //the header will be repeated etc., and we need to know
820 //the exact column size (which equals number of rows in Vertical)
821 //before printing (and number of rows includes and extra headers
822 //possibly repeated
823 if (config.getHeader() != null) {
824 ps.println("QueryUtil.printResultSet(): Seperate headers not supported when using Vertical orientation. To print Result Set headers, specify 'true' for the 'header' parameter when invoking this method");
825 return;
826 }
827
828 fprint = new TablePrinter(printcols, ps, config);
829 rs.beforeFirst();
830
831 fprint.startTable();
832 for (int n = 1; n <= numcols; n++)
833 {
834 fprint.startRow();
835
836 if (headers) {
837 fprint.printCell(metadata.getColumnLabel(n));
838 }
839
840 while (rs.next()) {
841 fprint.printCell(rs.getString(n));
842 }
843
844 fprint.endRow();
845 rs.beforeFirst();
846 }
847 fprint.endTable();
848 }
849
850 else
851 ps.println("QueryUtil.printResultSet(): PrintConfig not understood");
852
853 }
854
855
856 //we have to implement this method identically twice --once
857 //for printstream, once for jspwriter
858 //because darn it, jspwriter is NOT a subclass of printwriter
859 //or printstream, freaking idiots designed jsp's
860
861 /**
862 Prints the given ResultSet as a HTML table to the specified JspWriter. The
863 ResultSet is transversed/printed based on the direction parameter (normal
864 output where each row is printed horizontally is specified via {@link
865 QueryUtil.ResultSetPrintDirection#HORIZONTAL}).
866 <p>
867 The output table has the following CSS styles added to it:
868 <ul>
869 <li>For the table: class <tt>QueryUtil_Table</tt>
870 <li>For the Header row: class <tt>QueryUtil_HeaderRow</tt>
871 <li>For the Header cells: class <tt>QueryUtil_HeaderCell</tt>
872 <li>For a normal row: class <tt>QueryUtil_Row</tt>
873 <li>For a normal cell: class <tt>QueryUtil_Cell</tt>
874 </ul>
875 **/
876 public static final void printResultSetHTMLTable(
877 ResultSet rs, javax.servlet.jsp.JspWriter out,
878 ResultSetPrintDirection direction) throws IOException, SQLException
879 {
880 boolean headers = true;
881 ResultSetMetaData metadata = rs.getMetaData();
882 int numcols = metadata.getColumnCount();
883
884 out.println("<table class=\"QueryUtil_Table\">");
885 if (direction == ResultSetPrintDirection.HORIZONTAL)
886 {
887 //print headers
888 if (headers)
889 {
890 out.print("<tr class=\"QueryUtil_HeaderRow\">");
891 for (int i = 1; i <= numcols; i++) {
892 out.print("<td class=\"QueryUtil_HeaderCell\">");
893 out.print(metadata.getColumnLabel(i));
894 out.print("</td>");
895 }
896 out.println("</tr>");
897 }
898
899 while (rs.next()) {
900 out.println("<tr class=\"QueryUtil_Row\">");
901 for (int i = 1; i <= numcols; i++) {
902 out.print("<td class=\"QueryUtil_Cell\">");
903 out.print(rs.getString(i));
904 out.print("</td>");
905 }
906 out.println("</tr>");
907 }
908 out.println("</table>");
909 }
910
911 else if (direction == ResultSetPrintDirection.VERTICAL)
912 {
913 if (rs.getType() == ResultSet.TYPE_FORWARD_ONLY) {
914 out.println("QueryUtil.printResultSet(): Vertical print orientation requires a scrollable resultset");
915 return;
916 }
917
918 rs.last();
919 int rows = rs.getRow(); //number of row in rs == our columns
920 int printcols = (headers) ? rows + 1 : rows;
921
922 rs.beforeFirst();
923
924 out.println("<table class=\"QueryUtil_Table\">");
925 for (int n = 1; n <= numcols; n++)
926 {
927 out.println("<tr>");
928
929 if (headers) {
930 out.print("<td class=\"QueryUtil_HeaderCell\">");
931 out.println(metadata.getColumnLabel(n));
932 out.println("</td>");
933 }
934
935 while (rs.next()) {
936 out.println("<td class=\"QueryUtil_Cell\">");
937 out.println(rs.getString(n));
938 out.println("</td>");
939 }
940
941 out.println("</tr>");
942 rs.beforeFirst();
943 }
944 out.println("</table>");
945 }
946
947 }
948
949
950 /**
951 Prints the given ResultSet as a HTML table to the specified PrintWriter.
952 The ResultSet is transversed/printed based on the direction parameter
953 (normal output where each row is printed horizontally is specified via
954 {@link QueryUtil.ResultSetPrintDirection#HORIZONTAL}).
955 <p>
956 The output table has the following CSS styles added to it:
957 <ul>
958 <li>For the table: class <tt>QueryUtil_Table</tt>
959 <li>For the Header row: class <tt>QueryUtil_HeaderRow</tt>
960 <li>For the Header cells: class <tt>QueryUtil_HeaderCell</tt>
961 <li>For a normal row: class <tt>QueryUtil_Row</tt>
962 <li>For a normal cell: class <tt>QueryUtil_Cell</tt>
963 </ul>
964 **/
965 public static final void printResultSetHTMLTable(
966 ResultSet rs, PrintWriter out,
967 ResultSetPrintDirection direction) throws IOException, SQLException
968 {
969 boolean headers = true;
970 ResultSetMetaData metadata = rs.getMetaData();
971 int numcols = metadata.getColumnCount();
972
973 out.println("<table class=\"QueryUtil_Table\">");
974 if (direction == ResultSetPrintDirection.HORIZONTAL)
975 {
976 //print headers
977 if (headers)
978 {
979 out.print("<tr class=\"QueryUtil_HeaderRow\">");
980 for (int i = 1; i <= numcols; i++) {
981 out.print("<td class=\"QueryUtil_HeaderCell\">");
982 out.print(metadata.getColumnLabel(i));
983 out.print("</td>");
984 }
985 out.println("</tr>");
986 }
987
988 while (rs.next()) {
989 out.println("<tr class=\"QueryUtil_Row\">");
990 for (int i = 1; i <= numcols; i++) {
991 out.print("<td class=\"QueryUtil_Cell\">");
992 out.print(rs.getString(i));
993 out.print("</td>");
994 }
995 out.println("</tr>");
996 }
997 out.println("</table>");
998 }
999
1000 else if (direction == ResultSetPrintDirection.VERTICAL)
1001 {
1002 if (rs.getType() == ResultSet.TYPE_FORWARD_ONLY) {
1003 out.println("QueryUtil.printResultSet(): Vertical print orientation requires a scrollable resultset");
1004 return;
1005 }
1006
1007 rs.last();
1008 int rows = rs.getRow(); //number of row in rs == our columns
1009 int printcols = (headers) ? rows + 1 : rows;
1010
1011 rs.beforeFirst();
1012
1013 out.println("<table class=\"QueryUtil_Table\">");
1014 for (int n = 1; n <= numcols; n++)
1015 {
1016 out.println("<tr>");
1017
1018 if (headers) {
1019 out.print("<td class=\"QueryUtil_HeaderCell\">");
1020 out.println(metadata.getColumnLabel(n));
1021 out.println("</td>");
1022 }
1023
1024 while (rs.next()) {
1025 out.println("<td class=\"QueryUtil_Cell\">");
1026 out.println(rs.getString(n));
1027 out.println("</td>");
1028 }
1029
1030 out.println("</tr>");
1031 rs.beforeFirst();
1032 }
1033 out.println("</table>");
1034 }
1035 }
1036
1037
1038 /**
1039 Specifies the orientation of the result set when printed (via
1040 methods like {@link printResultSet()}).
1041 **/
1042 public static final class ResultSetPrintDirection
1043 {
1044 /**
1045 The result set will be printed in the typical table format
1046 with the columns going across the page and the rows going
1047 downwards.
1048 **/
1049 public static final ResultSetPrintDirection HORIZONTAL =
1050 new ResultSetPrintDirection("QueryUtil.ResultSetPrintDirection.HORIZONTAL");
1051
1052 /**
1053 The result set will be printed with columns going downwards
1054 and rows going across the page.
1055 **/
1056 public static final ResultSetPrintDirection VERTICAL =
1057 new ResultSetPrintDirection("QueryUtil.ResultSetPrintDirection.VERTICAL");
1058
1059 private String name;
1060 private ResultSetPrintDirection(String name) {
1061 this.name = name;
1062 }
1063 public String toString() {
1064 return name;
1065 }
1066 } //~ResultSetPrintDirection
1067
1068 /* this is not really needed for anything so taken out
1069
1070 //Used to specify the printing options (used by methods that print
1071 //result sets for example). Printed fields and records are simply seperated
1072 //by field and record seperators respectively.
1073
1074 public static class SimplePrintConfig
1075 {
1076 The newline for this JVM's platform. Used to separate horizontal
1077 records by default.
1078 public static final String NEWLINE = IOUtil.LINE_SEP;
1079
1080 Used to separate vertical records by default.
1081 public static final String COMMA = ",";
1082
1083 private String fieldsep = COMMA;
1084 private String recsep = NEWLINE;
1085 private String name;
1086
1087 public SimplePrintConfig() {
1088 }
1089
1090 Sets the string used to separate records. The default value is:
1091 {@link #NEWLINE}.
1092 public void setRecordSeperator(String str) {
1093 recsep = str;
1094 }
1095
1096 Sets the string used to separate fields. The default value is:
1097 {@link #COMMA}.
1098 public void setFieldSeperator(String str) {
1099 fieldsep = str;
1100 }
1101
1102 public String getRecordSeperator() {
1103 return recsep;
1104 }
1105
1106 public String getFieldSeperator() {
1107 return fieldsep;
1108 }
1109
1110 Prints a short description of this object. Field and record
1111 seperators containing control characters are printed as
1112 readable equivalents.
1113 public String toString() {
1114 return "SimplePrintConfig: Field seperator=" +
1115 StringUtil.viewableAscii(fieldsep) +
1116 "; Record seperator=" +
1117 StringUtil.viewableAscii(recsep);
1118 }
1119
1120 } //~SimplePrintConfig
1121
1122 */
1123
1124 } //~class QueryUtil