1 package pl.aislib.tools.mapping.db;
2
3 import pl.aislib.tools.mapping.XmlDesc;
4
5 import java.sql.Connection;
6 import java.sql.DatabaseMetaData;
7 import java.sql.DriverManager;
8 import java.sql.PreparedStatement;
9 import java.sql.ResultSet;
10 import java.sql.ResultSetMetaData;
11 import java.sql.SQLException;
12 import java.sql.Statement;
13 import java.util.HashMap;
14 import java.util.Iterator;
15 import java.util.List;
16 import java.util.Set;
17 import java.util.Vector;
18
19 import org.jdom.Element;
20
21 /***
22 * @author Micha? Ja?tak, AIS.PL
23 * @author Daniel Rychcik, AIS.PL
24 */
25 public class Database {
26
27 private boolean verboseFlag;
28
29 private Connection connection;
30 private DatabaseMetaData dbmd;
31
32 private String dbCatalog;
33 private String dbDriver;
34 private String dbPassword;
35 private String dbSchema;
36 private String dbURL;
37 private String dbUser;
38
39 private HashMap tables;
40 private HashMap sequences;
41
42 /***
43 *
44 */
45 public Database(boolean tVerboseFlag) {
46 verboseFlag = tVerboseFlag;
47 tables = new HashMap();
48 sequences = new HashMap();
49 }
50
51 /***
52 *
53 */
54 public void setCatalog(String tCatalog) {
55 dbCatalog = tCatalog;
56 }
57
58 /***
59 *
60 */
61 public void setDriver(String tDriver) {
62 dbDriver = tDriver;
63 }
64
65 /***
66 *
67 */
68 public void setSchema(String tSchema) {
69 dbSchema = tSchema;
70 }
71
72 /***
73 *
74 */
75 public void connect(String tURL, String tUser, String tPassword)
76 throws SQLException {
77 dbURL = tURL;
78 dbUser = tUser;
79 dbPassword = tPassword;
80 connect();
81 }
82
83 /***
84 *
85 */
86 public void connect() throws SQLException {
87
88 if (dbURL == null) {
89 throw new SQLException ("Cannot estabilish connection: empty URL");
90 }
91
92 if (dbDriver != null) {
93 try {
94 Class.forName (dbDriver);
95 } catch (ClassNotFoundException e) {
96 throw new SQLException ("Can't find database driver: " + e.getMessage());
97 }
98 }
99
100 connection = DriverManager.getConnection (dbURL, dbUser, dbPassword);
101 dbmd = connection.getMetaData ();
102 }
103
104 /***
105 *
106 */
107 public void genericInfo() throws SQLException {
108
109 if ((connection == null) || (dbmd == null)) {
110 throw new SQLException ("Connection not initialized");
111 }
112
113 if (verboseFlag) {
114 System.err.println ("\nDBMS Name: " + dbmd.getDatabaseProductName () + ", version: ");
115 System.err.println (dbmd.getDatabaseProductVersion ());
116 System.err.println ("\nJDBC driver: " + dbmd.getDriverName () + ", version: " + dbmd.getDriverVersion ());
117 }
118 }
119
120 /***
121 *
122 */
123 public void readTables(boolean fixedTableTypes, Set table_names ) throws SQLException {
124
125 if ((connection == null) || (dbmd == null)) {
126 throw new SQLException ("Connection not initialized");
127 }
128
129 String[] types = new String[1];
130 Vector typesV = new Vector ();
131 ResultSet rs = null;
132
133 if (fixedTableTypes) {
134 typesV.add("TABLE");
135 typesV.add("VIEW");
136 } else {
137 rs = dbmd.getTableTypes ();
138 while (rs.next ()) {
139 typesV.add (rs.getString ("TABLE_TYPE"));
140 }
141 rs.close ();
142 }
143
144 types = (String[]) typesV.toArray (types);
145 if (verboseFlag) {
146 System.err.println ("\nSearch for following Database Table Types: ");
147 for (int i = 0, j = types.length; i < j; i++) {
148 System.err.print ("\t" + types[i]);
149 }
150 System.err.println ("");
151 }
152
153 if (verboseFlag) {
154 System.err.print ("\nProcessing tables: \t(C - columns, I - indexes, FK - foreign keys, "
155 +"PK - primary keys, S - additional info)");
156 }
157
158 rs = dbmd.getTables (dbCatalog, dbSchema, "%", types);
159 while (rs.next ()) {
160 String table_name = rs.getString ("TABLE_NAME");
161 if(table_names != null && !table_names.contains(table_name) ) {
162
163 continue;
164 }
165 Table table = new Table (rs.getString ("TABLE_NAME"));
166 if (verboseFlag) {
167 System.err.print ("\n\t" + table.getName () + "\n\t");
168 }
169 table.setType (rs.getString ("TABLE_TYPE"));
170 table.setCatalog (rs.getString ("TABLE_CAT"));
171 table.setSchema (rs.getString ("TABLE_SCHEM"));
172
173 readColumns (table);
174 readIndexes (table);
175 readPrimaryKeys (table);
176 readForeignKeys (table);
177 try {
178 readAdditionalInfo(table);
179 } catch (SQLException sqle) {
180 ;
181 }
182
183 tables.put (table.getName (), table);
184 }
185 rs.close ();
186 if (verboseFlag) { System.err.println (""); }
187 }
188
189 /***
190 * @author Daniel Rychcik, AIS.PL
191 * @author Micha? Ja?tak, AIS.PL
192 */
193 public void readSequences() throws SQLException {
194 PreparedStatement pstmt = null;
195 ResultSet rs = null;
196
197 if ((connection == null) || (dbmd == null)) {
198 throw new SQLException("Connection not initialized");
199 }
200
201 if (verboseFlag) {
202 System.err.println("\nSearch for Sequences:");
203 }
204
205 try {
206 pstmt = connection.prepareStatement("SELECT * FROM ALL_SEQUENCES WHERE (SEQUENCE_OWNER = ?)");
207 pstmt.setString(1, dbSchema);
208 rs = pstmt.executeQuery();
209 while (rs.next()) {
210 if (verboseFlag) {
211 System.err.println(" Found sequence "+rs.getString("SEQUENCE_NAME"));
212 }
213 Sequence seq = new Sequence();
214 seq.setName(rs.getString("SEQUENCE_NAME"));
215 seq.setSchema(dbSchema);
216 seq.setMinValue(rs.getBigDecimal("MIN_VALUE"));
217 seq.setMaxValue(rs.getBigDecimal("MAX_VALUE"));
218 seq.setIncrement(rs.getBigDecimal("INCREMENT_BY"));
219 seq.setInitialValue(rs.getBigDecimal("LAST_NUMBER"));
220 seq.setCycleFlag(new Boolean(rs.getBoolean("CYCLE_FLAG")));
221 seq.setOrderFlag(new Boolean(rs.getBoolean("ORDER_FLAG")));
222 seq.setCacheSize(rs.getBigDecimal("CACHE_SIZE"));
223 sequences.put(seq.getName(),seq);
224 }
225 } catch (SQLException sqle) {
226 ;
227 } finally {
228 if (rs != null) { try { rs.close(); } catch (SQLException sqle) { ; } }
229 if (pstmt != null) { try { pstmt.close(); } catch (SQLException sqle) { ; } }
230 if (verboseFlag) {
231 System.err.println("\n Exception occured during reading system table ALL_SEQUENCES "
232 + "\n - maybe not an Oracle database ?");
233 }
234 }
235 }
236
237
238
239 /***
240 *
241 */
242 public void readForeignKeys(Table table) throws SQLException {
243
244 if ((connection == null) || (dbmd == null)) {
245 throw new SQLException ("Connection not initialized");
246 }
247
248 ResultSet rs = dbmd.getImportedKeys(dbCatalog, dbSchema, table.getName ());
249 while (rs.next()) {
250 ForeignKey fKey = new ForeignKey(rs.getString ("FKCOLUMN_NAME"));
251 fKey.setSourceTableName(rs.getString ("FKTABLE_NAME"));
252 fKey.setSourceName(rs.getString ("FK_NAME"));
253
254 fKey.setDestinationTableName(rs.getString ("PKTABLE_NAME"));
255 fKey.setDestinationColumnName(rs.getString ("PKCOLUMN_NAME"));
256 fKey.setDestinationName(rs.getString ("PK_NAME"));
257
258 short temp = rs.getShort ("DELETE_RULE");
259 if (!rs.wasNull()) {
260 fKey.setDeleteRule(temp);
261 }
262 temp = rs.getShort ("UPDATE_RULE");
263 if (!rs.wasNull()) {
264 fKey.setUpdateRule(temp);
265 }
266 table.addForeignKey(fKey);
267 }
268 rs.close ();
269 if (verboseFlag) { System.err.print("\tFK"); }
270 }
271
272 /***
273 *
274 */
275 public void readPrimaryKeys(Table table) throws SQLException {
276
277 if ((connection == null) || (dbmd == null)) {
278 throw new SQLException ("Connection not initialized");
279 }
280
281 ResultSet rs = dbmd.getPrimaryKeys(dbCatalog, dbSchema, table.getName ());
282 while (rs.next()) {
283 PrimaryKey pKey = new PrimaryKey(rs.getString("COLUMN_NAME"));
284 pKey.setCatalog(rs.getString("TABLE_CAT"));
285 pKey.setSchema(rs.getString("TABLE_SCHEM"));
286 pKey.setTableName(rs.getString("TABLE_NAME"));
287 pKey.setName(rs.getString("PK_NAME"));
288 short temp = rs.getShort("KEY_SEQ");
289 if (!rs.wasNull()) {
290 pKey.setSequenceNumber(temp);
291 }
292 table.addPrimaryKey(pKey);
293 }
294 rs.close ();
295 if (verboseFlag) { System.err.print ("\tPK"); }
296 }
297
298 /***
299 *
300 */
301 public void readColumns(Table table) throws SQLException {
302
303 if ((connection == null) || (dbmd == null)) {
304 throw new SQLException ("Connection not initialized");
305 }
306
307 ResultSet rs = dbmd.getColumns(dbCatalog, dbSchema, table.getName (), "%");
308 while (rs.next()) {
309 Column column = new Column(rs.getString("COLUMN_NAME"));
310 column.setCatalog(rs.getString("TABLE_CAT"));
311 column.setSchema(rs.getString("TABLE_SCHEM"));
312 column.setTableName(rs.getString("TABLE_NAME"));
313 column.setDataType(rs.getShort("DATA_TYPE"));
314 column.setTypeName(rs.getString("TYPE_NAME"));
315 int temp = rs.getInt("COLUMN_SIZE");
316 if (!rs.wasNull()) {
317 column.setColumnSize(temp);
318 }
319 temp = rs.getInt("DECIMAL_DIGITS");
320 if (!rs.wasNull()) {
321 column.setDecimalDigits(temp);
322 }
323 temp = rs.getInt("NULLABLE");
324 if (!rs.wasNull()) {
325 column.setNullable(temp);
326 }
327 column.setColumnDef(rs.getString("COLUMN_DEF"));
328 temp = rs.getInt("ORDINAL_POSITION");
329 if (!rs.wasNull()) {
330 column.setOrdinalPosition(temp);
331 }
332 table.addColumn(column);
333 }
334 rs.close ();
335 if (verboseFlag) { System.err.print("\tC"); }
336 }
337
338 /***
339 *
340 */
341 public void readIndexes(Table table) throws SQLException {
342
343 if ((connection == null) || (dbmd == null)) {
344 throw new SQLException ("Connection not initialized");
345 }
346
347 if (! table.getType().equalsIgnoreCase("table")) { return; }
348 ResultSet rs = dbmd.getIndexInfo(dbCatalog, dbSchema, table.getName (), false, false);
349 while (rs.next()) {
350 Index index = new Index(rs.getShort("ORDINAL_POSITION"));
351 index.setCatalog(rs.getString("TABLE_CAT"));
352 index.setSchema(rs.getString("TABLE_SCHEM"));
353 index.setTableName(rs.getString("TABLE_NAME"));
354 index.setName(rs.getString("INDEX_NAME"));
355 index.setColumnName(rs.getString("COLUMN_NAME"));
356 table.addIndex(index);
357 }
358 rs.close ();
359 if (verboseFlag) { System.err.print("\tI"); }
360 }
361
362 /***
363 *
364 */
365 public void readAdditionalInfo (Table table) throws SQLException {
366 Statement stmt = null;
367 ResultSet rs = null;
368
369 if ((connection == null) || (dbmd == null)) {
370 throw new SQLException ("Connection not initialized");
371 }
372
373 try {
374 stmt = connection.createStatement();
375 stmt.setFetchSize(1);
376 stmt.setMaxRows(1);
377 rs = stmt.executeQuery("SELECT * FROM " + table.getName());
378
379 ResultSetMetaData rsmd = rs.getMetaData();
380 for (int i = 1, j = rsmd.getColumnCount(); i <= j; i++) {
381 String columnName = rsmd.getColumnName(i);
382 Column column = table.getColumn(columnName);
383 if (column != null) {
384 column.setClassName(rsmd.getColumnClassName (i));
385 }
386 }
387 } catch (SQLException sqle) {
388 throw sqle;
389 } finally {
390 if (rs != null) { try { rs.close(); } catch (SQLException sqle) { ; } }
391 if (stmt != null) { try { stmt.close(); } catch (SQLException sqle) { ; } }
392 }
393 if (verboseFlag) { System.err.print("\tS"); }
394 }
395
396 /***
397 *
398 */
399 public String toString() {
400 return new String("" + tables + sequences);
401 }
402
403 /***
404 *
405 * @return
406 */
407 public List toSeparateXMLs() {
408 List xmls = new Vector();
409 if (!tables.isEmpty ()) {
410
411 for (Iterator it = tables.keySet ().iterator (); it.hasNext (); ) {
412
413 String tableName = (String) it.next ();
414 Table table = (Table) tables.get (tableName);
415 Element result = table.toXML();
416 xmls.add( new XmlDesc( tableName, result ) );
417 }
418 }
419 if (!sequences.isEmpty()) {
420
421 for (Iterator it = sequences.keySet().iterator(); it.hasNext();) {
422
423 String sequenceName = (String) it.next();
424 Sequence sequence = (Sequence) sequences.get(sequenceName);
425 Element result = sequence.toXML();
426
427 xmls.add( new XmlDesc( sequenceName, result ) );
428 }
429 }
430 return xmls;
431 }
432
433 /***
434 *
435 */
436 public Element toXML() {
437 Element result = new Element ("database");
438 if (!tables.isEmpty ()) {
439 for (Iterator it = tables.keySet ().iterator (); it.hasNext (); ) {
440 String tableName = (String) it.next ();
441 Table table = (Table) tables.get (tableName);
442 result.addContent (table.toXML ());
443 }
444 }
445 if (!sequences.isEmpty()) {
446 for (Iterator it = sequences.keySet().iterator(); it.hasNext();) {
447 String sequenceName = (String) it.next();
448 Sequence sequence = (Sequence) sequences.get(sequenceName);
449 result.addContent(sequence.toXML());
450 }
451 }
452 return result;
453 }
454
455 }