Apache DBUtils Tutorial

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




Previous 1 2 3 4








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

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