1 package salto.tool.sql;
2
3 import java.sql.Connection;
4 import java.sql.DatabaseMetaData;
5 import java.sql.DriverManager;
6 import java.sql.ResultSet;
7 import java.sql.ResultSetMetaData;
8 import java.sql.SQLException;
9 import java.sql.Statement;
10 import java.util.ArrayList;
11 import java.util.HashMap;
12 import java.util.HashSet;
13 import java.util.Set;
14 import java.util.Vector;
15
16 import salto.tool.sql.data.CatalogInfo;
17 import salto.tool.sql.data.ShemaInfo;
18 import salto.tool.sql.data.TableColInfo;
19 import salto.tool.sql.data.TableFK;
20 import salto.tool.sql.data.TableInfo;
21 import salto.tool.sql.data.TablePK;
22 import salto.tool.sql.data.TypeInfo;
23
24 /***
25 * Cette classe permet de r�cup�rer toutes les informations conernant une base
26 * de donn�e. Un finalizer permet de supprimer la connection. Toutefois, il est
27 * pr�f�rable de la fermer explicitement avec la m�thode closeConnection Les
28 * informations sont renvoy�es par l'interm�diaires des objets TableColInfo
29 * TaleFK TablePK TableInfo Ces objets respectent les normes JDBC 2.0. Si les
30 * informations sont incompl�tes, il faut v�rifier la compatibilit� de votre
31 * driver JDBC. Date de cr�ation : (28/01/01 7:56:42)
32 *
33 * @author : E. Loiez
34 */
35 public class DatabaseInfo {
36 private static String shema = null;
37
38 private static Connection defaultConn = null;
39
40
41 private static String driver = null;
42
43 private static String pwd = null;
44
45 private static String url = null;
46
47 private static String user = null;
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62 public static void main(String[] args) {
63 try {
64 getConnection();
65 TableInfo[] infos = getTables();
66 getColumns(infos[0]);
67
68 getFK(infos[0],IMPORTED_KEY);
69 } catch (SQLException e) {
70 } catch (DatabaseException e) {
71 }
72 }
73
74 private static HashMap typMap = new HashMap();
75
76 public static synchronized boolean isAutoIncrement(int sqlTyp, Connection conn) throws SQLException {
77 String dbName = conn.getMetaData().getDatabaseProductName();
78 Boolean result = (Boolean) typMap.get(dbName + "#" + new Integer(sqlTyp));
79 if (result == null) {
80 TypeInfo[] info = getTypeInfo(conn);
81 for (int i = 0; i < info.length; i++) {
82 typMap.put(dbName + "#" + new Integer(info[i].getDataTyp()), new Boolean(info[i].getAutoIncrement()));
83 }
84 }
85 return ((Boolean) typMap.get(dbName + "#" + new Integer(sqlTyp))).booleanValue();
86 }
87
88 public static synchronized boolean isAutoIncrement(String typeName, Connection conn) throws SQLException {
89 String dbName = conn.getMetaData().getDatabaseProductName();
90 Boolean result = (Boolean) typMap.get(dbName + "#" + typeName.toUpperCase());
91 if (result == null) {
92 TypeInfo[] info = getTypeInfo(conn);
93 for (int i = 0; i < info.length; i++) {
94 typMap.put(dbName + "#" + info[i].getTypeName().toUpperCase(), new Boolean(info[i].getAutoIncrement()));
95 }
96 }
97 Boolean tmp = (Boolean) typMap.get(dbName + "#" + typeName.toUpperCase());
98 if (tmp == null)
99 return false;
100 else
101 return tmp.booleanValue();
102 }
103
104 /***
105 * Return a set of auto increment columns
106 *
107 * @param tableName
108 * @param conn
109 * @return
110 * @throws SQLException
111 */
112 private static Set getAutoIncrementColumns(String tableName, Connection conn) throws SQLException {
113
114 Statement s = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
115 ResultSet rs = s.executeQuery("SELECT * FROM " + tableName + " WHERE 1 = 0");
116 ResultSetMetaData meta = rs.getMetaData();
117 int columnCount = meta.getColumnCount();
118 Set results = new HashSet();
119 for (int i = 1; i <= columnCount; i++) {
120 if (meta.isAutoIncrement(i)) {
121 String colName = meta.getColumnName(i);
122 results.add(colName);
123 }
124 }
125 return results;
126
127 }
128
129 /***
130 * Retrieves a description of all the standard SQL types supported by this
131 * database. They are ordered by DATA_TYPE and then by how closely the data
132 * type maps to the corresponding JDBC SQL type.
133 *
134 * <P>
135 * Each type description has the following columns:
136 * <OL>
137 * <LI><B>TYPE_NAME</B> String => Type name
138 * <LI><B>DATA_TYPE</B> short => SQL data type from java.sql.Types
139 * <LI><B>PRECISION</B> int => maximum precision
140 * <LI><B>LITERAL_PREFIX</B> String => prefix used to quote a literal (may
141 * be <code>null</code>)
142 * <LI><B>LITERAL_SUFFIX</B> String => suffix used to quote a literal (may
143 * be <code>null</code>)
144 * <LI><B>CREATE_PARAMS</B> String => parameters used in creating the type
145 * (may be <code>null</code>)
146 * <LI><B>NULLABLE</B> short => can you use NULL for this type.
147 * <UL>
148 * <LI>typeNoNulls - does not allow NULL values
149 * <LI>typeNullable - allows NULL values
150 * <LI>typeNullableUnknown - nullability unknown
151 * </UL>
152 * <LI><B>CASE_SENSITIVE</B> boolean=> is it case sensitive.
153 * <LI><B>SEARCHABLE</B> short => can you use "WHERE" based on this type:
154 * <UL>
155 * <LI>typePredNone - No support
156 * <LI>typePredChar - Only supported with WHERE .. LIKE
157 * <LI>typePredBasic - Supported except for WHERE .. LIKE
158 * <LI>typeSearchable - Supported for all WHERE ..
159 * </UL>
160 * <LI><B>UNSIGNED_ATTRIBUTE</B> boolean => is it unsigned.
161 * <LI><B>FIXED_PREC_SCALE</B> boolean => can it be a money value.
162 * <LI><B>AUTO_INCREMENT</B> boolean => can it be used for an
163 * auto-increment value.
164 * <LI><B>LOCAL_TYPE_NAME</B> String => localized version of type name
165 * (may be <code>null</code>)
166 * <LI><B>MINIMUM_SCALE</B> short => minimum scale supported
167 * <LI><B>MAXIMUM_SCALE</B> short => maximum scale supported
168 * <LI><B>SQL_DATA_TYPE</B> int => unused
169 * <LI><B>SQL_DATETIME_SUB</B> int => unused
170 * <LI><B>NUM_PREC_RADIX</B> int => usually 2 or 10
171 * </OL>
172 *
173 * @return a <code>ResultSet</code> object in which each row is an SQL
174 * type description
175 * @exception SQLException
176 * if a database access error occurs
177 */
178 public static TypeInfo[] getTypeInfo(Connection conn) throws SQLException {
179 ResultSet rs = conn.getMetaData().getTypeInfo();
180 ArrayList list = new ArrayList();
181 while (rs.next()) {
182 list.add(new TypeInfo(rs));
183 }
184 TypeInfo[] res = (TypeInfo[]) list.toArray(new TypeInfo[list.size()]);
185 return res;
186 }
187
188 /***
189 * Ins�rez la description de la m�thode � cet endroit. Date de cr�ation :
190 * (28/01/01 9:03:32) Retour de getColumns TABLE_CAT String => table catalog
191 * (may be null) TABLE_SCHEM String => table schema (may be null) TABLE_NAME
192 * String => table name COLUMN_NAME String => column name DATA_TYPE short =>
193 * SQL type from java.sql.Types TYPE_NAME String => Data source dependent
194 * type name, for a UDT the type name is fully qualified COLUMN_SIZE int =>
195 * column size. For char or date types this is the maximum number of
196 * characters, for numeric or decimal types this is precision. BUFFER_LENGTH
197 * is not used. DECIMAL_DIGITS int => the number of fractional digits
198 * NUM_PREC_RADIX int => Radix (typically either 10 or 2) NULLABLE int => is
199 * NULL allowed? columnNoNulls - might not allow NULL values columnNullable -
200 * definitely allows NULL values columnNullableUnknown - nullability unknown
201 * REMARKS String => comment describing column (may be null) COLUMN_DEF
202 * String => default value (may be null) SQL_DATA_TYPE int => unused
203 * SQL_DATETIME_SUB int => unused CHAR_OCTET_LENGTH int => for char types
204 * the maximum number of bytes in the column ORDINAL_POSITION int => index
205 * of column in table (starting at 1) IS_NULLABLE String => "NO" means
206 * column definitely does not allow NULL values; "YES" means the column
207 * might allow NULL values. An empty string means nobody knows.
208 *
209 */
210
211 public static synchronized TableColInfo[] getColumns(TableInfo tbInfo) {
212 TableColInfo[] res = null;
213 ResultSet rs = null;
214 try {
215 DatabaseMetaData dbInfo = tbInfo.getConn().getMetaData();
216 rs = dbInfo.getColumns(tbInfo.getTableCat(), tbInfo.getTableShem(), tbInfo.getTableName(), "%");
217 String tableName = tbInfo.getTableName();
218 if (tbInfo.getTableShem() != null && !"".equals(tbInfo.getTableShem())) {
219 tableName = tbInfo.getTableShem() + "." + tableName;
220 }
221 Set autoIncrementolumns = getAutoIncrementColumns(tableName, tbInfo.getConn());
222
223
224
225
226
227 int count = 1;
228 Vector element = new Vector();
229 while (rs.next()) {
230 TableColInfo ti = new TableColInfo(tbInfo);
231
232 ti.setTableInfo(tbInfo);
233 ti.setTableCat(rs.getString(1));
234 ti.setTableShem(rs.getString(2));
235 ti.setTableName(rs.getString(3));
236 ti.setColName(rs.getString(4));
237 ti.setColTyp(rs.getShort(5));
238 ti.setTypName(rs.getString(6));
239 ti.setColSize(rs.getInt(7));
240 ti.setDecDigit(rs.getInt(9));
241 ti.setNumPrecRadix(rs.getInt(10));
242 ti.setColNullable(rs.getInt(11));
243 try {
244 ti.setColRemarks(rs.getString(12));
245 } catch (Exception e) {
246 }
247 try {
248 ti.setColDefaultVal(rs.getString(16));
249 } catch (Exception e) {
250 }
251 try {
252 ti.setColCharOctetLength(rs.getInt(19));
253 } catch (Exception e) {
254 }
255 try {
256 ti.setColPosition(rs.getInt(20));
257 } catch (Exception e) {
258 }
259 try {
260 ti.setColIsNullable(rs.getString(21));
261 } catch (Exception e) {
262 }
263 try {
264 boolean isAutoIncrementable = isAutoIncrement(ti.getTypName(), tbInfo.getConn());
265 isAutoIncrementable &= autoIncrementolumns.contains(ti.getColName());
266 ti.setAutoIncrement(isAutoIncrementable);
267 } catch (Exception e) {
268 e.printStackTrace();
269 }
270 element.add(ti);
271 count++;
272 }
273 Object[] o = element.toArray();
274 res = new TableColInfo[o.length];
275 for (int i = 0; i < o.length; i++)
276 res[i] = (TableColInfo) o[i];
277
278 } catch (Exception e) {
279 e.printStackTrace();
280
281
282 } finally {
283 try {
284 if (rs != null)
285 rs.close();
286 } catch (Exception e) {
287 }
288 }
289 return res;
290 }
291
292 /***
293 * Recup�re une connexion sur la base de donn�es.
294 *
295 * @return La connexion � la base
296 * @exception DatabaseException
297 */
298 private static java.sql.Connection getConnection() throws SQLException, DatabaseException {
299 try {
300 if (defaultConn != null && !defaultConn.isClosed()) {
301 defaultConn.close();
302 }
303
304 Class.forName(driver);
305 defaultConn = DriverManager.getConnection(url, user, pwd);
306 } catch (ClassNotFoundException e) {
307 defaultConn = null;
308 throw new DatabaseException("getConnection-Le driver n'a pas �t� trouv� dans le classpath");
309 }
310 return defaultConn;
311 }
312
313 /***
314 * Gets a description of the foreign key columns in the foreign key table
315 * that reference the primary key columns of the primary key table (describe
316 * how one table imports another's key.) This should normally return a
317 * single foreign key/primary key pair (most tables only import a foreign
318 * key from a table once.) They are ordered by FKTABLE_CAT, FKTABLE_SCHEM,
319 * FKTABLE_NAME, and KEY_SEQ.
320 *
321 * <P>
322 * Each foreign key column description has the following columns:
323 * <OL>
324 * <LI><B>PKTABLE_CAT</B> String => primary key table catalog (may be
325 * null)
326 * <LI><B>PKTABLE_SCHEM</B> String => primary key table schema (may be
327 * null)
328 * <LI><B>PKTABLE_NAME</B> String => primary key table name
329 * <LI><B>PKCOLUMN_NAME</B> String => primary key column name
330 * <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be
331 * null) being exported (may be null)
332 * <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be
333 * null) being exported (may be null)
334 * <LI><B>FKTABLE_NAME</B> String => foreign key table name being exported
335 * <LI><B>FKCOLUMN_NAME</B> String => foreign key column name being
336 * exported
337 * <LI><B>KEY_SEQ</B> short => sequence number within foreign key
338 * <LI><B>UPDATE_RULE</B> short => What happens to foreign key when
339 * primary is updated:
340 * <UL>
341 * <LI>importedNoAction - do not allow update of primary key if it has been
342 * imported
343 * <LI>importedKeyCascade - change imported key to agree with primary key
344 * update
345 * <LI>importedKeySetNull - change imported key to NULL if its primary key
346 * has been updated
347 * <LI>importedKeySetDefault - change imported key to default values if its
348 * primary key has been updated
349 * <LI>importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x
350 * compatibility)
351 * </UL>
352 * <LI><B>DELETE_RULE</B> short => What happens to the foreign key when
353 * primary is deleted.
354 * <UL>
355 * <LI>importedKeyNoAction - do not allow delete of primary key if it has
356 * been imported
357 * <LI>importedKeyCascade - delete rows that import a deleted key
358 * <LI>importedKeySetNull - change imported key to NULL if its primary key
359 * has been deleted
360 * <LI>importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x
361 * compatibility)
362 * <LI>importedKeySetDefault - change imported key to default if its
363 * primary key has been deleted
364 * </UL>
365 * <LI><B>FK_NAME</B> String => foreign key name (may be null)
366 * <LI><B>PK_NAME</B> String => primary key name (may be null)
367 * <LI><B>DEFERRABILITY</B> short => can the evaluation of foreign key
368 * constraints be deferred until commit
369 * <UL>
370 * <LI>importedKeyInitiallyDeferred - see SQL92 for definition
371 * <LI>importedKeyInitiallyImmediate - see SQL92 for definition
372 * <LI>importedKeyNotDeferrable - see SQL92 for definition
373 * </UL>
374 * </OL>
375 *
376 * @param primaryCatalog
377 * a catalog name; "" retrieves those without a catalog; null
378 * means drop catalog name from the selection criteria
379 * @param primarySchema
380 * a schema name; "" retrieves those without a schema
381 * @param primaryTable
382 * the table name that exports the key
383 * @param foreignCatalog
384 * a catalog name; "" retrieves those without a catalog; null
385 * means drop catalog name from the selection criteria
386 * @param foreignSchema
387 * a schema name; "" retrieves those without a schema
388 * @param foreignTable
389 * the table name that imports the key
390 * @return ResultSet - each row is a foreign key column description
391 * @exception SQLException
392 * if a database access error occurs
393 * @see #getImportedKeys
394 */
395 public synchronized TableFK[] getFK(Connection conn, String[] tableName) {
396 String pkCat = null;
397 String fkCat = null;
398
399 TableFK[] res = null;
400 Vector tempRes = new Vector();
401 ResultSet rs = null;
402 try {
403 DatabaseMetaData dbInfo = conn.getMetaData();
404 for (int i = 0; i < tableName.length; i++) {
405 for (int j = 0; j < tableName.length; j++) {
406 if (i != j) {
407 rs = dbInfo.getCrossReference(pkCat, shema, tableName[i], fkCat, shema, tableName[j]);
408 while (rs.next()) {
409 TableFK tbfk = new TableFK();
410 tbfk.setPkTableCat(rs.getString(1));
411 tbfk.setPkTableShem(rs.getString(2));
412 tbfk.setPkTableName(rs.getString(3));
413 tbfk.setPkColumnName(rs.getString(4));
414 tbfk.setFkTableCat(rs.getString(5));
415 tbfk.setFkTableShem(rs.getString(6));
416 tbfk.setFkTableName(rs.getString(7));
417 tbfk.setFkColumnName(rs.getString(8));
418 tbfk.setKeySeq(rs.getShort(9));
419 tempRes.add(tbfk);
420 }
421
422 }
423 }
424 }
425 res = new TableFK[tempRes.size()];
426 for (int i = 0; i < tempRes.size(); i++)
427 res[i] = (TableFK) tempRes.get(i);
428 } catch (Exception e) {
429 System.out.println(e);
430 } finally {
431 try {
432 if (rs != null)
433 rs.close();
434 } catch (Exception e) {
435 }
436 }
437
438 return res;
439 }
440
441 /***
442 * Retrieves a description of the foreign key columns that reference the
443 * given table's primary key columns (the foreign keys exported by a table).
444 * They are ordered by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and
445 * KEY_SEQ.
446 *
447 * <P>
448 * Each foreign key column description has the following columns:
449 * <OL>
450 * <LI><B>PKTABLE_CAT</B> String => primary key table catalog (may be
451 * <code>null</code>)
452 * <LI><B>PKTABLE_SCHEM</B> String => primary key table schema (may be
453 * <code>null</code>)
454 * <LI><B>PKTABLE_NAME</B> String => primary key table name
455 * <LI><B>PKCOLUMN_NAME</B> String => primary key column name
456 * <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be
457 * <code>null</code>) being exported (may be <code>null</code>)
458 * <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be
459 * <code>null</code>) being exported (may be <code>null</code>)
460 * <LI><B>FKTABLE_NAME</B> String => foreign key table name being exported
461 * <LI><B>FKCOLUMN_NAME</B> String => foreign key column name being
462 * exported
463 * <LI><B>KEY_SEQ</B> short => sequence number within foreign key
464 * <LI><B>UPDATE_RULE</B> short => What happens to foreign key when
465 * primary is updated:
466 * <UL>
467 * <LI>importedNoAction - do not allow update of primary key if it has been
468 * imported
469 * <LI>importedKeyCascade - change imported key to agree with primary key
470 * update
471 * <LI>importedKeySetNull - change imported key to <code>NULL</code> if
472 * its primary key has been updated
473 * <LI>importedKeySetDefault - change imported key to default values if its
474 * primary key has been updated
475 * <LI>importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x
476 * compatibility)
477 * </UL>
478 * <LI><B>DELETE_RULE</B> short => What happens to the foreign key when
479 * primary is deleted.
480 * <UL>
481 * <LI>importedKeyNoAction - do not allow delete of primary key if it has
482 * been imported
483 * <LI>importedKeyCascade - delete rows that import a deleted key
484 * <LI>importedKeySetNull - change imported key to <code>NULL</code> if
485 * its primary key has been deleted
486 * <LI>importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x
487 * compatibility)
488 * <LI>importedKeySetDefault - change imported key to default if its
489 * primary key has been deleted
490 * </UL>
491 * <LI><B>FK_NAME</B> String => foreign key name (may be <code>null</code>)
492 * <LI><B>PK_NAME</B> String => primary key name (may be <code>null</code>)
493 * <LI><B>DEFERRABILITY</B> short => can the evaluation of foreign key
494 * constraints be deferred until commit
495 * <UL>
496 * <LI>importedKeyInitiallyDeferred - see SQL92 for definition
497 * <LI>importedKeyInitiallyImmediate - see SQL92 for definition
498 * <LI>importedKeyNotDeferrable - see SQL92 for definition
499 * </UL>
500 * </OL>
501 *
502 * @param catalog
503 * a catalog name; must match the catalog name as it is stored in
504 * this database; "" retrieves those without a catalog;
505 * <code>null</code> means that the catalog name should not be
506 * used to narrow the search
507 * @param schema
508 * a schema name; must match the schema name as it is stored in
509 * the database; "" retrieves those without a schema;
510 * <code>null</code> means that the schema name should not be
511 * used to narrow the search
512 * @param table
513 * a table name; must match the table name as it is stored in
514 * this database
515 * @return a <code>ResultSet</code> object in which each row is a foreign
516 * key column description
517 * @exception SQLException
518 * if a database access error occurs
519 * @see java.sql.DatabaseMetaData.getExportedKeys
520 */
521 public static final int IMPORTED_KEY = 0;
522
523 public static final int EXPORTED_KEY = 1;
524
525 public static synchronized TableFK[][] getImportedFK(TableInfo table) {
526 return getFK(table, IMPORTED_KEY);
527 }
528
529 public static synchronized TableFK[][] getExportedFK(TableInfo table) {
530 return getFK(table, EXPORTED_KEY);
531 }
532
533 public static synchronized TableFK[][] getFK(TableInfo table, int type) {
534 ArrayList firstDim = new ArrayList();
535 ArrayList secondDim = new ArrayList();
536 ResultSet rs = null;
537 try {
538 Connection conn = table.getConnInfo().getConn();
539 DatabaseMetaData dbInfo = conn.getMetaData();
540 if (type == IMPORTED_KEY)
541 rs = dbInfo.getImportedKeys(table.getTableCat(), table.getTableShem(), table.getTableName());
542 else
543 rs = dbInfo.getExportedKeys(table.getTableCat(), table.getTableShem(), table.getTableName());
544 String previousTableName = null;
545 while (rs.next()) {
546 TableFK tbfk = new TableFK();
547 tbfk.setPkTableCat(rs.getString(1));
548 tbfk.setPkTableShem(rs.getString(2));
549 tbfk.setPkTableName(rs.getString(3));
550 tbfk.setPkColumnName(rs.getString(4));
551 tbfk.setFkTableCat(rs.getString(5));
552 tbfk.setFkTableShem(rs.getString(6));
553 tbfk.setFkTableName(rs.getString(7));
554 tbfk.setFkColumnName(rs.getString(8));
555 tbfk.setKeySeq(rs.getShort(9));
556 tbfk.setFkName(rs.getString(12));
557 if (previousTableName == null) {
558 previousTableName = tbfk.getFkName();
559 }
560 if (previousTableName != null && !previousTableName.equals(tbfk.getFkName())) {
561 firstDim.add(secondDim.toArray(new TableFK[secondDim.size()]));
562 secondDim.clear();
563 }
564 secondDim.add(tbfk);
565 }
566 if (secondDim.size() > 0)
567 firstDim.add(secondDim.toArray(new TableFK[secondDim.size()]));
568 TableFK[][] res = new TableFK[firstDim.size()][];
569 for (int i = 0; i < res.length; i++) {
570 res[i] = (TableFK[]) firstDim.get(i);
571 }
572 return res;
573 } catch (SQLException e) {
574 System.out.println(e);
575 } finally {
576 try {
577 rs.close();
578 } catch (Exception e) {
579 }
580 }
581 return new TableFK[0][0];
582
583 }
584
585 /***
586 * Gets a description of a table's primary key columns. They are ordered by
587 * COLUMN_NAME. Each primary key column description has the following
588 * columns:
589 *
590 * TABLE_CAT String => table catalog (may be null) TABLE_SCHEM String =>
591 * table schema (may be null) TABLE_NAME String => table name COLUMN_NAME
592 * String => column name KEY_SEQ short => sequence number within primary key
593 * PK_NAME String => primary key name (may be null)
594 *
595 */
596 public static synchronized TablePK[] getPK(TableInfo tbInfo) {
597 return getPK(tbInfo.getConnInfo().getConn(), tbInfo.getTableCat(), tbInfo.getTableShem(), tbInfo.getTableName());
598 }
599
600 public static synchronized TablePK[] getPK(Connection conn, String catalogue, String shema, String tableName) {
601
602 TablePK tbpk = null;
603 TablePK[] res = null;
604 ArrayList temp = new ArrayList();
605 ResultSet rs = null;
606 try {
607 DatabaseMetaData dbInfo = conn.getMetaData();
608 if ("default".equals(shema)) {
609 shema = null;
610 catalogue = null;
611 }
612 rs = dbInfo.getPrimaryKeys(catalogue, shema, tableName);
613 while (rs.next()) {
614 tbpk = new TablePK();
615 tbpk.setCat(rs.getString(1));
616 tbpk.setShem(rs.getString(2));
617 tbpk.setTable(rs.getString(3));
618 tbpk.setCol(rs.getString(4));
619 try {
620 tbpk.setKeySeq((short) rs.getInt(5));
621 } catch (SQLException e) {
622 }
623 try {
624 tbpk.setPkName(rs.getString(6));
625 } catch (SQLException e) {
626 }
627 temp.add(tbpk);
628 }
629 res = (TablePK[]) temp.toArray(new TablePK[temp.size()]);
630 } catch (Exception e) {
631 System.out.println(e);
632 } finally {
633 try {
634 if (rs != null)
635 rs.close();
636 } catch (Exception e) {
637 }
638 }
639 return res;
640
641 }
642
643 /***
644 * Ins�rez la description de la m�thode � cet endroit. Date de cr�ation :
645 * (28/01/01 8:13:15) Retour de getTables TABLE_CAT String => table catalog
646 * (may be null) TABLE_SCHEM String => table schema (may be null) TABLE_NAME
647 * String => table name TABLE_TYPE String => table type. Typical types are
648 * "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY",
649 * "ALIAS", "SYNONYM". REMARKS String => explanatory comment on the table
650 *
651 */
652 public static synchronized TableInfo[] getTables() throws DatabaseException {
653 return getTables(defaultConn);
654 }
655
656 public static synchronized TableInfo[] getTables(Connection conn) throws DatabaseException {
657 TableInfo[] res;
658 ResultSet rs = null;
659 try {
660 DatabaseMetaData dbInfo = conn.getMetaData();
661 rs = dbInfo.getTables(null, null, "%", null);
662 Vector element = new Vector();
663 while (rs.next()) {
664 TableInfo ti = new TableInfo();
665 ti.setConn(conn);
666 ti.setTableCat(rs.getString(1));
667 ti.setTableShem(rs.getString(2));
668 ti.setTableName(rs.getString(3));
669 ti.setTableType(rs.getString(4));
670 ti.setTableRemarks(rs.getString(5));
671 element.add(ti);
672 }
673 Object[] o = element.toArray();
674 res = new TableInfo[o.length];
675 for (int i = 0; i < o.length; i++)
676 res[i] = (TableInfo) o[i];
677
678 } catch (Exception e) {
679 throw new DatabaseException("Erreur dans la r�cup�ration des tables" + e);
680 } finally {
681 try {
682 if (rs != null)
683 rs.close();
684 } catch (Exception e) {
685 }
686 }
687 return res;
688 }
689
690 /***
691 * Ins�rez la description de la m�thode � cet endroit. Date de cr�ation :
692 * (3/02/01 11:52:21)
693 *
694 * @return java.lang.String
695 */
696 public String getWhereClause(String[] tableNames) {
697 return getWhereClause(defaultConn, tableNames);
698 }
699
700 public String getWhereClause(Connection conn, String[] tableNames) {
701 TableFK[] tbfk = getFK(conn, tableNames);
702 String whereValue = "";
703 if (tbfk != null) {
704 for (int i = 0; i < tbfk.length; i++) {
705 if (i == 0)
706 whereValue += "\n\t where " + tbfk[i].getPkTableName() + "." + tbfk[i].getPkColumnName() + " = " + tbfk[i].getFkTableName() + "." + tbfk[i].getFkColumnName();
707 else
708 whereValue += "\n\t and " + tbfk[i].getPkTableName() + "." + tbfk[i].getPkColumnName() + " = " + tbfk[i].getFkTableName() + "." + tbfk[i].getFkColumnName();
709 }
710 }
711 return whereValue;
712 }
713
714 /***
715 * Gets a description of the foreign key columns in the foreign key table
716 * that reference the primary key columns of the primary key table (describe
717 * how one table imports another's key.) This should normally return a
718 * single foreign key/primary key pair (most tables only import a foreign
719 * key from a table once.) They are ordered by FKTABLE_CAT, FKTABLE_SCHEM,
720 * FKTABLE_NAME, and KEY_SEQ.
721 *
722 * <P>
723 * Each foreign key column description has the following columns:
724 * <OL>
725 * <LI><B>PKTABLE_CAT</B> String => primary key table catalog (may be
726 * null)
727 * <LI><B>PKTABLE_SCHEM</B> String => primary key table schema (may be
728 * null)
729 * <LI><B>PKTABLE_NAME</B> String => primary key table name
730 * <LI><B>PKCOLUMN_NAME</B> String => primary key column name
731 * <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be
732 * null) being exported (may be null)
733 * <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be
734 * null) being exported (may be null)
735 * <LI><B>FKTABLE_NAME</B> String => foreign key table name being exported
736 * <LI><B>FKCOLUMN_NAME</B> String => foreign key column name being
737 * exported
738 * <LI><B>KEY_SEQ</B> short => sequence number within foreign key
739 * <LI><B>UPDATE_RULE</B> short => What happens to foreign key when
740 * primary is updated:
741 * <UL>
742 * <LI>importedNoAction - do not allow update of primary key if it has been
743 * imported
744 * <LI>importedKeyCascade - change imported key to agree with primary key
745 * update
746 * <LI>importedKeySetNull - change imported key to NULL if its primary key
747 * has been updated
748 * <LI>importedKeySetDefault - change imported key to default values if its
749 * primary key has been updated
750 * <LI>importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x
751 * compatibility)
752 * </UL>
753 * <LI><B>DELETE_RULE</B> short => What happens to the foreign key when
754 * primary is deleted.
755 * <UL>
756 * <LI>importedKeyNoAction - do not allow delete of primary key if it has
757 * been imported
758 * <LI>importedKeyCascade - delete rows that import a deleted key
759 * <LI>importedKeySetNull - change imported key to NULL if its primary key
760 * has been deleted
761 * <LI>importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x
762 * compatibility)
763 * <LI>importedKeySetDefault - change imported key to default if its
764 * primary key has been deleted
765 * </UL>
766 * <LI><B>FK_NAME</B> String => foreign key name (may be null)
767 * <LI><B>PK_NAME</B> String => primary key name (may be null)
768 * <LI><B>DEFERRABILITY</B> short => can the evaluation of foreign key
769 * constraints be deferred until commit
770 * <UL>
771 * <LI>importedKeyInitiallyDeferred - see SQL92 for definition
772 * <LI>importedKeyInitiallyImmediate - see SQL92 for definition
773 * <LI>importedKeyNotDeferrable - see SQL92 for definition
774 * </UL>
775 * </OL>
776 *
777 * @param primaryCatalog
778 * a catalog name; "" retrieves those without a catalog; null
779 * means drop catalog name from the selection criteria
780 * @param primarySchema
781 * a schema name; "" retrieves those without a schema
782 * @param primaryTable
783 * the table name that exports the key
784 * @param foreignCatalog
785 * a catalog name; "" retrieves those without a catalog; null
786 * means drop catalog name from the selection criteria
787 * @param foreignSchema
788 * a schema name; "" retrieves those without a schema
789 * @param foreignTable
790 * the table name that imports the key
791 * @return ResultSet - each row is a foreign key column description
792 * @exception SQLException
793 * if a database access error occurs
794 * @see #getImportedKeys
795 */
796 public static synchronized TableFK[] getFK(String tableName) {
797 return getFK(defaultConn, tableName);
798 }
799
800 public static synchronized TableFK[] getFK(Connection conn, String tableName) {
801 String pkCat = null;
802 String fkCat = null;
803
804 TableFK[] res = null;
805 Vector tempRes = new Vector();
806 ResultSet rs = null;
807 try {
808 DatabaseMetaData dbInfo = conn.getMetaData();
809 rs = dbInfo.getCrossReference(pkCat, shema, tableName, fkCat, shema, null);
810 while (rs.next()) {
811 TableFK tbfk = new TableFK();
812 tbfk.setPkTableCat(rs.getString(1));
813 tbfk.setPkTableShem(rs.getString(2));
814 tbfk.setPkTableName(rs.getString(3));
815 tbfk.setPkColumnName(rs.getString(4));
816 tbfk.setFkTableCat(rs.getString(5));
817 tbfk.setFkTableShem(rs.getString(6));
818 tbfk.setFkTableName(rs.getString(7));
819 tbfk.setFkColumnName(rs.getString(8));
820 tbfk.setKeySeq(rs.getShort(9));
821 tempRes.add(tbfk);
822 }
823 res = new TableFK[tempRes.size()];
824 for (int i = 0; i < tempRes.size(); i++)
825 res[i] = (TableFK) tempRes.get(i);
826 } catch (Exception e) {
827 System.out.println(e);
828 } finally {
829 try {
830 if (rs != null)
831 rs.close();
832 } catch (Exception e) {
833 }
834 }
835
836 return res;
837 }
838
839 /***
840 * Commentaire relatif au constructeur DatabaseInfo.
841 */
842 public DatabaseInfo(String driver, String url, String user, String pwd) throws SQLException, DatabaseException {
843 super();
844 shema = user;
845 DatabaseInfo.driver = driver;
846 DatabaseInfo.url = url;
847 DatabaseInfo.user = user;
848 DatabaseInfo.pwd = pwd;
849
850 }
851
852 /***
853 * Commentaire relatif au constructeur DatabaseInfo.
854 */
855 public DatabaseInfo(Connection conn) {
856 DatabaseInfo.defaultConn = conn;
857 }
858
859 /***
860 * @param conn
861 */
862 public static CatalogInfo[] getCatalogs(ConnectionInfo info) {
863 try {
864 ResultSet rs = info.getConn().getMetaData().getCatalogs();
865 ArrayList list = new ArrayList();
866 while (rs.next()) {
867 list.add(new CatalogInfo(info, rs.getString(1)));
868 }
869 return (CatalogInfo[]) list.toArray(new CatalogInfo[list.size()]);
870 } catch (SQLException e) {
871 e.printStackTrace();
872 }
873 return null;
874 }
875
876 /***
877 * @param conn
878 * @param catalog
879 * @return
880 */
881 public static ShemaInfo[] getShemas(ConnectionInfo info) {
882 try {
883 ResultSet rs = info.getConn().getMetaData().getSchemas();
884 ArrayList list = new ArrayList();
885 ShemaInfo shemaInfo;
886 while (rs.next()) {
887 shemaInfo = new ShemaInfo(info);
888 try {
889 shemaInfo.setCatalog(rs.getString(2));
890 } catch (Exception e) {
891 }
892 shemaInfo.setName(rs.getString(1));
893 list.add(shemaInfo);
894 }
895 return (ShemaInfo[]) list.toArray(new ShemaInfo[list.size()]);
896 } catch (SQLException e) {
897 e.printStackTrace();
898 }
899 return null;
900 }
901
902 /***
903 * @param conn
904 * @param catalog
905 * @param name
906 * @return
907 */
908 public static TableInfo[] getTables(ShemaInfo shemaInfo) {
909 String catalog = shemaInfo.getCatalog();
910 String shema = shemaInfo.getName();
911 if ("default".equals(shema)) {
912 shema = null;
913 catalog = null;
914 }
915 return getTables(shemaInfo.getConn(), catalog, shema);
916 }
917
918 public static synchronized TableInfo[] getTables(Connection conn, String catalog, String schema) {
919 return getTables(conn, catalog, schema, "%");
920 }
921
922 public static TableInfo[] getTables(Connection conn, String catalog, String shema, String pattern) {
923 try {
924 ResultSet rs = conn.getMetaData().getTables(catalog, shema, pattern, null);
925 ArrayList list = new ArrayList();
926 TableInfo tableInfo;
927 while (rs.next()) {
928 tableInfo = new TableInfo();
929 tableInfo.setConn(conn);
930 tableInfo.setTableCat(rs.getString(1));
931 tableInfo.setTableShem(rs.getString(2));
932 tableInfo.setTableName(rs.getString(3));
933 tableInfo.setTableType(rs.getString(4));
934 tableInfo.setTableRemarks(rs.getString(5));
935 list.add(tableInfo);
936 }
937 return (TableInfo[]) list.toArray(new TableInfo[list.size()]);
938 } catch (SQLException e) {
939 e.printStackTrace();
940 }
941 return null;
942 }
943
944 /***
945 * @param info
946 * @return
947 */
948 public static TableInfo[] getTables(CatalogInfo info) {
949 String catalog = info.getCatalog();
950 return getTables(info.getConnInfo().getConn(), catalog, null);
951 }
952 }