On this page
Handling Result Sets
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. Returnsfalse
if there are no more rows.previous()
: Moves the cursor to the previous row (requiresResultSet.TYPE_SCROLL_INSENSITIVE
).first()
: Moves the cursor to the first row (requiresResultSet.TYPE_SCROLL_INSENSITIVE
).last()
: Moves the cursor to the last row (requiresResultSet.TYPE_SCROLL_INSENSITIVE
).absolute(int row)
: Moves the cursor to a specific row (requiresResultSet.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()
, andabsolute()
methods. - Retrieving Data: Use methods like
getInt()
,getString()
,getDate()
, etc. - Handling NULL: Check for NULL values using
wasNull()
. - Resource Management: Always close
ResultSet
,Statement
, andConnection
objects.