001: /* Copyright (C) 2003 Finalist IT Group
002: *
003: * This file is part of JAG - the Java J2EE Application Generator
004: *
005: * JAG is free software; you can redistribute it and/or modify
006: * it under the terms of the GNU General Public License as published by
007: * the Free Software Foundation; either version 2 of the License, or
008: * (at your option) any later version.
009: * JAG is distributed in the hope that it will be useful,
010: * but WITHOUT ANY WARRANTY; without even the implied warranty of
011: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
012: * GNU General Public License for more details.
013: * You should have received a copy of the GNU General Public License
014: * along with JAG; if not, write to the Free Software
015: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
016: */
017:
018: package com.finalist.jaggenerator;
019:
020: import org.apache.commons.logging.Log;
021: import org.apache.commons.logging.LogFactory;
022:
023: import java.util.ArrayList;
024: import java.util.HashMap;
025: import java.util.List;
026: import java.util.Collections;
027: import java.sql.Connection;
028: import java.sql.ResultSet;
029: import java.sql.SQLException;
030: import java.sql.DatabaseMetaData;
031:
032: /**
033: * This class is a refactoring mid-step. The original code contained duplicate methods for database access
034: * in different classes - I've rehoused them here.
035: *
036: * @author Michael O'Connor, Rudie Ekkelenkamp - Finalist IT Group
037: */
038: public class DatabaseUtils {
039: static Log log = LogFactory.getLog(DatabaseUtils.class);
040:
041: //a map of table name (String) --> ArrayList of Column objects.
042: private static final HashMap columnsCache = new HashMap();
043:
044: //a list of table names (String)
045: private static ArrayList tablesCache;
046:
047: //I think this can be refactored out, once getPrimaryKeys() reference in JagGenerator is removed..
048: private static final HashMap pkCache = new HashMap();
049: private static final String TABLE_NAME = "TABLE_NAME";
050: private static final String[] DEFAULT_TABLE_TYPES = new String[] { "TABLE" };
051:
052: /**
053: * Gets all columns in the specified table, setting up a database connection if one isn't already available.
054: *
055: * @param tablename the name of the table.
056: * @return an ArrayList of Column objects for all columns in the specified table,
057: * or <code>null</code> if the table/column doesn't exist.
058: */
059: public static ArrayList getColumns(String tablename) {
060: return getColumns(tablename, true);
061: }
062:
063: /**
064: * Gets all columns in the specified table.
065: *
066: * @param tablename the name of the table.
067: * @param forceConnection set to <code>true</code> if this method should force a database connect,
068: * if not already connected.
069: * @return an ArrayList of Column objects for all columns in the specified table;
070: * or <code>null</code> if the table/column doesn't exist, or if no database connection was available and
071: * <code>forceConnection</code> was set to <code>false</code>.
072: */
073: public static ArrayList getColumns(String tablename,
074: boolean forceConnection) {
075: if (columnsCache.get(tablename) != null) {
076: return (ArrayList) columnsCache.get(tablename);
077: }
078:
079: if (!forceConnection && JagGenerator.getConManager() == null) {
080: return null;
081: }
082: ArrayList pkeys = getPrimaryKeys(tablename);
083: GenericJdbcManager conManager = JagGenerator.getConManager();
084: Connection con = null;
085: ArrayList list = new ArrayList();
086: try {
087: con = conManager.connect();
088: DatabaseMetaData meta = con.getMetaData();
089: ResultSet columns = meta.getColumns(null, conManager
090: .getSchema(), tablename, "%");
091: Column c = null;
092: while (columns.next()) {
093: c = new Column();
094: switch (columns.getInt("NULLABLE")) {
095: case DatabaseMetaData.columnNullable:
096: c.setNullable(true);
097: break;
098: case DatabaseMetaData.columnNoNulls:
099: c.setNullable(false);
100: break;
101: case DatabaseMetaData.columnNullableUnknown:
102: c.setNullable(false);
103: default:
104: c.setNullable(true);
105: }
106:
107: c.setName(columns.getString("COLUMN_NAME"));
108: if (pkeys.contains(columns.getString("COLUMN_NAME"))) {
109: c.setPrimaryKey(true);
110: } else {
111: c.setPrimaryKey(false);
112: }
113:
114: c.setLength(columns.getInt("COLUMN_SIZE"));
115: c.setPrecision(columns.getInt("COLUMN_SIZE"));
116: c.setScale(columns.getInt("DECIMAL_DIGITS"));
117: c.setSqlType(columns.getString("TYPE_NAME"));
118: list.add(c);
119: }
120: columns.close();
121:
122: } catch (Exception e) {
123: e.printStackTrace();
124: } finally {
125: if (con != null) {
126: try {
127: con.close();
128: } catch (SQLException e) {
129: }
130: }
131: }
132:
133: columnsCache.put(tablename, list);
134: return list;
135: }
136:
137: /**
138: * Gets information about any foreign keys that are imported into the specified table.
139: *
140: * @param tablename
141: * @return a List of ForeignKey objects, never <code>null</code>..
142: */
143: public static List getForeignKeys(String tablename) {
144: log.debug("Get the foreign keys for table: " + tablename);
145: ArrayList fkeys = new ArrayList();
146: GenericJdbcManager conManager = JagGenerator.getConManager();
147: if (conManager == null) {
148: JagGenerator
149: .logToConsole("Can't retrieve foreign keys - no database connection!");
150: } else {
151: Connection con = null;
152: try {
153: con = conManager.connect();
154: ResultSet foreignKeys = con.getMetaData()
155: .getImportedKeys("", conManager.getSchema(),
156: tablename);
157:
158: while (foreignKeys.next()) {
159: ForeignKey fk = new ForeignKey();
160: try {
161: fk.setPkTableCat(foreignKeys
162: .getString("PKTABLE_CAT"));
163: } catch (Exception e) {
164: // Ignore, in case the JDBC driver doesn't support this propery.
165: }
166: try {
167: fk.setPkTableSchem(foreignKeys
168: .getString("PKTABLE_SCHEM"));
169: } catch (Exception e) {
170: // Ignore, in case the JDBC driver doesn't support this propery.
171: }
172: try {
173: fk.setPkTableName(foreignKeys
174: .getString("PKTABLE_NAME"));
175: } catch (Exception e) {
176: // Ignore, in case the JDBC driver doesn't support this propery.
177: }
178: try {
179:
180: fk.setPkColumnName(foreignKeys
181: .getString("PKCOLUMN_NAME"));
182: } catch (Exception e) {
183: // Ignore, in case the JDBC driver doesn't support this propery.
184: }
185: try {
186: fk.setFkTableCat(foreignKeys
187: .getString("FKTABLE_CAT"));
188: } catch (Exception e) {
189: // Ignore, in case the JDBC driver doesn't support this propery.
190: }
191: try {
192:
193: fk.setFkTableSchem(foreignKeys
194: .getString("FKTABLE_SCHEM"));
195: } catch (Exception e) {
196: // Ignore, in case the JDBC driver doesn't support this propery.
197: }
198: try {
199:
200: fk.setFkTableName(foreignKeys
201: .getString("FKTABLE_NAME"));
202: } catch (Exception e) {
203: // Ignore, in case the JDBC driver doesn't support this propery.
204: }
205: try {
206:
207: fk.setFkColumnName(foreignKeys
208: .getString("FKCOLUMN_NAME"));
209: } catch (Exception e) {
210: // Ignore, in case the JDBC driver doesn't support this propery.
211: }
212: try {
213:
214: fk.setKeySeq(foreignKeys.getShort("KEY_SEQ"));
215: } catch (Exception e) {
216: // Ignore, in case the JDBC driver doesn't support this propery.
217: }
218: try {
219: fk.setUpdateRule(foreignKeys
220: .getShort("UPDATE_RULE"));
221: } catch (Exception e) {
222: // Ignore, in case the JDBC driver doesn't support this propery.
223: }
224: try {
225:
226: fk.setDeleteRule(foreignKeys
227: .getShort("DELETE_RULE"));
228: } catch (Exception e) {
229: // Ignore, in case the JDBC driver doesn't support this propery.
230: }
231: try {
232:
233: fk.setPkName(foreignKeys.getString("PK_NAME"));
234: } catch (Exception e) {
235: // Ignore, in case the JDBC driver doesn't support this propery.
236: }
237: try {
238:
239: fk.setDeferrability(foreignKeys
240: .getShort("DEFERRABILITY"));
241: } catch (Exception e) {
242: // Ignore, in case the JDBC driver doesn't support this propery.
243: }
244: // Log the tables and columns from which relations are generated.
245: log.debug("Foreign key table and column name: "
246: + fk.getFkTableName() + " - "
247: + fk.getFkColumnName());
248: log.debug("foreign table and pk column name: "
249: + fk.getPkTableName() + " - "
250: + fk.getPkColumnName());
251:
252: // Since the fk name is not set, we use the column name to set it.s
253: fk.setFkName(Utils.format(fk.getFkColumnName()));
254: fkeys.add(fk);
255: }
256: } catch (Exception e) {
257: e.printStackTrace();
258: } finally {
259: if (con != null) {
260: try {
261: con.close();
262: } catch (SQLException e) {
263: }
264: }
265: }
266: }
267:
268: return fkeys;
269: }
270:
271: /**
272: * A list with Strings of all primary key fields.
273: *
274: * @param tablename
275: * @return an ArrayList of primary key column names for the specified table, never <code>null</code>.
276: * @todo make this private - all primary key work should be done in this class.
277: */
278: public static ArrayList getPrimaryKeys(String tablename) {
279: if (pkCache.get(tablename) != null) {
280: return (ArrayList) pkCache.get(tablename);
281: }
282: GenericJdbcManager conManager = JagGenerator.getConManager();
283: Connection con = null;
284: ArrayList pkeys = new ArrayList();
285: try {
286: con = conManager.connect();
287: ResultSet r = con.getMetaData().getPrimaryKeys(null,
288: conManager.getSchema(), tablename);
289: while (r.next()) {
290: pkeys.add(r.getString("COLUMN_NAME"));
291: }
292: } catch (Exception e) {
293: e.printStackTrace();
294: } finally {
295: if (con != null) {
296: try {
297: con.close();
298: } catch (SQLException e) {
299: }
300: }
301: }
302:
303: pkCache.put(tablename, pkeys);
304: return pkeys;
305: }
306:
307: /**
308: * Grabs the list of tables from the database.
309: *
310: * @return a List of table names (String), never <code>null</code>.
311: */
312: public static ArrayList getTables() {
313: if (tablesCache == null) {
314: tablesCache = new ArrayList();
315: GenericJdbcManager conManager = JagGenerator
316: .getConManager();
317: String[] displayTableTypes = conManager
318: .getDisplayTableTypes();
319: if (displayTableTypes == null) {
320: displayTableTypes = DEFAULT_TABLE_TYPES;
321: }
322: ResultSet tables = null;
323: Connection con = null;
324: try {
325: con = conManager.connect();
326: // Look for tables of the type table, not for synonyms: ,"SYNONYM"
327: ResultSet schemas = con.getMetaData().getSchemas();
328: while (schemas.next()) {
329: // Do nothing.
330: }
331: tables = con.getMetaData().getTables(null,
332: conManager.getSchema(), "%", displayTableTypes);
333: while (tables.next()) {
334: // Iterate over the tablenames and get the tablenames.
335: String tableName = tables.getString(TABLE_NAME);
336: if (tableName != null) {
337: tablesCache.add(tableName);
338: }
339: }
340: } catch (Exception e) {
341: e.printStackTrace();
342: JagGenerator.logToConsole("Error getting tables list: "
343: + e.toString());
344: } finally {
345: if (tables != null)
346: try {
347: tables.close();
348: } catch (Exception e) {
349: }
350:
351: if (con != null)
352: try {
353: con.close();
354: } catch (SQLException e) {
355: }
356: }
357: }
358: if (tablesCache != null)
359: Collections.sort(tablesCache);
360: return tablesCache;
361: }
362:
363: /**
364: * This needs to be called when databases are switched.
365: */
366: public static void clearCache() {
367: tablesCache = null;
368: }
369:
370: /**
371: * Forces an update of a particular table's columns the next time they are required.
372: *
373: * @param tableName
374: */
375: public static void clearColumnsCacheForTable(String tableName) {
376: columnsCache.remove(tableName);
377: }
378:
379: }
|