View Javadoc

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  	/* information de connection */
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  	/*static {
50  
51  		driver = "com.mysql.jdbc.Driver";
52  		url = "jdbc:mysql://localhost/teste";
53  		shema = "teste";
54  		try {
55  			getConnection();
56  		} catch (Exception e) {
57  			e.printStackTrace();
58  		}
59  
60  	}*/
61  
62  	public static void main(String[] args) {
63  		try {
64  			getConnection();
65  			TableInfo[] infos = getTables();
66  			getColumns(infos[0]);
67  		//	infos[0].setConnInfo(connInfo);
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 			// ResultSet rs2 =
224 			// tbInfo.getConn().createStatement().executeQuery("SELECT * from "
225 			// + tableName);
226 			// ResultSetMetaData rsmd = rs2.getMetaData();
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 			// throw new DatabaseException("Erreur dans la r�cup�ration des
281 			// tables");
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 		// conn = getConnection();
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 }