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:
What Is Metadata?
- The MySQL database does not understand “schema”; you have to use “catalog.”
- The Oracle database does not understand “catalog”; you have to use “schema.”
Metadata is data about data (or information about information), which provides structured, descriptive information about other data:
Metadata (Greek: meta-+ Latin: data “information”), literally “data about data”, is information that describes another set of data. A common example is a liary catalog card, which contains data about the contents and location of a book: It is data about the data in the book referred to by the card. Other common contents of metadata include the source or author of the described dataset, how it should be accessed, and its limitations.
The following quote from the NOAA Coastal Services Center, or CSC , illustrates the importance of the concept of metadata:
Imagine trying to find a book in a liary without the help of a card catalog or computerized search interface. Could you do it? Perhaps, but it would be difficult at best. The information contained in such a system is essentially metadata about the books that are housed at that liary or at other liaries. It provides you with vital information to help you find a particular book and aids you in making a decision as to whether that book might fit your needs.Metadata serves a similar purpose for geospatial data.
The NOAA CSC further adds that “metadata is a component of data which describes the data. It is ‘data about data.’” Metadata describes the content, quality, condition, and other characteristics of data. Metadata describes the who, what, when, where, why, and how of a data set. Without proper documentation, a data set is incomplete.
KTWEB ) defines metadata as “data about data, or nformation about information; in practice, metadata comprises a structured set of descriptive elements to describe an information resource or, more generally, any definable entity.” Relational databases (such as MySQL and Oracle) use tables and other means (such as operating system file systems) to store their own data and metadata. Each relational database has its own proprietary methods for storing metadata. Examples of relational database metadata include
- A list of all the tables in the database, including their names, sizes, and the number of rows
- A list of the columns in each database, and what tables they are used in, as well as the type of data stored in each column
For example, the Oracle database keeps metadata in several tables (I have listed two here):
- ALL_TABLES: A list of all tables in the current database
- ALL_TAB_COLS: A list of all columns in the database
Imagine, at runtime, trying to execute a SQL query in a relational database without knowing the name of tables, columns, or views. Could you do it? Of course not. Metadata helps you to find out what is available in the database and then, with the help of that information (called metadata), you can build proper SQL queries at runtime. Also, having access to structured database metadata relieves a JDBC programmer of having to know the characteristics of relational databases in advance.
Metadata describes the data but is not the actual data itself. For example, the records in a card catalog in a local liary give ief details about the actual book. The card catalog—as metadata—provides enough information to tell you what the book is called, its unique identification number, and how and where you can find it. These details are metadata—in this case, bibliographic elements such as author, title, abstract, publisher, and published date.
In a nutshell, database metadata enables dynamic database access. Typically, most JDBC programmers know their target database’s schema definitions: the names of tables, views, columns, and their associated types. In this case, the JDBC programmer can use the strongly typed JDBC interfaces. However, there is another important class of database access where an application (or an application builder) dynamically (in other words, at runtime) discovers the database schema information and uses that information to perform appropriate dynamic data access. This chapter describes the JDBC support for dynamic access. A dynamic database access application may include building dynamic queries, dynamic owsers, and GUI database adapters, just to mention a few.