Apache DBUtils Tutorial

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 *