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

Enhancing Database Efficiency with Asynchronous Queries Using DBUtils

To handle long running operations, we can consider utilizing the AsyncQueryRunner for executing tasks asynchronously. The AsyncQueryRunner class has the same methods as the QueryRunner calls; however, the methods return a Callable.

Define AsyncQueryRunner.
ExecutorService executorService = new ThreadPoolExecutor(5, 5, 0L, TimeUnit.MILLISECONDS,new LinkedBlockingQueue<Runnable>());
AsyncQueryRunner queryRunner = new AsyncQueryRunner(executorService);

Execute a query and get the result as a Future.

Future<List<Object[]>> resultFuture = queryRunner.query(connection, query, handler); 

Find the below working application.

 

RunQueriesAsynchronously.java

package com.sample.app;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.Future;

import org.apache.commons.dbutils.AsyncQueryRunner;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import java.util.concurrent.ExecutionException;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;

public class RunQueriesAsynchronously {

  private static ResultSetHandler<List<Object[]>> handler = new ResultSetHandler<List<Object[]>>() {

    public List<Object[]> handle(ResultSet rs) throws SQLException {
      // Initialize the list to hold rows
      List<Object[]> rows = new ArrayList<>();

      // Get the metadata of the result set
      ResultSetMetaData meta = rs.getMetaData();
      int columnCount = meta.getColumnCount();

      // Iterate through the result set
      while (rs.next()) {
        // Create an array to hold column values for the current row
        Object[] result = new Object[columnCount];

        // Populate the array with column values
        for (int i = 0; i < columnCount; i++) {
          result[i] = rs.getObject(i + 1);
        }

        // Add the row to the list
        rows.add(result);
      }

      // Return the list of rows
      return rows;
    }
  };

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

    // Create a AsyncQueryRunner
    ExecutorService executorService = new ThreadPoolExecutor(5, 5, 0L, TimeUnit.MILLISECONDS,
        new LinkedBlockingQueue<Runnable>());
    AsyncQueryRunner queryRunner = new AsyncQueryRunner(executorService);

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

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

      // Execute the query and retrieve the result
      Future<List<Object[]>> resultFuture = queryRunner.query(connection, query, handler);

      List<Object[]> result = resultFuture.get();

      for (Object[] row : result) {
        for (Object column : row) {
          System.out.print(column + " ");
        }
        System.out.println();
      }

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

Output

1 Hari Krishna 
3 Sudhir 

 

Previous                                                    Next                                                    Home

Integrating Apache Commons DBUtils with DataSource

QueryRunner class can take a DataSource object, and use this while executing the queries.

Example

private static DataSource getDataSource() {
    BasicDataSource dataSource = new BasicDataSource();
    dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql://localhost:3306/sample");
    dataSource.setUsername("root");
    dataSource.setPassword("tiger");
    return dataSource;
}

QueryRunner queryRunner = new QueryRunner(datasource);

Above code snippet offers a streamlined approach to configuring database connectivity in Java applications. It defines a getDataSource() method that returns a DataSource object for connecting to a MySQL database. Additionally, the QueryRunner class is initialized with the DataSource obtained from getDataSource(), facilitating the execution of SQL queries with ease.

 

Find the below working application.

 

DataSourceIntegration.java

package com.sample.app;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

public class DataSourceIntegration {

	private static DataSource getDataSource() {
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/sample");
        dataSource.setUsername("root");
        dataSource.setPassword("tiger");
        return dataSource;
    }
	
	private static ResultSetHandler<List<Object[]>> handler = new ResultSetHandler<List<Object[]>>() {

		public List<Object[]> handle(ResultSet rs) throws SQLException {
			// Initialize the list to hold rows
			List<Object[]> rows = new ArrayList<>();

			// Get the metadata of the result set
			ResultSetMetaData meta = rs.getMetaData();
			int columnCount = meta.getColumnCount();

			// Iterate through the result set
			while (rs.next()) {
				// Create an array to hold column values for the current row
				Object[] result = new Object[columnCount];

				// Populate the array with column values
				for (int i = 0; i < columnCount; i++) {
					result[i] = rs.getObject(i + 1);
				}

				// Add the row to the list
				rows.add(result);
			}

			// Return the list of rows
			return rows;
		}
	};
	
	public static void main(String[] args) {
		DataSource datasource = getDataSource();
		
		// Create a QueryRunner
		QueryRunner queryRunner = new QueryRunner(datasource);

		try {

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

			// Execute the query and retrieve the result
			List<Object[]> result = queryRunner.query(query, handler);

			for (Object[] row : result) {
				for (Object column : row) {
					System.out.print(column + " ");
				}
				System.out.println();
			}

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

Output

1 Hari Krishna 
3 Sudhir

 

 

Previous                                                    Next                                                    Home

Getting Started with Apache Commons DBUtils: A Hello World Application

There are two core classes in DBUtils.

a.   QueryRunner: Runs SQL queries using pluggable approaches for managing ResultSets. This class ensures thread safety.

b.   ResultSetHandler: Responsible to convert ResultSets into other objects.

 

Let’s define a ResultHandler that convert the ResultSet to a List of recrods.

private static ResultSetHandler<List<Object[]>> handler = new ResultSetHandler<List<Object[]>>() {

	public List<Object[]> handle(ResultSet rs) throws SQLException {
		// Initialize the list to hold rows
		List<Object[]> rows = new ArrayList<>();

		// Get the metadata of the result set
		ResultSetMetaData meta = rs.getMetaData();
		int columnCount = meta.getColumnCount();

		// Iterate through the result set
		while (rs.next()) {
			// Create an array to hold column values for the current row
			Object[] result = new Object[columnCount];

			// Populate the array with column values
			for (int i = 0; i < columnCount; i++) {
				result[i] = rs.getObject(i + 1);
			}

			// Add the row to the list
			rows.add(result);
		}

		// Return the list of rows
		return rows;
	}
};

Above snippet defined a ResultSetHandler instance named handler that's capable of handling ResultSet objects and converting them into a list of arrays of objects. This handler is defined using an anonymous class implementation of the ResultSetHandler interface.

 

Use Query runner to execute a query

List<Object[]> result = queryRunner.query(connection, query, handler)

Above code executes a database query, processes the results using the specified ResultSetHandler, and stores the processed results in the result variable for further use in the application.

 

Find the below working application.

 

HelloWorld.java

package com.sample.app;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

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

public class HelloWorld {

	private static ResultSetHandler<List<Object[]>> handler = new ResultSetHandler<List<Object[]>>() {

		public List<Object[]> handle(ResultSet rs) throws SQLException {
			// Initialize the list to hold rows
			List<Object[]> rows = new ArrayList<>();

			// Get the metadata of the result set
			ResultSetMetaData meta = rs.getMetaData();
			int columnCount = meta.getColumnCount();

			// Iterate through the result set
			while (rs.next()) {
				// Create an array to hold column values for the current row
				Object[] result = new Object[columnCount];

				// Populate the array with column values
				for (int i = 0; i < columnCount; i++) {
					result[i] = rs.getObject(i + 1);
				}

				// Add the row to the list
				rows.add(result);
			}

			// Return the list of rows
			return rows;
		}
	};

	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<Object[]> result = queryRunner.query(connection, query, handler);

			for (Object[] row : result) {
				for (Object column : row) {
					System.out.print(column + " ");
				}
				System.out.println();
			}

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

Output

1 Hari Krishna 
3 Sudhir

 

Previous                                                    Next                                                    Home

Commons DbUtils tutorial

The Commons DbUtils library is a small group of tools that makes working with databases easier. When you use JDBC, you usually have to do a lot of boring, error-prone work to clean up resources. These tools handle all that cleanup for you, so you can focus on what you really want to do: getting and updating data.

 

Here are some reasons why using DbUtils is a good idea:

 

1.   No possibility for resource leaks, doing JDBC correctly isn't hard, but it takes time and can be tedious. Sometimes, connections are left open accidentally, which can cause problems. DbUtils makes sure this doesn't happen.

2.   Writing code to save data in a database becomes simpler and clearer. You need much less code, and what's left is easy to understand because it's not cluttered with resource cleanup tasks.

3.   Automatically filling in JavaBean properties from query results. You don't have to manually copy values from the database into your Java objects; DbUtils does it for you. Each row of data from the database corresponds to a Java object with all its properties already set.

 

DbUtils is not

 

1.   It's not an Object/Relational bridge. There are already many good tools for that. DbUtils is for developers who want to use JDBC without dealing with all the boring parts.

2.   It's not a Data Access Object (DAO) framework on its own, but you can use DbUtils to build one.

3.   It's not an object-oriented abstraction of database elements like tables, columns, or primary keys.

 

It's not a heavy framework of any sort. The aim of DbUtils is is to provide a simple and user-friendly JDBC helper library.

 

Dependency used for this tutorial

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

 

I am using below employee table to demonstrate the examples for this tutorial series.

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)

mysql> 
mysql> 
mysql> SELECT * FROM employee;
+------+--------------+
| id   | name         |
+------+--------------+
|    1 | Hari Krishna |
|    3 | Sudhir       |
+------+--------------+
2 rows in set (0.01 sec)

 

You can download all the examples from this link.

Getting Started with Apache Commons DBUtils: A Hello World Application
Integrating Apache Commons DBUtils with DataSource
Enhancing Database Efficiency with Asynchronous Queries Using DBUtils
Mapping Result Sets to Lists with DBUtils' BeanListHandler

 

Previous                                                    Next                                                    Home