|
|
Optimize with a SATA RAID Storage Solution
Range of capacities as low as $1250 per TB. Ideal if you currently rely on servers/disks/JBODs
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.
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 patterntypes: 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 SYNONYMTABLE_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.TypesTYPE_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 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 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.
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.
Archived Discussions (Read only)
(