Getting column information

The ResultSetMetaData object has a set of get and is methods you can use to dynamically determine information about a result set at runtime. The first method in the list that follows,

getColumnCount( ), is the only method that is not column-specific. It returns the number of columns in the result set, starting with 1. Following is a list of the get and is methods. For most of the methods in this list, you'll pass the column number as a parameter.

int getColumnCount( )

Gets the number of columns in the ResultSet.

String getSchemaName(int column)

Gets a column's table's schema name. Unfortunately, this method does not work for JDBC driver Version 8.1.6.

String getTableName(int column)

Gets a column's table name. Unfortunately, this method does not work for JDBC driver Version 8.1.6.

String getCatalogName(int column)

Gets a column's table's catalog name. Since there are no catalogs in Oracle, this method has no use.

String getColumnName(int column)

Gets a column's name. This should return the column name as it exists in the database, but it returns the alias for a column if an alias was used.

String getColumnLabel(int column)

Gets the suggested column title for printouts and displays. This method returns the column name or the alias if one was used.

String getColumnTypeName(int column)

Gets a column's database-specific data type name.

int getColumnType(int column)

Gets a column's java.sql.Types constant.

String getColumnClassName(int column)

Gets the fully qualified Java class name of the object that will be returned by a call to the

ResultSet.getObject( ) method.

int getColumnDisplaySize(int column)

Gets the column's normal maximum width in characters.

Gets the number of decimal digits supported by a NUMBER column.

int getScale(int column)

Gets the number of digits to the right of the decimal point in a NUMBER column.

int isNullable(int column)

Indicates whether the column is nullable. This method returns one of the following

ResultSetMetaData constants:

static int columnNoNulls

The column may not contain NULL.

static int columnNullable

The column may contain NULL.

static int columnNullableUnknown

The nullability of the column is unknown.

boolean isAutoIncrement(int column)

Indicates whether the column is automatically numbered and is thus read-only. There is currently no use for this method with Oracle, because Oracle does not implement auto- incrementing columns.

boolean isCaseSensitive(int column)

Indicates whether a column's case matters.

boolean isCurrency(int column)

Indicates whether the column is a cash value. Oracle does not have a money or currency SQL data type, so this method returns true for any numeric SQL data type.

boolean isSigned(int column)

Indicates whether values in the column are signed numbers.

boolean isSearchable(int column)

Indicates whether the column can be used in a WHERE clause.

boolean isReadOnly(int column)

Indicates whether a column is definitely not writeable.

boolean isWritable(int column)

Indicates whether it is possible for a write on the column to succeed.

boolean isDefinitelyWritable(int column)

Indicates whether a write on the column will definitely succeed. 10.3.2.3 Getting column values

When you are retrieving column values from a result set but do not know which data type they are, you can use a special getXXX( ) method, getObject( ), to retrieve a column value as an instance of the Java class Object. Since all Java objects are descendants of the class

Object, you can cast the retrieved Object to a specific descendant type. You'll utilize this technique if you use the ResultSetMetaData object. If you combine the getColumnCount( )

method with the getColumnClassName( ) and ResultSet object's getObject( ) method, you can dynamically get the column values of a result set. Here's how it works. The

getColumnCount( ) method returns the actual number of columns in the result set. You can use this number in a for loop to retrieve the column values for each column and use the

object that is returned when you call the getObject( ) method. You can then use the class name to create an appropriate variable to hold the value returned by getObject( ) and perform an appropriate cast. One of the four overloaded getObject( ) method signatures is:

Object getObject(int columnIndex)

If you call the getColumnClassName( ) method, and it returns java.lang.BigDecimal, then you'll create a BigDecimal variable and cast the results of a call to getObject( ) to a

BigDecimal:

BigDecimal column1 = (BigDecimal) rslt.getObject(1)

10.3.2.4 A ResultSetMetaData example

Now that we've covered the ResultSetMetaData object's capabilities, let's take a look at Example 10-5, which uses some of the ResultSetMetaData methods to dynamically retrieve the result set data.

In our sample program, TestMetaData, the program uses the Statement object's execute( ) method to enable the program to execute any SQL statement. If the execute( ) method returns true, a ResultSet object is available, and the program proceeds by calling the

Statement object's getResultSet( ) method to retrieve the ResultSet object. Otherwise, the program calls the Statement object's getUpdateCount( ) method to get the number of rows affected by the SQL statement just executed.

If a ResultSet object was returned, the program continues by calling the ResultSet object's

getMetaData( ) method to retrieve the result set's ResultSetMetaData object. Using that object, the program calls the getColumnCount( ) method to determine the number of columns in the result set. Next, the program enters a while loop where it iterates through the entire result set one row at a time. For the first row, the program calls a helper method, formatHeader( ), passing the values returned by the ResultSetMetaData object's getColumnLabel( ),

getColumnClassName( ), and getColumnDisplaySize( ) methods. The

formatHeader( ) method creates an appropriate heading for each column. For each row, the program calls a helper method, formatColumn( ), passing the values returned by the

ResultSet object's getObject( ) method and the ResultSetMetaData object's

getColumnClassName( ) and getColumnDisplaySize( ) methods. Both helper methods work in a similar fashion, so let's just talk about formatColumn( ) in detail.

In formatColumn( ), the program initializes a String value to an empty set. For each possible class name, the program tests to see if the object is a null reference. If it is,

formatColumn( ) returns a properly padded blank String object; otherwise, it creates a

String representation of the corresponding object and returns it right- or left-padded with spaces.

Example 10-5. Using ResultSetMetaData

import java.io.*; import java.math.*; import java.sql.*; import java.text.*;

public class TestMetaData { Connection conn;

public TestMetaData( ) { try {

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver( ));

conn = DriverManager.getConnection(

"jdbc:oracle:thin:@dssw2k01:1521:orcl", "scott", "tiger"); } catch (SQLException e) { System.err.println(e.getMessage( )); e.printStackTrace( ); } }

public static void main(String[] args) throws Exception, IOException {

TestMetaData tmd = new TestMetaData( );

tmd.process(

"select to_char( NULL ) a_cha r, " + " to_date( NULL ) a_date, " + " to_number( NULL ) a_number " + "from sys.dual");

tmd.process(

"select 'ABCDEFG' a_char, " + " sysdate a_date, " + " 1 an_integer, " + " 1.1 a_float " + "from sys.dual"); tmd.process( "delete PERSON " + "where 1 = 0"); }

public void process(String sql) throws IOException, SQLException { int columns = 0;

int i = 0; int rows = 0; ResultSet rslt = null; ResultSetMetaData meta = null; Statement stmt = null; try { stmt = conn.createStatement( ); if (stmt.execute(sql)) { rslt = stmt.getResultSet( ); meta = rslt.getMetaData( ); columns = meta.getColumnCount( ); while (rslt.next( )) { rows++; if (rows == 1) {

for (i = 1;i <= columns;i++) { System.out.print( formatHeading( meta.getColumnLabel(i), meta.getColumnClassName(i), meta.getColumnDisplaySize(i))); }

System.out.println(""); }

for (i = 1;i <= columns;i++) { System.out.print( formatColumn( rslt.getObject(i), meta.getColumnClassName(i), meta.getColumnDisplaySize(i))); } System.out.println(""); } System.out.println(""); rslt.close( ); rslt = null; meta = null; } else { rows = stmt.getUpdateCount( );

System.out.println(Integer.toString(rows) + " rows affected"); System.out.println(""); } stmt.close( ); stmt = null; } catch (SQLException e) { System.err.println(e.getMessage( )); } finally { if (rslt != null)

try { rslt.close( ); } catch (SQLException ignore) { } if (stmt != null)

try { stmt.close( ); } catch (SQLException ignore) { } }

}

private String formatColumn(

Object object, String className, int displaySize) { String value = "";

if (className.equals("java.lang.String")) { if (object != null) {

value = rpad((String)object, displaySize, ' '); }

else {

value = rpad(value, displaySize, ' '); } } else if (className.equals("java.math.BigDecimal")) { if (object != null) { BigDecimal n = (BigDecimal)object; value = lpad(n.toString( ), 9, ' '); } else { value = rpad(value, 9, ' '); } } else if (className.equals("java.sql.Timestamp")) {

if (object != null) { Timestamp ts = (Timestamp)object; value = rpad(ts.toString( ), 21, ' '); } else { value = rpad(value, 21, ' '); } } else {

System.err.println("Unsupported class name: " + className); }

return value + " "; }

private String formatHeading(

String heading, String className, int displaySize) { int length = displaySize;

String value = ""; if (heading != null) { value = heading; if (className.equals("java.lang.String")) { } else if (className.equals("java.math.BigDecimal")) { length = 9; } else if (className.equals("java.sql.Timestamp")) { length = 21; } else {

System.err.println("Unsupported class name: " + className); }

}

return rpad(value, length, ' ') + " "; }

private String rpad(String in, int length, char pad) { StringBuffer out = new StringBuffer(length);

int least = in.length( );

if (least > length) least = length;

out.append(in.substring(0, least));

int fill = length - out.length( ); for (int i=0;i < fill;i++) {

out.append(pad); }

return out.toString( ); }

private String lpad(String in, int length, char pad) { StringBuffer out = new StringBuffer(length);

int least = in.length( );

if (least > length) least = length;

int fill = length - out.length( ); for (int i=0;i < fill;i++) {

out.insert(0, pad); }

return out.toString( ); }

protected void finalize( ) throws Throwable {

if (conn != null)

try { conn.close( ); } catch (SQLException ignore) { } super.finalize( );

} }

Instead of using the ResultSetMetaData object's getColumnClassName( ) method, the program can use the Java instanceof operator to determine the class to which an object belongs. For example:

private String formatColumn(Object object, int displaySize) { String value = "";

if (object instanceof java.lang.String) { value = rpad((String)object, displaySize, ' '); }

else if (object instanceof java.math.BigDecimal) { BigDecimal n = (BigDecimal)object;

value = lpad(n.toString( ), 9, ' '); }

else if (object instanceof java.sql.Timestamp) { Timestamp ts = (Timestamp)object;

value = rpad(ts.toString( ), 21, ' '); }

else if (object == null) {

value = rpad(value, displaySize, ' '); } else { System.err.println("Unsupported class: " + object.getClass().getName( )); } return value + " "; }

One problem with using instanceof is that you can't identify an object's type when the underlying database column is NULL, because getObject( ) will return a null reference. For this reason, it is better to use the ResultSetMetaData object's getColumnClassName( )

method to determine the class of an object.

As far as the ResultSetMetaData object is concerned, we have only scratched the surface here, but if you have to work with a SQL statement built dynamically in your program, you'll know that the JDBC API has all the enabling methods you need in the DatabaseMetaData and

ResultSetMetaData objects.

Up to this point all we've been talking about is getting the data from a ResultSet; we have conveniently skipped any discussion about row positioning capabilities. So let's tackle that topic next.

In document Java Programming with Oracle JDBC pdf (Page 163-169)