How Do You Get a List of Tables and Views 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 combined tables and views for a specific database. You can use DatabaseMetaData.getTables() to retrieve a description of the tables and views available in the given catalog (database). Only table and view descriptions matching the catalog, schema, table/view 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/XML, which can be quite useful to web-based applications. In invoking this method, you need to pass the correct information for the types parameter. To get both tables and views, you must pass {"TABLE", "VIEW"} for the types parameter.

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

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

You may add other 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

MySQL Database Setup

Oracle Database Setup

The Solution

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

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

Invoking GetTablesAndViews for MySQL

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

Invoking GetTablesAndViews for Oracle

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



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

JDBC Topics