Of course! In Java, when you work with a database, you often need to convert a ResultSet into a List of objects. This is a very common and essential task.
Here’s a comprehensive guide covering the best ways to do this, from the classic approach to modern, recommended practices.
The Core Problem
A ResultSet is a connected object, meaning it maintains an active connection to the database. You cannot store it directly in a List and close the connection, as the List would become useless.
The goal is to iterate through the ResultSet, extract the data from each row, map it to a Java object, and store that object in a List. This process is called Object-Relational Mapping (ORM).
Method 1: The Classic Manual Approach (JDBC while loop)
This is the fundamental way to do it. It's verbose but helps you understand the underlying mechanics.
Steps:
- Create an empty
List(e.g.,ArrayList). - Loop through the
ResultSetusingwhile (resultSet.next()). - Inside the loop, for each column, use the appropriate
getXXX()method (e.g.,getString(),getInt(),getTimestamp()). - Create a new instance of your data object (POJO - Plain Old Java Object).
- Set the values on the object using the data from the
ResultSet. - Add the populated object to your
List. - After the loop, close the
ResultSetandConnectionin afinallyblock.
Example Code:
The Data Object (POJO)
Let's assume you have a table users with columns id, name, email, and created_at.
// User.java
public class User {
private int id;
private String name;
private String email;
private Timestamp createdAt;
// Constructors, Getters, and Setters (Alt + Insert in IntelliJ / Ctrl + N in Eclipse)
public User() {}
public User(int id, String name, String email, Timestamp createdAt) {
this.id = id;
this.name = name;
this.email = email;
this.createdAt = createdAt;
}
public int getId() { return id; }
public void setId(int id) { this.id = id; }
public String getName() { return name; }
public void setName(String name) { this.name = name; }
public String getEmail() { return email; }
public void setEmail(String email) { this.email = email; }
public Timestamp getCreatedAt() { return createdAt; }
public void setCreatedAt(Timestamp createdAt) { this.createdAt = createdAt; }
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", email='" + email + '\'' +
", createdAt=" + createdAt +
'}';
}
}
The JDBC Code to Convert ResultSet to List<User>
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JdbcExample {
// Use a try-with-resources block for the connection
private static final String DB_URL = "jdbc:postgresql://localhost:5432/your_database";
private static final String USER = "your_username";
private static final String PASS = "your_password";
public List<User> getAllUsers() {
String sql = "SELECT id, name, email, created_at FROM users";
List<User> users = new ArrayList<>();
// Using try-with-resources to ensure resources are closed automatically
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
// Retrieve by column name is safer and more readable than by index
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
Timestamp createdAt = rs.getTimestamp("created_at");
// Create a new User object and add it to the list
users.add(new User(id, name, email, createdAt));
}
} catch (SQLException e) {
e.printStackTrace();
}
return users;
}
public static void main(String[] args) {
JdbcExample example = new JdbcExample();
List<User> userList = example.getAllUsers();
userList.forEach(System.out::println);
}
}
Method 2: Using a Functional Approach (Java 8+ Streams)
Since Java 8, you can make the code more concise and functional using Streams. This is a great improvement over the classic loop.
Example Code:
The User class remains the same. We just change the data access method.
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;
import java.util.stream.StreamSupport;
public class JdbcStreamExample {
// ... (DB_URL, USER, PASS constants from the previous example) ...
public List<User> getAllUsersWithStream() {
String sql = "SELECT id, name, email, created_at FROM users";
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
// Convert ResultSet to a Stream of User objects
Stream<User> userStream = StreamSupport.stream(
// Spliterators.spliteratorUnknownSize() creates a spliterator from an iterator
Spliterators.spliteratorUnknownSize(
// The ResultSet's own iterator
rs,
// Characteristics for the spliterator
Spliterator.ORDERED | Spliterator.NONNULL
),
// false means this stream is not parallel
false
).map(rs -> {
// This lambda is executed for each row in the ResultSet
try {
return new User(
rs.getInt("id"),
rs.getString("name"),
rs.getString("email"),
rs.getTimestamp("created_at")
);
} catch (SQLException e) {
// Wrap the checked SQLException in a RuntimeException to fit the functional stream
throw new RuntimeException("Error mapping row to User", e);
}
});
// Collect the Stream into a List
return userStream.collect(Collectors.toList());
} catch (SQLException e) {
e.printStackTrace();
return new ArrayList<>(); // Return an empty list on error
}
}
public static void main(String[] args) {
JdbcStreamExample example = new JdbcStreamExample();
List<User> userList = example.getAllUsersWithStream();
userList.forEach(System.out::println);
}
}
Pros of the Stream approach:
- More declarative and concise.
- Easier to chain operations (e.g.,
filter(),map()).
Cons:
- Can be less performant for very large result sets due to intermediate object creation.
- Wrapping
SQLExceptionin aRuntimeExceptionis a necessary but not ideal workaround.
Method 3: Using a JDBC Helper Library (Recommended)
Manually writing the mapping code for every query is tedious and error-prone. Helper libraries like jOOQ or Spring's JdbcTemplate are designed to solve this elegantly.
Example with Spring's JdbcTemplate
This is the most common approach in modern Spring applications. JdbcTemplate handles the resource management and simplifies data mapping.
Add Spring JDBC Dependency (Maven)
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- And your database driver, e.g., PostgreSQL -->
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
Create a RowMapper
The RowMapper is a functional interface that tells Spring how to map one row of the ResultSet to an object.
import org.springframework.jdbc.core.RowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
public class UserRowMapper implements RowMapper<User> {
@Override
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
User user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setEmail(rs.getString("email"));
user.setCreatedAt(rs.getTimestamp("created_at"));
return user;
}
}
Use JdbcTemplate to Query
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public class UserRepository {
private final JdbcTemplate jdbcTemplate;
// Autowire JdbcTemplate. Spring Boot automatically configures it.
@Autowired
public UserRepository(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public List<User> findAll() {
String sql = "SELECT id, name, email, created_at FROM users";
// query() method takes the SQL, the RowMapper, and any parameters (none here)
return jdbcTemplate.query(sql, new UserRowMapper());
}
}
This is clean, type-safe, and separates the database access
