13.3 Dynamic Database Access

So far, we have dealt with applications in which you know exactly what needs to be done at compile time. If this were the only kind of database support that JDBC provided, no one could ever write tools like the mysql interactive command-line tool that determines SQL calls at runtime and executes them. The JDBC Statement class provides the execute( ) method for executing SQL that can be either a query or an update. Additionally, ResultSet instances provide runtime information about themselves in the form of an interface called ResultSetMetaData, which you can access via the getMetaData( ) call in the ResultSet.

13.3.1 Metadata

The term metadata sounds officious, but it is really nothing more than extra data about some object that would otherwise waste resources if it were actually kept in the object. For example, simple applications do not need the name of the columns associated with a ResultSet—the programmer probably knew that when the code was written. Embedding this extra information in the ResultSet class is thus not considered by JDBC's designers to be part of the core of ResultSet functionality. Data such as the column names, however, is very important to some database programmers—especially those writing dynamic database access. The JDBC designers provide access to this extra information—the metadata—via the ResultSetMetaData interface. This class specifically provides:

• The number of columns in a result set

• Whether NULL is a valid value for a column

• The label to use for a column header

• The name for a given column

• The source table for a given column

• The data type of a given column

Example 13-6 shows some of the source code from a command-line tool that accepts arbitrary user input and sends it to MySQL for execution. The rest of the code for this example can be found at the O'Reilly web site with the other examples from this book.

Example 13-6. An application for executing dynamic SQL

import java.sql.*;

public class Exec {
    public static void main(String args[]) {
        Connection con = null;
        String sql = "";
      
        for(int i=0; i<args.length; i++) {
            sql = sql + args[i];
            if( i < args.length - 1 ) {
                sql = sql + " ";
            }
        }
        System.out.println("Executing: " + sql);
        try {
            Class.forName("com.caucho.jdbc.mysql.Driver").newInstance(  );
            String url = "jdbc:mysql-caucho://athens.imaginary.com/TEST";
            con = DriverManager.getConnection(url, "test", "test");
            Statement s = con.createStatement(  );   
          
            if( s.execute(sql) ) {
                ResultSet r = s.getResultSet(  );
                ResultSetMetaData meta = r.getMetaData(  );
                int cols = meta.getColumnCount(  );
                int rownum = 0;
              
                while( r.next(  ) ) {
                    rownum++;
                    System.out.println("Row: " + rownum);
                    for(int i=0; i<cols; i++) {
                        System.out.print(meta.getColumnLabel(i+1) + ": "
                                         + r.getObject(i+1) +  ", ");
                    }
                    System.out.println("");
                }
            }
            else {
                System.out.println(s.getUpdateCount(  ) + " rows affected.");
            }
            s.close(  );
            con.close(  );
        }
        catch( Exception e ) {
            e.printStackTrace(  );
        }
        finally {
            if( con != null ) {
                try { con.close(  ); }
                catch( SQLException e ) { }
            }
        }
    }
}

Each result set provides a ResultSetMetaData instance via the getMetaData( ) method. In the case of dynamic database access, we need to find out how many columns are in a result set so we are certain to retrieve each column as well as the names of each column for display to the user. The metadata for our result set provides all of this information via the getColumnCount( ) and getColumnLabel( ) methods.

13.3.2 Processing Dynamic SQL

The overriding concept introduced in Example 13-6 is the dynamic SQL call. Because we do not know whether we will be processing a query or an update, we need to pass the SQL call through the execute( ) method. This method returns true if the statement returned a result set or false if none was produced. In the example, if it returns true, the application gets the returned ResultSet through a call to getResultSet( ). The application can then go on to do normal result set processing. If, on the other hand, the statement performed some sort of database modification, you can call getUpdateCount( ) to find out how many rows were modified by the statement.