![]() |
|||
![]() ![]() |
![]() |
![]()
|
![]() |
Database Engine Specifications and Limitations Four specification sheets are available. The categories are somewhat arbitrary, but it is more convenient to see them split in four screens. Figure 10-12 illustrates one of the four sheets. It shows a small part of the features supported by the database engine. Each topic has its corresponding JDBC database metadata method, which, in general, returns true or false.
Browsing Database Catalogs and Their Content The following items are the most interesting. The main navigation widget allows you to browse through many database objects by expanding and collapsing its nodes. Each activation of a node issues database metadata methods to dynamically discover the database objects. Figure 10-13 shows the database catalog nodes.
Figure 10-14 shows the catalog subnodes.
For example, when double-clicking on the Objects Catalogs node, the program fetches information about database objects such as system tables, user tables, and stored procedures. Catalogs are simply database subspaces. In a DBMS managing multiple databases, a catalog is one database. As shown in Figure 10-15, all table nodes give useful structure information such as the columns and types it contains, and the primary, imported, and exported keys.
The procedure node lists all database-stored procedures in the current catalog and displays the results of the DatabaseMetaData.getProcedureColumns() method. This is shown in Figure 10-16.
The procedure node lists all database-stored procedures in the current catalog and displays the results of the DatabaseMetaData.getProcedureColumns() method. Extra Tools: An Interactive SQL Gadget Figure 10-17 depicts the interactive SQL gadget.
The tools node contains only one tool, ISQL. This is the interactive ISQL query tool explained earlier. It supports SQL queries and SQL updates that can be entered in the little query window. Figure 10-18 shows the window that accepts SQL statements.
As shown in Figure 10-19, the results finally appear in a nice, formatted spreadsheet-like window. It is possible to sort the data again with a mouse click on any column label.
This stand-alone application is easily rewritten to run as an applet within a WWW browser such as Netscape Navigator or Microsoft Internet Explorer. Adding BLOB import/export tools to allow the insertion and retrieval of multimedia content such as pictures is an interesting exercise. See the specific examples on this topic at the beginning of this chapter. Sources Listing 10-22 contains the source code for the Java database explorer. Listing 10-22: Jexplorer.java. import java.io.*; import java.awt.*; import java.sql.*; import java.lang.*; import java.util.*; import marimba.gui.*; /** * the main class */ public class Jexplorer extends PlayerFrame { Connection conn = null; DatabaseMetaData mtdt = null; ResultSet rs = null; String text = ""; static final String welcome = "\n Welcome to my universal database explorer.\n" + " This is a 100% Java stand-alone application. It works with JDBC 1.21\n\n" + " (C) 1996, 1997 by Bernard Van Haecke.\n" + " Comments and questions to: bernard.vanhaecke@Belgium.Sun.COM\n\n"; static final String extracomment = "\n\n\n\n\n\n" + " Choose a JDBC driver to target the database of your choice, then log\n" + " in and double click on any tree node to begin the exploration. Have fun!"; /** * constructor */ public Jexplorer() { } /** * we handle all gui events here */ public boolean handleEvent(Event evt) { try { if ((evt.id == Event.ACTION_EVENT) && (evt.target instanceof Widget)) { Widget w = (Widget)evt.target; String nm = w.getName(); if (nm != null) System.out.println("Event: " + nm); // The user has logged in. if (nm.equals("dataLoginButton")) { String url = util.getText("dataURL").trim(); String uid = util.getText("dataLogin").trim(); String pwd = util.getText("dataPasswd").trim(); String driver = util.getText("dataDriver").trim(); text = welcome; try { Class.forName(driver); conn = DriverManager.getConnection(url, uid, pwd); if (conn != null) { text = text + "You are now logged in. Enjoy..."; mtdt = conn.getMetaData(); } } catch (SQLException ex) { text = text + "Could not log into the database. Verify the parameters:\nURL: " + url + "\nDriver: " + driver + "\n\n"; text = text + ex; System.out.println(ex); } catch (java.lang.Exception ex) { System.out.println(ex); } util.setText("dataWindowControl", text); } // The user has clicked logout if (nm.equals("dataLogoutButton")) { TreeNodeWidget catalogRoot = (TreeNodeWidget) util.getWidget("catalogsNode"); catalogRoot.clear(); util.show("isqlQueryWindow", false); util.show("isqlResultWindow", false); if (conn != null) { conn.close(); text = welcome + "You are now logged off. Bye bye..."; } else text = welcome + extracomment; util.setText("dataWindowControl", text); } // The user has clicked exit if (nm.equals("dataExitButton")) { System.out.println("Normal termination"); if (conn != null) conn.close(); System.exit(0); return true; } // Database Node ////////////////////////////////////////////// if (nm.equals("databaseNode")) { util.setText("dataWindowLabel", nm); text = welcome; text = text + extracomment; util.setText("dataWindowControl", text); } // Session Node /////////////////////////////////////////////// if (nm.equals("sessionNode")) { util.setText("dataWindowLabel", nm); text ="\nURL in use\t: " + mtdt.getURL() + "\nUsername\t: " + mtdt.getUserName(); util.setText("dataWindowControl", text); } // Engine Node //////////////////////////////////////////////// if (nm.equals("engineNode")) { util.setText("dataWindowLabel", nm); text = "\nMiscellaneous database engine informations."; util.setText("dataWindowControl", text); } // give database informations if (nm.equals("infoNode")) { util.setText("dataWindowLabel", nm); text = "\nDatabase\t: " + mtdt.getDatabaseProductName() + "\nVersion\t: " + mtdt.getDatabaseProductVersion() + "\n\nDriver\t: " + mtdt.getDriverName() + "\nVersion\t: " + mtdt.getDriverVersion(); util.setText("dataWindowControl", text); } // give database specs if (nm.equals("specsNode")) { util.setText("dataWindowLabel", nm); text = "\nUses local files\t\t\t\t\t: " + mtdt.usesLocalFiles() + "\nUses local file per table\t\t\t: " + mtdt.usesLocalFilePerTable() + "\nNulls are sorted high\t\t\t: " + mtdt.nullsAreSortedHigh() + "\nNulls are sorted at end\t\t\t: " + mtdt.nullsAreSortedAtEnd() + "\nSupports mixed case identifiers\t: " + mtdt.supportsMixedCaseIdentifiers() + "\nStores mixed case identifiers\t\t: " + mtdt.storesMixedCaseIdentifiers() + "\nIdentifier quote string\t\t\t: " + mtdt.getIdentifierQuoteString() + "\n\nSupported SQL keywords\t: " + mtdt.getSQLKeywords() + "\n\nNumeric functions\t: " + mtdt.getNumericFunctions() + "\n\nString functions\t: " + mtdt.getStringFunctions() + "\n\nSystem functions\t: " + mtdt.getSystemFunctions() + "\n\nTime and date functions\t: " + mtdt.getTimeDateFunctions(); util.setText("dataWindowControl", text); } // give database features if (nm.equals("featuresNode")) { util.setText("dataWindowLabel", nm); text = "\nThe database supports:\n" + "\nMinimun SQL grammar\t\t: " + mtdt.supportsMinimumSQLGrammar() + "\nCore SQL grammar\t\t\t: " + mtdt.supportsCoreSQLGrammar() + "\nExtended SQL grammar\t\t: " + mtdt.supportsExtendedSQLGrammar() + "\nANSI-92 Entry Level SQL\t: " + mtdt.supportsANSI92EntryLevelSQL() + "\nANSI-92 Intermediate SQL\t: " + mtdt.supportsANSI92IntermediateSQL() + "\nANSI-92 Full SQL grammar\t: " + mtdt.supportsANSI92FullSQL() + "\nIntegrity enhancement facility\t: " + mtdt.supportsIntegrityEnhancementFacility() + "\nOuter joins\t\t\t\t: " + mtdt.supportsOuterJoins() + "\nFull outer joins\t\t\t\t: " + mtdt.supportsFullOuterJoins() + "\nLimited outer joins\t\t\t: " + mtdt.supportsLimitedOuterJoins() + "\n\nAlter table with add column\t: " + mtdt.supportsAlterTableWithAddColumn() + "\nAlter table with drop column\t: " + mtdt.supportsAlterTableWithDropColumn() + "\nColumn aliasing\t\t\t: " + mtdt.supportsColumnAliasing() + "\nTable correlation names\t\t: " + mtdt.supportsTableCorrelationNames() + "\nExpressions in order by\t\t: " + mtdt.supportsExpressionsInOrderBy() + "\nUnrelated order by\t\t\t: " + mtdt.supportsOrderByUnrelated() + "\nGroup by\t\t\t\t\t: " + mtdt.supportsGroupBy() + "\nUnrelated group by\t\t\t: " + mtdt.supportsGroupByUnrelated() + "\nGroup by beyond select\t\t: " + mtdt.supportsGroupByBeyondSelect() + "\nLike escape clause\t\t\t: " + mtdt.supportsLikeEscapeClause() + "\nMultiple result sets\t\t\t: " + mtdt.supportsMultipleResultSets() + "\nMultiple transactions\t\t: " + mtdt.supportsMultipleTransactions() + "\nNon nullable columns\t\t: " + mtdt.supportsNonNullableColumns() + "\n\nTerm for schemas\t: " + mtdt.getSchemaTerm() + "\nTerm for procedures\t: " + mtdt.getProcedureTerm() + "\nTerm for catalogs\t: " + mtdt.getCatalogTerm() + "\n\n..."; util.setText("dataWindowControl", text); } // give database limitations if (nm.equals("limitationsNode")) { util.setText("dataWindowLabel", nm); text = "\nMaximums:\n" + "\nBinary literal length\t\t: " + mtdt.getMaxBinaryLiteralLength() + "\nCharacter literal length\t: " + mtdt.getMaxCharLiteralLength() + "\nColumn name length\t: " + mtdt.getMaxColumnNameLength() + "\nColumns in group by\t\t: " + mtdt.getMaxColumnsInGroupBy() + "\nColumns in index\t\t: " + mtdt.getMaxColumnsInIndex() + "\nColumns in order by\t\t: " + mtdt.getMaxColumnsInOrderBy() + "\nColumns in select\t\t: " + mtdt.getMaxColumnsInSelect() + "\nColumns in table\t\t: " + mtdt.getMaxColumnsInTable() + "\nNumber of connections\t: " + mtdt.getMaxConnections() + "\n"; util.setText("dataWindowControl", text); } // Object Node //////////////////////////////////////////////// if (nm.equals("objectsNode")) { util.setText("dataWindowLabel", nm); text = "\nDatabase objects."; util.setText("dataWindowControl", text); } // browse database catalogs if (nm.equals("catalogsNode")) { util.setText("dataWindowLabel", nm); text = getRS(mtdt.getCatalogs()); util.setText("dataWindowControl", text); // get a vector of catalogs Vector v = getRSColumnAsVector(mtdt.getCatalogs(), "TABLE_CAT"); // Vector v = getRSColumnAsVector(mtdt.getCatalogs(), "TABLE_QUALIFIER"); boolean useCatalog = true; // create a default catalog if the database // does not support catalogs if (v.size() == 0) { useCatalog = false; v.addElement("default"); } // add nodes for each catalog TreeNodeWidget catalogRoot = (TreeNodeWidget) util.getWidget("catalogsNode"); if (catalogRoot.hasChildren()) catalogRoot.clear(); for (int i=0; i < v.size(); i++) { addCatalogNode(catalogRoot, (String) v.elementAt(i)); } } // browse tables in this catalog if (nm.startsWith("newTableColumnsNode.")) { util.setText("dataWindowLabel", nm); int p1 = nm.indexOf("."); int p2 = nm.indexOf(".", p1 + 1); String catalog = nm.substring(p1 + 1, p2); if (catalog.equals("null")) catalog = null; String table = nm.substring(p2 + 1); text = getRS(mtdt.getColumns(catalog, null, table, "%")); util.setText("dataWindowControl", text); } // browse primary keys for this table if (nm.startsWith("newTablePrimaryKeysNode.")) { util.setText("dataWindowLabel", nm); int p1 = nm.indexOf("."); int p2 = nm.indexOf(".", p1 + 1); String catalog = nm.substring(p1 + 1, p2); if (catalog.equals("null")) catalog = null; String table = nm.substring(p2 + 1); text = getRS(mtdt.getPrimaryKeys(catalog, null, table)); util.setText("dataWindowControl", text); } // browse imported keys for this table if (nm.startsWith("newTableImportedKeysNode.")) { util.setText("dataWindowLabel", nm); int p1 = nm.indexOf("."); int p2 = nm.indexOf(".", p1 + 1); String catalog = nm.substring(p1 + 1, p2); if (catalog.equals("null")) catalog = null; String table = nm.substring(p2 + 1); text = getRS(mtdt.getImportedKeys(catalog, null, table)); util.setText("dataWindowControl", text); } // browse exported keys for this table if (nm.startsWith("newTableExportedKeysNode.")) { util.setText("dataWindowLabel", nm); int p1 = nm.indexOf("."); int p2 = nm.indexOf(".", p1 + 1); String catalog = nm.substring(p1 + 1, p2); if (catalog.equals("null")) catalog = null; String table = nm.substring(p2 + 1); text = getRS(mtdt.getExportedKeys(catalog, null, table)); util.setText("dataWindowControl", text); } // browse procedures in this catalog if (nm.startsWith("newProcedureNode.")) { util.setText("dataWindowLabel", nm); int p1 = nm.indexOf("."); int p2 = nm.indexOf(".", p1 + 1); String catalog = nm.substring(p1 + 1, p2); if (catalog.equals("null")) catalog = null; String procedure = nm.substring(p2 + 1); text = getRS(mtdt.getProcedureColumns(catalog, null, procedure, "%")); util.setText("dataWindowControl", text); } // Tools Node ///////////////////////////////////////////////// if (nm.equals("toolsNode")) { util.setText("dataWindowLabel", nm); text = ""; util.setText("dataWindowControl", text); } // display the isql query window if (nm.equals("isqlNode")) { util.setText("dataWindowLabel", nm); text = "\nType your queries in the floating query window..."; util.setText("dataWindowControl", text); util.show("isqlQueryWindow", true); } // execute the sql query if (nm.equals("isqlGoButton")) { String query = util.getText("isqlQueryText"); // test if we want a nicely formatted result if (((CheckBoxWidget) util.getWidget("isqlFormatCheckBox")).getBooleanValue()) { TableWidget tbl = (TableWidget) util.getWidget("isqlResultTable"); tbl.removeAllRows(); tbl.removeAllColumns(); util.show("isqlResultWindow", true); ResultSet rs = getSingleRS(query); Vector headers = getRSColumnHeadersAsVector(rs); int i; for (i=0; i<headers.size(); i++) //tbl.addColumn((String) headers.elementAt(i), ((String) headers.elementAt(i)).length()); tbl.addColumn((String) headers.elementAt(i)); Vector rows = getRSRowsAsVector(rs); for (i=0; i<rows.size(); i++) tbl.addRow((Vector) rows.elementAt(i)); } else { text = getMultipleRS(query); util.appendText("dataWindowControl", "\n\n" + text); } } // close the isql query window if (nm.equals("isqlCloseButton")) { util.show("isqlQueryWindow", false); util.show("isqlResultWindow", false); } // close the isql result window if (nm.equals("resultCloseButton")) { util.show("isqlResultWindow", false); } } } catch(java.lang.Exception ex) { // ex.printStackTrace(); } return super.handleEvent(evt); } /** * create a new catalog node */ public void addCatalogNode(TreeNodeWidget w, String label) { w.hide(); String catalogName = label; if (label.equals("default")) catalogName = null; TreeNodeWidget newCatalogNode = new TreeNodeWidget(); newCatalogNode.setName("newCatalogNode." + catalogName); newCatalogNode.setText(label); newCatalogNode.setImage("folder.gif"); w.addSorted(newCatalogNode); // add main node for tables TreeNodeWidget newCatalogTablesNode = addNode(newCatalogNode, "newCatalogTablesNode." + catalogName, "Tables", "folder.gif"); // add nodes for system tables TreeNodeWidget newTablesSystemNode = addNode(newCatalogTablesNode, "newTablesSystemNode." + catalogName, "System", "folder.gif"); String types[] = new String[1]; types[0] = "SYSTEM TABLE"; Vector v = null; try { v = getRSColumnAsVector(mtdt.getTables(catalogName, null, "%", types), "TABLE_NAME"); } catch (SQLException ex) { ex.printStackTrace(); } if (newTablesSystemNode.hasChildren()) newTablesSystemNode.clear(); for (int i=0; i < v.size(); i++) { addTableNode(newTablesSystemNode, catalogName, (String) v.elementAt(i)); } // add nodes for system tables TreeNodeWidget newTablesUserNode = addNode(newCatalogTablesNode, "newTablesUserNode." + catalogName, "User", "folder.gif"); types[0] = "TABLE"; v = null; try { v = getRSColumnAsVector(mtdt.getTables(catalogName, null, "%", types), "TABLE_NAME"); } catch (SQLException ex) { ex.printStackTrace(); } if (newTablesUserNode.hasChildren()) newTablesUserNode.clear(); for (int i=0; i < v.size(); i++) { addTableNode(newTablesUserNode, catalogName, (String) v.elementAt(i)); } // add main node for stored procedures TreeNodeWidget newCatalogProceduresNode = addNode(newCatalogNode, "newCatalogProceduresNode." + catalogName, "Procedures", "folder.gif"); // add nodes for procedures v = null; try { v = getRSColumnAsVector(mtdt.getProcedures(catalogName, null, "%"), "PROCEDURE_NAME"); } catch (SQLException ex) { ex.printStackTrace(); } if (newCatalogProceduresNode.hasChildren()) newCatalogProceduresNode.clear(); for (int i=0; i < v.size(); i++) { addNode(newCatalogProceduresNode, "newProcedureNode." + catalogName + "." + (String) v.elementAt(i), (String) v.elementAt(i), "archivs.gif"); } // collapse the catalog leaves w.collapseAll(); w.show(); } /** * create a new node for a table */ public void addTableNode(TreeNodeWidget w, String catalog, String label) { TreeNodeWidget newTableNode = new TreeNodeWidget(); newTableNode.setName("newTableNode." + catalog + "." + label); newTableNode.setText(label); newTableNode.setImage("table.gif"); w.addSorted(newTableNode); addNode(newTableNode, "newTableColumnsNode." + catalog + "." + label, "Columns", "archivs.gif"); addNode(newTableNode, "newTablePrimaryKeysNode." + catalog + "." + label, "Primary Keys", "archivs.gif"); addNode(newTableNode, "newTableImportedKeysNode." + catalog + "." + label, "Imported Keys", "archivs.gif"); addNode(newTableNode, "newTableExportedKeysNode." + catalog + "." + label, "Exported Keys", "archivs.gif"); w.collapseAll(); } /** * add a node */ public TreeNodeWidget addNode(TreeNodeWidget w, String name, String text, String image) { TreeNodeWidget child = new TreeNodeWidget(); child.setName(name); child.setText(text); child.setImage(image); w.add(child); return child; } /** * transform one column of a resultset into a vector */ public Vector getRSColumnAsVector(ResultSet rs, String column) { Vector v = new Vector(); try { while (rs.next()) { v.addElement(rs.getString(column)); } } catch (SQLException ex) { ex.printStackTrace(); } return v; } /** * return the resultset of a simple query */ public ResultSet getSingleRS(String sqlText) { ResultSet rs = null; try { Statement st = conn.createStatement(); if (st.execute(sqlText)) { // okay it's not an update count rs = st.getResultSet(); } } catch (SQLException ex) { ex.printStackTrace(); } return rs; } /** * return the result of a statement as text * the statement may be an update, a query, or mix */ public String getMultipleRS(String sqlText) { boolean ResultSetIsAvailable; boolean moreResultsAvailable; int i = 0; int res=0; String result = ""; try { Statement curStmt = conn.createStatement(); ResultSetIsAvailable = curStmt.execute(sqlText); ResultSet rs = null; for (moreResultsAvailable = true; moreResultsAvailable; ) { if (ResultSetIsAvailable) { if ((rs = curStmt.getResultSet()) != null) { // we have a resultset result = getRS(rs); } } else { if ((res = curStmt.getUpdateCount()) != -1) { // we have an updatecount result = res + " row(s) affected."; } // else no more results else { moreResultsAvailable = false; } } if (moreResultsAvailable) { ResultSetIsAvailable = curStmt.getMoreResults(); } } if (rs != null) rs.close(); curStmt.close(); } catch (SQLException ex) { // Unexpected SQL exception. // Occurs often with weird jdbc driver implementations. // ex.printStackTrace (); } catch (java.lang.Exception ex) { // Got some other type of exception. Dump it. // ex.printStackTrace (); } return result; } /** * return the result of a query as text */ public String getRS(ResultSet rs) { String s = "\n"; int i,j; Vector headers = getRSColumnHeadersAsVector(rs); Vector rows = getRSRowsAsVector(rs); for (i = 0; i < headers.size(); i++) { if (i > 0) s += ", "; s += headers.elementAt(i); } s += "\n\n"; for (i = 0; i < rows.size(); i++) { for (j = 0; j < ((Vector) rows.elementAt(i)).size(); j++) { if (j > 0) s += ", "; s += ((Vector) rows.elementAt(i)).elementAt(j); } s += "\n"; } return s; } /** * return the column headers of a resultset as vector */ public Vector getRSColumnHeadersAsVector(ResultSet rs) { int i; Vector v = new Vector(); try { ResultSetMetaData rsmd = rs.getMetaData(); int numCols = rsmd.getColumnCount(); // fetch column headers for (i = 1; i <= numCols; i++) { v.addElement(rsmd.getColumnLabel(i)); } } catch (SQLException ex) { } return v; } /** * return a resultset as vector */ public Vector getRSRowsAsVector(ResultSet rs) { Vector v = new Vector(); Vector r = null; int i; try { ResultSetMetaData rsmd = rs.getMetaData(); int numCols = rsmd.getColumnCount(); // step through the rows while (rs.next()) { // process the columns r = new Vector(); for (i = 1; i <= numCols; i++) { r.addElement(rs.getString(i)); } v.addElement(r); } rs.close(); } catch (SQLException ex) { } return v; } /** * add a new entry in the url and driver listboxes */ public void addDriverInfo(String url, String driver) { // add entry for this driver provider ((ChoiceWidget) util.getWidget("dataURL")).addChoice(url); ((ChoiceWidget) util.getWidget("dataDriver")).addChoice(driver); } /** * the main program */ public static void main(String argv[]) { // Create the frame Jexplorer frm = new Jexplorer(); frm.util.setPresentation("Jexplorer.gui"); frm.util.setText("dataWindowControl", welcome + extracomment); // allow miscellaneous drivers and urls to be preset // jdbc-odbc bridge frm.addDriverInfo("jdbc:odbc:data-source-name", "sun.jdbc.odbc.JdbcOdbcDriver"); // sybase's driver frm.addDriverInfo("jdbc:sybase:Tds:host.domain.com:8192", "com.sybase.jdbc.SybDriver"); // connect software's sybase driver frm.addDriverInfo("jdbc:sybase://host.domain.com:8192", "connect.sybase.SybaseDriver"); // funny driver frm.addDriverInfo("foo:bar:database", "foo.bar.Driver"); frm.show(); } }
|
![]() |
|