001: /* ====================================================================
002: * The QueryForm License, Version 1.1
003: *
004: * Copyright (c) 1998 - 2003 David F. Glasser. All rights
005: * reserved.
006: *
007: * Redistribution and use in source and binary forms, with or without
008: * modification, are permitted provided that the following conditions
009: * are met:
010: *
011: * 1. Redistributions of source code must retain the above copyright
012: * notice, this list of conditions and the following disclaimer.
013: *
014: * 2. Redistributions in binary form must reproduce the above copyright
015: * notice, this list of conditions and the following disclaimer in
016: * the documentation and/or other materials provided with the
017: * distribution.
018: *
019: * 3. The end-user documentation included with the redistribution,
020: * if any, must include the following acknowledgment:
021: * "This product includes software developed by
022: * David F. Glasser."
023: * Alternately, this acknowledgment may appear in the software itself,
024: * if and wherever such third-party acknowledgments normally appear.
025: *
026: * 4. The names "QueryForm" and "David F. Glasser" must
027: * not be used to endorse or promote products derived from this
028: * software without prior written permission. For written
029: * permission, please contact dglasser@pobox.com.
030: *
031: * 5. Products derived from this software may not be called "QueryForm",
032: * nor may "QueryForm" appear in their name, without prior written
033: * permission of David F. Glasser.
034: *
035: * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
036: * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
037: * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
038: * DISCLAIMED. IN NO EVENT SHALL DAVID F. GLASSER, THE APACHE SOFTWARE
039: * FOUNDATION OR ITS CONTRIBUTORS, OR ANY AUTHORS OR DISTRIBUTORS
040: * OF THIS SOFTWARE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
041: * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
042: * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
043: * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
044: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
045: * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
046: * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
047: * SUCH DAMAGE.
048: * ====================================================================
049: *
050: * This product includes software developed by the
051: * Apache Software Foundation (http://www.apache.org/).
052: *
053: * ====================================================================
054: *
055: * $Source: /cvsroot/qform/qform/src/org/glasser/sql/DBUtil.java,v $
056: * $Revision: 1.3 $
057: * $Author: dglasser $
058: * $Date: 2005/04/29 05:02:45 $
059: *
060: * --------------------------------------------------------------------
061: */
062: package org.glasser.sql;
063:
064: import java.sql.*;
065: import java.util.*;
066:
067: public class DBUtil implements java.io.Serializable {
068:
069: public static class COLUMN {
070:
071: public final static int TABLE_CAT = 1;
072: public final static int TABLE_SCHEM = 2;
073: public final static int TABLE_NAME = 3;
074: public final static int COLUMN_NAME = 4;
075: public final static int DATA_TYPE = 5;
076: public final static int TYPE_NAME = 6;
077: public final static int COLUMN_SIZE = 7;
078: public final static int BUFFER_LENGTH = 8;
079: public final static int DECIMAL_DIGITS = 9;
080: public final static int NUM_PREC_RADIX = 10;
081: public final static int NULLABLE = 11;
082: public final static int REMARKS = 12;
083: public final static int COLUMN_DEF = 13;
084: public final static int SQL_DATA_TYPE = 14;
085: public final static int SQL_DATETIME_SUB = 15;
086: public final static int CHAR_OCTET_LENGTH = 16;
087: public final static int ORDINAL_POSITION = 17;
088: public final static int IS_NULLABLE = 18;
089:
090: }
091:
092: public static Column[] getColumns(ResultSet rs) throws SQLException {
093: ArrayList columns = new ArrayList();
094:
095: while (rs.next()) {
096:
097: Column col = new Column();
098: col.setTableCatalog(rs.getString(COLUMN.TABLE_CAT));
099: col.setTableSchema(rs.getString(COLUMN.TABLE_SCHEM));
100: col.setTableName(rs.getString(COLUMN.TABLE_NAME));
101: col.setColumnName(rs.getString(COLUMN.COLUMN_NAME));
102: col.setDataType(rs.getInt(COLUMN.DATA_TYPE));
103: col.setTypeName(rs.getString(COLUMN.TYPE_NAME));
104: col.setColumnSize(rs.getInt(COLUMN.COLUMN_SIZE));
105: col.setDecimalDigits(rs.getInt(COLUMN.DECIMAL_DIGITS));
106: col.setRadix(rs.getInt(COLUMN.NUM_PREC_RADIX));
107: col.setNullable(DatabaseMetaData.columnNoNulls != rs
108: .getInt(COLUMN.NULLABLE));
109: col.setRemarks(rs.getString(COLUMN.REMARKS));
110: col.setColumnDefaultValue(rs.getString(COLUMN.COLUMN_DEF));
111: col.setOrdinal(rs.getInt(COLUMN.ORDINAL_POSITION));
112: columns.add(col);
113: }
114:
115: return (Column[]) columns.toArray(new Column[columns.size()]);
116:
117: }
118:
119: // 1 TABLE_CAT String => table catalog (may be null)
120: // 2 TABLE_SCHEM String => table schema (may be null)
121: // 3 TABLE_NAME String => table name
122: // 4 TABLE_TYPE String => table type. Typical types are "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
123: // 5 REMARKS String => explanatory comment on the table
124: // 6 TYPE_CAT String => the types catalog (may be null)
125: // 7 TYPE_SCHEM String => the types schema (may be null)
126: // 8 TYPE_NAME String => type name (may be null)
127: // 9 SELF_REFERENCING_COL_NAME String => name of the designated "identifier" column of a typed table (may be null)
128: // 10 REF_GENERATION String => specifies how values in SELF_REFERENCING_COL_NAME are created. Values are "SYSTEM", "USER", "DERIVED". (may be null)
129: //
130:
131: public static TableInfo[] getTableInfos(ResultSet rs)
132: throws SQLException {
133:
134: ArrayList list = new ArrayList(100);
135: while (rs.next()) {
136: TableInfo ti = new TableInfo();
137: ti.setTableCat(rs.getString("TABLE_CAT"));
138: ti.setTableSchem(rs.getString(2));
139: ti.setTableName(rs.getString(3));
140: ti.setTableType(rs.getString(4));
141: ti.setRemarks(rs.getString(5));
142: list.add(ti);
143: }
144:
145: return (TableInfo[]) list.toArray(new TableInfo[list.size()]);
146:
147: }
148:
149: public static HashMap getTableInfoLists(TableInfo[] tables,
150: String defaultSchemaString) {
151:
152: // first sort the list of tables by their name so the individual
153: // lists will be sorted.
154: // Arrays.sort(tables, TableInfo.NAME_COMPARATOR);
155:
156: HashMap map = new HashMap();
157:
158: // if there are now tables in the list, we'll create
159: // an empty list for the default schema
160: if (tables == null || tables.length == 0) {
161: map.put(defaultSchemaString, new Vector());
162: }
163: for (int j = 0; j < tables.length; j++) {
164: TableInfo ti = tables[j];
165: String schema = ti.getTableSchem();
166: if (schema == null)
167: schema = defaultSchemaString;
168: Vector v = (Vector) map.get(schema);
169: if (v == null) {
170: v = new Vector();
171: map.put(schema, v);
172: }
173: v.add(ti);
174: // System.out.println(ti.debugString());
175: }
176:
177: return map;
178: }
179:
180: public static class FK {
181: public final static int PKTABLE_CAT = 1;
182: public final static int PKTABLE_SCHEM = 2;
183: public final static int PKTABLE_NAME = 3;
184: public final static int PKCOLUMN_NAME = 4;
185: public final static int FKTABLE_CAT = 5;
186: public final static int FKTABLE_SCHEM = 6;
187: public final static int FKTABLE_NAME = 7;
188: public final static int FKCOLUMN_NAME = 8;
189: public final static int KEY_SEQ = 9;
190: public final static int UPDATE_RULE = 10;
191: public final static int DELETE_RULE = 11;
192: public final static int FK_NAME = 12;
193: public final static int PK_NAME = 13;
194: public final static int DEFERRABILITY = 14;
195:
196: }
197:
198: public static ForeignKey[] getForeignKeys(ResultSet fk)
199: throws SQLException {
200: Hashtable fKeys = new Hashtable();
201: while (fk != null && fk.next()) {
202:
203: // we're supposed to read the result set in column-order
204: // to insure portability.
205: String foreignTableSchema = fk.getString(FK.PKTABLE_SCHEM);
206: String foreignTableName = fk.getString(FK.PKTABLE_NAME);
207: String foreignColumnName = fk.getString(FK.PKCOLUMN_NAME);
208: String localTableSchema = fk.getString(FK.FKTABLE_SCHEM);
209: String localTableName = fk.getString(FK.FKTABLE_NAME);
210: String localColumnName = fk.getString(FK.FKCOLUMN_NAME);
211: int keySeq = fk.getInt(FK.KEY_SEQ);
212: String fkName = fk.getString(FK.FK_NAME);
213:
214: // the actual name of the foreign key ("FK_NAME") is the better item to use as a hashkey,
215: // however, the API docs for getImportedKeys() says that might be null.
216: // if it is null, we'll use the foreignTableName, which might produce incorrect results
217: // when a table has more than one foreign key into another table.
218: String hashkey = fkName;
219: if (fkName == null)
220: hashkey = foreignTableName;
221:
222: // see if we've already found a component of this table's key.
223: ForeignKey fkey = (ForeignKey) fKeys.get(hashkey);
224:
225: // if not, create a new ForeignKey object and add it to the
226: // hashtable
227: if (fkey == null) {
228: fkey = new ForeignKey(foreignTableName);
229: fkey.setForeignKeyName(fkName);
230: fkey.setLocalTableSchema(localTableSchema);
231: fkey.setLocalTableName(localTableName);
232: fkey.setForeignTableSchema(foreignTableSchema);
233: fkey.setForeignTableName(foreignTableName);
234: fKeys.put(hashkey, fkey);
235: }
236:
237: // now create a ForeignKeyColumn object for the column we're
238: // examining
239: ForeignKeyColumn fcol = new ForeignKeyColumn();
240: fcol.setLocalTableSchema(localTableSchema);
241: fcol.setLocalTableName(localTableName);
242: fcol.setLocalColumnName(localColumnName);
243: fcol.setForeignTableSchema(foreignTableSchema);
244: fcol.setForeignTableName(foreignTableName);
245: fcol.setForeignColumnName(foreignColumnName);
246:
247: // add it to this fk's collection of columns.
248: fkey.addForeignKeyColumn(fcol);
249:
250: }
251:
252: ForeignKey[] results = new ForeignKey[fKeys.size()];
253: Iterator i = fKeys.values().iterator();
254: for (int j = 0; j < results.length; j++) {
255: results[j] = (ForeignKey) i.next();
256: }
257:
258: return results;
259:
260: }
261:
262: /**
263: * This reads each row of a ResultSet and stores it in a HashMap object, with the
264: * column names, force to upper case, used as keys for each value. This will break
265: * if a ResultSet contains duplicate column names.
266: */
267: public static Map[] readResultSet(ResultSet rs) throws SQLException {
268: return readResultSet(rs, -1);
269:
270: }
271:
272: /**
273: * This reads each row of a ResultSet and stores it in a HashMap object, with the
274: * column names, forced to upper case, used as keys for each value. This will break
275: * if a ResultSet contains duplicate column names.
276: *
277: * @param rs the ResultSet that will be read. It will NOT be closed after being read.
278: * @param maxRows the maximum number of rows to read; 0 or below indicates no limit.
279: */
280: public static Map[] readResultSet(ResultSet rs, int maxRows)
281: throws SQLException {
282:
283: // handle the trival case of an empty ResultSet quickly.
284: boolean flag = rs.next();
285:
286: // if ResultSet is empty, return a zero-length array.
287: if (flag == false)
288: return new HashMap[0];
289:
290: boolean hasLimit = maxRows > 0;
291:
292: ResultSetMetaData rsmd = rs.getMetaData();
293: int numColumns = rsmd.getColumnCount();
294: String[] columnNames = new String[numColumns];
295: for (int j = 0; j < numColumns; j++) {
296: // column indexes are 1-based.
297: String columnName = rsmd.getColumnName(j + 1);
298: if (columnName != null)
299: columnName = columnName.toUpperCase();
300: columnNames[j] = columnName;
301: }
302:
303: ArrayList results = new ArrayList(40);
304:
305: // the first time the loop is entered, rs.next() won't get
306: // called because the flag will short-circuit it.
307: int rowsRead = 0;
308: while (flag || rs.next()) {
309: flag = false;
310:
311: HashMap map = new HashMap(columnNames.length + 10);
312:
313: for (int j = 0; j < columnNames.length; j++) {
314: map.put(columnNames[j], rs.getObject(j + 1));
315: }
316:
317: results.add(map);
318: if (hasLimit) {
319: rowsRead++;
320: if (rowsRead >= maxRows) {
321: break;
322: }
323: }
324:
325: }
326:
327: return (HashMap[]) results.toArray(new HashMap[results.size()]);
328:
329: }
330:
331: /**
332: * This reads each row of a ResultSet and stores it in an List object, with each element
333: * of the list representing the corresponding column from the ResultSet row.
334: */
335: public static List[] readResultSet2(ResultSet rs)
336: throws SQLException {
337: return readResultSet2(rs, -1);
338:
339: }
340:
341: /**
342: * This reads each row of a ResultSet and stores it in an List object, with each element
343: * of the list representing the corresponding column from the ResultSet row.
344: *
345: * @param rs the ResultSet that will be read. It will NOT be closed after being read.
346: * @param maxRows the maximum number of rows to read; 0 or below indicates no limit.
347: */
348: public static List[] readResultSet2(ResultSet rs, int maxRows)
349: throws SQLException {
350:
351: // handle the trival case of an empty ResultSet quickly.
352: boolean flag = rs.next();
353:
354: // if ResultSet is empty, return a zero-length array.
355: if (flag == false)
356: return new ArrayList[0];
357:
358: boolean hasLimit = maxRows > 0;
359:
360: ResultSetMetaData rsmd = rs.getMetaData();
361: int numColumns = rsmd.getColumnCount();
362:
363: ArrayList results = new ArrayList(40);
364:
365: // the first time the loop is entered, rs.next() won't get
366: // called because the flag will short-circuit it.
367: int rowsRead = 0;
368: while (flag || rs.next()) {
369: flag = false;
370:
371: ArrayList rowList = new ArrayList(numColumns);
372:
373: for (int j = 0; j < numColumns; j++) {
374: rowList.add(rs.getObject(j + 1));
375: }
376:
377: results.add(rowList);
378: if (hasLimit) {
379: rowsRead++;
380: if (rowsRead >= maxRows) {
381: break;
382: }
383: }
384:
385: }
386:
387: return (ArrayList[]) results.toArray(new ArrayList[results
388: .size()]);
389:
390: }
391:
392: /**
393: * This reads each row of a ResultSet and stores it in an List object, with each element
394: * of the list representing the corresponding column from the ResultSet row.
395: */
396: public static Object[] readResultSet3(ResultSet rs)
397: throws SQLException {
398: return readResultSet3(rs, -1);
399:
400: }
401:
402: /**
403: * This reads each row of a ResultSet and stores it in an List object, with each element
404: * of the list representing the corresponding column from the ResultSet row.
405: *
406: * @param rs the ResultSet that will be read. It will NOT be closed after being read.
407: * @param maxRows the maximum number of rows to read; 0 or below indicates no limit.
408: */
409: public static Object[] readResultSet3(ResultSet rs, int maxRows)
410: throws SQLException {
411:
412: // handle the trival case of an empty ResultSet quickly.
413: boolean flag = rs.next();
414:
415: // if ResultSet is empty, return a zero-length array.
416: if (flag == false)
417: return new Object[0];
418:
419: boolean hasLimit = maxRows > 0;
420:
421: ArrayList results = new ArrayList(40);
422:
423: // the first time the loop is entered, rs.next() won't get
424: // called because the flag will short-circuit it.
425: int rowsRead = 0;
426: while (flag || rs.next()) {
427: flag = false;
428: results.add(rs.getObject(1));
429:
430: if (hasLimit) {
431: rowsRead++;
432: if (rowsRead >= maxRows) {
433: break;
434: }
435: }
436:
437: }
438:
439: return results.toArray();
440:
441: }
442:
443: public static void closeConnection(Connection conn) {
444: if (conn == null)
445: return;
446: try {
447: conn.close();
448: } catch (Exception ex) {
449: ex.printStackTrace();
450: }
451: }
452:
453: public static void rollback(Connection conn) {
454: if (conn == null)
455: return;
456: try {
457: conn.rollback();
458: } catch (Exception ex) {
459: ex.printStackTrace();
460: }
461: }
462:
463: public static void setAutoCommit(Connection conn, boolean autoCommit) {
464: if (conn == null)
465: return;
466: try {
467: conn.setAutoCommit(autoCommit);
468: } catch (Exception ex) {
469: ex.printStackTrace();
470: }
471: }
472:
473: public static void closeResultSet(ResultSet rs) {
474: if (rs == null)
475: return;
476: try {
477: rs.close();
478: } catch (Exception ex) {
479: ex.printStackTrace();
480: }
481: }
482:
483: public static void closeStatement(Statement s) {
484: if (s == null)
485: return;
486: try {
487: s.close();
488: } catch (Exception ex) {
489: ex.printStackTrace();
490: }
491: }
492:
493: private static final int[] CHAR_TYPES = { java.sql.Types.CHAR,
494: java.sql.Types.VARCHAR, java.sql.Types.LONGVARCHAR };
495:
496: private static final int[] NUMERIC_TYPES = { java.sql.Types.BIT,
497: java.sql.Types.TINYINT, java.sql.Types.SMALLINT,
498: java.sql.Types.INTEGER, java.sql.Types.BIGINT,
499: java.sql.Types.FLOAT, java.sql.Types.REAL,
500: java.sql.Types.DOUBLE, java.sql.Types.NUMERIC,
501: java.sql.Types.DECIMAL };
502:
503: private final static int[] DATETIME_TYPES = { java.sql.Types.DATE,
504: java.sql.Types.TIME, java.sql.Types.TIMESTAMP };
505:
506: private static final int[] BINARY_TYPES = { java.sql.Types.BINARY,
507: java.sql.Types.BLOB, java.sql.Types.DISTINCT,
508: java.sql.Types.JAVA_OBJECT, java.sql.Types.LONGVARBINARY,
509: java.sql.Types.NULL, java.sql.Types.OTHER,
510: java.sql.Types.STRUCT, java.sql.Types.VARBINARY };
511:
512: private static final int[] DISPLAYABLE_TYPES = new int[NUMERIC_TYPES.length
513: + CHAR_TYPES.length + DATETIME_TYPES.length];
514:
515: static {
516:
517: // DISPLAYABLE_TYPES
518:
519: System.arraycopy(NUMERIC_TYPES, 0, DISPLAYABLE_TYPES, 0,
520: NUMERIC_TYPES.length);
521: System.arraycopy(CHAR_TYPES, 0, DISPLAYABLE_TYPES,
522: NUMERIC_TYPES.length, CHAR_TYPES.length);
523: System.arraycopy(DATETIME_TYPES, 0, DISPLAYABLE_TYPES,
524: NUMERIC_TYPES.length + CHAR_TYPES.length,
525: DATETIME_TYPES.length);
526:
527: Arrays.sort(DISPLAYABLE_TYPES);
528: Arrays.sort(NUMERIC_TYPES);
529: Arrays.sort(BINARY_TYPES);
530: Arrays.sort(CHAR_TYPES);
531: Arrays.sort(DATETIME_TYPES);
532:
533: }
534:
535: private static boolean arrayContains(int[] array, int number) {
536:
537: // a linear search is used instead of a binary
538: // search because the list is so small that the
539: // division operations needed by the binary sort
540: // would probably negate any gains.
541: for (int j = 0; j < array.length; j++) {
542:
543: if (number == array[j])
544: return true;
545: if (number < array[j])
546: return false;
547: }
548:
549: return false;
550: }
551:
552: public static boolean isBinaryType(int sqlType) {
553:
554: return arrayContains(BINARY_TYPES, sqlType);
555: }
556:
557: public static boolean isNumericType(int sqlType) {
558:
559: return arrayContains(NUMERIC_TYPES, sqlType);
560: }
561:
562: public static boolean isCharType(int sqlType) {
563:
564: return arrayContains(CHAR_TYPES, sqlType);
565: }
566:
567: public static boolean isDateTimeType(int sqlType) {
568:
569: return arrayContains(DATETIME_TYPES, sqlType);
570: }
571:
572: public static boolean isDisplayableType(int sqlType) {
573:
574: return arrayContains(DISPLAYABLE_TYPES, sqlType);
575: }
576:
577: public static String escape(String s) {
578: if (s.indexOf('\'') < 0)
579: return s;
580: StringBuffer buffer = new StringBuffer(s.length() + 10);
581: char[] characters = s.toCharArray();
582: for (int j = 0; j < characters.length; j++) {
583: if (characters[j] == '\'')
584: buffer.append('\'');
585: buffer.append(characters[j]);
586: }
587: return buffer.toString();
588: }
589:
590: }
|