Retrieving Query Results

While a simple query is easy to execute (as in the preceding example), the more complicated queries that select records from a database will require slightly more involved Java applications.

First of all, instead of using the executeUpdate() function, you'll need to run the query through the executeQuery() method. This function returns a ResultSet object, which will be used to access each returned row. The easiest way to do so is to use a while loop and the next() method:

ResultSet rs = stmt.executeQuery("SELECT * FROM tablename");
while (rs.next()) {
   // Do something with the results.
}

One way of using the results is to assign the column values to variables. You'll need to use one of the getXXX() functions, listed in Table 9.1, corresponding to the type of value being retrieved. These functions will accept either the column name or number (indexed starting at 1) as an argument. For example:

Table 9.1. These functions are used to fetch query results into variables. The right function should be used that matches the type of value returned (i.e., the MySQL data type of that column).
The getXXX() Functions
Function
Recommended For
getShort()
Small integers
getInt()
Integers
getLong()
Big integers
getFloat()
Floats
getdouble()
Floats and doubles
getBigDecimal()
Decimals
getString()
Char, varchar, and text types
geTDate()
Dates
getTime()
Time values
getTimestamp()
Timestamps

while (rs.next()) {
   int key = rs.getInt(1);
   String value = rs.getString ("stringcolumnname");
}

As an application of this, this next script will show a few of the clients in the database.

To retrieve query results:

Create a new Java application in your text editor or Java development tool (Script 9.3).

import java.sql.*;
public class Select {
   public static void main(String args[]) throws Exception {

Initialize the variables.

Connection con = null;
Statement stmt = null;
ResultSet rs = null;
int id = 0;
String name = null;

The application will introduce a new variable called rs of type ResultSet. This variable will be used to access the results of the query. Also, I've initialized an integer, which will be used to temporarily store the client's ID number, and a string, which will temporarily store the client's name.

Script 9.3. The Select class runs a basic query on a table and displays the results.

1 import java.sql.*; 2 3 // Script 9.3 'Select.java' 4 5 public class Select { 6 7 public static void main(String args[]) throws Exception { 8 9 // Initialize variables. 10 Connection con = null; 11 Statement stmt = null; 12 ResultSet rs = null; 13 int id = 0; 14 String name = null; 15 16 try { 17 18 // Connect to MySQL. 19 String url = "jdbc:mysql:///accounting"; 20 Class.forName("com.mysql.jdbc.Driver").newInstance(); 21 con = DriverManager.getConnection(url, "username", "password"); 22 23 // Run the query. 24 stmt = con.createStatement(); 25 rs = stmt.executeQuery("SELECT client_id, client_name FROM clients ORDER BY client_name ASC LIMIT 5"); 26 27 // Fetch the results. 28 while (rs.next()) { 29 id = rs.getInt(1); 30 name = rs.getString("client_name"); 31 32 System.out.println(id + ": " + name); 33 } 34 } 35 36 // Catch exceptions. 37 catch (SQLException e) { 38 System.out.println("Problem: " + e.toString()); 39 } 40 41 // Clean up. 42 finally { 43 if (rs != null) { 44 try { 45 rs.close(); 46 } catch (SQLException e) { 47 // Do nothing with exception. 48 } 49 rs = null; 50 } 51 if (stmt != null) { 52 try { 53 stmt.close(); 54 } catch (SQLException e) { 55 // Do nothing with exception. 56 } 57 stmt = null; 58 } 59 if (con != null) { 60 try { 61 con.close(); 62 } catch (SQLException e) { 63 // Do nothing with exception. 64 } 65 } 66 } 67 68 } // End of main(). 69 70 } // End of class Select.

Establish a connection to the accounting database.

try {
   String url = "jdbc:mysql:///accounting";
   Class.forName("com.mysql.jdbc.Driver").newInstance();
   con = DriverManager.getConnection(url, "username", "password");

Again, be certain to use a user/host/password combination that has permission to connect to and select from the accounting database.

Execute a SELECT query.

[View full width]stmt = con.createStatement();
rs = stmt.executeQuery("SELECT client_id, client_name FROM clients ORDER BY client_name
 ASC LIMIT 5");

Like before, the first step is to establish the stmt variable based upon the createStatement() method of the con connection variable. Then the query is fed as an argument to the executeQuery() method, rather than the executeUpdate() used previously. For demonstration purposes, this query will display five records of client information. Naturally, you can run any sort of SELECT query from Java, as long as it is SQL compliant.

Print out the returned rows.

while (rs.next()) {
   id = rs.getInt(1);
   name = rs.getString("client_name");
   System.out.println(id + ": " + name);
}

This loop will retrieve every record returned by the query (which should be five at the most). Then I use the getInt() and getString() methods to retrieve the value of an integer and a string column type, respectively. To use these functions, I refer to the column's indexed position in one case (the client_id is the first value returned) and the column's name in the other case.

Complete the try clause and catch any errors that might have occurred.

}
catch (SQLException e) {
   System.out.println("Problem: " + e.toString());
}

Wrap up the class, closing all resources.

finally {
   if (rs != null) {
       try {
           rs.close();
        } catch (SQLException e) {
           // Do nothing with exception.
        }
        rs = null;
    }
    if (stmt != null) {
       try {
          stmt.close();
       } catch (SQLException e) {
          // Do nothing with exception.
       }
       stmt = null;
    }
    if (con != null) {
       try {
          con.close();
       } catch (SQLException e) {
          // Do nothing with exception.
       }
   }
}
} // End of main().
} // End of class Select.

Especially when dealing with SELECT queries, freeing up the resources of a statement and a result set is a good programming practice. This will be more and more true as your queries become more complex. In the finally clause, I attempt to close the result set, statement, and connection (in that order), if each has a value.

Finding the Number of Returned Rows

As you know, MySQL will report upon the number of returned rows for any SELECT query. There's no one function that will reveal this value in Java, but because you can move around within the returned rows easily, a little bit of code will suffice. For starters, run the query and assign the results to a ResultSet variable:

ResultSet rs = stmt.executeQuery("SELECT * FROM tablename");

Now move to the last row in the result set:

rs.last();

Assign this row number to a variable to determine the total number of returned rows:

int num = rs.getRow();

If you want to fetch the rows, move back to the first row, prior to your while loop:

rs.beforeFirst(); while (rs.next()) { ...

I'm no longer doing anything with any exceptions that might occur (as they shouldn't), but you could print them out or whatever.

Save the file as Select.java, compile, and run the application (Figure 9.9).

Figure 9.9. The `Select` class will run a query and display the results.

Tips

As of JDBC 2.0, you can move through result sets using next()as demonstrated in this sectionand previous().
When using the getXXX() functions, referring to columns by their index is slightly faster than referring to columns by name.
The getMetaData() function can be used to find out information about a particular column, such as its name, data type, etc.
Most of the getXXX() functions will attempt to parse their type out of a value. For example, if you apply getString() to a number type, the number will be returned as a string.
If you are only fetching one row, you can do without the while loop but you still need to use next() to fetch that one row into the result set.