Retrieving Rows from the Database

To perform a SELECT statement using JDBC, you use the executeQuery() method of the Statement object, which accepts a Java String containing the text for the SELECT statement.

Now, because a SELECT statement may return more than one row, the executeQuery( ) method returns an object that stores the row(s) returned by your SELECT statement. This object is known as a JDBC result set and is of the java.sql.ResultSet class. When using a ResultSe t object to read rows from the database, there are three steps you follow:

1. Create a ResultSet object and populate it using a SELECT statement.

2. Read the column values from the ResultSet object using get methods .

3. Close the ResultSet object.

I will now walk you through an example that uses a ResultSet object to retrieve the rows from the customers table. This example will illustrate the use of these three steps.

Step 1: Create and Populate a ResultSet Object

You must first create a ResultSet object and populate it using a SELECT statement that retrieves the required rows from the database. The SELECT statement in the example is run using the Statement object named myStatement that was created earlier. The following example creates a ResultSet object named customerResultSet and populates it using a SELECT statement that retrieves the customer_id , first_name , last_name , dob , and phone columns for each row in the customers table:

 ResultSet customerResultSet = myStatement.executeQuery("SELECT customer_id, first_name, last_name, dob, phone " +  "FROM customers"); 

After this statement has been run, the ResultSet object will contain the column values for the rows retrieved by the SELECT statement. The ResultSet object may then be used to access the column values for the retrieved rows. In this example, customerResultSet will contain the five rows retrieved from the customers table. Of course, a ResultSet object may also be used to store one row when only one row is returned by your SELECT statement.

Because the execute() method accepts a Java String , you can build up your SQL statements when your program actually runs. This means that you can do some fairly powerful things in JDBC. For example, you could have the user of your program type in a string containing a WHERE clause for a SELECT statement when they run your program, or even enter the whole SELECT statement. The following example shows a WHERE clause being set and added to the query executed by another ResultSet object:

 String whereClause = "WHERE customer_id = 1"; ResultSet customerResultSet2 = myStatement.executeQuery("SELECT customer_id, first_name, last_name, dob, phone " +  "FROM customers " +  whereClause); 

You re not limited to building up SELECT statements dynamically: you can build up other SQL statements in a similar manner.

Step 2: Read the Column Values from the ResultSet Object

To read the column values for the rows stored in a ResultSet object, the ResultSet class provides a series of get methods. Before I get into the details of these get methods, you need to understand how the data types used to represent values in Oracle may be mapped to compatible Java data types.

Oracle and Java Types

A Java program uses a different set of types from the Oracle types to represent values. Fortunately, the types used by Oracle are compatible with certain Java types. This allows Java and Oracle to interchange data stored in their respective types. Table 15-3 shows one set of compatible type mappings.

Table 15-3: Compatible Type Mappings

Oracle Type	Java Type
CHAR	String
VARCHAR2	String
DATE	java.sql.Date java.sql.Time java.sql.Timestamp
INTEGER	short int long
NUMBER	float double java.math.BigDecimal

From this table, you can see that an Oracle INTEGER is compatible with a Java int . (I ll talk about the other numeric types later in this chapter in the section Handling Numbers.) This means that the customer_id column of the customers table (which is defined as an Oracle INTEGER ) may be stored in a Java int variable. Similarly, the first_name , last_name , and phon e column values may be stored in Java String variables .

The Oracle DATE type stores a year, month, day, hour , minute, and second. You may use a java.sql.Date object to store the date part of the dob column value and a java.sql.Time variable to store the time part. You may also use a java.sql.Timestamp object to store both the date and time parts of the dob column. Later in this chapter, I ll discuss the oracle.sql.DAT E type, which is an Oracle extension to the JDBC standard and provides an alternative to storing dates and times.

The customer_id , first_name , last_name , dob , and phone columns are retrieved by the SELECT statement in the previous section, and the following examples declare Java variables and objects that are compatible with those columns:

 int customerId = 0; String firstName = null; String lastName = null; java.sql.Date dob = null; String phone = null; 

The int and String types are part of the core Java language, while java.sql.Date is part of JDBC and is an extension of the core Java language. JDBC provides a number of such types that allow Java and a relational database to exchange data. However, JDBC doesn t cover types that handle all of the types used by Oracle, one example of which is the ROWID type ”you must use the oracle.sql.ROWID type to store an Oracle ROWID .

To handle all of the Oracle types, Oracle provides a number of additional types, which are defined in the oracle.sql package. Also, Oracle has a number of types that may be used as an alternative to the core Java and JDBC types, and in some cases these alternatives offer more functionality and better performance than the core Java and JDBC types. I ll talk more about the Oracle types defined in the oracle.sql package later in this chapter.

Now that you understand a little bit about compatible Java and Oracle types, let s continue with the discussion on using the get methods to read column values.

Using the Get Methods to Read Column Values

As mentioned earlier, the get methods are used to read values stored in a ResultSet object. The name of each get method is simple to understand: take the name of the Java type you want the column value to be retuned as and add the word get to the beginning. For example, use getInt( ) to read a column value as a Java int , and use getString() to read a column value as a Java String . To read the value as a java.sql.Date , you would use getDate() . Each get method accepts one parameter: an int representing the position of the column in the original SELEC T statement, or a String containing the name of the column. Let s examine some examples based on the earlier example that retrieved the columns from the customers table in the customerResultSet object.

To get the value of the customer_id column, which was the first column specified in the earlier SELECT statement, you use getInt(1) . You can also use the name of the column in the get method, so you could also use getInt(customer_id) to get the same value.

Tip  

Using the column name rather than the column position number in a get method makes your code easier to read.

To get the value of the first_name column, which was the second column specified in the earlier SELECT statement, you use getString(2) or getString(first_name) . You use similar method calls to get the last_name and phone column values because those columns are also text strings. To get the value of the dob column, you could use getDate(4) or getDate(dob) . To actually read the values stored in a ResultSet object, you must call the get methods using that ResultSet object.

Because a ResultSet object may contain more than one row, JDBC provides a method named next () that allows you to step through each row stored in a ResultSet object. You must call the next() method to access the first row in the ResultSet object, and each successive call to next() steps to the next row. When there are no more rows in the ResultSet object to read, the next() method returns the Boolean false value.

Okay, let s get back to our example: we have a ResultSet object named customerResultSe t that has five rows containing the column values retrieved from the customer_id , first_name , last_name , dob , and phone columns in the customers table. The following example shows a while loop that reads the column values from customerResultSet into the customerId , firstName , lastName , dob , and phone objects created earlier, the contents of which are displayed:

 while (customerResultSet.next()) {  customerId = customerResultSet.getInt("customer_id");  firstName = customerResultSet.getString("first_name");  lastName = customerResultSet.getString("last_name");  dob = customerResultSet.getDate("dob");  phone = customerResultSet.getString("phone");  System.out.println("customerId = " + customerId);  System.out.println("firstName = " + firstName);  System.out.println("lastName = " + lastName);  System.out.println("dob = " + dob);  System.out.println("phone = " + phone); } // end of while loop 

When there are no more rows to read from customerResultSet , the next() method returns false and the loop terminates. You ll notice that the example passes the name of the column to be read to the get methods, rather than numeric positions . Also, I ve copied the column values into Java variables and objects; for example, the value returned from customerResultSe t.getInt(customer_id) is copied to customerId . You don t have to do that copy: you could simply use the get method call whenever you need the value. However, it is generally better if you copy it to a Java variable or object because it will save time if you use that value more than once, and it makes your code more readable.

Step 3: Close the ResultSet Object

Once you ve finished with your ResultSet object, you must close that ResultSet object using the close() method. The following example closes customerResultSet :

 customerResultSet.close(); 

Note  

It is important that you remember to close your ResultSet object once you ve finished with it. This ensures that it is scheduled for garbage collection.

Now that you ve seen how to retrieve rows, I will show you how to add rows to a database table using JDBC.