You’re getting tired of all this setup and want to see results.

Get a Statement and use it to execute a query. You’ll get a set of results, a ResultSet object.

The Connection object can generate various kinds of statements; the simplest is a Statement created by createStatement( ) and used to send your SQL query as an arbitrary string:

Statement stmt = conn.createStatement(  );
stmt.executeQuery("select * from myTable");

The result of the query is returned as a ResultSet object. The ResultSet works like an iterator in that it lets you access all the rows of the result that match the query. This process is shown in Figure 20-1.

Figure 20-1. ResultSet illustrated

Typically, you use it like this:

while (rs.next(  )) {
    int i = rs.getInt(1);        // or getInt("UserID");

As the comment suggests, you can retrieve elements from the ResultSet either by their column index (which starts at one, unlike most Java things, which typically start at zero) or column name. In JDBC 1, you must retrieve the values in increasing order by the order of the SELECT (or by their column order in the database if the query is SELECT *). In JDBC 2, you can retrieve them in any order (and in fact, many JDBC 1 drivers don’t enforce the retrieving of values in certain orders). If you want to learn the column names (a sort of introspection), you can use a ResultSet’s getResultSetMetaData( ) method, described in Section 20.11. There are many types of data in SQL, and there are methods to get them from a ResultSet; the common ones are shown in Table 20-3.

Assuming that we have a relational database containing the User data, we can retrieve it as demonstrated in Example 20-7. This program retrieves any or all entries that have a username of ian and prints the ResultSets in a loop. It prints lines like:

User ian is named Ian Darwin

The source code is shown in Example 20-7.

import jabadot.*;

import java.sql.*;
import java.io.*;
import java.util.*;

/** Look up one use from the relational database using JDBC.
 */
public class UserQuery {

    public static void main(String[] fn)
    throws ClassNotFoundException, SQLException, IOException {

        // Load the database driver
        Class.forName(JDConstants.getProperty("jabadot.userdb.driver"));

        System.out.println("Getting Connection");
        Connection conn = DriverManager.getConnection(
            JDConstants.getProperty("jabadot.dburl"));

        Statement stmt = conn.createStatement(  );

        ResultSet rs = stmt.executeQuery(
            "SELECT * from userdb where name='ian'");

        // Now retrieve (all) the rows that matched the query
        while (rs.next(  )) {

            // Field 1 is login name
            String name = rs.getString(1);

            // Password is field 2 - do not display.

            // Column 3 is fullname
            String fullName = rs.getString(3);

            System.out.println("User " + name + " is named " + fullName);
        }

        rs.close(  );            // All done with that resultset
        stmt.close(  );        // All done with that statement
        conn.close(  );        // All done with that DB connection
        System.exit(0);        // All done with this program.
    }
}

Note that a ResultSet is tied to its Connection object; if the Connection is closed, the ResultSet becomes invalid. You should either extract the data from the ResultSet before closing it, or cache it in a CachingRowSet , an experimental RowSet subclass currently available from Sun’s Java web site (RowSet is a JDBC 2 subclass of ResultSet).