How Do You Get a List of Table Names for a Database? in JDBC

Each database has a finite set of tables, views, and other objects (such as indexes and stored procedures). JDBC enables you to get a list of tables for a specific database. You can use DatabaseMetaData.getTables() to retrieve a description of the tables available in the given catalog (database). Only table descriptions matching the catalog, schema, table name, and type criteria are returned. They are ordered by TABLE_TYPE, TABLE_SCHEM, and TABLE_NAME. The signature of DatabaseMetaData.getTables() is

This method returns the result as a ResultSet object, which is not very useful for web-based applications. Therefore, we will convert the ResultSet object to HTML or XML, which can be quite useful to web-based applications.

I present a Java servlet (GetTables), which will be able to get table names (plus other metadata information) as HTML or XML. Here is the signature of the servlet:

GetTables?vendor=<vendor-name>&format=<HTML-or-XML>

You may add additional parameters to suit your database application. Here, I use vendor to act as a database selector. So, to get the table information as XML for a MySQL database, you may invoke it as

Figure 8-9. Invoking GetTableTypes for Oracle (XML output)

MySQL Database Setup

Oracle Database Setup

The Solution

We treat MySQL and Oracle differently, because Oracle’s driver returns extra tables (the tables not created by the database user) for DatabaseMetaData.getTables(). To eliminate this problem, for Oracle’s tables I use the following SQL query:

select object_name from user_objects where object_type = 'TABLE'

Because of all of these differences, I provide an additional method (getOracleTableNames()) to handle Oracle’s special case. This again proves that the “vendor” factor is crucial for handling data and metadata for JDBC applications. Here is a complete solution (the GetTables servlet) for getting table names:

Invoking GetTables for MySQL

Figure :shows how to run the solution for the MySQL database.

Invoking GetTables for Oracle

Figure: shows how to run the solution for the Oracle database.

Invoking GetTables to Handle Errors

Figure: shows how to run the solution for handling errors.



All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

JDBC Topics