Much of what you have done with JDBC so far requires you to know a lot about the database you are using, including the capabilities of the database engine and the data model against which you are operating. Requiring this level of knowledge may not bother you much, but JDBC does provide the tools to free you from these limitations. These tools come in the form of meta-data.
The term “meta” here means information about your data
that does not interest the end users at all, but which you need to
know in order to handle the data. JDBC provides two meta-data
classes: java.sql.ResultSetMetaData and
java.sql.DatabaseMetaData. The meta-data described
by these classes was included in the original JDBC
ResultSet and Connection
classes. The team that developed the JDBC specification decided
instead that it was better to keep the ResultSet
and Connection classes small and simple to serve
the most common database requirements. The extra functionality could
be served by creating meta-data classes to provide the often esoteric
information required by a minority of developers.
As its name implies, the
ResultSetMetaData
class provides extra information about
ResultSet objects returned from a database query.
In the embedded queries you made earlier in the book, you hardcoded
into your queries much of the information a
ResultSetMetaData object gives you. This class
provides you with answers to the following questions:
How many columns are in the result set?
Are column names case-sensitive?
Can you search on a given column?
Is
NULLa valid value for a given column?How many characters is the maximum display size for a given column?
What label should be used in a display header for the column?
What is the name of a given column?
What table did a given column come from?
What is the datatype of a given column?
If you have a generic database class that blindly receives SQL to execute from other classes, this is the sort of information you need in order to process any result sets that are produced. Take a look at the following code, for example:
public ArrayList executeSQL(String sql) {
ArrayList results = new ArrayList( );
try {
Statement stmt = conn.createStatement( );
if( stmt.execute(sql) ) {
ResultSet rs = stmt.getResultSet( );
ResultSetMetaData meta = rs.getMetaData( );
int count;
count = meta.getColumnCount( );
while( rs.next( ) ) {
HashMap cols = new Hashtable(count);
int i;
for(i=0; i<count; i++) {
Object ob = rs.getObject(i+1);
if( rs.wasNull( ) ) {
ob = null;
}
cols.put(meta.getColumnLabel(i+1), ob);
}
results.add(cols);
}
return results;
}
return null;
}
catch( SQLException e ) {
e.printStackTrace( );
return null;
}
}This example introduces the execute( )
method in the
Statement class (as well as its subclasses). This
method is more generic than executeUpdate() or
executeQuery() in that it will send any SQL you
pass it without any preconception regarding what kind of SQL it is.
If the SQL produced a result set—if it was a query—it
will return true. For modifications that do not
produce result sets, execute() returns
false. If it did produce a result set, you can get
that result set by calling the getResultSet( )
method.
For a given ResultSet object, an application can
call the ResultSet’s getMetaData( )
method in order to get its associated
ResultSetMetaData object. You can then use this
meta-data object to find out extra information about the result set
and its columns. In the previous example, whenever the
execute() method in the
Statement class returns a true
value, it gets the ResultSet object using the
getResultSet() method and the
ResultSetMetaData object for that result set using
the getMetaData() method. For each row in the
result set, the example figures out the column count using the
meta-data method getColumnCount(). Knowing the
column count, the application can then retrieve each column. Once it
has a column, it again uses the meta-data to get a column label via
getColumnLabel() and stick the column’s
value in a HashMap with the label as a key and the
column’s meta-data value as an element. The entire set of rows
is then returned as
an ArrayList.
As the
ResultSetMetaData class relates to the
ResultSet class, the
DatabaseMetaData class relates to the
Connection class (in spite of the naming
inconsistency). The DatabaseMetaData class
provides methods that tell you about the database for a given
Connection object, including:
What tables exist in the database visible to the user?
What username is being used by this connection?
Is this database connection read-only?
What keywords are used by the database that are not SQL2?
Does the database support column aliasing?
Are multiple result sets from a single
execute()call supported?Are outer joins supported?
What are the primary keys for a table?
The list of information provided by this class is way too long to list here, but you can check the reference section for the methods and what they do. The class has two primary uses:
It provides methods that tell GUI applications and other general-purpose applications about the database being used.
It provides methods that let application developers make their applications database-independent.
Though driver property information is not
represented in JDBC by an official meta-data class, the class does
represent extra information about your driver. Specifically, every
database requires different information in order to make a
connection. Some of this information is necessary for the connection;
some of it is optional. The mSQL-JDBC driver I have been using for
many of the examples in this book requires a username to make a
connection, and it optionally will accept a character set encoding.
Other drivers usually require a password. A tool designed to connect
to any database therefore needs a way of finding out what properties
a specific JDBC driver requires. The
DriverPropertyInfo
class provides this information.
The Driver
class provides the method
getPropertyInfo( )
that returns an array of DriverPropertyInfo
objects. Each DriverPropertyInfo object represents
a specific property. This class tells you:
The name of the property
A description of the property
The current value of the property
An array of possible choices the value can be taken from
A flag that notes whether the property is required or optional
At the end of this chapter is an example that uses driver property information to prompt a user for property values required for a database connection.
I will
demonstrate the power of the
meta-data classes with a simple, but widely useful, SQL terminal
monitor application that provides a generic command-line interface to
any potential database. The application should allow a user to enter
SQL statements at the command line and view formatted results. This
program shown in Example 4.4 requires only a single
class with static methods. The main() method
creates a user input loop when the user enters commands or SQL
statements. Each input is interpreted as either a command or a SQL
statement. If it is interpreted as a command, the command is executed
immediately. If it is not interpreted as a command, it is assumed to
be part of a SQL statement and thus appended to a buffer. The
application supports the following commands:
- commit
Sends a commit to the database, committing any pending transactions.
- go
Sends anything currently in the buffer to the database for processing as a SQL statement. The SQL is parsed through the
executeStatement()method.- quit
Closes any database resources and exits the application.
- reset
Clears the buffer without sending it to the database.
- rollback
Aborts any uncommitted transactions.
- show version
Displays version information on this program, the database, and the JDBC driver using the
DatabaseMetaDatainterface implementation.
Example 4-4. The main( ) Method for a SQL Terminal Monitor Application
static public void main(String args[]) {
DriverPropertyInfo[] required;
StringBuffer buffer = new StringBuffer( );
Properties props = new Properties( );
boolean connected = false;
Driver driver;
String url;
int line = 1; // Mark current input line
if( args.length < 1 ) {
System.out.println("Syntax: <java -Djdbc.drivers=DRIVER_NAME " +
"TerminalMonitor JDBC_URL>");
return;
}
url = args[0];
// We have to get a reference to the driver so we can
// find out what values to prompt the user for in order
// to make a connection.
try {
driver = DriverManager.getDriver(url);
}
catch( SQLException e ) {
e.printStackTrace( );
System.err.println("Unable to find a driver for the specified " +
"URL.");
System.err.println("Make sure you passed the jdbc.drivers " +
"property on the command line to specify " +
"the driver to be used.");
return;
}
try {
required = driver.getPropertyInfo(url, props);
}
catch( SQLException e ) {
e.printStackTrace( );
System.err.println("Unable to get driver " +
"property information.");
return;
}
input = new BufferedReader(new InputStreamReader(System.in));
// Some drivers do not implement getProperty properly
// If that is the case, prompt for user name and password
try {
if( required.length < 1 ) {
props.put("user", prompt("user: "));
props.put("password", prompt("password: "));
}
else {
// for each required attribute in the driver property info
// prompt the user for the value
for(int i=0; i<required.length; i++) {
if( !required[i].required ) {
continue;
}
props.put(required[i].name,
prompt(required[i].name + ": "));
}
}
}
catch( IOException e ) {
e.printStackTrace( );
System.err.println("Unable to read property info.");
return;
}
// Make the connection.
try {
connection = DriverManager.getConnection(url, props);
}
catch( SQLException e ) {
e.printStackTrace( );
System.err.println("Unable to connect to the database.");
return;
}
connected = true;
System.out.println("Connected to " + url);
// Enter into a user input loop
while( connected ) {
String tmp, cmd;
// Print a prompt
if( line == 1 ) {
System.out.print("TM > ");
}
else {
System.out.print(line + " -> ");
}
System.out.flush( );
// Get the next line of input
try {
tmp = input.readLine( );
}
catch( java.io.IOException e ) {
e.printStackTrace( );
return;
}
// Get rid of extra space in the command
cmd = tmp.trim( );
// The user wants to commit pending transactions
if( cmd.equals("commit") ) {
try {
connection.commit( );
System.out.println("Commit successful.");
}
catch( SQLException e ) {
System.out.println("Error in commit: " +
e.getMessage( ));
}
buffer = new StringBuffer( );
line = 1;
}
// The user wants to execute the current buffer
else if( cmd.equals("go") ) {
if( !buffer.equals("") ) {
try { // processes results, if any
executeStatement(buffer);
}
catch( SQLException e ) {
System.out.println(e.getMessage( ));
}
}
buffer = new StringBuffer( );
line = 1;
continue;
}
// The user wants to quit
else if( cmd.equals("quit") ) {
connected = false;
continue;
}
// The user wants to clear the current buffer
else if( cmd.equals("reset") ) {
buffer = new StringBuffer( );
line = 1;
continue;
}
// The user wants to abort a pending transaction
else if( cmd.equals("rollback") ) {
try {
connection.rollback( );
System.out.println("Rollback successful.");
}
catch( SQLException e ) {
System.out.println("An error occurred during rollback: " +
e.getMessage( ));
}
buffer = new StringBuffer( );
line = 1;
}
// The user wants version info
else if( cmd.startsWith("show") ) {
DatabaseMetaData meta;
try {
meta = connection.getMetaData( );
cmd = cmd.substring(5, cmd.length()).trim( );
if( cmd.equals("version") ) {
showVersion(meta);
}
else {
System.out.println("show version"); // Bad arg
}
}
catch( SQLException e ) {
System.out.println("Failed to load meta data: " +
e.getMessage( ));
}
buffer = new StringBuffer( );
line = 1;
}
// The input that is not a keyword
// it should appended be to the buffer
else {
buffer.append(" " + tmp);
line++;
continue;
}
}
try {
connection.close( );
}
catch( SQLException e ) {
System.out.println("Error closing connection: " +
e.getMessage( ));
}
System.out.println("Connection closed.");
}In Example 4.4, the application expects the user to use the jdbc.drivers property to identify the JDBC driver being used and to pass the JDBC URL as the sole command line argument. The program will then query the specified driver for its driver property information, prompt the user to enter values for the required properties, and finally attempt to make a connection.
The meat of main() is the loop that accepts user
input and acts on it. It first checks if any line of input matches
one of the applications commands. If so, it executes the specified
command. Otherwise it treats the input as part of a larger SQL
statement and waits for further input.
The interesting parts of the application are in the
executeStatement( )
and processResults( )
methods. In executeStatement(), the application
blindly accepts any SQL the user sends it, creates a
Statement, and executes it. At that point, several
things might happen:
The SQL could have errors. If it does, the application displays the errors to the user and returns to the main loop for more input.
The SQL could have been a nonquery. If this is the case, that application lets the user know how many rows were affected by the query.
The SQL could have been a query. If it is, the application grabs the result set and sends it to
processResults()for display.
Example 4.5 shows the executeStatement( ) method, which takes a raw SQL string and executes it
using the specified JDBC Connection object.
Example 4-5. The executeStatement( ) Method for the Terminal Monitor Application
static public void executeStatement(StringBuffer buff)
throws SQLException {
String sql = buff.toString( );
Statement statement = null;
try {
statement = connection.createStatement( );
if( statement.execute(sql) ) {
// true means the SQL was a SELECT
processResults(statement.getResultSet( ));
}
else {
// no result sets, see how many rows were affected
int num;
switch(num = statement.getUpdateCount( )) {
case 0:
System.out.println("No rows affected.");
break;
case 1:
System.out.println(num + " row affected.");
break;
default:
System.out.println(num + " rows affected.");
}
}
}
catch( SQLException e ) {
throw e;
}
finally { // close out the statement
if( statement != null ) {
try { statement.close( ); }
catch( SQLException e ) { }
}
}
}To handle dynamic result sets, use the
ResultSetMetaData class. The
processResults( )
method shown in Example 4.6 uses these methods:
-
getColumnCount( ) Finds out how many columns are in the result set. You need to know how many columns there are so that you do not ask for a column that does not exist or miss one that does exist.
-
getColumnType( ) Finds out the datatype for each column. You need to know the datatype when you retrieve it from the result set.
-
getColumnLabel( ) Gives a display name to place at the top of each column.
-
getColumnDisplaySize( ) Tells how wide the display of the columns should be.
Example 4-6. The processResults( ) Method from the Terminal Monitor Application
static public void processResults(ResultSet results)
throws SQLException {
try {
ResultSetMetaData meta = results.getMetaData( );
StringBuffer bar = new StringBuffer( );
StringBuffer buffer = new StringBuffer( );
int cols = meta.getColumnCount( );
int row_count = 0;
int i, width = 0;
// Prepare headers for each of the columns
// The display should look like:
// --------------------------------------
// | Column One | Column Two |
// --------------------------------------
// | Row 1 Value | Row 1 Value |
// --------------------------------------
// create the bar that is as long as the total of all columns
for(i=1; i<=cols; i++) {
width += meta.getColumnDisplaySize(i);
}
width += 1 + cols;
for(i=0; i<width; i++) {
bar.append('-');
}
bar.append('\n');
buffer.append(bar.toString( ) + "|");
// After the first bar goes the column labels
for(i=1; i<=cols; i++) {
StringBuffer filler = new StringBuffer( );
String label = meta.getColumnLabel(i);
int size = meta.getColumnDisplaySize(i);
int x;
// If the label is longer than the column is wide,
// then we truncate the column label
if( label.length( ) > size ) {
label = label.substring(0, size);
}
// If the label is shorter than the column,
// pad it with spaces
if( label.length( ) < size ) {
int j;
x = (size-label.length( ))/2;
for(j=0; j<x; j++) {
filler.append(' ');
}
label = filler + label + filler;
if( label.length( ) > size ) {
label = label.substring(0, size);
}
else {
while( label.length( ) < size ) {
label += " ";
}
}
}
// Add the column header to the buffer
buffer.append(label + "|");
}
// Add the lower bar
buffer.append("\n" + bar.toString( ));
// Format each row in the result set and add it on
while( results.next( ) ) {
row_count++;
buffer.append('|');
// Format each column of the row
for(i=1; i<=cols; i++) {
StringBuffer filler = new StringBuffer( );
Object value = results.getObject(i);
int size = meta.getColumnDisplaySize(i);
String str;
if( results.wasNull( ) ) {
str = "NULL";
}
else {
str = value.toString( );
}
if( str.length( ) > size ) {
str = str.substring(0, size);
}
if( str.length( ) < size ) {
int j, x;
x = (size-str.length( ))/2;
for(j=0; j<x; j++) {
filler.append(' ');
}
str = filler + str + filler;
if( str.length( ) > size ) {
str = str.substring(0, size);
}
else {
while( str.length( ) < size ) {
str += " ";
}
}
}
buffer.append(str + "|");
}
buffer.append("\n");
}
// Stick a row count up at the top
if( row_count == 0 ) {
buffer = new StringBuffer("No rows selected.\n");
}
else if( row_count == 1 ) {
buffer = new StringBuffer("1 row selected.\n" +
buffer.toString( ) +
bar.toString( ));
}
else {
buffer = new StringBuffer(row_count + " rows selected.\n" +
buffer.toString( ) +
bar.toString( ));
}
System.out.print(buffer.toString( ));
System.out.flush( );
}
catch( SQLException e ) {
throw e;
}
finally {
try { results.close( ); }
catch( SQLException e ) { }
}
}As a small demonstration of the workings of the
DatabaseMetaData class, I have also added a
showVersion() method that grabs database and
driver version information from
the
DatabaseMetaData
class:
static public void showVersion(DatabaseMetaData meta) {
try {
System.out.println("TerminalMonitor v2.0");
System.out.println("DBMS: " + meta.getDatabaseProductName( ) +
" " + meta.getDatabaseProductVersion( ));
System.out.println("JDBC Driver: " + meta.getDriverName( ) +
" " + meta.getDriverVersion( ));
}
catch( SQLException e ) {
System.out.println("Failed to get version info: " +
e.getMessage( ));
}
}Get Database Programming with JDBC & Java, Second Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
