Java Tip 143: Utilize the Database Schema Browser

The Database Schema Browser utility uses Java Database Connectivity (JDBC) APIs DatabaseMetaData and ResultSet to fetch information from a database and display it to users.

This simple servlet provides users with customizable options to retrieve a listing of all tables in a schema, tables matching a certain criteria (e.g., only tables that start with TBL), and all tables of a specific schema. You can also get a listing of all views.

One more hidden treasure exists. The utility’s JDBCConnect class is generic so you can use it to connect to the database repeatedly. You can pass the database configuration file as an input, and the database connection is established accordingly. I provide a separate method in case you want to use a default configuration instead.

The Database Schema Browser utility includes three classes: Database, DBServlet, and JDBCConnect. I explain each class in more detail below.

Note: You can download this tip’s source code from Resources.

Database class

The Database class has the logic to get metadata information from the database. It uses Java APIs DatabaseMetaData and ResultSet to fetch the database information. DatabaseMetaData has two methods: getTables() and getColumns(). The getTables() and getColumns() methods need special attention because they are the core methods that retrieve the database information. getTables() calls DatabaseMetaData.getTables(), and getColumns() calls DatabaseMetaData.getColumns() to get the data.

The getTables() method gets a description of tables available in a catalog:

public ResultSet getTables(String catalog,
                           String schemaPattern,
                           String tableNamePattern,
                           String[] types)
                    throws SQLException

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 getTables() method takes the following parameters:

• catalog: a catalog name; “” retrieves those without a catalog; null means drop catalog name from the selection criteria
• schemaPattern: a schema name pattern; “” retrieves those without a schema

• tableNamePattern: a table name pattern

• types: a list of table types to include; null returns all types

The getTables() method returns:

ResultSet: each row is a table description.

Each table description has the following columns:

• TABLE_CAT String: table catalog (may be null)
• TABLE_SCHEM String: table schema (may be null)
• TABLE_NAME String: table name
• TABLE_TYPE String: table type; typical types are TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, LOCAL TEMPORARY, ALIAS, and SYNONYM

TABLE_TYPE and TABLE_NAME (pattern) are read from the configuration file 'dbConfig.properties'/'User specified property file', which gives you the flexibility to provide a pattern for the output. For example, if TABLE_TYPE is TABLE and PATTERN is % then all tables in the specified schema are retrieved.

The getColumns() method gets a description of table columns available in the specified catalog:

public ResultSet getColumns(String catalog,
                            String schemaPattern,
                            String tableNamePattern,
                            String columnNamePattern)
                     throws SQLException

Only column descriptions matching the catalog, schema, table, and column name criteria are returned. They are ordered by TABLE_SCHEM, TABLE_NAME, and ORDINAL_POSITION.

The getColumns() method takes the following parameters:

• catalog: a catalog name; “” retrieves those without a catalog; null means drop catalog name from the selection criteria
• schemaPattern: a schema name pattern; “” retrieves those without a schema
• tableNamePattern: a table name pattern
• columnNamePattern: a column name pattern

The getColumns() method returns:

ResultSet: each row is a column description.

Each column description has the following columns:

• TABLE_CAT String: table catalog (may be null)
• TABLE_SCHEM String: table schema (may be null)
• TABLE_NAME String: table name
• COLUMN_NAME String: column name
• DATA_TYPE short: SQL type from java.sql.Types
• TYPE_NAME String: data source dependent type name; for a UDT the type name is fully qualified
• COLUMN_SIZE int: column size; for char or date types this is the maximum number of characters; for numeric or decimal types this is precision
• BUFFER_LENGTH: unused
• DECIMAL_DIGITS int: the number of fractional digits
• NUM_PREC_RADIX int: Radix (typically either 10 or 2)
• NULLABLE int: is NULL allowed?
• • columnNoNulls: might not allow NULL values
  • columnNullable: definitely allows NULL values
  • columnNullableUnknown: nullability unknown
• REMARKS String: comment describing column (may be null)
• COLUMN_DEF String: default value (may be null)
• SQL_DATA_TYPE int: unused
• SQL_DATETIME_SUB int: unused
• CHAR_OCTET_LENGTH int: for char types the maximum number of bytes in the column
• ORDINAL_POSITION int: index of columns in table (starting at 1)
• IS_NULLABLE String: NO means column definitely does not allow NULL values; YES means the column might allow NULL values

I only used the most commonly looked up attributes in my code (e.g., COLUMN_NAME, TYPE_NAME, COLUMN_SIZE, and IS_NULLABLE). You can use many other similar Java APIs to fetch further database details.

DBServlet class

DBServlet is a simple servlet: when it receives a request, it delegates the call to the Database class, which gets the data and throws the response. This class simply prints the response. You can change this class by writing dynamic HTML code to improve the output format and develop it into a full database schema browser.

JDBCConnect class

JDBCConnect is a generic class that makes a connection with the database. The class has two methods (overridden method getConnection()): one takes the property file name as an input, and the other does not take any input parameters. If you want to specify your own property file you must call the getConnection(String propFile) method. Or you can call getConnection(), which will read the dbConfig.properties property file. This property file contains the database information the user wants. Users must edit dbConfig.properties to provide the database username, password, and other important database details. JDBCConnect simply returns the connection to the calling class. You can add more methods to JDBCConnect and implement connection pooling if you use JDBCConnect outside the scope of the Database Schema Browser utility.

Simple servlet

The Database Schema Browser is not complex and uses Java APIs for processing. You can easily extend it, for example, and make Java classes for each table and write simple getters and setters. Instead of printing the output on the response writer, you can redirect the output to Java I/O stream classes. Then by writing simple code to parse data you can embed the table and column information into a Java class template.