The DatabaseMetaData Interface

The DatabaseMetaData interface provides a number of methods to access database metadata. The methods fit into different categories, such as information on the database itself, information on what kind of features it supports, on its limitations, and on all the database objects it contains. Most of these methods return string, Boolean, or integer values, but a number of them return ResultSets. How to deal with ResultSets is discussed later in this chapter, in the section "The ResultSetMetaData Interface."

Database information

The DatabaseMetaData interface is a rich interface. It provides many methods useful for discovering various properties of the database engine, as listed in the following. This information might prove useful in building dynamic programs, when the target database isn’t known at design time. In fact, it is a good practice to use the DatabaseMetaData interface even in static programs to verify the capabilities or availability of database features.

DatabaseMetaData Methods for Database Information

boolean allProceduresAreCallable(); boolean allTablesAreSelectable(); String getURL(); String getUserName(); boolean isReadOnly(); boolean nullsAreSortedHigh(); boolean nullsAreSortedLow(); boolean nullsAreSortedAtStart(); boolean nullsAreSortedAtEnd(); String getDatabaseProductName(); String getDatabaseProductVersion(); String getDriverName(); String getDriverVersion(); int getDriverMajorVersion(); int getDriverMinorVersion(); boolean usesLocalFiles(); boolean usesLocalFilePerTable(); boolean supportsMixedCaseIdentifiers(); boolean storesUpperCaseIdentifiers(); boolean storesLowerCaseIdentifiers(); boolean storesMixedCaseIdentifiers(); boolean supportsMixedCaseQuotedIdentifiers(); boolean storesUpperCaseQuotedIdentifiers(); boolean storesLowerCaseQuotedIdentifiers(); boolean storesMixedCaseQuotedIdentifiers(); String getIdentifierQuoteString(); String getSQLKeywords(); String getNumericFunctions(); String getStringFunctions(); String getSystemFunctions(); String getTimeDateFunctions(); String getSearchStringEscape(); String getExtraNameCharacters();

Listing 9-1 illustrates how to get various kinds of information using some of the DatabaseMetaData object methods. Most of the properties relate to the current connection; others reflect the database engine version and the SQL keywords supported by the database. Note the getSQLKeywords() method call; it provides interesting information about the SQL keywords that the database recognizes and supports. After opening a connection to the database, this code will display this type of information.

Listing 9-1: Obtaining Information with DatabaseMetaData Methods

start example
// databasemetadata import java.sql.*; class SimpleExample {          public static void main(String args[])          {                  String url = "jdbc:odbc:mysource";                  try                  {                           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");                           Connection myConnection =                                   DriverManager.getConnection(url,                                       "javauser", "hotjava");                                           DatabaseMetaData mtdt =                                   myConnection.getMetaData();                           System.out.println("URL in use: " +                                   mtdt.getURL());                           System.out.println("User name: " +                                   mtdt.getUserName());                           System.out.println("DBMS name: " +                                   mtdt.getDatabaseProductName());                           System.out.println("DBMS version: " +                                   mtdt.getDatabaseProductVersion());                           System.out.println("Driver name: " +                                   mtdt.getDriverName());                           System.out.println("Driver version: " +                                   mtdt.getDriverVersion());                           System.out.println("supp. SQL Keywords: " +                                   mtdt.getSQLKeywords());                           myConnection.close();                  }                   catch(java.lang.Exception ex)                  {                           ex.printStackTrace();                  }          } }
end example

Listing 9-1’s output may be similar to the following, which is particular to Sybase System 11. This simple program is a good start when trying a new JDBC driver or a very first connection to a database server, as it will provide you with all the available SQL keywords.

URL in use: jdbc:odbc:mysource User name: javauser DBMS name: SQL Server DBMS version: SQL Server/11.0/P/Sun_svr4/OS 5.4/1/OPT/Thu Dec 7 23:58:01 PST 1995 Driver name: JDBC-ODBC Bridge (SYSYB95.DLL) Driver version: 1.0101 (02.12.0000) supp. SQL Keywords: arith_overflow,break,browse,bulk,char_convert,checkpoint, clustered,commit,compute,confirm,controlrow,data_pgs,database,dbcc,disk,dummy, dump,endtran,errlvl,errorexit,exit,fillfactor,holdlock,identity_insert,if,kill, lineno,load,mirror,mirrorexit,noholdlock,nonclustered,numeric_truncation,offsets, once,over,perm,permanent,plan,print,proc,processexit,raiserror,read,readtext, reconfigure,replace,reserved_pgs,return,role,rowcnt,rowcount,rule,save,setuser, shared,shutdown,some,statistics,stripe,syb_identity,syb_restree,syb_terminate, temp,textsize,tran,trigger,truncate,tsequal,used_pgs,user_option,waitfor,while, writetext

Supported features

The DatabaseMetaData interface also provides a large number of methods to query about the supported features of a database, as listed in the following. Not all database server implementations support all features, so again, it is good advice to check the availability of these functions prior to attempting to use them in a program.

DatabaseMetaData Methods for Information on the Database’s Supported Features

boolean supportsAlterTableWithAddColumn(); boolean supportsAlterTableWithDropColumn(); boolean supportsColumnAliasing(); boolean nullPlusNonNullIsNull(); boolean supportsConvert(); boolean supportsConvert(int fromType, int toType); boolean supportsTableCorrelationNames(); boolean supportsDifferentTableCorrelationNames(); boolean supportsExpressionsInOrderBy(); boolean supportsOrderByUnrelated(); boolean supportsGroupBy(); boolean supportsGroupByUnrelated(); boolean supportsGroupByBeyondSelect(); boolean supportsLikeEscapeClause(); boolean supportsMultipleResultSets(); boolean supportsMultipleTransactions(); boolean supportsNonNullableColumns(); boolean supportsMinimumSQLGrammar(); boolean supportsCoreSQLGrammar(); boolean supportsExtendedSQLGrammar(); boolean supportsANSI92EntryLevelSQL(); boolean supportsANSI92IntermediateSQL(); boolean supportsANSI92FullSQL(); boolean supportsIntegrityEnhancementFacility(); boolean supportsOuterJoins(); boolean supportsFullOuterJoins(); boolean supportsLimitedOuterJoins(); String getSchemaTerm(); String getProcedureTerm(); String getCatalogTerm(); boolean isCatalogAtStart(); String getCatalogSeparator(); boolean supportsSchemasInDataManipulation(); boolean supportsSchemasInProcedureCalls(); boolean supportsSchemasInTableDefinitions(); boolean supportsSchemasInIndexDefinitions(); boolean supportsSchemaInPrivilegeDefinitions(); boolean supportsCatalogsInDataManipulation(); boolean supportsCatalogsInProcedureCalls(); boolean supportsCatalogsInTableDefinitions(); boolean supportsCatalogsInIndexDefinitions(); boolean supportsCatalogsInPrivilegeDefinitions(); boolean supportsPositionedDelete(); boolean supportsPositionedUpdate(); boolean supportsSelectForUpdate(); boolean supportsStoredProcedures(); boolean supportsSubqueriesInComparisons(); boolean supportsSubqueriesInExists(); boolean supportsSubqueriesInIns(); boolean supportsSubqueriesInQuantifieds(); boolean supportsCorrelatedSubqueries(); boolean supportsUnion(); boolean supportsUnionAll(); boolean supportsOpenCursorAcrossCommit(); boolean supportsOpenCursorAcrossRollback(); boolean supportsOpenStatementAcrossCommit(); boolean supportsOpenStatementAcrossRollback();

These methods can be used in the same way the getSQLKeywords() method is used in Listing 9-1.

Database limitations

The DatabaseMetaData interface also provides methods for discovering the database’s limitations such as the maximum size for a row (expressed in bytes), the maximum length of a column name, the maximum allowed number of simultaneous connections, and so on, as listed in the following.

DatabaseMetaData Methods for Information on the Database’s Limitations

int getMaxBinaryLiteralLength(); int getMaxCharLiteralLength(); int getMaxColumnNameLength(); int getMaxColumnsInGroupBy(); int getMaxColumnsInIndex(); int getMaxColumnsInOrderBy(); int getMaxColumnsInSelect(); int getMaxColumnsInTable(); int getMaxConnections(); int getMaxCursorNameLength(); int getMaxIndexLength(); int getMaxSchemaNameLength(); int getMaxProcedureNameLength(); int getMaxCatalogNameLength(); int getMaxRowSize(); boolean doesMaxRowSizeIncludeBlobs(); int getMaxStatementLength(); int getMaxStatements(); int getMaxTableNameLength(); int getMaxTablesInSelect(); int getMaxUserNameLength(); int getDefaultTransactionIsolation(); boolean supportsTransactions(); boolean supportsTransactionIsolationLevel(int level); boolean supportsDataDefinitionAndDataManipulationTransactions(); boolean supportsDataManipulationTransactionsOnly(); boolean dataDefinitionCausesTransactionCommit(); boolean dataDefinitionIgnoredInTransactions();

Listing 9-2 illustrates a metadata method for discovering the database’s ANSI compliance level, such as Entry, Intermediate, Full, and so on. The code displays a selected subset of what the database supports.

Listing 9-2: A Metadata Method for Discovering the ANSI Compliance Level

start example
// discovering ANSI compliance import java.sql.*; class SimpleExample {          public static void main(String args[])          {                  String url = "jdbc:odbc:mysource";                  try                 {                           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");                           Connection myConnection =                                   DriverManager.getConnection(url,                                   "javauser", "hotjava");                           DatabaseMetaData mtdt =                                   myConnection.getMetaData();                           System.out.println("ANSI92 Entry Level: " +                                   mtdt.supportsANSI92EntryLevelSQL());                           System.out.println("ANSI92 Intermediate: " +                                   mtdt.supportsANSI92IntermediateSQL());                           System.out.println("ANSI92 Full SQL: " +                                   mtdt.supportsANSI92FullSQL());                           System.out.println("Minimum SQL Grammar: " +                                   mtdt.supportsMinimumSQLGrammar());                           System.out.println("Core SQL Grammar: " +                                   mtdt.supportsCoreSQLGrammar());                           System.out.println("Extended SQL Grammar: "                                   + mtdt.supportsExtendedSQLGrammar());                           myConnection.close();                  }                  catch(java.lang.Exception ex)                  {                          ex.printStackTrace ();                  }         } }
end example

Database objects

The DatabaseMetaData interface also provides several methods for discovering the database’s objects, as listed in the following. This is quite useful for programs that must discover the content of a database at runtime, such as a database browser, a CASE tool, and so on.

DatabaseMetaData Methods for Information on the Database’s Contents

ResultSet getProcedures(String catalog, String schemaPattern, String procedureNamePattern); ResultSet getProcedureColumns(String catalog, String schemaPattern,  String procedureNamePattern, String columnNamePattern); ResultSet getTables(String catalog, String schemaPattern,  String tableNamePattern, String types[]); ResultSet getschemas(); ResultSet getCatalogs(); ResultSet getTableTypes(); ResultSet getColumns(String catalog, String schemaPattern,  String tableNamePattern, String columnNamePattern); ResultSet getColumnPrivileges(String catalog, String schema, String table,  String columnNamePattern); ResultSet getTablePrivileges(String catalog, String schemaPattern,  String tableNamePattern); ResultSet getBestRowIdentifier(String catalog, String schema, String table,  int scope, boolean nullable); ResultSet getVersionColumns(String catalog, String schema, String table); ResultSet getPrimaryKeys(String catalog, String schema, String table); ResultSet getImportedKeys(String catalog, String schema, String table); ResultSet getExportedKeys(String catalog, String schema, String table); ResultSet getCrossReference(String primaryCatalog, String primarySchema,  String primaryTable, String foreignCatalog, String foreignSchema, String foreignTable); ResultSet getTypeInfo(); ResultSet getIndexInfo(String catalog, String schema, String table,  boolean unique, boolean approximate);

Note 

Certain DBMSs may not support some features. An empty ResultSet may be returned by the driver in such circumstances. Note that the exact definition of catalog and schema varies from database to database. A catalog is usually a database subspace, as if a database server would contain several logical databases in it. The catalog name refers to the name of such subspaces. Database schemas usually refer to a group of database objects such as tables and stored procedures belonging to a particular database user. The schema name can be a user’s login identifier. I recommend that you refer to the administration guide of your particular database if you need to specify individual catalog and schema names when using these methods.

Most of these methods need arguments, such as catalog name, schema name, procedure, table, and column name. All the parameters named xxxPattern don’t have to match a particular database object name. If necessary, they may be composed of the % and _ matching characters. The % matches zero or more characters, while _ matches any one character.

All these methods return ResultSets. Indeed, they usually return multiple values, which means providing results through ResultSets is very convenient. As Figure 9-1 shows, the usual method is used to scan those result sets. The different values are retrieved exactly as when dealing with SELECT statements that return several rows of data. Individual rows are accessed using the ResultSet object.

click to expand
Figure 9-1: Processing methods that return a ResultSet.

Listing 9-3 includes some database metadata calls that show how to query metadata information using the preceding methods. This code opens a database connection and displays the catalog names as well as table and stored procedures names for all schemas belonging to the default catalog. The default catalog is the “home” catalog of the user who opened the connection, as defined by the DBA.

Listing 9-3: Metadata about Database Objects

start example
// retrieving results import java.sql.*; class SimpleExample {          public static void main(String args[])          {                  String url = "jdbc:odbc:mysource";                  try                  {                          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");                          Connection myConnection =                                  DriverManager.getConnection(url,                                   "javauser", "hotjava");                          DatabaseMetaData dmtd = myConnection.getMetaData();                                                   // list catalogs managed by this dbms                          scanRS("Info on " + dmtd.getCatalogTerm() + "(s):",                                  dmtd.getCatalogs());                          // list all tables in current catalog, belonging                          // to all schemas, of all types                          scanRS("Info on tables:",                                  dmtd.getTables(myConnection.getCatalog(), "%",                                   "%", null));                          // list all procedures in current catalog,                          // belonging to all schemas                          scanRS("Info on " + dmtd.getProcedureTerm() + "(s):",                                  dmtd.getProcedures(myConnection.getCatalog(),                                   "%", "%"));                          myConnection.close();                 }                 catch(java.lang.Exception ex)                 {                          ex.printStackTrace();                 }         }         private static void scanRS(String info, ResultSet rs) throws SQLException         {                 System.out.println(info);                 System.out.println();                 if (rs != null)                 {                          int i;                          // we have a ResultSet                          ResultSetMetaData rsmd = rs.getMetaData();                          int numCols = rsmd.getColumnCount();                           // display column headers                           for (i = 1; i <= numCols; i++)                           {                                    if (i > 1) System.out.print(", ");                                    System.out.print(rsmd.getColumnLabel(i));                           }                           System.out.println("");                                                     // step through the rows                           while (rs.next())                           {                                    // process the columns                                    for (i = 1; i <= numCols; i++)                                    {                                            if (i > 1) System.out.print(", ");                                            System.out.print(rs.getString(i));                                    }                                    System.out.println("");                           }                  }                  else                  {                           System.out.println("no data returned...");                  }                  System.out.println ();         } }
end example

Listing 9-3 prints information similar to the following output. The data returned here came from a Sybase System 11 server. The user’s current catalog is the default sample database provided by Sybase.

Note 

Note that a SQLException message appearing on the stack trace indicates the nature of the problem, usually showing that an unimplemented feature has been called.

Info on database(s) TABLE_QUALIFIER master model pubs2 sybsecurity sybsyntax sybsystemprocs tempdb test testdb2 userdb Info on tables TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, REMARKS pubs2, dbo, sysalternates, SYSTEM TABLE, null pubs2, dbo, syscolumns, SYSTEM TABLE, null pubs2, dbo, syscomments, SYSTEM TABLE, null pubs2, dbo, sysconstraints, SYSTEM TABLE, null pubs2, dbo, sysdepends, SYSTEM TABLE, null pubs2, dbo, sysindexes, SYSTEM TABLE, null pubs2, dbo, syskeys, SYSTEM TABLE, null pubs2, dbo, syslogs, SYSTEM TABLE, null pubs2, dbo, sysobjects, SYSTEM TABLE, null pubs2, dbo, sysprocedures, SYSTEM TABLE, null pubs2, dbo, sysprotects, SYSTEM TABLE, null pubs2, dbo, sysreferences, SYSTEM TABLE, null pubs2, dbo, syssegments, SYSTEM TABLE, null pubs2, dbo, systypes, SYSTEM TABLE, null pubs2, dbo, sysusermessages, SYSTEM TABLE, null pubs2, dbo, sysusers, SYSTEM TABLE, null pubs2, dbo, au_pix, TABLE, null pubs2, dbo, authors, TABLE, null pubs2, dbo, blurbs, TABLE, null pubs2, dbo, discounts, TABLE, null pubs2, dbo, publishers, TABLE, null pubs2, dbo, roysched, TABLE, null pubs2, dbo, sales, TABLE, null pubs2, dbo, salesdetail, TABLE, null pubs2, dbo, stores, TABLE, null pubs2, dbo, titleauthor, TABLE, null pubs2, dbo, titles, TABLE, null Info on Stored Procedure(s) PROCEDURE_QUALIFIER, PROCEDURE_OWNER, PROCEDURE_NAME, NUM_INPUT_PARAMS, NUM_OUTPUT_PARAMS, NUM_RESULT_SETS, REMARKS, PROCEDURE_TYPE pubs2, dbo, byroyalty, null, null, null, null, 1 pubs2, dbo, discount_proc, null, null, null, null, 1 pubs2, dbo, history_proc, null, null, null, null, 1 pubs2, dbo, insert_sales_proc, null, null, null, null, 1 pubs2, dbo, insert_salesdetail_proc, null, null, null, null, 1 pubs2, dbo, storeid_proc, null, null, null, null, 1 pubs2, dbo, storename_proc, null, null, null, null, 1 pubs2, dbo, title_proc, null, null, null, null, 1 pubs2, dbo, titleid_proc, null, null, null, null, 1 pubs2, guest, testproc, null, null, null, null, 1



JDBC 3. 0. JAVA Database Connectivity
JDBC 3: Java Database Connectivity
ISBN: 0764548751
EAN: 2147483647
Year: 2002
Pages: 148

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net