KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > finalist > jaggenerator > DatabaseUtils


1 /* Copyright (C) 2003 Finalist IT Group
2  *
3  * This file is part of JAG - the Java J2EE Application Generator
4  *
5  * JAG is free software; you can redistribute it and/or modify
6  * it under the terms of the GNU General Public License as published by
7  * the Free Software Foundation; either version 2 of the License, or
8  * (at your option) any later version.
9  * JAG is distributed in the hope that it will be useful,
10  * but WITHOUT ANY WARRANTY; without even the implied warranty of
11  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12  * GNU General Public License for more details.
13  * You should have received a copy of the GNU General Public License
14  * along with JAG; if not, write to the Free Software
15  * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
16  */
17
18 package com.finalist.jaggenerator;
19
20 import org.apache.commons.logging.Log;
21 import org.apache.commons.logging.LogFactory;
22
23 import java.util.ArrayList JavaDoc;
24 import java.util.HashMap JavaDoc;
25 import java.util.List JavaDoc;
26 import java.util.Collections JavaDoc;
27 import java.sql.Connection JavaDoc;
28 import java.sql.ResultSet JavaDoc;
29 import java.sql.SQLException JavaDoc;
30 import java.sql.DatabaseMetaData JavaDoc;
31
32 /**
33  * This class is a refactoring mid-step. The original code contained duplicate methods for database access
34  * in different classes - I've rehoused them here.
35  *
36  * @author Michael O'Connor, Rudie Ekkelenkamp - Finalist IT Group
37  */
38 public class DatabaseUtils {
39    static Log log = LogFactory.getLog(DatabaseUtils.class);
40
41    //a map of table name (String) --> ArrayList of Column objects.
42 private static final HashMap JavaDoc columnsCache = new HashMap JavaDoc();
43
44    //a list of table names (String)
45 private static ArrayList JavaDoc tablesCache;
46
47    //I think this can be refactored out, once getPrimaryKeys() reference in JagGenerator is removed..
48 private static final HashMap JavaDoc pkCache = new HashMap JavaDoc();
49    private static final String JavaDoc TABLE_NAME = "TABLE_NAME";
50    private static final String JavaDoc[] DEFAULT_TABLE_TYPES = new String JavaDoc[]{"TABLE"};
51
52
53    /**
54     * Gets all columns in the specified table, setting up a database connection if one isn't already available.
55     *
56     * @param tablename the name of the table.
57     * @return an ArrayList of Column objects for all columns in the specified table,
58     * or <code>null</code> if the table/column doesn't exist.
59     */
60    public static ArrayList JavaDoc getColumns(String JavaDoc tablename) {
61       return getColumns(tablename, true);
62    }
63
64    /**
65     * Gets all columns in the specified table.
66     *
67     * @param tablename the name of the table.
68     * @param forceConnection set to <code>true</code> if this method should force a database connect,
69     * if not already connected.
70     * @return an ArrayList of Column objects for all columns in the specified table;
71     * or <code>null</code> if the table/column doesn't exist, or if no database connection was available and
72     * <code>forceConnection</code> was set to <code>false</code>.
73     */
74    public static ArrayList JavaDoc getColumns(String JavaDoc tablename, boolean forceConnection) {
75       if (columnsCache.get(tablename) != null) {
76          return (ArrayList JavaDoc) columnsCache.get(tablename);
77       }
78
79       if (!forceConnection && JagGenerator.getConManager() == null) {
80          return null;
81       }
82       ArrayList JavaDoc pkeys = getPrimaryKeys(tablename);
83       GenericJdbcManager conManager = JagGenerator.getConManager();
84       Connection JavaDoc con = null;
85       ArrayList JavaDoc list = new ArrayList JavaDoc();
86       try {
87          con = conManager.connect();
88          DatabaseMetaData JavaDoc meta = con.getMetaData();
89          ResultSet JavaDoc columns = meta.getColumns(null, conManager.getSchema(), tablename, "%");
90          Column c = null;
91          while (columns.next()) {
92             c = new Column();
93             switch (columns.getInt("NULLABLE")) {
94                case DatabaseMetaData.columnNullable:
95                   c.setNullable(true);
96                   break;
97                case DatabaseMetaData.columnNoNulls:
98                   c.setNullable(false);
99                   break;
100                case DatabaseMetaData.columnNullableUnknown:
101                   c.setNullable(false);
102                default:
103                   c.setNullable(true);
104             }
105
106             c.setName(columns.getString("COLUMN_NAME"));
107             if (pkeys.contains(columns.getString("COLUMN_NAME"))) {
108                c.setPrimaryKey(true);
109             } else {
110                c.setPrimaryKey(false);
111             }
112
113             c.setLength(columns.getInt("COLUMN_SIZE"));
114             c.setPrecision(columns.getInt("COLUMN_SIZE"));
115             c.setScale(columns.getInt("DECIMAL_DIGITS"));
116             c.setSqlType(columns.getString("TYPE_NAME"));
117             list.add(c);
118          }
119          columns.close();
120
121       } catch (Exception JavaDoc e) {
122          e.printStackTrace();
123       } finally {
124          if (con != null) {
125             try {
126                con.close();
127             } catch (SQLException JavaDoc e) {
128             }
129          }
130       }
131
132       columnsCache.put(tablename, list);
133       return list;
134    }
135
136    /**
137     * Gets information about any foreign keys that are imported into the specified table.
138     *
139     * @param tablename
140     * @return a List of ForeignKey objects, never <code>null</code>..
141     */
142    public static List JavaDoc getForeignKeys(String JavaDoc tablename) {
143       log.debug("Get the foreign keys for table: " + tablename);
144       ArrayList JavaDoc fkeys = new ArrayList JavaDoc();
145       GenericJdbcManager conManager = JagGenerator.getConManager();
146       if (conManager == null) {
147          JagGenerator.logToConsole("Can't retrieve foreign keys - no database connection!");
148       } else {
149          Connection JavaDoc con = null;
150          try {
151             con = conManager.connect();
152             ResultSet JavaDoc foreignKeys = con.getMetaData().getImportedKeys("", conManager.getSchema(), tablename);
153
154             while (foreignKeys.next()) {
155                ForeignKey fk = new ForeignKey();
156                try {
157                   fk.setPkTableCat(foreignKeys.getString("PKTABLE_CAT"));
158                } catch (Exception JavaDoc e) {
159                   // Ignore, in case the JDBC driver doesn't support this propery.
160 }
161                try {
162                   fk.setPkTableSchem(foreignKeys.getString("PKTABLE_SCHEM"));
163                } catch (Exception JavaDoc e) {
164                   // Ignore, in case the JDBC driver doesn't support this propery.
165 }
166                try {
167                   fk.setPkTableName(foreignKeys.getString("PKTABLE_NAME"));
168                } catch (Exception JavaDoc e) {
169                   // Ignore, in case the JDBC driver doesn't support this propery.
170 }
171                try {
172
173                   fk.setPkColumnName(foreignKeys.getString("PKCOLUMN_NAME"));
174                } catch (Exception JavaDoc e) {
175                   // Ignore, in case the JDBC driver doesn't support this propery.
176 }
177                try {
178                   fk.setFkTableCat(foreignKeys.getString("FKTABLE_CAT"));
179                } catch (Exception JavaDoc e) {
180                   // Ignore, in case the JDBC driver doesn't support this propery.
181 }
182                try {
183
184                   fk.setFkTableSchem(foreignKeys.getString("FKTABLE_SCHEM"));
185                } catch (Exception JavaDoc e) {
186                   // Ignore, in case the JDBC driver doesn't support this propery.
187 }
188                try {
189
190                   fk.setFkTableName(foreignKeys.getString("FKTABLE_NAME"));
191                } catch (Exception JavaDoc e) {
192                   // Ignore, in case the JDBC driver doesn't support this propery.
193 }
194                try {
195
196                   fk.setFkColumnName(foreignKeys.getString("FKCOLUMN_NAME"));
197                } catch (Exception JavaDoc e) {
198                   // Ignore, in case the JDBC driver doesn't support this propery.
199 }
200                try {
201
202                   fk.setKeySeq(foreignKeys.getShort("KEY_SEQ"));
203                } catch (Exception JavaDoc e) {
204                   // Ignore, in case the JDBC driver doesn't support this propery.
205 }
206                try {
207                   fk.setUpdateRule(foreignKeys.getShort("UPDATE_RULE"));
208                } catch (Exception JavaDoc e) {
209                   // Ignore, in case the JDBC driver doesn't support this propery.
210 }
211                try {
212
213                   fk.setDeleteRule(foreignKeys.getShort("DELETE_RULE"));
214                } catch (Exception JavaDoc e) {
215                   // Ignore, in case the JDBC driver doesn't support this propery.
216 }
217                try {
218
219                   fk.setPkName(foreignKeys.getString("PK_NAME"));
220                } catch (Exception JavaDoc e) {
221                   // Ignore, in case the JDBC driver doesn't support this propery.
222 }
223                try {
224
225                   fk.setDeferrability(foreignKeys.getShort("DEFERRABILITY"));
226                } catch (Exception JavaDoc e) {
227                   // Ignore, in case the JDBC driver doesn't support this propery.
228 }
229                // Log the tables and columns from which relations are generated.
230 log.debug("Foreign key table and column name: " + fk.getFkTableName() + " - " + fk.getFkColumnName());
231                log.debug("foreign table and pk column name: " + fk.getPkTableName() + " - " + fk.getPkColumnName());
232
233                // Since the fk name is not set, we use the column name to set it.s
234 fk.setFkName(Utils.format(fk.getFkColumnName()));
235                fkeys.add(fk);
236             }
237          } catch (Exception JavaDoc e) {
238             e.printStackTrace();
239          } finally {
240             if (con != null) {
241                try {
242                   con.close();
243                } catch (SQLException JavaDoc e) {
244                }
245             }
246          }
247       }
248
249       return fkeys;
250    }
251
252    /**
253     * A list with Strings of all primary key fields.
254     *
255     * @param tablename
256     * @return an ArrayList of primary key column names for the specified table, never <code>null</code>.
257     * @todo make this private - all primary key work should be done in this class.
258     */
259    public static ArrayList JavaDoc getPrimaryKeys(String JavaDoc tablename) {
260       if (pkCache.get(tablename) != null) {
261          return (ArrayList JavaDoc) pkCache.get(tablename);
262       }
263       GenericJdbcManager conManager = JagGenerator.getConManager();
264       Connection JavaDoc con = null;
265       ArrayList JavaDoc pkeys = new ArrayList JavaDoc();
266       try {
267          con = conManager.connect();
268          ResultSet JavaDoc r = con.getMetaData().getPrimaryKeys(null, conManager.getSchema(), tablename);
269          while (r.next()) {
270             pkeys.add(r.getString("COLUMN_NAME"));
271          }
272       } catch (Exception JavaDoc e) {
273          e.printStackTrace();
274       } finally {
275          if (con != null) {
276             try {
277                con.close();
278             } catch (SQLException JavaDoc e) {
279             }
280          }
281       }
282
283       pkCache.put(tablename, pkeys);
284       return pkeys;
285    }
286
287    /**
288     * Grabs the list of tables from the database.
289     *
290     * @return a List of table names (String), never <code>null</code>.
291     */
292    public static ArrayList JavaDoc getTables() {
293       if (tablesCache == null) {
294          tablesCache = new ArrayList JavaDoc();
295          GenericJdbcManager conManager = JagGenerator.getConManager();
296          String JavaDoc[] displayTableTypes = conManager.getDisplayTableTypes();
297          if (displayTableTypes == null) {
298             displayTableTypes = DEFAULT_TABLE_TYPES;
299          }
300          ResultSet JavaDoc tables = null;
301          Connection JavaDoc con = null;
302          try {
303             con = conManager.connect();
304             // Look for tables of the type table, not for synonyms: ,"SYNONYM"
305 ResultSet JavaDoc schemas = con.getMetaData().getSchemas();
306             while (schemas.next()) {
307                // Do nothing.
308 }
309             tables = con.getMetaData().getTables(null, conManager.getSchema(), "%", displayTableTypes);
310             while (tables.next()) {
311                // Iterate over the tablenames and get the tablenames.
312 String JavaDoc tableName = tables.getString(TABLE_NAME);
313                if (tableName != null) {
314                   tablesCache.add(tableName);
315                }
316             }
317          } catch (Exception JavaDoc e) {
318             e.printStackTrace();
319             JagGenerator.logToConsole("Error getting tables list: " + e.toString());
320          } finally {
321             if (tables != null)
322                try {
323                   tables.close();
324                } catch (Exception JavaDoc e) {
325                }
326
327             if (con != null)
328                try {
329                   con.close();
330                } catch (SQLException JavaDoc e) {
331                }
332          }
333       }
334       if (tablesCache != null)
335          Collections.sort(tablesCache);
336       return tablesCache;
337    }
338
339    /**
340     * This needs to be called when databases are switched.
341     */
342    public static void clearCache() {
343       tablesCache = null;
344    }
345
346    /**
347     * Forces an update of a particular table's columns the next time they are required.
348     *
349     * @param tableName
350     */
351    public static void clearColumnsCacheForTable(String JavaDoc tableName) {
352       columnsCache.remove(tableName);
353    }
354
355 }
356
Popular Tags