Java > Open Source Codes > jimm > datavision > source > sql > Database


1 package jimm.datavision.source.sql;
2 import jimm.datavision.*;
3 import jimm.datavision.source.*;
4 import jimm.util.XMLWriter;
5 import jimm.util.I18N;
6 import java.sql.*;
7 import java.util.*;
8
9 /**
10  * A database knows about the tables in a database.
11  *
12  * @author Jim Menard, <a HREF="mailto:jimm@io.com">jimm@io.com</a>
13  */
14 public class Database extends DataSource {
15
16 protected static final String [] DB_OBJECT_TYPES = { "TABLE", "VIEW" };
17
18 protected String driverClassName;
19 protected String connInfo;
20 protected String name;
21 protected String username;
22 protected TreeMap tables;
23 protected HashMap tableCacheMap;
24 protected String schemaName;
25 protected Connection conn;
26 private String password;
27 protected boolean hasPassword;
28 protected boolean connectionOwnedByMe;
29 protected boolean storesLowerCaseIdentifiers;
30 protected boolean storesUpperCaseIdentifiers;
31
32 public Database(Connection conn, Report report) throws SQLException {
33     super(report, new SQLQuery(report));
34
35     this.driverClassName = "";
36     this.connInfo = "";
37     this.name = "";
38     this.username = "";
39     this.password = ""; // Not null so we won't bother asking
40 hasPassword = true;
41     tables = null;
42
43     this.conn = conn;
44     connectionOwnedByMe = false;
45     loadAllTables();
46 }
47
48 /**
49  * Constructor.
50  *
51  * @param driverClassName database driver class name
52  * @param connInfo database connection info string
53  * @param report the report using this database
54  * @param name the database name
55  * @param user the user name to use when logging in to the database
56  */
57 public Database(String driverClassName, String connInfo, Report report,
58         String name, String user)
59     throws SQLException, ClassNotFoundException , InstantiationException ,
60        IllegalAccessException , UserCancellationException
61 {
62     this(driverClassName, connInfo, report, name, user, null, false);
63 }
64
65 /**
66  * Constructor.
67  *
68  * @param driverClassName database driver class name
69  * @param connInfo database connection info string
70  * @param report the report using this database
71  * @param name the database name
72  * @param user the user name to use when logging in to the database
73  * @param password the database password
74  */
75 public Database(String driverClassName, String connInfo, Report report,
76         String name, String user, String password)
77     throws SQLException, ClassNotFoundException , InstantiationException ,
78        IllegalAccessException , UserCancellationException
79 {
80     this(driverClassName, connInfo, report, name, user, password, true);
81 }
82
83
84 /**
85  * Constructor.
86  *
87  * @param driverClassName database driver class name
88  * @param connInfo database connection info string
89  * @param report the report using this database
90  * @param name the database name
91  * @param user the user name to use when logging in to the database
92  * @param password the database password
93  * @param givenPassword if <code>true</code>, the password was passed in
94  * to some other constructor
95  */
96 protected Database(String driverClassName, String connInfo, Report report,
97            String name, String user, String password,
98            boolean givenPassword)
99     throws SQLException, ClassNotFoundException , InstantiationException ,
100        IllegalAccessException , UserCancellationException
101 {
102     super(report, new SQLQuery(report));
103
104     this.driverClassName = driverClassName;
105     this.connInfo = connInfo;
106     this.name = name;
107     this.username = (user == null) ? "" : user;
108     this.password = password;
109     tables = null;
110     hasPassword = givenPassword;
111
112     initializeConnection();
113     loadAllTables();
114 }
115
116 public boolean canJoinTables() { return true; }
117 public boolean isSQLGenerated() { return true; }
118 public boolean isConnectionEditable() { return true; }
119 public boolean areRecordsSelectable() { return true; }
120 public boolean areRecordsSortable() { return true; }
121 public boolean canGroupRecords() { return true; }
122
123 /**
124  * Given an id (a column name), returns the column that has that id. If no
125  * column with the specified id exists, returns <code>null</code>. Uses
126  * <code>Table.findColumn</code>.
127  *
128  * @param id a column id
129  * @return a column, or <code>null</code> if no column with the specified
130  * id exists
131  * @see Table#findColumn
132  */
133 public Column findColumn(Object id) {
134     if (tables == null)
135     return null;
136
137     String str = id.toString();
138     int pos = str.lastIndexOf('.');
139     String tableName = str.substring(0, pos);
140     Table t = findTable(tableName);
141     return t == null ? null : t.findColumn(id);
142 }
143
144 /**
145  * Given a table name, find the table. The table name may have a schema name
146  * or not. We look for the table first using <var>tableName</var> as-is,
147  * then we use this database's schema name, then we try no schema name
148  * at all.
149  *
150  * @param tableName a table name, perhaps including a schema name.
151  */
152 protected Table findTable(String tableName) {
153     // First try a simple exact match using tableCacheMap. This will often
154 // fail the first time, but after we have found a table using the quite
155 // convoluted search below we store the table in tableCacheMap.
156 Table t = (Table)tableCacheMap.get(tableName);
157     if (t != null)
158     return t;
159
160     String origTableName = tableName; // Preserve passed-in value for map key
161
162     String schemaName = null;
163     int pos = tableName.indexOf('.');
164     if (pos >= 0) {
165     schemaName = tableName.substring(0, pos);
166     tableName = tableName.substring(pos + 1);
167     }
168
169     if (!getReport().caseSensitiveDatabaseNames()) {
170     if (schemaName != null) schemaName = schemaName.toLowerCase();
171     tableName = tableName.toLowerCase();
172     }
173
174     // First try with table's schema name, if any.
175 if (schemaName != null) {
176     if ((t = findTableWithId(schemaName + '.' + tableName)) != null) {
177         tableCacheMap.put(origTableName, t);
178         return t;
179     }
180     }
181
182     // Now try with database's schema name if it's different from the
183 // table's schema name.
184 if (name != null) {
185     String dbSchemaName = name;
186     if (!getReport().caseSensitiveDatabaseNames())
187         dbSchemaName = dbSchemaName.toLowerCase();
188
189     if (!dbSchemaName.equals(schemaName)) {
190         if ((t = findTableWithId(dbSchemaName + '.' + tableName)) != null)
191         {
192         tableCacheMap.put(origTableName, t);
193         return t;
194         }
195     }
196     }
197
198     // Finally, try with just the table name.
199 if ((t = findTableWithId(tableName)) != null) {
200     tableCacheMap.put(origTableName, t);
201     return t;
202     }
203
204     return null;
205 }
206
207 /**
208  * Finds table with given <var>id</var> string. Returns <code>null</code> if
209  * it is not found. If the report says that database names are not
210  * case-sensitive, then we do a case-insensitive comparison.
211  *
212  * @param id a table id; if names are not case-sensitive then <var>id</var>
213  * will be lower-case when it is passed in
214  */
215 protected Table findTableWithId(String id) {
216     boolean caseSensitive = getReport().caseSensitiveDatabaseNames();
217     for (Iterator iter = tables.keySet().iterator(); iter.hasNext(); ) {
218     String key = (String )iter.next();
219     if (key.equals(id) || (!caseSensitive && key.equalsIgnoreCase(id)))
220         return (Table)tables.get(key);
221     }
222
223     return null;
224 }
225
226 public Iterator tables() {
227     return tables.values().iterator();
228 }
229
230 public Iterator tablesUsedInReport() {
231     return ((SQLQuery)query).getTablesUsed().iterator();
232 }
233
234 public Iterator columns() {
235     return new ColumnIterator(tables.values().iterator());
236 }
237
238 public DataCursor execute() throws SQLException {
239     return new ResultSetRow(conn, (SQLQuery)query);
240 }
241
242 public boolean storesLowerCaseIdentifiers() {
243     return storesLowerCaseIdentifiers;
244 }
245
246 public boolean storesUpperCaseIdentifiers() {
247     return storesUpperCaseIdentifiers;
248 }
249
250 /**
251  * Initializes the connection to the database.
252  *
253  * @return a connection to the database
254  */
255 public void initializeConnection()
256     throws ClassNotFoundException , InstantiationException ,
257        IllegalAccessException , UserCancellationException
258 {
259     // Keep trying until we succeed. We will only exit if we succeed,
260 // if the user cancels, or if something bad happens while obtaining
261 // the database connection.
262 boolean ok = false;
263     while (!ok) {
264     if (!hasPassword) {
265         // Calling askForPassword sets our user name and password
266 report.askForPassword(this);
267         hasPassword = true;
268     }
269     if (password == null)
270         throw new UserCancellationException(I18N.get("Database.cancelled"));
271
272     try {
273         if (connInfo == null || connInfo.length() == 0)
274         throw new IllegalArgumentException (I18N.get("Database.missing_conn_info"));
275         if (username == null || username.length() == 0)
276         throw new IllegalArgumentException (I18N.get("Database.missing_user_name"));
277         if (password == null)
278         throw new IllegalArgumentException (I18N.get("Database.null_password"));
279
280         // Load the database JDBC driver
281 Driver d = (Driver)Class.forName(driverClassName).newInstance();
282         DriverManager.registerDriver(d);
283
284         // Connect to the database
285 conn = DriverManager.getConnection(connInfo, username, password);
286         connectionOwnedByMe = true;
287
288         ok = true;
289     }
290     catch (SQLException sqle) { // Force another password request
291 ErrorHandler.error(sqle);
292         hasPassword = false;
293         report.setDatabasePassword(null);
294     }
295     }
296 }
297
298 /**
299  * Returns a connection to the database.
300  *
301  * @return a connection to the database
302  */
303 public Connection getConnection() { return conn; }
304
305 /**
306  * Reset key instance variables, closes current connection, and "reloads"
307  * all table information (compares new info with existing info and complains
308  * if any existing info is not in the new info).
309  * <p>
310  * <em>Note:</em> if the connection we currently have was created by this
311  * object, we close it. If the connection was handed to us, we do
312  * <em>not</em> close the connection.
313  *
314  * @param driverClassName database driver class name
315  * @param connInfo database connection info string
316  * @param dbName database name
317  * @param username the user name to use when logging in to the database
318  */
319 public void reset(String driverClassName, String connInfo, String dbName,
320           String username, String password)
321     throws SQLException, ClassNotFoundException , InstantiationException ,
322        IllegalAccessException , UserCancellationException
323 {
324     setDriverClassName(driverClassName);
325     setConnectionInfo(connInfo);
326     setName(dbName);
327     setUserName(username);
328     this.password = password;
329     hasPassword = true;
330
331     if (conn != null) {
332     if (connectionOwnedByMe)
333         conn.close();
334     conn = null;
335     }
336     initializeConnection();
337     loadAllTables();
338
339     report.reloadColumns();
340 }
341
342 /**
343  * Loads information about all tables in the database. If no tables are
344  * found when using the database schema name, try again with a
345  * <code>null</code> schema name.
346  */
347 protected void loadAllTables() throws SQLException {
348     tables = new TreeMap();
349     tableCacheMap = new HashMap();
350     schemaName = null;
351
352     DatabaseMetaData dbmd = getConnection().getMetaData();
353     storesLowerCaseIdentifiers = dbmd.storesLowerCaseIdentifiers();
354     storesUpperCaseIdentifiers = dbmd.storesUpperCaseIdentifiers();
355
356     try {
357     // Specify both schema name and DB_OBJECT_TYPES.
358 loadTablesUsingSchemaNameAndTypes(dbmd, name, DB_OBJECT_TYPES);
359     } catch (SQLException e) {}
360     catch (NullPointerException npe) {}
361
362     try {
363     if (tables.isEmpty() && name != null) // Only schema name
364 loadTablesUsingSchemaNameAndTypes(dbmd, name, null);
365     } catch (SQLException e) {}
366     catch (NullPointerException npe) {}
367
368     try {
369     if (tables.isEmpty()) // No schema name, use types
370 loadTablesUsingSchemaNameAndTypes(dbmd, null, DB_OBJECT_TYPES);
371     } catch (SQLException e) {}
372     catch (NullPointerException npe) {}
373
374     // If no tables found, try again with null database name. This time,
375 // throw an exception if there is a problem.
376 if (tables.isEmpty()) // No schema name, no types
377 loadTablesUsingSchemaNameAndTypes(dbmd, null, null);
378 }
379
380 /**
381  * Loads our list of tables using a database meta data object and a
382  * schema name. The schema name may be <code>null</code>.
383  *
384  * @param dbmd the database meta data object
385  * @param objectTypes a list of database object types
386  * @param schema the schema name; may be <code>null</code>
387  */
388 protected void loadTablesUsingSchemaNameAndTypes(DatabaseMetaData dbmd,
389                          String schema,
390                          String [] objectTypes)
391     throws SQLException
392 {
393     ResultSet rset = dbmd.getTables(null, schema, "%", objectTypes);
394     if (rset == null) return;
395
396     boolean schemaNameFailed = false; // Avoid banging our head against a wall
397 while (rset.next()) {
398     String name = rset.getString("TABLE_NAME").trim();
399
400     if (!schemaNameFailed) {
401         try {
402         schemaName = rset.getString("TABLE_SCHEM");
403         }
404         catch (SQLException sqle) {
405         schemaNameFailed = true;
406         }
407         if (schemaName != null && schemaName.length() > 0)
408         name = schemaName.trim() + '.' + name;
409     }
410
411     SQLTable t = new SQLTable(this, name, dbmd);
412     tables.put(t.getId().toString(), t);
413     }
414     rset.close();
415 }
416
417 /**
418  * Returns the driver class name.
419  *
420  * @return the driver class name
421  */
422 public String getDriverClassName() { return driverClassName; }
423
424 /**
425  * Sets the driver class name.
426  *
427  * @param newDriverClassName the driver class name
428  */
429 protected void setDriverClassName(String newDriverClassName) {
430     driverClassName = newDriverClassName;
431 }
432
433 /**
434  * Returns the connection info string.
435  *
436  * @return the connection info string
437  */
438 public String getConnectionInfo() { return connInfo; }
439
440 /**
441  * Sets the connection info string.
442  *
443  * @param newConnectionInfo the connection info string
444  */
445 protected void setConnectionInfo(String newConnectionInfo) {
446     connInfo = newConnectionInfo;
447 }
448
449 /**
450  * Returns the database name.
451  *
452  * @return the database name
453  */
454 public String getName() { return name; }
455
456 /**
457  * Sets the name.
458  *
459  * @param newName the new name
460  */
461 protected void setName(String newName) { name = newName; }
462
463 /**
464  * Returns the user name.
465  *
466  * @return the user name
467  */
468 public String getUserName() { return username; }
469
470 /**
471  * Sets the user name.
472  *
473  * @param newUserName the new user name
474  */
475 public void setUserName(String newUserName) { username = newUserName; }
476
477 /**
478  * Returns the password.
479  *
480  * @return the password
481  */
482 public String getPassword() { return hasPassword ? password : null; }
483
484 /**
485  * Sets the password.
486  *
487  * @param newPassword the new password
488  */
489 public void setPassword(String newPassword) {
490     password = newPassword;
491     hasPassword = true;
492 }
493
494 /**
495  * Writes this database and all its tables as an XML tag.
496  *
497  * @param out a writer that knows how to write XML
498  */
499 protected void doWriteXML(XMLWriter out) {
500     out.startElement("database");
501     out.attr("driverClassName", driverClassName);
502     out.attr("connInfo", connInfo);
503     out.attr("name", name);
504     out.attr("username", username);
505     out.endElement();
506 }
507
508 }
509

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags