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