Apache DBUtils Tutorial

The Apache Commons DbUtils library is a small set of classes designed to make working with JDBC easier. JDBC resource cleanup code is mundane, error prone work so these classes abstract out all of the cleanup tasks from your code leaving you with what you really wanted to do with JDBC in the first place: query and update data.

Some of the advantages of using DbUtils are:

No possibility for resource leaks. Correct JDBC coding isn't difficult but it is time-consuming and tedious. This often leads to connection leaks that may be difficult to track down.

Cleaner, clearer persistence code. The amount of code needed to persist data in a database is drastically reduced. The remaining code clearly expresses your intention without being cluttered with resource cleanup.

Automatically populate JavaBean properties from ResultSets. You don't need to manually copy column values into bean instances by calling setter methods. Each row of the ResultSet can be represented by one fully populated bean instance.

We can also use Connection Pool(Datasource) with DBUtils, it is good practice to use a connection pool in your applications in order to increase the performance and scalability

DBUtils simplifies the CRUD (Create,Retrieve,Update,Delete) operations, with any database.

DbUtils is designed to be:

Small - you should be able to understand the whole package in a short amount of time.

Transparent - DbUtils doesn't do any magic behind the scenes. You give it a query, it executes it and cleans up for you.

Fast - You don't need to create a million temporary objects to work with DbUtils.

Referenece - > http://commons.apache.org/proper/commons-dbutils/

Required Libraries

You need to download

  1. Apache Commons DbUtils

Following jar must be in classpath

  1. commons-dbutils-1.5.jar
  2. mysql-connector-java-5.1.28-bin.jar

Create Table Structure

CREATE TABLE `employee` (
  `EMPLOYEEID` bigint(20) NOT NULL AUTO_INCREMENT,
  `EMPLOYEENAME` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEEID`)
);

insert into `employee` (`EMPLOYEEID`, `EMPLOYEENAME`, `EMPLOYEE_ADDRESS`) values('1','Rockey','1');
insert into `employee` (`EMPLOYEEID`, `EMPLOYEENAME`, `EMPLOYEE_ADDRESS`) values('2','Jose','2');

DBUtils BeanHandler Example

Query Runner Executes SQL queries with pluggable strategies for handling ResultSets. This class is thread safe (see below example)

package com.test.dbutils;

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

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;

public class DBUtilsWithConnection {

   
public static void main(String[] args) throws SQLException {

       
final String url = "jdbc:mysql://localhost:3306/test";
       
final String driver = "com.mysql.jdbc.Driver";
       
final String usr = "root";
       
final String pwd = "root";

        QueryRunner run =
new QueryRunner();

        DbUtils.loadDriver
(driver);
        Connection conn = DriverManager.getConnection
(url, usr, pwd);
       
// -----------------------------------------------------------------------------------
       
ResultSetHandler<Employee> resultHandler = new BeanHandler<Employee>(Employee.class);
       
       
       
try {
           
Employee emp = run.query(conn, "SELECT * FROM employee WHERE employeename=?",
                    resultHandler,
"Jose");
            System.out.println
(emp.getEmployeeId());
       
} finally {
           
DbUtils.close(conn);
       
}
       
    }
}
Output
2

DBUtils BeanListHandler Example

package com.test.dbutils;

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

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

public class DBUtilsWithConnection {

   
public static void main(String[] args) throws SQLException {

       
final String url = "jdbc:mysql://localhost:3306/test";
       
final String driver = "com.mysql.jdbc.Driver";
       
final String usr = "root";
       
final String pwd = "root";

        QueryRunner run =
new QueryRunner();

        DbUtils.loadDriver
(driver);
        Connection conn = DriverManager.getConnection
(url, usr, pwd);
       
// -----------------------------------------------------------------------------------
       
ResultSetHandler<List<Employee>> resultListHandler = new BeanListHandler<Employee>(Employee.class);

       
try {
           
List<Employee> empList = run.query(conn, "SELECT * FROM employee", resultListHandler);
            System.out.println
(empList);
       
} finally {
           
DbUtils.close(conn);
       
}

    }
}
Output
[Employee [employeeId=1, employeeName=Rockey], Employee [employeeId=2, employeeName=Jose]]

DBUtils MapListHandler Example

package com.test.dbutils;

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

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;

public class DBUtilsWithConnection {

   
public static void main(String[] args) throws SQLException {

       
final String url = "jdbc:mysql://localhost:3306/test";
       
final String driver = "com.mysql.jdbc.Driver";
       
final String usr = "root";
       
final String pwd = "root";

        QueryRunner run =
new QueryRunner();

        DbUtils.loadDriver
(driver);
        Connection conn = DriverManager.getConnection
(url, usr, pwd);
       
// -----------------------------------------------------------------------------------
       
       
try {
           
List<Map<String, Object>> maps = run.query(conn, "SELECT * FROM employee", new MapListHandler());
            System.out.println
(maps);
       
} finally {
           
DbUtils.close(conn);
       
}

    }
}
Output
[{EMPLOYEEID=1, EMPLOYEENAME=Rockey, EMPLOYEE_ADDRESS=1}, {EMPLOYEEID=2, EMPLOYEENAME=Jose, EMPLOYEE_ADDRESS=2}]

DBUtils ArrayListHandler Example

package com.test.dbutils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;

public class DBUtilsWithConnection {

   
public static void main(String[] args) throws SQLException {

       
final String url = "jdbc:mysql://localhost:3306/test";
       
final String driver = "com.mysql.jdbc.Driver";
       
final String usr = "root";
       
final String pwd = "root";

        QueryRunner run =
new QueryRunner();

        DbUtils.loadDriver
(driver);
        Connection conn = DriverManager.getConnection
(url, usr, pwd);
       
// -----------------------------------------------------------------------------------

       
try {
           
List<Object[]> query = run.query(conn, "SELECT * FROM employee", new ArrayListHandler());
           
for (Object[] objects : query) {
               
System.out.println(Arrays.toString(objects));
           
}
        }
finally {
           
DbUtils.close(conn);
       
}

    }
}
Output
[1, Rockey, 1]

[2, Jose, 2]

DBUtils ResultSetHandler Example

ResultSetHandler implementation that converts the first ResultSet row into a JavaBean. This class is thread safe.

package com.test.dbutils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Arrays;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

public class DBUtilsWithConnection {

   
public static void main(String[] args) throws SQLException {

       
final String url = "jdbc:mysql://localhost:3306/test";
       
final String driver = "com.mysql.jdbc.Driver";
       
final String usr = "root";
       
final String pwd = "root";

        QueryRunner run =
new QueryRunner();

        DbUtils.loadDriver
(driver);
        Connection conn = DriverManager.getConnection
(url, usr, pwd);
       
// -----------------------------------------------------------------------------------

       
ResultSetHandler<Object[]> handler = new ResultSetHandler<Object[]>() {
           
public Object[] handle(ResultSet rs) throws SQLException {
               
if (!rs.next()) {
                   
return null;
               
}
               
ResultSetMetaData meta = rs.getMetaData();
               
int cols = meta.getColumnCount();
                Object
[] result = new Object[cols];

               
for (int i = 0; i < cols; i++) {
                   
result[i] = rs.getObject(i + 1);
               
}
               
return result;
           
}
        }
;

       
try {
           
Object[] result = run.query(conn, "SELECT * FROM employee WHERE employeename=?", handler, "Rockey");
            System.out.println
(Arrays.toString(result));

       
} finally { // Use this helper method so we don't have to check for
           
DbUtils.close(conn);
       
}

    }
}
Output
[1, Rockey, 1]

DBUtils Insert,Update Example

package com.test.dbutils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;

public class DBUtilsWithConnection {

   
public static void main(String[] args) throws SQLException {

       
final String url = "jdbc:mysql://localhost:3306/test";
       
final String driver = "com.mysql.jdbc.Driver";
       
final String usr = "root";
       
final String pwd = "root";

        QueryRunner run =
new QueryRunner();

        DbUtils.loadDriver
(driver);
        Connection conn = DriverManager.getConnection
(url, usr, pwd);
       
// -----------------------------------------------------------------------------------

       
try {
           
// Execute the SQL update statement and return the number of
            // inserts that were made
           
int inserts = run.update(conn, "INSERT INTO employee (employeename) VALUES (?)", "Arun");
           
// The line before uses varargs and autoboxing to simplify the code
           
System.out.println("inserts "+inserts);
           
// Now it's time to rise to the occation...
           
int updates = run.update(conn, "UPDATE employee SET employeename=? WHERE employeeid=?", "Simon",1);
            System.out.println
("updates "+updates);
       
} catch (SQLException sqle) {
           
// Handle it
           
sqle.printStackTrace();
       
}

    }
}
Output
inserts 1

updates 1

DBUtils DataSource Example

All the above examples are combined together, to showing an example of DBUtils with Datasource. I am resusing the below article for creating DataSource Connection Pool

1)    DBCP Connection Pooling Example
package com.test.dbutils;

import java.beans.PropertyVetoException;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

public class DBUtilsWithDataSource {

   
public static void main(String[] args) throws SQLException, IOException, PropertyVetoException {

       
QueryRunner run = new QueryRunner(DataSource.getInstance().getDataSource());

       
       
// -----------------------------------------------------------------------------------
       
ResultSetHandler<Employee> resultHandler = new BeanHandler<Employee>(Employee.class);
        ResultSetHandler<List<Employee>> resultListHandler =
new BeanListHandler<Employee>(Employee.class);
       
        Employee emp = run.query
("SELECT * FROM employee WHERE employeename=?",resultHandler, "Jose");
        System.out.println
(emp.getEmployeeId());
       
// -----------------------------------------------------------------------------------
       
       
List<Employee> empList = run.query("SELECT * FROM employee",resultListHandler);
        System.out.println
(empList);
       
// -----------------------------------------------------------------------------------
       
       
List<Map<String, Object>> maps = run.query("SELECT * FROM employee",new MapListHandler());
        System.out.println
(maps);
       
// -----------------------------------------------------------------------------------

       
List<Object[]> query = run.query("SELECT * FROM employee", new ArrayListHandler());
       
for (Object[] objects : query) {
           
System.out.println(Arrays.toString(objects));
       
}
       
// -----------------------------------------------------------------------------------
       
       
ResultSetHandler<Object[]> handler = new ResultSetHandler<Object[]>() {
           
public Object[] handle(ResultSet rs) throws SQLException {
               
if (!rs.next()) {
                   
return null;
               
}
               
ResultSetMetaData meta = rs.getMetaData();
               
int cols = meta.getColumnCount();
                Object
[] result = new Object[cols];

               
for (int i = 0; i < cols; i++) {
                   
result[i] = rs.getObject(i + 1);
               
}
               
return result;
           
}
        }
;

        Object
[] result = run.query("SELECT * FROM employee WHERE employeename=?",handler, "Jose");
        System.out.println
(Arrays.toString(result));

       
// -----------------------------------------------------------------------------------

       
try {
           
// Execute the SQL update statement and return the number of
            // inserts that were made
           
int inserts = run.update("INSERT INTO employee (employeename) VALUES (?)", "Arun");
           
// The line before uses varargs and autoboxing to simplify the code
           
System.out.println("inserts "+inserts);
           
// Now it's time to rise to the occation...
           
int updates = run.update("UPDATE employee SET employeename=? WHERE employeeid=?","Simon", 1);
            System.out.println
("updates "+updates);
       
} catch (SQLException sqle) {
           
// Handle it
           
sqle.printStackTrace();
       
}

    }
}
Output
2
[Employee [employeeId=1, employeeName=Rockey], Employee [employeeId=2, employeeName=Jose]]
[{EMPLOYEEID=1, EMPLOYEENAME=Rockey, EMPLOYEE_ADDRESS=1}, {EMPLOYEEID=2, EMPLOYEENAME=Jose, EMPLOYEE_ADDRESS=2}]
[1, Rockey, 1]
[2, Jose, 2]
[1, Rockey, 1]
inserts 1
updates 1




3 Responses to "Apache DBUtils Tutorial"
  1. Jim E 2014-03-01 09:57:19.0
  1. admin 2014-03-02 09:57:19.0
  1. Chon Nguyen 2018-01-02 23:43:03.0

Your email address will not be published. Required fields are marked *