Recipe 20.12 Finding JDBC Metadata

Problem

You want to learn about a database or table.

Solution

Read the documentation provided by your vendor or database administrator. Or ask the software for a MetaData object.

Discussion

There are two classes relating to metadata (data about data) that you can ask for in the JDBC API: DatabaseMetaData and ResultSetMetaData . Each of these has methods that let you interrogate particular aspects. The former class is obtained from a get method in a Connection object; the latter from a get method in the given ResultSet .

ResultSetMetaData

First, let's look at the class ResultsDecoratorHTML , a "generic query" formatter shown in Example 20-13. This is one of several "ResultSet Formatters" used in the SQLRunner program of Recipe 20.13 (the parent class ResultsDecorator , discussed with SQLRunner , simply defines a Constructor that saves the given PrintWriter as a field, as well as providing two abstract methods that ResultsDecoratorHTML implements). When a program using ResultsDecoratorHTML calls the write( ) method, the ResultSet is interrogated and formatted into a neat little HTML table, using the column names from the ResultSetMetaData as the headings for the HTML table. The nice part about this program is that it responds to whatever columns are in the ResultSet , which need not be in the same order as they are in the database. Consider the two queries:

select name, address from userdb

select address, name from userdb

Any code that depends upon knowing the order in the database would look very strange indeed if the user query requested fields in a different order than they were stored in the database.

Example 20-13. ResultsDecoratorHTML.java

import java.io.PrintWriter;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;



/** Print ResultSet in HTML

 */

class ResultsDecoratorHTML extends ResultsDecorator {

    

    ResultsDecoratorHTML(PrintWriter out) {

        super(out);

    }

    

    public void write(ResultSet rs) throws SQLException {

        out.println("<br>Your response:");



        ResultSetMetaData md = rs.getMetaData( );

        int count = md.getColumnCount( );

        out.println("<table border=1>");

        out.print("<tr>");

        for (int i=1; i<=count; i++) {

            out.print("<th>");

            out.print(md.getColumnName(i));

        }

        out.println("</tr>");

        while (rs.next( )) {

            out.print("<tr>");

            for (int i=1; i<=count; i++) {

                out.print("<td>");

                out.print(rs.getString(i));

            }

            out.println("</tr>");

        }

        out.println("</table>");

        out.flush( );

    }



    /* (non-Javadoc)

     * @see ResultSetDecorator#write(int)

     */

    void write(int updateCount) throws SQLException {

        out.println("<br/>RowCount: updateCount = <b>" + 

                    updateCount + "</p>");

    }

}

DatabaseMetaData

Example 20-14 uses DatabaseMetaData to print out the name and version number of the database product and its default transaction isolation (basically, the extent to which users of a database can interfere with each other; see any good book on databases for information on transactions and why it's often really important to know your database's default transaction isolation).

Example 20-14. JDBCMeta.java

import com.darwinsys.util.FileProperties;



import java.awt.*;

import java.sql.*;



/** A database MetaData query

 */

public class JDBCMeta {



    public static void main(String[] av) {

        int i;

        try {

            FileProperties fp = new FileProperties("JDBCMeta.properties");



            // Load the driver

            Class.forName(fp.getProperty("driver"));



            // Get the connection

            Connection conn = DriverManager.getConnection (

                fp.getProperty("dburl"),

                fp.getProperty("user"),

                fp.getProperty("password"));



            // Get a Database MetaData as a way of interrogating 

            // the names of the tables in this database.

            DatabaseMetaData meta = conn.getMetaData( );



            System.out.println("We are using " + meta.getDatabaseProductName( ));

            System.out.println("Version is " + meta.getDatabaseProductVersion( ) );

        

            int txisolation = meta.getDefaultTransactionIsolation( );

            System.out.println("Database default transaction isolation is " + 

                txisolation + " (" +

                transactionIsolationToString(txisolation) + ").");



            conn.close( );



            System.out.println("All done!");



        } catch (java.io.IOException e) {

            System.out.println("Can't load PROPERTIES " + e);

        } catch (ClassNotFoundException e) {

            System.out.println("Can't load driver " + e);

        } catch (SQLException ex) {

            System.out.println("Database access failed:");

            System.out.println(ex);

        }

    }



    /** Convert a TransactionIsolation int (defined in java.sql.Connection)

     * to the corresponding printable string.

     */

    public static String transactionIsolationToString(int txisolation) {

        switch(txisolation) {

            case Connection.TRANSACTION_NONE: 

                // transactions not supported.

                return "TRANSACTION_NONE";

            case Connection.TRANSACTION_READ_UNCOMMITTED: 

                // All three phenomena can occur

                return "TRANSACTION_NONE";

            case Connection.TRANSACTION_READ_COMMITTED: 

            // Dirty reads are prevented; non-repeatable reads and 

            // phantom reads can occur.

                return "TRANSACTION_READ_COMMITTED";

            case Connection.TRANSACTION_REPEATABLE_READ: 

                // Dirty reads and non-repeatable reads are prevented;

                // phantom reads can occur.

                return "TRANSACTION_REPEATABLE_READ";

            case Connection.TRANSACTION_SERIALIZABLE:

                // All three phenomena prvented; slowest!

                return "TRANSACTION_SERIALIZABLE";

            default:

                throw new IllegalArgumentException(

                    txisolation + " not a valid TX_ISOLATION");

        }

    }

}

When you run it, in addition to some debugging information, you'll see something like this. The details, of course, depend on your database:

>

java JDBCMeta

Enhydra InstantDB - Version 3.13

The Initial Developer of the Original Code is Lutris Technologies Inc.

Portions created by Lutris are Copyright (C) 1997-2000 Lutris Technologies, Inc.All 

Rights Reserved.

We are using InstantDB

Version is Version 3.13

Database default transaction isolation is 0 (TRANSACTION_NONE).

All done!

>