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