This example fetches all BEVERAGE products and their associated suppliers as well as the prices charged for the product, then prints this list, sorted by product. The ResultSet rs is returned with its cursor set before the first record. This is convenient for starting a while loop with rs.next().The first time through, the cursor is on the first record. Inside the loop, the values for the associated row columns are referenced by name. Use the getInt() method for the price. We didn’t mind losing some data for our simple display purposes. You could have used getFloat() or getDouble() and then formatted the output.

Figure 3.11  ResultSetExample output.

import java.sql.*;

public class ResultSetExample
{
    public static void main (String args[])
    {
        try
        {
            Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
        } catch (Exception e)
        {
            System.out.println(“JDBC–ODBC driver failed to load.”);
            return;
        }
  
        try
        {  
            Connection con =
              DriverManager.getConnection(
                                          “jdbc:odbc:Inventory”,
                                          “”,“”);
          
            Statement stmt = con.createStatement();
  
            // Execute a query and retrieve ResultSet.
            ResultSet rs = stmt.executeQuery(“SELECT SupplierName,” +
                                “ ProductName, Price ” +
                                “FROM ProductSuppliersView ” +
                                “WHERE CategoryName ” +
                                “LIKE ‘%BEVERAGES%’ ” +
                                “ORDER BY ProductName”);

            /*
               Now iterate through ResultSet and print the values
               of each attribute in the row. ResultSet maintains a
               cursor pointing to its current row of data. Initially, 
               the cursor is positioned before the first row. The
               next method moves the cursor to the next row.
            */
            while(rs.next()) 
            {   
                String supplier = rs.getString(“SupplierName”);
                String product = rs.getString(“ProductName”);
                //ResultSet has methods to map values to Java types.
                 int price = rs.getInt(“Price”);
      
                System.out.println(supplier + “ sells ” + product +
                                   “ for $” + price);
            }

            stmt.close();
            con.close();
        } catch (Exception e)
        {
            System.out.println(e);
        }
    }
}

The results of a query are returned in a result set. ResultSet stores the results in rows and maintains a pointer to the current row. Each value within a row can be accessed by its name or by its position. For example, instead of the line:

int price = rs.getInt(“Price”);

the following could have been used:

int price = rs.getInt(3);

This works only because we know that Price is the third column in the preceding query. You could also find this out by looking at the metadata of ResultSet. The next section explores the ResultSetMetaData class.

Working with ResultSetMetaData

In the previous example, the query was hard coded, so using the column names was a reasonable choice for fetching the data. However, what happens when you don’t know the column names before accessing ResultSet? Each ResultSet can return an object that implements the ResultSetMetaData interface. This object contains information about the returned results, including the number of columns returned and the names and types of the columns. This information can be used to dynamically display values. Table 3.11 lists the ResultSetMetaData values.

In this next example, the user is asked to type in SQL SELECT statements. The program will dynamically make requests against the database and display the results. The program will continue until the user types the word “exit.” In this example, only SQL statements that begin with the word SELECT are executed. This example could easily be extended to handle any SQL calls.