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, and department of employees from the employees table.
• 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 the ResultSetMetaData object.
• The number of columns is retrieved using getColumnCount().
• We iterate over each column and retrieve its name and data type using getColumnName() and getColumnTypeName() 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 ResultSetMetaData to 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:

• ResultSet is used to store the data retrieved from a SELECT query, and we can retrieve data using methods like getInt(), getString(), etc.
• ResultSetMetaData provides information about the structure of the result set, such as the number of columns, column names, and data types.
• You can use ResultSetMetaData to dynamically handle the result set, making your code more flexible and generic.