Processing JDBC ResultSet

JDBC (Java Database Connectivity) is the standard API in Java that connects with relational databases. The ResultSet is a key component of JDBC, allowing developers to retrieve and process query results. Optimizing the handling of ResultSet can improve performance and ensure efficient resource management.

Steps to Process JDBC ResultSet

1. Establish a Database Connection

Before executing a query, a connection to the database must be established using DriverManager or a DataSource.

Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbname", "user", "password");

2. Create a Statement Object

A Statement, PreparedStatement, or CallableStatement is used to execute SQL queries.

Statement statement = connection.createStatement();

3. Execute a Query

Use the executeQuery method to obtain a ResultSet.

ResultSet resultSet = statement.executeQuery("SELECT * FROM employees");

4. Process the ResultSet

Iterate through the ResultSet using while(resultSet.next()) and retrieve data using getter methods.

while (resultSet.next()) {
    int id = resultSet.getInt("id");
    String name = resultSet.getString("name");
    double salary = resultSet.getDouble("salary");
    System.out.println("ID: " + id + ", Name: " + name + ", Salary: " + salary);
}

5. Close Resources

Always close the ResultSet, Statement, and Connection to free up database resources.

resultSet.close();
statement.close();
connection.close();

Example: Processing a JDBC ResultSet

import java.sql.*;

public class JDBCResultSetExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/yourdatabase";
        String user = "yourusername";
        String password = "yourpassword";

        String query = "SELECT id, name, email FROM users";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement stmt = conn.prepareStatement(query);
             ResultSet rs = stmt.executeQuery()) {

            // Process the ResultSet
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");

                System.out.println("ID: " + id + ", Name: " + name + ", Email: " + email);
            }

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

Key Points

1. Try-with-Resources

   • The try block ensures Connection, PreparedStatement, and ResultSet are closed automatically.

2. Using PreparedStatement instead of Statement

   • Prevents SQL injection and improves performance.

3. Processing ResultSet using rs.next()

   • Moves the cursor forward and retrieves row data.

4. Handling SQL Exceptions

   • Wrap the JDBC logic in a try-catch block to handle database errors properly.

In Java 21, you can obtain a ResultSet by executing an SQL query using different types of statements in JDBC:

1. Statement - Used for simple SQL queries without parameters.
2. PreparedStatement - Used for queries with parameters to prevent SQL injection and improve performance.
3. CallableStatement - Used to execute stored procedures from the database.

Best Practices for Handling ResultSet

1. Use Try-With-Resources for Auto-Close

Java 7 introduced try-with-resources to automatically close JDBC objects.

try (Connection conn = DriverManager.getConnection(url, user, password);
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT * FROM employees")) {
    while (rs.next()) {
        System.out.println(rs.getString("name"));
    }
} catch (SQLException e) {
    e.printStackTrace();
}

2. Use Column Index Instead of Column Name

Fetching data using column index is faster than using column names.

int id = resultSet.getInt(1);
String name = resultSet.getString(2);

For historical reasons, columns of java.sql.ResultSet and java.sql.PreparedStatement are numbered starting with 1, rather than with 0, and accessing column 0 is a common error in JDBC programming. Also in this code snipets is used columnLabel – the label for the column specified with the SQL AS clause. If the SQL AS clause was not specified, then the label is the name of the column is better for beginers.

3. Optimize Fetch Size

Setting an appropriate fetch size can improve performance, especially for large datasets.

statement.setFetchSize(100);

4. Use Scrollable and Updatable ResultSet When Needed

By default, ResultSet is forward-only and read-only. To enable scrolling and updating, use:

Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT * FROM employees");

5. Avoid Unnecessary Data Retrieval

Retrieve only the required columns instead of using SELECT * to reduce memory usage.

ResultSet rs = stmt.executeQuery("SELECT id, name FROM employees");

Efficiently processing JDBC ResultSet involves best practices such as proper resource management, optimized query execution, and performance tuning. By following these guidelines, you can improve the efficiency and scalability of your database operations in Java applications.

Using Statement

• Suitable for simple queries without parameters.
• Not recommended for dynamic queries (security risk: SQL Injection).

package com.celac.jdbc.app.dao.impl.statementinterface;

import com.celac.jdbc.app.dao.UserDao;
import com.celac.jdbc.app.dao.impl.AbstractDAO;
import com.celac.jdbc.app.entities.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl extends AbstractDAO<User> implements UserDao {

  public UserDaoImpl(Connection dataSourcesConnection) {
    super(dataSourcesConnection);
  }

  @Override
  public User findOne(Long id) {
    String sql = "select u.* from users u where u.id = " + id;
    User user = null;

    try (Statement statement = getDataSourcesConnection().createStatement();
         ResultSet result = statement.executeQuery(sql)){
      while (result.next()) {
        user =
            new User(
                result.getLong("id"),
                result.getString("user_name"),
                result.getString("first_name"),
                result.getString("last_name"));
      }

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

    return user;
  }

  @Override
  public List<User> selectAllPageable(int fromRow, int rows) {
    List<User> users = new ArrayList<>(rows);
    String sql = "SELECT * FROM users u  LIMIT" + fromRow + "," + rows;
    try (Statement statement = getDataSourcesConnection().createStatement();
         ResultSet result = statement.executeQuery(sql)){     
      while (result.next()) {
        users.add(
            new User(
                result.getLong("id"),
                result.getString("user_name"),
                result.getString("first_name"),
                result.getString("last_name")));
      }

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

  @Override
  public int[] batchInsert(List<User> userList) {
    return new int[0];
  }

  @Override
  public int[] batchUpdate(List<User> userList) {
    return new int[0];
  }
}

✅ Use Case: Running simple SELECT queries without parameters.
❌ Disadvantage: Vulnerable to SQL Injection if used with user input.

2. Using PreparedStatement

• Prevents SQL injection and improves performance by precompiling queries.

public class UserDaoImpl extends AbstractDAO<User> implements UserDao {
  private final static Logger logger = LogManager.getLogger(UserDaoImpl.class);
  public UserDaoImpl(Connection dataSourcesConnection) {
    super(dataSourcesConnection);
  }

  @Override
  public User findOne(Long id) {
    String sql = "select u.* from users u where u.id = ?";
    User user = null;

    try (PreparedStatement statement = getDataSourcesConnection().prepareStatement(sql)) {
      statement.setLong(1, id);
      try (ResultSet result = statement.executeQuery()) {
        while (result.next()) {
          user =
              new User(
                  result.getLong("id"),
                  result.getString("user_name"),
                  result.getString("first_name"),
                  result.getString("last_name"));
        }
      }
    } catch (SQLException e) {
      logger.error(e);
    }

    return user;
  }

  @Override
  public List<User> selectAllPageable(int fromRow, int rows) {
    List<User> users = new ArrayList<>(rows);
    String sql = "SELECT * FROM users u  LIMIT ?, ?";
    try ( PreparedStatement statement = getDataSourcesConnection().prepareStatement(sql)) {
      statement.setInt(1, fromRow);
      statement.setInt(2, rows);

     try(ResultSet result = statement.executeQuery()){
       while (result.next()) {
         users.add(
                 new User(
                         result.getLong("id"),
                         result.getString("user_name"),
                         result.getString("first_name"),
                         result.getString("last_name")));
       }

     }

    } catch (SQLException e) {
      logger.error(e);
    }
    return users;
  }
}

Using CallableStatement (Stored Procedure Execution)

• Used for executing stored procedures.

Example: Stored Procedure in MySQL

DELIMITER //
CREATE PROCEDURE GetUserById(IN userId INT)
BEGIN
    SELECT id, name, email FROM users WHERE id = userId;
END //
DELIMITER ;

Java Code to Call Stored Procedure

import java.sql.*;

public class CallableStatementExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/yourdatabase";
        String user = "yourusername";
        String password = "yourpassword";

        String storedProc = "{CALL GetUserById(?)}";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             CallableStatement cstmt = conn.prepareCall(storedProc)) {

            cstmt.setInt(1, 1); // Setting parameter value

            try (ResultSet rs = cstmt.executeQuery()) {
                while (rs.next()) {
                    System.out.println("ID: " + rs.getInt("id") +
                                       ", Name: " + rs.getString("name") +
                                       ", Email: " + rs.getString("email"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

✅ Use Case: When working with stored procedures in databases.
✅ Advantage: Useful for encapsulating logic in the database for better performance and maintainability.

Comparison Table

Processing JDBC ResultSet on external row mapper 

When our web application starts looking like a mature soft with a lot of business logic there, the DAO layer is growing day by day, and the implementations of DAO interfaces start to have thousands of code lines - this becomes a nightmare for supporting code base, for avoid it, I can recommend moving ResultSet parsing/mapping in separate classes,  for  that also will need to design small  framework 

1. Create a generic interface ResultSetRowMapper

package com.celac.jdbc.app.sql;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Result Set row mapper
 * @author scelac
 */
public interface ResultSetRowMapper<T> {
    T mapRow(ResultSet rs) throws SQLException;
}

2. Create a generic interface ResultSetProcessor

package com.celac.jdbc.app.sql;

import java.sql.SQLException;

/**
 * @author scelac
 */
public interface ResultSetProcessor<T> {
    T process() throws SQLException;
}

3. Create an implementation of  ResultSetProcessor  ResultSetProcessorImpl

import com.celac.jdbc.app.sql.ResultSetProcessor;
import com.celac.jdbc.app.sql.ResultSetRowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *  This should be used when need to map list of row response to list of object
 * @author scelac
 */
public class ResultSetProcessorImpl<T> implements ResultSetProcessor {
    private final ResultSet resultSet;
    private final ResultSetRowMapper<T> rowMapper;

    public ResultSetProcessorImpl(ResultSet resultSet, ResultSetRowMapper<T> rowMapper) {
        this.resultSet = resultSet;
        this.rowMapper = rowMapper;
    }
    public T process() throws SQLException {
        return rowMapper.mapRow(resultSet);
    }
}

4. Create an implementation of  ResultSetRowMapper

 This class will code logic about how to map exact object from the JDBC ResultSet

import com.celac.jdbc.app.entities.Task;
import com.celac.jdbc.app.entities.enums.TaskStatus;
import com.celac.jdbc.app.sql.ResultSetRowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author scelac
 */

public class TaskMapper implements ResultSetRowMapper<Task> {

  @Override
  public Task mapRow(ResultSet rs) throws SQLException {
    Task task = null;
    while (rs.next()) {
      task =
          new Task(
              rs.getLong("id"),
              rs.getString("title"),
              rs.getString("description"),
              TaskStatus.fromValue(rs.getString("status")));
    }
    return task;
  }
}

5 Use this in DAO impl class

import com.celac.jdbc.app.dao.TasksDao;
import com.celac.jdbc.app.dao.mappers.TaskMapper;
import com.celac.jdbc.app.entities.Task;
import com.celac.jdbc.app.entities.enums.TaskStatus;

import com.celac.jdbc.app.sql.impl.ResultSetProcessorImpl;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

/**
 * @author scelac
 */
public class TasksDaoImpl extends AbstractDAO<Task> implements TasksDao {
  private final static Logger logger = LogManager.getLogger(TasksDaoImpl.class);
  public TasksDaoImpl(Connection dataSourcesConnection) {
    super(dataSourcesConnection);
  }
  @Override
  public Task findOne(Long id) {
    String sql = "select t.* from tasks t where u.id = ?";
     try (Statement statement = getDataSourcesConnection().createStatement();
          ResultSet result = statement.executeQuery(sql)){
       return new ResultSetProcessorImpl<>(result, new TaskMapper()).process();
     }  catch (SQLException e) {
       logger.error(e.getMessage(), e);
       return null;
     }
  }
  @Override
  public List<Task> getUsersTask(Long userId) {
    return null;
  }

  @Override
  public List<Task> getUsersTaskByStatus(Long userId, TaskStatus status) {
    return null;
  }
}