Database Metadata, Part 1

The goal of this chapter(and the next) is to show you how to use JDBC’s database metadata API, which you can use to get information about tables, views, column names, column types, stored procedures, result sets, and databases. It will be of most interest to those who need to write applications that adapt themselves to the specific capabilities of several database systems or to the content of any database. If you write programs—such as graphical user interface(GUI) database applications using database adapters—that use advanced database features or programs that discover database stored procedures and tables or views at runtime (i.e., dynamically), you will have to use metadata. You can use database metadata to

• Discover database schema and catalog information.
• Discover database users, tables, views, and stored procedures.
• Understand and analyze the result sets returned by SQL queries.
• Find out the table, view, or column privileges.
• Determine the signature of a specific stored procedure in the database.
• Identify the primary/foreign keys for a given table.

As you will discover, metadata not only helps you to effectively manage resources, it also helps you find the data you need and determine how best to use it. In addition, metadata provides a structured description of database information resources and services. Some JDBC methods, such as getProcedureColumns() and getProcedures(), return the result as a ResultSet object. Unfortunately, this is not very useful to the client; because the ResultSet object cannot be passed to some client programs, these programs cannot analyze and understand the content of the ResultSet object. For this reason, you need to return the results in XML (and possibly an XML object serialized as a String object), which is suitable for all clients. To be efficient, you generate XML expressed as a String object, which can be easily converted to an XML document. The Source Code section of the Apress website provides utilities for converting Strings to org.w3.dom.Document and org.w3.dom.Document objects to Strings.

When you write JDBC applications, you should strive for good performance. But what is “good” performance? Should it be subjective or objective? This depends on the requirements of your application. In other words, if you write “slow” code, the JDBC driver does not throw an exception, but you get a performance hit (which might translate to losing clients). “Good” performance means that you are satisfying your project’s performance requirements, which should be defined precisely in requirements and design documents. To get acceptable performance from JDBC drivers, avoid passing null parameters to most of the methods; for example, passing a null value to the schema parameter might result in a search of all database schemas— so if you know the name of your desired schema, then pass the actual schema value.

In general, developing performance-oriented JDBC applications is not easy. In every step of the solution, you must make sure that your application will not choke under heavy requirements. For performance reasons, you should avoid excessive metadata calls, because database metadata methods that generate ResultSet objects are relatively slow. JDBC applications may cache information returned from result sets that generate database metadata methods so that multiple executions are not needed. For this purpose you may use Java Caching System(JCS) from the Apache Software Foundation. JCS is a distributed caching system written in Java for server-side Java applications.

A Java class called DatabaseMetaDataTool(which is defined in the jcb.meta package)will be available for download from the Source Code section of the Apress website. It provides ready-touse methods for answering the database metadata questions. For questions about database metadata, I list portions of these classes in some sections of this book, but you’ll find the complete class definitions(including JavaDoc-style comments) at the Apress website.

All of the methods in this chapter are static, and each method is written to be as independent as possible. This is so you can cut and paste solutions from this book whenever possible. The methods will return the result as XML(serialized as a String object, which can be easily converted to an XML document ). Also, I provide a utility class, DocumentManager,which can

• Convert org.w3c.dom.Document to XML as a serialized String object
• Convert org.jdom.Document to XML as a serialized String object
• Convert XML as a serialized String object into org.w3c.dom.Document
• Convert XML as a serialized String object into org.jdom.Document

In general, it is efficient to create XML as a serialized String object. The Java and JDBC solutions are grouped in a Java package called jcb (JDBC CookBook). Table shows the structure of the package. All of the code will be available from the Source Code section of the Apress website.

Table : JCB Package Structure

When using the DatabaseMetaData object, you should observe two key facts:

• The MySQL database does not understand “schema”; you have to use “catalog.”
• The Oracle database does not understand “catalog”; you have to use “schema.”