O'Reilly logo

Database Programming with JDBC & Java, Second Edition by George Reese

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Meta-Data

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.

Result Set Meta-Data

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 NULL a 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.

Database Meta-Data

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.

Driver Property Information

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.

A Generic Terminal Monitor

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 DatabaseMetaData interface 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( ));
    }
}

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required