Handling ResultSet objects in JDBC involves processing the results returned by SQL queries. Here’s a comprehensive guide on how to handle ResultSet for different types of queries.

1. Retrieving Data from a ResultSet

After executing a SELECT query using Statement or PreparedStatement, you get a ResultSet object that contains the data returned by the query.

Basic ResultSet Handling 

  import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ResultSetExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/dbname"; // Update with your database URL
        String user = "username"; // Update with your database username
        String password = "password"; // Update with your database password
        
        try (Connection connection = DriverManager.getConnection(url, user, password);
             Statement statement = connection.createStatement()) {

            String query = "SELECT id, name FROM tablename"; // Replace with your SQL query
            ResultSet resultSet = statement.executeQuery(query);
            
            while (resultSet.next()) {
                // Retrieve data by column name or index
                int id = resultSet.getInt("id"); // Replace "id" with your column name
                String name = resultSet.getString("name"); // Replace "name" with your column name
                
                // Process the retrieved data
                System.out.println("ID: " + id + ", Name: " + name);
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

2. Navigating the ResultSet

  • next(): Moves the cursor to the next row. Returns false if there are no more rows.
  • previous(): Moves the cursor to the previous row (requires ResultSet.TYPE_SCROLL_INSENSITIVE).
  • first(): Moves the cursor to the first row (requires ResultSet.TYPE_SCROLL_INSENSITIVE).
  • last(): Moves the cursor to the last row (requires ResultSet.TYPE_SCROLL_INSENSITIVE).
  • absolute(int row): Moves the cursor to a specific row (requires ResultSet.TYPE_SCROLL_INSENSITIVE).

Example of using cursor navigation:

  resultSet.beforeFirst(); // Move to the beginning of the result set
while (resultSet.next()) {
    // Process each row
}
resultSet.last(); // Move to the last row
int rowCount = resultSet.getRow(); // Get the number of rows

3. Handling Different Data Types

ResultSet provides methods to retrieve data by column name or index. Here are some common methods:

  • Numeric Data: getInt(), getDouble(), getFloat(), getLong(), etc.
  • String Data: getString()
  • Date and Time: getDate(), getTime(), getTimestamp()

Example:

  int id = resultSet.getInt("id");
String name = resultSet.getString("name");
java.sql.Date date = resultSet.getDate("date_column"); // For SQL DATE type

4. Handling NULL Values

Use ResultSet’s wasNull() method to check if the last retrieved value was NULL.

  String name = resultSet.getString("name");
if (resultSet.wasNull()) {
    name = "N/A"; // Handle NULL value
}

5. Closing the ResultSet

Always close the ResultSet, Statement, and Connection objects to free up resources. Use try-with-resources or manually close them in a finally block.

Example:

  try (Connection connection = DriverManager.getConnection(url, user, password);
     Statement statement = connection.createStatement();
     ResultSet resultSet = statement.executeQuery("SELECT * FROM tablename")) {

    while (resultSet.next()) {
        // Process results
    }

} catch (SQLException e) {
    e.printStackTrace();
}

Summary

  • Navigating: Use next(), previous(), first(), last(), and absolute() methods.
  • Retrieving Data: Use methods like getInt(), getString(), getDate(), etc.
  • Handling NULL: Check for NULL values using wasNull().
  • Resource Management: Always close ResultSet, Statement, and Connection objects.