Java Tip 143: Utilize
the Database Schema
Browser
Tutorial Details:
Java Tip 143: Utilize the Database Schema Browser
Java Tip 143: Utilize the Database Schema Browser
By: By Saurabh Singh
Let DB Schema Browser fetch information from a database
he 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.
This page formated for crawlers and browsers that don't support scripts and tables.
Home
EZone
Read
Tutorial at: Click here to view the tutorial
Rate Tutorial: Java Tip 143: Utilize
the Database Schema
Browser
View Tutorial: Java Tip 143: Utilize
the Database Schema
Browser
Related
Tutorials:
 Reloading Applets
Reloading Applets
|
Applet to Applet
Communication
Applet to Applet
Communication
|
Java Tip 42: Write
Java apps that work with
proxy-based firewalls - JavaWorld - December 1997
Java Tip 42: Write
Java apps that work with
proxy-based firewalls - JavaWorld - December 1997
|
SQLJ: The 'open
sesame' of Java database applications
SQLJ: The 'open
sesame' of Java database applications
|
Java Tip 66: Control
browsers from your Java application - JavaWorld
Java Tip 66: Control
browsers from your Java application - JavaWorld
|
Tip 80: Resize
applets within browser frames - JavaWorld
Tip 80: Resize
applets within browser frames - JavaWorld
|
Read all about EJB 2.0 - JavaWorld June
2000
Read all about EJB 2.0 - JavaWorld June
2000
|
Adelard, one year later - JavaWorld
Adelard, one year later - JavaWorld
|
Validation with Java and XML Schema, Part 2 - JavaWorld October 2000
Validation with Java and XML Schema, Part 2 - JavaWorld October 2000
|
Validation with Java and XML schema, Part 3 - JavaWorld November 2000
Validation with Java and XML schema, Part 3 - JavaWorld November 2000
|
Validation with Java and XML schema, Part 4 - JavaWorld December 2000
Validation with Java and XML schema, Part 4 - JavaWorld December 2000
|
Use XML data binding to do your
laundry
Use XML data binding to do your
laundry
|
Build database-powered mobile applications on the
Java platform
Build database-powered mobile applications on the
Java platform
|
XML glossary
XML glossary
|
Java Tip 143: Utilize
the Database Schema
Browser
Java Tip 143: Utilize
the Database Schema
Browser
|
IberAgents
Introduction
IberAgents is a web application framework that enables the creation of SOAP-interoperable components in Java, with life cycle management and remote configuration. Development started in 2001; we now have a mature, solid open-source platform
|
Application Integration: Sun Java System Access Manager 2004Q2 and JDBC Authentication Module
Today, most user authentication solutions for Web applications are ad hoc and are based on proprietary schema definitions in relational databases. So, if you have multiple Web applications, you may have a separate user database for each of your applicatio
|
Simple Object Persistence with the db4o Object Database
Simple Object Persistence with the db4o Object Database. db4o has been chosen for applications in embedded systems in which zero administration, reliability, and low footprint are critical features. In Germany, BMW Car IT, for example, uses it in an embed
|
Generating an XML Document with JAXB
In this tutorial, JAXB is used to generate Java classes from an XML Schema. An example XML document shall be created from the Java classes.
|
DB Visual Architect for Eclipse
DB Visual Architect for Eclipse (DBVA-EC) is a full featured Object Relational Mapping (ORM) plugin for Eclipse that provides the industry\'s best round-trip code engineering support with Java.
|
|
|
|
