1
2
3 package net.sourceforge.sql2java;
4
5 import java.sql.Connection;
6 import java.sql.DatabaseMetaData;
7 import java.sql.DriverManager;
8 import java.sql.ResultSet;
9 import java.sql.SQLException;
10 import java.util.Hashtable;
11 import java.util.Iterator;
12 import java.util.SortedMap;
13 import java.util.StringTokenizer;
14 import java.util.TreeMap;
15 import java.util.Vector;
16
17 public class Database
18 {
19 private String tableTypes[];
20 private Connection pConnection;
21 private DatabaseMetaData meta;
22 private Vector tables;
23 private Hashtable tableHash;
24 private String engine, driver, url, username, password, catalog, schema, tablenamepattern;
25 private boolean retrieveRemarks = true;
26 private String activeConnections, idleConnections, maxWait;
27
28 public void setOracleRetrieveRemarks(boolean retrieveRemarks) { this.retrieveRemarks = retrieveRemarks;}
29 public void setDriver(String driver) { this.driver = driver; }
30 public void setUrl(String url) { this.url = url; }
31 public void setUsername(String username) { this.username = username; }
32 public void setPassword(String password) { this.password = password; }
33 public void setCatalog(String catalog) { this.catalog = catalog; }
34 public void setTableNamePattern(String tablenamepattern) { this.tablenamepattern = tablenamepattern; }
35 public void setTableTypes(String[] tt) { this.tableTypes = tt; }
36 public void setActiveConnections(String activeConnections) { this.activeConnections = activeConnections; }
37 public void setIdleConnections(String idleConnections) { this.idleConnections = idleConnections; }
38 public void setMaxWait(String maxWait) { this.maxWait = maxWait; }
39
40 public boolean getOracleRetrieveRemarks() { return this.retrieveRemarks; }
41 public String getEngine() { return engine; }
42 public String getDriver() { return driver; }
43 public String getUrl() { return url; }
44 public String getUsername() { return username; }
45 public String getPassword() { return password; }
46 public String getCatalog() { return catalog; }
47 public String getSchema() { return schema; }
48 public String getTableNamePattern() { return tablenamepattern; }
49 public String[] getTableTypes() { return tableTypes; }
50 public String getActiveConnections() { return activeConnections; }
51 public String getIdleConnections() { return idleConnections; }
52 public String getMaxWait() { return maxWait; }
53
54 public void setSchema(String schema)
55 {
56 if ("null".equalsIgnoreCase(schema))
57 this.schema = null;
58 else
59 this.schema = schema;
60 }
61
62
63 /**
64 * Return an array of tables having foreign key pointing to the
65 * passed table.
66 */
67 public Table[] getRelationTable(Table table)
68 {
69 Vector vector = new Vector();
70
71 for (int iIndex = 0; iIndex < tables.size(); iIndex ++)
72 {
73 Table tempTable = (Table)tables.get(iIndex);
74
75
76 if (table.equals(tempTable))
77 continue;
78
79
80 if (tempTable.isRelationTable())
81 {
82 if (tempTable.relationConnectsTo(table))
83 {
84 if (!vector.contains(tempTable))
85 vector.add(tempTable);
86 }
87 }
88 }
89 return (Table[])vector.toArray(new Table[vector.size()]);
90 }
91
92 public void load() throws SQLException, ClassNotFoundException
93 {
94
95 Class.forName(driver);
96
97
98 System.out.println("Connecting to " + username + " on " + url + " ...");
99 pConnection = DriverManager.getConnection(url, username, password);
100 System.out.println(" Connected.");
101 try
102 {
103 if (pConnection instanceof oracle.jdbc.driver.OracleConnection)
104 ((oracle.jdbc.driver.OracleConnection)pConnection).setRemarksReporting(getOracleRetrieveRemarks());
105 }
106 catch(NoClassDefFoundError ncdfe)
107 {
108
109 }
110 catch(Exception e)
111 {
112
113 }
114
115 meta = pConnection.getMetaData();
116 engine = meta.getDatabaseProductName();
117 System.out.println(" Database server :" + engine + ".");
118 engine = new StringTokenizer(engine).nextToken();
119 tables = new Vector();
120 tableHash = new Hashtable();
121
122 loadTables();
123 loadColumns();
124 loadPrimaryKeys();
125 loadImportedKeys();
126 loadIndexes();
127 loadProcedures();
128
129 pConnection.close();
130 }
131
132 public Table[] getTables()
133 {
134 return (Table[])tables.toArray(new Table[tables.size()]);
135 }
136
137 private void addTable(Table t) {
138 tables.addElement(t);
139 tableHash.put(t.getName(), t);
140 }
141
142 public Table getTable(String name) {
143 return (Table)tableHash.get(name);
144 }
145
146 /**
147 * Load all the tables for this schema.
148 */
149 private void loadTables() throws SQLException
150 {
151 System.out.println("Loading table list according to pattern " + tablenamepattern + " ...");
152
153
154 java.util.StringTokenizer st = new java.util.StringTokenizer(tablenamepattern, ",; \t");
155 while(st.hasMoreTokens()) {
156 String pattern = ((String)st.nextToken()).trim();
157 String tableSchema = schema;
158 int index = pattern.indexOf('.');
159 if (index > 0) {
160 tableSchema = pattern.substring(0, index);
161 pattern = pattern.substring(index+1);
162 }
163 ResultSet resultSet = meta.getTables(catalog, tableSchema, pattern, tableTypes);
164 while(resultSet.next())
165 {
166 Table table = new Table();
167 table.setCatalog(resultSet.getString("TABLE_CAT"));
168 table.setSchema(resultSet.getString("TABLE_SCHEM"));
169 table.setName(resultSet.getString("TABLE_NAME"));
170 table.setType(resultSet.getString("TABLE_TYPE"));
171 table.setRemarks(resultSet.getString("REMARKS"));
172 if (CodeWriter.authorizeProcess(table.getName(), "tables.include", "tables.exclude")) {
173 addTable(table);
174 System.out.println(" table " + table.getName() + " found");
175 }
176 }
177 resultSet.close();
178 }
179 }
180
181 /**
182 * For each table, load all the columns.
183 */
184 private void loadColumns() throws SQLException
185 {
186 System.out.println("Loading columns ...");
187 for (Iterator it = tables.iterator(); it.hasNext(); /**/) {
188 Table table = (Table) it.next();
189 Column c = null;
190
191 ResultSet resultSet = meta.getColumns(table.getCatalog(), table.getSchema(), table.getName(), "%");
192 while(resultSet.next())
193 {
194 c = new Column();
195 c.setDatabase(this);
196 c.setCatalog(resultSet.getString("TABLE_CAT"));
197 c.setSchema(resultSet.getString("TABLE_SCHEM"));
198 c.setTableName(resultSet.getString("TABLE_NAME"));
199 c.setName(resultSet.getString("COLUMN_NAME"));
200 c.setType(resultSet.getShort("DATA_TYPE"));
201 c.setSize(resultSet.getInt("COLUMN_SIZE"));
202 c.setDecimalDigits(resultSet.getInt("DECIMAL_DIGITS"));
203 c.setRadix(resultSet.getInt("NUM_PREC_RADIX"));
204 c.setNullable(resultSet.getInt("NULLABLE"));
205 c.setRemarks(resultSet.getString("REMARKS"));
206 c.setDefaultValue(resultSet.getString("COLUMN_DEF"));
207 c.setOrdinalPosition(resultSet.getInt("ORDINAL_POSITION"));
208 table.addColumn(c);
209 }
210 resultSet.close();
211
212 System.out.println(" " + table.getName() + " found " + table.countColumns() + " columns");
213 }
214 }
215
216 /**
217 * For each table, load the primary keys.
218 */
219 private void loadPrimaryKeys() throws SQLException {
220 System.out.println("Database::loadPrimaryKeys");
221
222 for (Iterator it = tables.iterator(); it.hasNext(); /**/) {
223 Table table = (Table) it.next();
224 SortedMap map = new TreeMap();
225
226 ResultSet pResultSet = meta.getPrimaryKeys(table.getCatalog(), table.getSchema(), table.getName());
227 while (pResultSet.next()) {
228 String colName = pResultSet.getString("COLUMN_NAME");
229 int seq = pResultSet.getShort("KEY_SEQ");
230 System.out.println("Found primary key (seq,name) (" + seq
231 + "," + colName
232 + ") for table '" + table.getName() + "'");
233 Column col = table.getColumn(colName);
234 if (col != null) {
235 map.put(String.valueOf(seq), col);
236 }
237 }
238 pResultSet.close();
239
240 int size = map.size();
241 for (int k = 1; k <= size; k++) {
242 Column col = (Column) map.get(String.valueOf(k));
243 table.addPrimaryKey(col);
244 }
245 } // for each table
246 }
247
248 /**
249 * For each table, load the imported key. <br>
250 * An imported key is the other's table column clone. Its ForeignKeyColName
251 * corresponds to the table's column name that points to the other's table.
252 */
253 private void loadImportedKeys() throws SQLException
254 {
255 System.out.println("Loading imported keys ...");
256
257 for (Iterator it = tables.iterator(); it.hasNext(); /**/) {
258 Table table = (Table) it.next();
259 ResultSet resultSet;
260 try {
261 resultSet = meta.getImportedKeys(table.getCatalog(), table.getSchema(), table.getName());
262 } catch (SQLException sqle) {
263 System.out.println(" Error while loading imported keys for table "+ table.getName());
264 continue;
265 }
266 while(resultSet.next())
267 {
268 String tabName = resultSet.getString("FKTABLE_NAME");
269 String colName = resultSet.getString("FKCOLUMN_NAME");
270
271 String foreignTabName= resultSet.getString("PKTABLE_NAME");
272 String foreignColName= resultSet.getString("PKCOLUMN_NAME");
273
274 Column col = getTable(tabName).getColumn(colName);
275 Table foreignTable = getTable(foreignTabName);
276 if (null != foreignTable) {
277 Column foreignCol = foreignTable.getColumn(foreignColName);
278
279 col.addForeignKey(foreignCol);
280 foreignCol.addImportedKey(col);
281 //getTable(foreignTabName).addImportedKey(col);
282
283 System.out.println(" " + col.getFullName() + " -> " + foreignCol.getFullName() + " found ");
284 }
285 }
286
287 resultSet.close();
288 }
289 }
290
291 /**
292 * For each table, load the indexes.
293 */
294 /
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350 private void loadProcedures() throws SQLException {
351 System.out.println("Loading procedures ...");
352
353 for (Iterator it = tables.iterator(); it.hasNext(); ) {
354 Table table = (Table) it.next();
355 String procedurePattern = table.getTableProperty("procedures");
356 if ((null == procedurePattern) || "".equals(procedurePattern)) {
357 procedurePattern = "%" + table.getName() + "%";
358 }
359 ResultSet resultSet = null;
360 try {
361 resultSet = meta.getProcedures(table.getCatalog(),
362 table.getSchema(),
363 procedurePattern);
364 } catch (SQLException sqle) {
365 System.out.println(" Error while loading procedures for table "+ table.getName());
366 continue;
367 }
368 while(resultSet.next())
369 {
370 String spName = resultSet.getString("PROCEDURE_NAME");
371 String spRemarks = resultSet.getString("REMARKS");
372
373
374 Procedure procedure = new Procedure();
375 procedure.setName(spName);
376 procedure.setRemarks(spRemarks);
377 procedure.setReturnType("void");
378 table.addProcedure(procedure);
379
380 System.out.println(" Found procedure " + spName + " for table " + table.getName());
381
382 ResultSet rs = meta.getProcedureColumns(catalog, schema, spName, null);
383 while(rs.next()) {
384 String colName = rs.getString("COLUMN_NAME");
385 short columnType = rs.getShort("COLUMN_TYPE");
386 if (DatabaseMetaData.procedureColumnUnknown == columnType) {
387 System.err.println(" Column " + colName + " of unknown type in procedure " + spName);
388 continue;
389 }
390 Column c = new Column();
391 c.setType(rs.getShort("DATA_TYPE"));
392 if (DatabaseMetaData.procedureColumnReturn == columnType) {
393 procedure.setReturnType(c.getJavaType());
394 continue;
395 }
396 c.setDatabase(this);
397 c.setCatalog(rs.getString("PROCEDURE_CAT"));
398 c.setSchema(rs.getString("PROCEDURE_SCHEM"));
399 c.setTableName(rs.getString("PROCEDURE_NAME"));
400 c.setName(colName);
401 c.setSize(rs.getInt("LENGTH"));
402 c.setDecimalDigits(rs.getInt("SCALE"));
403 c.setRadix(rs.getInt("RADIX"));
404 c.setNullable(rs.getInt("NULLABLE"));
405 c.setRemarks(rs.getString("REMARKS"));
406 switch (columnType) {
407 case DatabaseMetaData.procedureColumnIn:
408 procedure.addInColumn(c);
409 break;
410 case DatabaseMetaData.procedureColumnInOut:
411 procedure.addInOutColumn(c);
412 break;
413 case DatabaseMetaData.procedureColumnOut:
414 procedure.addOutColumn(c);
415 break;
416 default:
417
418 procedure.setReturnType("List");
419 }
420 }
421 rs.close();
422 }
423
424 resultSet.close();
425 }
426 }
427
428 public String[] getAllPackages()
429 {
430 Vector vector = new Vector();
431 for (int iIndex = 0; iIndex < tables.size(); iIndex ++)
432 {
433 Table table = (Table)tables.get(iIndex);
434 if (vector.contains(table.getPackage()) == false)
435 {
436 vector.add(table.getPackage());
437 }
438 }
439 return (String[])vector.toArray(new String[vector.size()]);
440 }
441 }