Java Cookbook

Sending a JDBC Query and Getting Results

Problem

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

Solution

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

Discussion

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 ...