Result Set Methods to Extract Column Values of the Current Row – Database Connectivity

Result Set Methods to Extract Column Values of the Current Row

The following get methods of the ResultSet interface can be used to extract the column values of the current row indicated by the cursor in the result set. The column index or the column label can be passed as a parameter to designate the appropriate column in the table. The CallableStatement interface provides analogous getXXX() methods to retrieve values of OUT parameters (p. 1530).

Selected get methods from the ResultSet interface to extract column values of the current row are shown below:

Click here to view code image

String getString(int columnIndex)
String getString(String columnLabel)

Retrieve the value of the column as a String.

Click here to view code image

int getBoolean(int columnIndex)
int getBoolean(String columnLabel)

Retrieve the value of the column as a boolean.

Click here to view code image

int getInt(int columnIndex)
int getInt(String columnLabel)

Retrieve the value of the column as an int.

Click here to view code image

double getLong(int columnIndex)
double getLong(String columnLabel)

Retrieve the value of the column as a long.

Click here to view code image

double getDouble(int columnIndex)
double getDouble(String columnLabel)

Retrieve the value of the column as a double.

Click here to view code image

BigDecimal getBigDecimal(int columnIndex)
BigDecimal getBigDecimal(String columnLabel)

Retrieve the value of the column as a BigDecimal.

Click here to view code image

Date getDate(int columnIndex)
Date getDate(int columnLabel)

Retrieve the value of the column as a Date.

Click here to view code image

<T> T getObject(int columnIndex, Class<T> type)
<T> T getObject(int columnLabel, Class<T> type)

Retrieve the value of the column as an object of the indicated Java type.

Example 24.5 Processing a ResultSet

Click here to view code image

package dbDemo;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.time.Duration;
import static java.lang.System.out;
public class ResultSetProcessing {
  public static void main(String[] args) {
    final String jdbcURL = “jdbc:derby:musicDB”;
    final String sql = “select * from compositions where duration > ?”;    // (1)
    try (var connection = DriverManager.getConnection(jdbcURL);            // (2)
        var pStatement = connection.prepareStatement(sql);) {              // (3)
      pStatement.setInt(1, 0);                                             // (4)
      var resultSet = pStatement.executeQuery();
      try (resultSet) {                                                    // (5)
        while (resultSet.next()) {                                         // (6)
          String isrc = resultSet.getString(1);                            // (7)
          String title = resultSet.getObject(2, String.class);             // (8)
          int duration = resultSet.getInt(“duration”);                     // (9)
          out.println(“[” + isrc + “, ” + title + “, ” + duration + “]”);  // (10)
        }
      } // Closes the result set.
    } catch (SQLException e) {                                             // (11)
      e.printStackTrace();
    } // Closes the prepared statement and the connection.
  }
}

Probable output from the program:

Click here to view code image

[ushm91736697, Vacation, 231]
[ushm91736698, Rage, 308]
[ushm91736699, Why Don’t, 178]

Example 24.5 illustrates the main steps in the interaction between a Java application and a relational database. The example uses the musicDB database that has been used so far to illustrate salient features of programming this interaction. The numbered comments below correspond to the numbered code lines in Example 24.5.

1. Define the SELECT statement to execute, having one marker parameter for the second operand of the boolean expression.
2. Use the try-with-resources statement to declare the resources and to close the connection and the prepared statement afterward. Obtain the connection to the database.
3. Create the prepared statement in the header of the try-with-resources statement.
4. Substitute the marker parameter with a value in the SELECT statement at (1).
5. Use the nested try-with-resources statement to close the result set after processing it. The prepared statement is executed in the header of the try-with-resources statement, returning a result set. Any exception thrown will be propagated to the catch clause at (11) of the outer try-with-resources statement.
6. The while loop processes the rows in the result set.
7. Extract the column values of the current row denoted by the result set cursor by calling the appropriate get methods of the result set. Extract a string (isrc) from column 1 using the getString() method.
8. Extract a string (title) from column 2 using the getObject() method.
9. Extract an int value (duration) by column name using the getInt() method.
10. Print the column values of the current row.
11. Use the catch clause of the outer try-with-resources statement to catch any SQLException.