Apache Commons 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.
Advantages of Using DbUtils are:
- No possibilities for resource leak.
- Cleaner, clearer persistence code. The amount of code needed to persist data in a database is drastically reduced.
- 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.
The main classes used from this DbUtils library are DbUtils, QueryRunner and the ResultSetHandler.
DbUtils: A collection of JDBC helper methods, all the methods in this class are static, and this class is a thread safe means multiple threads can access concurrently.
ResultSetHandler: It is an interface, implementations of this interface convert ResultSets into other objects.
- BeanHandler: It converts the ResultSet row into a JavaBean.
- MapHandler: It converts the ResultSet row into a Map.
- BeanListHandler: It converts a ResultSet into a List of beans. etc
QueryRunner: Executes SQL queries with pluggable strategies for handling ResultSets. This class is thread safe.
Simple example to use DbUtils, QueryRunner and ResultSetHandler:
1. The User table, here is the script to create the database table and inserting the data into the User table.
CREATE TABLE IF NOT EXISTS `user` (
`userId` int(11) NOT NULL AUTO_INCREMENT,
`firstName` varchar(50) NOT NULL,
`lastName` varchar(50) NOT NULL,
`phoneNo` varchar(50) NOT NULL,
`emailId` varchar(50) NOT NULL,
PRIMARY KEY (`userId`)
) ENGINE=InnoDB; INSERT INTO `user` (`userId`, `firstName`, `lastName`, `phoneNo`, `emailId`) VALUES
(1, 'Pramod', 'Ganta', '', 'pramod@codesuggestions.com'),
(2, 'Suman', 'Manthena', '', 'suman@codesuggestions.com'),
(3, 'Prakash', 'Puli', '', 'prakash@codesuggestions.com'),
(4, 'Rohit', 'Sunkari', '', 'rohit@codesuggestions.com');
2. The Java Bean class representing the User table. The property name are same as the column names in User table.
package db.utisl2;
public class User {
private String userId;
private String firstName;
private String lastName;
private String phoneNO;
private String emailId;
public User() {
}
public String getUserId() {
return userId;
}
public void setUserId(String userId) {
this.userId = userId;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getPhoneNO() {
return phoneNO;
}
public void setPhoneNO(String phoneNO) {
this.phoneNO = phoneNO;
}
public String getEmailId() {
return emailId;
}
public void setEmailId(String emailId) {
this.emailId = emailId;
}
}
3. In this example class using BeanHandler which is the implementation of the ResultSetHandler interface, and it returns the ResultSet into a Java Bean Object.
package db.utisl2; 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.handlers.BeanHandler; public class DbUtilsBeanHandler {
public static void main(String[] args) {
Connection conn = null;
String url = "jdbc:mysql://localhost:3306/test";
String driver = "com.mysql.jdbc.Driver";
String usr = "root";
String pwd = "root";
User user = null;
try {
// Loading the Driver using DbUtils static method
DbUtils.loadDriver(driver);
conn = DriverManager.getConnection(url, usr, pwd);
QueryRunner query = new QueryRunner();
user = query.query(conn, "select * from user where userId=3", new BeanHandler<User>(
User.class));
// query.query
System.out.println("User Object:: " + user.getUserId() + "\t" + user.getFirstName()
+ "\t" + user.getLastName() + "\t" + user.getEmailId()); } catch (SQLException se) {
se.printStackTrace();
} finally {
// Closing the connection quietly, means it will handles the
// SQLException
DbUtils.closeQuietly(conn);
}
}
}
Output:
User Object:: 3 Prakash Puli
4. In this example class using BeanListHandler which also the implementation of the ResultSetHandler interface, and it returns the ResultSet into a List of Java Bean Objects.
package db.utisl2; 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.handlers.BeanListHandler; public class DbUtilsBeanListHandler { public static void main(String[] args) {
Connection conn = null;
String url = "jdbc:mysql://localhost:3306/test";
String driver = "com.mysql.jdbc.Driver";
String user = "root";
String pwd = "root";
List<User> users = null;
try {
DbUtils.loadDriver(driver);
conn = DriverManager.getConnection(url, user, pwd);
QueryRunner query = new QueryRunner();
users = query.query(conn, "select * from user", new BeanListHandler<User>(User.class));
for (int i = 0; i < users.size(); i++) {
User bean = users.get(i);
System.out.println("User Objects:: " + bean.getUserId() + "\t"
+ bean.getFirstName() + "\t" + bean.getLastName() + "\t"
+ bean.getEmailId());
}
} catch (SQLException se) {
se.printStackTrace();
} finally {
DbUtils.closeQuietly(conn);
}
}
}
Output:
User Objects:: 1 Pramod Ganta pramod@codesuggestions.com
User Objects:: 2 Suman Manthena suman@codesuggestions.com
User Objects:: 3 Prakash Puli prakash@codesuggestions.com
User Objects:: 4 Rohit Sunkari rohit@codesuggestions.com
5. In this example class using MapListHandler which also the implementation of the ResultSetHandler interface, and it returns the ResultSet into a List of Map Objects. The Map object contains the each row, the column name as key and value as value in the Map object.
package db.utisl2; 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 DbUtilsMapListHandler { public static void main(String[] args) {
Connection conn = null;
String url = "jdbc:mysql://localhost:3306/test";
String driver = "com.mysql.jdbc.Driver";
String user = "root";
String pwd = "root";
try {
DbUtils.loadDriver(driver);
conn = DriverManager.getConnection(url, user, pwd);
QueryRunner query = new QueryRunner();
List<Map<String, Object>> mapList = query.query(conn, "select * from user",
new MapListHandler());
for (int i = 0; i < mapList.size(); i++) {
Map<String, Object> map = mapList.get(i);
System.out.println("------> " + map.get("userId") + "\t" + map.get("firstName")
+ "\t" + map.get("emailId"));
}
} catch (SQLException se) {
se.printStackTrace();
} finally {
DbUtils.closeQuietly(conn);
}
}
}
Output:
------> 1 Pramod pramod@codesuggestions.com
------> 2 Suman suman@codesuggestions.com
------> 3 Prakash prakash@codesuggestions.com
------> 4 Rohit rohit@codesuggestions.com
Note: When we fetching from the Map, if we give the wrong column name it ruturn null value. Ex: map.get("fistName"), here mispelling the firstName. The same with BeanHandler to, if the Bean properties and table columns are not in match, it will return null values.