How to Convert JDBC ResultSet to Java Objects: Efficient Mapping Techniques

Why Convert ResultSet to Objects?

• Clean Code: Avoid repetitive manual mapping.
• Scalability: Handle large datasets efficiently.
• Type Safety: Leverage Java’s object-oriented design.

Method 1: Manual Mapping (Not Recommended for Large Tables)

For small tables, manually map each column to object fields:

List<Customer> customers = new ArrayList<>();  
while (resultSet.next()) {  
    Customer customer = new Customer();  
    customer.setId(resultSet.getLong("id"));  
    customer.setName(resultSet.getString("name"));  
    // Repeat for all columns...  
    customers.add(customer);  
}  

Drawbacks:

• Error-prone for hundreds of columns.
• High maintenance effort.

Method 2: Using Spring’s JdbcTemplate

Even without DataSource configuration, JdbcTemplate can be used with a one-off connection:

import org.springframework.jdbc.core.JdbcTemplate;  
import org.springframework.jdbc.core.RowMapper;  

// Create JdbcTemplate with existing Connection  
JdbcTemplate jdbcTemplate = new JdbcTemplate(new SingleConnectionDataSource(con, true));  

// Define RowMapper to map rows to Customer objects  
RowMapper<Customer> rowMapper = (rs, rowNum) -> {  
    Customer customer = new Customer();  
    customer.setId(rs.getLong("id"));  
    customer.setName(rs.getString("name"));  
    return customer;  
};  

// Execute query and map results  
List<Customer> customers = jdbcTemplate.query("SELECT * FROM customers", rowMapper);  

Pros:

• Reuses Spring’s battle-tested utilities.
• Integrates seamlessly with Spring Boot projects.

Method 3: Apache DbUtils (Lightweight Solution)

Apache Commons DbUtils simplifies ResultSet mapping using BeanListHandler:

1. Add Dependency

<dependency>  
    <groupId>commons-dbutils</groupId>  
    <artifactId>commons-dbutils</artifactId>  
    <version>1.7</version>  
</dependency>  

2. Map ResultSet to Objects

import org.apache.commons.dbutils.QueryRunner;  
import org.apache.commons.dbutils.handlers.BeanListHandler;  

QueryRunner queryRunner = new QueryRunner();  
BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);  

List<Customer> customers = queryRunner.query(con, "SELECT * FROM customers", handler);  

How It Works:

• Automatically maps column names to object fields (case-insensitive).
• Requires column names to match field names (e.g., customer_name → customerName).

Method 4: ModelMapper (Reflection-Based Mapping)

ModelMapper allows dynamic mapping from ResultSet to POJOs:

1. Add Dependency

<dependency>  
    <groupId>org.modelmapper</groupId>  
    <artifactId>modelmapper</artifactId>  
    <version>3.2.0</version>  
</dependency>  

2. Convert ResultSet to List

import org.modelmapper.ModelMapper;  
import org.modelmapper.jdbc.ResultSetConverter;  

ModelMapper modelMapper = new ModelMapper();  
ResultSetConverter converter = new ResultSetConverter();  

List<Customer> customers = converter.convert(resultSet, modelMapper.getTypeFactory()  
    .constructCollectionType(List.class, Customer.class));  

Pros:

• Handles complex mappings via configuration.
• Eliminates boilerplate code.

Method 5: Java 16+ Records (Immutable Objects)

For simple DTOs, Java Records can be used with reflection-based tools:

public record Customer(Long id, String name) {}  

// Using DbUtils with Java Records  
BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);  
List<Customer> customers = queryRunner.query(con, "SELECT * FROM customers", handler);  

Best Practices for Large Tables

1. Batch Processing: Optimize memory usage with FETCH_SIZE: statement.setFetchSize(100);
2. Lazy Loading: Process rows incrementally rather than loading all at once.
3. Column Aliases: Ensure SQL aliases match object field names (e.g., first_name AS firstName).

FAQ

Q: How to handle column name mismatches?

• Use annotations like @Column(name = "full_name") with Hibernate or ModelMapper.

Q: What if I need custom type conversions?

• Extend RowMapper or use ResultSet#getObject() with Java 8+ type inference.

Q: Are these methods compatible with Java 21?

• Yes! All mentioned libraries support Java 21.

Conclusion

Converting ResultSet to Java objects doesn’t have to involve manual mapping for large tables. Choose the method that suits your project’s needs:

• Spring’s JdbcTemplate for Spring Boot integration.
• Apache DbUtils for lightweight automation.
• ModelMapper for complex object-relational mappings.

For scheduled jobs (like daily Snowflake imports), DbUtils or JdbcTemplate provide the best balance of simplicity and efficiency.

Further Reading:

• Apache DbUtils Documentation
• Spring JdbcTemplate Guide
• ModelMapper JDBC Integration

Need to optimize your JDBC workflow? Explore our Java Database Tutorials for more tips!