Brought to you by EarthWeb
ITKnowledge Logo Login Graphic Click Here!
Click Here!
ITKnowledge
Search this book:
 
Search the site:
 
EXPERT SEARCH ----- nav

EarthWeb Direct

EarthWeb Direct

EarthWeb sites: other sites

Previous Table of Contents Next


Getting the Number and Label of Columns

A row is usually composed of table data that may be organized in different columns of different types. It may be important to discover the properties of the result set’s rows, the number of columns, and the type of data in each column. We will see later how to get such information about result sets. Only the column number and column labels will be used now.

ResultSet

int getMetaData().getColumnCount();
String getMetaData().getColumnLabel(int i);

The getMetaData() method returns a ResultSetMetaData object that is explained later because it is quite complex. Calling getColumnCount() on this object returns the expected value.

int getMetaData().getColumnCount();

The return type is integer and is the number of columns in the rows composing this result set.

String getMetaData().getColumnLabel(int i);

The parameter is the column index where a value of 1 indicates the first column. The method obviously returns the label for the column at this index.

It may be more efficient to store the ResultSetMetaData object once instead of calling the method to create it each time it is necessary to access a property. The driver may provide caching, but it is often preferable not to abuse such features when not really needed. Listing 5-16 shows how to call these methods.

Listing 5-16: Getting the number and label of columns.

...
Connection myConnection = DriverManager.getConnection(url,
               “javauser”, “hotjava”);
Statement myStatement = myConnection.createStatement();
ResultSet rs = myStatement.executeQuery(sqlQuery);
int maxColumns = rs.getMetaData().getColumnCount();
for (int i = 1; i <= maxColumns; i++)
{
  if (i > 1) System.out.print(“, “);
  System.out.print(rs.getMetaData().getColumnLabel(i));
}
System.out.println(“====================================”);
while (rs.next())
{
  // we got a row
}
myStatement.close();
myConnection.close();
...
...

Accessing Columns

As shown in Figure 5-8, columns must be fetched one by one, in left-to-right order. Fetching may be done in a loop using the column indexes or column names discovered by the ResultSetMetaData object.


Figure 5-8:  Overview of accessing columns.

The row’s contents are accessible via “getXXX()” methods that allow the extraction of the various values of the columns of rows in the result set.

There are two ways of accessing columns: by column index or by column name. Accessing a column by name is more convenient, but less efficient because it internally needs many comparisons of strings before finding the column. Certain SQL statements return tables without column names or with multiple identical column names. It is absolutely necessary to use column numbers in these cases. Figure 5-9 illustrates the access to a row’s columns.


Figure 5-9:  Accessing columns with getXXX().

All columns within a row must be read in left-to-right order, and each column must only be read once. This rule may not be true with some DBMSs, but it is preferable to observe it to ensure maximum portability.

By Column Indexes

Below is the list of “getXXX()” methods available to fetch columns in a row:

Result Set

String getString(int columnIndex);
boolean getBoolean(int columnIndex);
byte getByte(int columnIndex);
short getShort(int columnIndex);
int getInt(int columnIndex);
long getLong(int columnIndex);
float getFloat(int columnIndex);
double getDouble(int columnIndex);
java.math.BigDecimal getBigDecimal(int columnIndex, int scale);
byte[] getBytes(int columnIndex);
java.sql.Date getDate(int columnIndex);
java.sql.Time getTime(int columnIndex);
java.sql.Timestamp getTimestamp(int columnIndex);
java.io.InputStream getAsciiStream(int columnIndex);
java.io.InputStream getUnicodeStream(int columnIndex);
java.io.InputStream getBinaryStream(int columnIndex);
Object getObject(int columnIndex);

return_type getXXX(int columnIndex);

All these methods return the column value in the current row. Column indexes are integers.

This example shows how to execute a SQL statement and retrieve the results using column indexes. Note that the rows are always read from left-to-right and that columns are only read once. Listing 5-17 shows the statement using column indexes.

Listing 5-17. Using column indexes.

...
java.sql.Statement myStatement = myConnection.createStatement();
ResultSet rs = myStatement.executeQuery(“SELECT name, title, salary
                           FROM employees”);
while (rs.next()) {
 // print the columns of the row that was retrieved
 String empName = rs.getString(1);
 String empTitle = rs.getString(2);
 long empSalary = rs.getLong(3);
 System.out.println(“Employee “ + empName + “ is “ + empTitle + “
                          and earns $” + empSalary);
}
...
...

By Column Names

Column names may be more convenient to use. Here are the “getXXX()” methods supporting column names:

ResultSet

String getString(String columnName);
boolean getBoolean(String columnName);
byte getByte(String columnName);
short getShort(String columnName);
int getInt(String columnName);
long getLong(String columnName);
float getFloat(String columnName);
double getDouble(String columnName);
java.math.BigDecimal getBigDecimal(String columnName, int scale);
byte[] getBytes(String columnName);
java.sql.Date getDate(String columnName);
java.sql.Time getTime(String columnName);
java.sql.Timestamp getTimestamp(String columnName);
java.io.InputStream getAsciiStream(String columnName);
java.io.InputStream getUnicodeStream(String columnName);
java.io.InputStream getBinaryStream(String columnName);
Object getObject(String columnName);

return_type getXXX(String columnName);

The parameter should match exactly with the row’s column name that needs to be accessed.

The same example using column names is shown in Listing 5-18.

Listing 5-18: Using column names.

...
java.sql.Statement myStatement =
                                   myConnection.createStatement();
ResultSet rs = myStatement.executeQuery(“SELECT name, title, salary FROM employees”);
while (rs.next()) {
// print the columns of the row that was retrieved
String empName = rs.getString(“name”);
String empTitle = rs.getString(“title”);
long empSalary = rs.getLong(“salary”);
System.out.println(“Employee “ + empName + “ is “ + empTitle + “ and earns $” +
  empSalary);
}
...
...


Previous Table of Contents Next
HomeAbout UsSearchSubscribeAdvertising InfoContact UsFAQs
Use of this site is subject to certain Terms & Conditions.
Copyright (c) 1996-1999 EarthWeb Inc. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.