ResultSet and ResultSetMetaData in Java
Introduction
In Java, the ResultSet and ResultSetMetaData classes are part of the JDBC API used to work with query results retrieved from a database. The ResultSet contains the data returned by executing a SELECT query, while the ResultSetMetaData provides information about the structure of the result set.
ResultSet
The ResultSet object is used to store the data returned by a query. It represents a table of data that can be accessed row by row. You can retrieve data from the result set using various methods like getInt(), getString(), getDouble(), etc.
Step 1: Retrieving Data from ResultSet
The first step in using a ResultSet is to execute a SELECT query and get the results. We can then use the next() method to iterate over the rows and retrieve the values.
import java.sql.*;
public class ResultSetExample {
public static void main(String[] args) {
try {
// Load the MySQL JDBC driver
Class.forName("com.mysql.cj.jdbc.Driver");
// Establish the connection
Connection connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/testdb", "root", "password");
// Create the SELECT query
String selectQuery = "SELECT id, name, department FROM employees";
Statement statement = connection.createStatement();
// Execute the query and get the result
ResultSet resultSet = statement.executeQuery(selectQuery);
// Process the result set
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String department = resultSet.getString("department");
System.out.println("ID: " + id + ", Name: " + name + ", Department: " + department);
}
// Close the result set, statement, and connection
resultSet.close();
statement.close();
connection.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}
In this example:
- We execute a SELECT query to retrieve the
id,name, anddepartmentof employees from theemployeestable. - The
next()method is used to iterate over each row in the result set. - The
getInt(),getString()methods are used to retrieve the column values for each row.
ResultSetMetaData
The ResultSetMetaData class provides information about the structure of the result set, such as the number of columns, column names, data types, etc. This class is useful when you need to dynamically handle the result set without knowing the column names or types in advance.
Step 1: Getting ResultSetMetaData
You can get the ResultSetMetaData object by calling the getMetaData() method on the ResultSet.
import java.sql.*;
public class ResultSetMetaDataExample {
public static void main(String[] args) {
try {
// Load the MySQL JDBC driver
Class.forName("com.mysql.cj.jdbc.Driver");
// Establish the connection
Connection connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/testdb", "root", "password");
// Create the SELECT query
String selectQuery = "SELECT id, name, department FROM employees";
Statement statement = connection.createStatement();
// Execute the query and get the result
ResultSet resultSet = statement.executeQuery(selectQuery);
// Get the ResultSetMetaData
ResultSetMetaData metaData = resultSet.getMetaData();
// Get the number of columns
int columnCount = metaData.getColumnCount();
System.out.println("Number of columns: " + columnCount);
// Get the column names and types
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnName(i);
String columnType = metaData.getColumnTypeName(i);
System.out.println("Column " + i + ": " + columnName + ", Type: " + columnType);
}
// Close the result set, statement, and connection
resultSet.close();
statement.close();
connection.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}
In this example:
- We use
getMetaData()to retrieve theResultSetMetaDataobject. - The number of columns is retrieved using
getColumnCount(). - We iterate over each column and retrieve its name and data type using
getColumnName()andgetColumnTypeName()methods.
Step 2: Dynamically Handling Data with ResultSetMetaData
With ResultSetMetaData, you can dynamically process the columns without knowing the exact names or types in advance.
import java.sql.*;
public class DynamicResultSetExample {
public static void main(String[] args) {
try {
// Load the MySQL JDBC driver
Class.forName("com.mysql.cj.jdbc.Driver");
// Establish the connection
Connection connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/testdb", "root", "password");
// Create the SELECT query
String selectQuery = "SELECT id, name, department FROM employees";
Statement statement = connection.createStatement();
// Execute the query and get the result
ResultSet resultSet = statement.executeQuery(selectQuery);
// Get the ResultSetMetaData
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
// Process the result set dynamically
while (resultSet.next()) {
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnName(i);
String columnValue = resultSet.getString(i);
System.out.println(columnName + ": " + columnValue);
}
System.out.println("----------");
}
// Close the result set, statement, and connection
resultSet.close();
statement.close();
connection.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}
In this example:
- We use the
ResultSetMetaDatato get the column names dynamically. - We retrieve each column's value using the column index in the
getString()method. - This allows us to process the result set without hardcoding column names or types.
Conclusion
In this tutorial, we have covered the following:
ResultSetis used to store the data retrieved from a SELECT query, and we can retrieve data using methods likegetInt(),getString(), etc.ResultSetMetaDataprovides information about the structure of the result set, such as the number of columns, column names, and data types.- You can use
ResultSetMetaDatato dynamically handle the result set, making your code more flexible and generic.