What Are the Table Names for a Database? JDBC

If you are providing dynamic SQL queries for a GUI database application, then you might need the names of the user tables. In building database adapters and GUI database applications, the GUI developers often need the name of the tables. The following program provides such a solution. The solution can vary depending on the database vendor. For a complete solution, refer to the DatabaseMetaDataTool class, described under the jcb.meta package (you can download the entire package from the Source Code section of the Apress website). Here, I’ll just list the portions of the programs that are most relevant to this topic. The DatabaseMetaData.getTables() method returns the table names for a given database connection object. The getTables() method works well for MySQL, but it does not work well for Oracle databases (in addition to user’s tables, it returns system tables, which are not needed by most of the client programs). To get a list of user-defined tables and views, I use the Oracle’s metadata table called user_objects, which keeps track of objects (tables, views, ...) owned by the user. To get a list of user's tables for an Oracle database, you may use the following SQL query:

This method retrieves a description of the tables available in the given catalog. Only table descriptions matching the catalog, schema, table name and type criteria are returned. The returned ResultSet object has 10 columns (for details, see JDK 1.5 documentation), which are ordered by TABLE_TYPE, TABLE_SCHEM and TABLE_NAME (column names for the returned Result- Set object). Here, for MySQL solution, I use the getTables() method. For better performance of this method and other metadata methods, it is highly recommended not to pass null/empty values as an actual parameters to these methods. Try to pass non-null and non-empty values to these metadata methods.

MySQL Solution

Testing the MySQL Solution: Client Program Oracle Solution

For Oracle databases, DatabaseMetaData.getTables() method does not work well (in addition to the user’s tables, it returns system-level tables). To get user’s tables, I use the following query:

Oracle’s user_object’s table keeps track of user objects (tables, views, and other useful objects). As you can observe, again, the database vendor name plays an important role in fetching metadata (based on vendor name, you may apply different methods for solving a specific metadata problem).

Testing the Oracle Solution: Client Program

Output of the Client Program

To simplify this for clients (so that you don’t have to call different methods to get the table names), we can introduce a wrapper object, which includes a Connection object and a vendor name:

Now, we can get the table names from the following method:

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

JDBC Topics