Mapping Result Sets to Lists with DBUtils' BeanListHandler

In this post, I am going to explain how to map result set to a list of objects.

 

To demonstrate the example, I am using below employee table.

mysql> describe employee;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.18 sec)

Let’s define a POJO, where the field names are matched to column names of employee table.

 

public class Employee {

  private Integer id;
  private String name;
}

Define an instance of BeanListHandler. BeanListHandler converts a ResultSet into a List of beans

 

private static ResultSetHandler<List<Employee>> empHandler = new BeanListHandler<Employee>(Employee.class);

 

Execute the query using the empHandler defined above.

List<Employee> emps = queryRunner.query(connection, query, empHandler);

 

Find the below working application.

 

Employee.java

package com.sample.app.model;

public class Employee {

  private Integer id;
  private String name;

  public Employee() {
  }

  public Employee(Integer id, String name) {
    this.id = id;
    this.name = name;
  }

  public Integer getId() {
    return id;
  }

  public String getName() {
    return name;
  }

  public void setId(Integer id) {
    this.id = id;
  }

  public void setName(String name) {
    this.name = name;
  }

  @Override
  public String toString() {
    return "Employee [id=" + id + ", name=" + name + "]";
  }

}

MapResultToPojoDemo.java

package com.sample.app;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

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

import com.sample.app.model.Employee;

public class MapResultToPojoDemo {

  private static ResultSetHandler<List<Employee>> empHandler = new BeanListHandler<Employee>(Employee.class);

  public static void main(String[] args) {
    // Database connection parameters
    String url = "jdbc:mysql://localhost:3306/sample";
    String username = "root";
    String password = "tiger";

    // Create a QueryRunner
    QueryRunner queryRunner = new QueryRunner();

    try (Connection connection = DriverManager.getConnection(url, username, password)) {

      // Define a SQL query
      String query = "SELECT * FROM employee";

      // Execute the query and retrieve the result
      List<Employee> emps = queryRunner.query(connection, query, empHandler);

      emps.forEach(System.out::println);

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

Output

Employee [id=1, name=Hari Krishna]
Employee [id=3, name=Sudhir]

Previous                                                    Next                                                    Home