DBUtils简明教程
2018-09-30 11:41 更新
本文以Insert和Select操作为例子，介绍apache DBUtils的使用方法和实现机制。
The Commons DbUtils library is a small set of classes designed to make working with JDBC easier
准备工作
建立表格：
CREATE TABLE `test`.`user` (
`id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(45) NOT NULL ,
`age` INT NOT NULL ,
PRIMARY KEY (`id`) ,
UNIQUE INDEX `name_UNIQUE` (`name` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
在http://dev.mysql.com/downloads/connector/j下载mysql-connector-java-5.1.34.tar.gz。
在http://commons.apache.org/proper/commons-dbutils/download_dbutils.cgi下载commons-dbutils-1.6-bin.tar.gz。
首先创建DBConf.java，内容如下：
package hellodbutils;
public class DBConf {
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/test";
static final String USER = "username";
static final String PASS = "password";
}
先看一个JDBC的示例
// 类hellodbutils.JDBCInsert
package hellodbutils;
import java.sql.*;
public class JDBCInsert {
public static void main(String args[]) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
Class.forName(DBConf.JDBC_DRIVER);
conn = DriverManager.getConnection(DBConf.DB_URL, DBConf.USER, DBConf.PASS);
String sql = "INSERT INTO user(name, age) VALUES(?,?)";
pstmt = conn.prepareStatement(sql);
// insert a user
pstmt.setString(1, "letian");
pstmt.setInt(2, 18);
pstmt.execute();
// insert another user
pstmt.setString(1, "letiantian");
pstmt.setInt(2, 19);
pstmt.execute();
//select
sql = "SELECT * FROM user";
pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
System.out.println(rs.getInt("id"));
System.out.println(rs.getString("name"));
System.out.println(rs.getInt("age"));
System.out.println("------");
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
运行结果如下：
1
letian
18
------
2
letiantian
19
------
MySQL命令行客户端查询结果：
mysql> select * from user;
+----+------------+-----+
| id | name | age |
+----+------------+-----+
| 1 | letian | 18 |
| 2 | letiantian | 19 |
+----+------------+-----+
使用DBUtils插入数据
首先清空user表中的数据：
mysql> delete from user;
mysql> ALTER TABLE user AUTO_INCREMENT = 1;
编写代码：
// 类hellodbutils.DBUtilsInsert
package hellodbutils;
import java.sql.*;
import org.apache.commons.dbutils.*;
import org.apache.commons.dbutils.handlers.ArrayHandler;
public class DBUtilsInsert {
public static void main(String args[]) {
Connection conn;
QueryRunner queryRunner = new QueryRunner();
ArrayHandler arrayHandler = new ArrayHandler();
try {
Class.forName(DBConf.JDBC_DRIVER);
conn = DriverManager.getConnection(DBConf.DB_URL, DBConf.USER, DBConf.PASS);
String sql = "INSERT INTO user(name, age) VALUES(?,?)";
Object[] objectArr= queryRunner.insert(conn, sql, arrayHandler, "樂天", 18);
System.out.println("数组长度：" + objectArr.length + "；第0个元素的值：" +objectArr[0]);
objectArr = queryRunner.insert(conn, sql, arrayHandler, "樂天天", 19);
System.out.println("数组长度：" + objectArr.length + "；第0个元素的值：" +objectArr[0]);
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
运行结果：
数组长度：1；第0个元素的值：1
数组长度：1；第0个元素的值：2
MySQL命令行客户端查询结果：
mysql> select * from user;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 1 | 樂天 | 18 |
| 2 | 樂天天 | 19 |
+----+-----------+-----+
2 rows in set (0.33 sec)
源码分析：
上面的句子调用了类org.apache.commons.dbutils.QueryRunner的这个方法：
// 类`org.apache.commons.dbutils.QueryRunner`下方法insert(...)
public <T> T insert(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
return insert(conn, false, sql, rsh, params);
}
最终调用的是下面的重载方法：
// 类`org.apache.commons.dbutils.QueryRunner`下方法insert(...)
private <T> T insert(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params)
throws SQLException {
// .... 删去了若干代码
PreparedStatement stmt = null;
T generatedKeys = null;
try {
stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
this.fillStatement(stmt, params);
stmt.executeUpdate();
ResultSet resultSet = stmt.getGeneratedKeys();
generatedKeys = rsh.handle(resultSet);
} catch (SQLException e) {
this.rethrow(e, sql, params);
} finally {
close(stmt);
if (closeConn) {
close(conn);
}
}
return generatedKeys;
}
this.fillStatement方法来自类org.apache.commons.dbutils.QueryRunner的父类org.apache.commons.dbutils.AbstractQueryRunner，fillStatement的主要内容是：
// 类`org.apache.commons.dbutils.AbstractQueryRunner`下方法fillStatement(...)
public void fillStatement(PreparedStatement stmt, Object... params)
throws SQLException {
// .... 删去了若干代码
for (int i = 0; i < params.length; i++) {
if (params[i] != null) {
stmt.setObject(i + 1, params[i]);
} else {
// .... 删去了若干代码
}
}
}
javadoc中如下介绍stmt.getGeneratedKeys()：
ResultSet getGeneratedKeys()
throws SQLException
Retrieves any auto-generated keys created as a result of executing this Statement object. If this Statement object did not generate any keys, an empty ResultSet object is returned.
Note:If the columns which represent the auto-generated keys were not specified, the JDBC driver implementation will determine the columns which best represent the auto-generated keys.
类org.apache.commons.dbutils.QueryRunner的insert(...)函数有一参数是ResultSetHandler<T> rsh，ResultSetHandler用来处理sql查询后得到的结果。在类hellodbutils.DBUtilsInsert中，我们使用的ResultSetHandler是类org.apache.commons.dbutils.handlers.ArrayHandler：
// 类`org.apache.commons.dbutils.handlers.ArrayHandler`
public class ArrayHandler implements ResultSetHandler<Object[]> {
// .... 删去了若干代码
@Override
public Object[] handle(ResultSet rs) throws SQLException {
return rs.next() ? this.convert.toArray(rs) : EMPTY_ARRAY;
}
}
this.convert默认值是new BasicRowProcessor()。类BasicRowProcessor在包org.apache.commons.dbutils中，其中toArray(...)函数详细如下：
// 类org.apache.commons.dbutils.BasicRowProcessor下的toArray(...)函数
@Override
public Object[] toArray(ResultSet rs) throws SQLException {
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;
}
好了，到了这里，类hellodbutils.DBUtilsInsert使用DBUtils插入数据的思路也就知道了。
使用DBUtils获取一条数据
编写代码：
// 类hellodbutils.DBUtilsSelect
package hellodbutils;
import java.util.Map;
import java.sql.*;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
public class DBUtilsSelect {
public static void main(String args[]) {
Connection conn;
QueryRunner queryRunner = new QueryRunner();
try {
Class.forName(DBConf.JDBC_DRIVER);
conn = DriverManager.getConnection(DBConf.DB_URL, DBConf.USER, DBConf.PASS);
String sql = "SELECT id, name, age FROM user WHERE id=?";
Map<String, Object> resultMap = queryRunner.query(conn, sql, new MapHandler(), 1);
System.out.println(resultMap);
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
运行结果：
{id=1, name=樂天, age=18}
源码分析：
我们看一下org.apache.commons.dbutils.handlers.MapHandler做了什么：
// 类org.apache.commons.dbutils.handlers.MapHandler
public class MapHandler implements ResultSetHandler<Map<String, Object>> {
// .... 删去了若干代码
@Override
public Map<String, Object> handle(ResultSet rs) throws SQLException {
return rs.next() ? this.convert.toMap(rs) : null;
}
}
这里，this.convert默认值也是new BasicRowProcessor()。看一下toMap(...)函数：
// 类org.apache.commons.dbutils.BasicRowProcessor下的toMap(...)函数
@Override
public Map<String, Object> toMap(ResultSet rs) throws SQLException {
Map<String, Object> result = new CaseInsensitiveHashMap();
ResultSetMetaData rsmd = rs.getMetaData();
int cols = rsmd.getColumnCount();
for (int i = 1; i <= cols; i++) {
String columnName = rsmd.getColumnLabel(i);
if (null == columnName || 0 == columnName.length()) {
columnName = rsmd.getColumnName(i);
}
result.put(columnName, rs.getObject(i));
}
return result;
}
CaseInsensitiveHashMap定义在类BasicRowProcessor内部：
private static class CaseInsensitiveHashMap extends LinkedHashMap<String, Object> {
private final Map<String, String> lowerCaseMap = new HashMap<String, String>();
private static final long serialVersionUID = -2848100435296897392L;
@Override
public boolean containsKey(Object key) {
Object realKey = lowerCaseMap.get(key.toString().toLowerCase(Locale.ENGLISH));
return super.containsKey(realKey);
}
@Override
public Object get(Object key) {
Object realKey = lowerCaseMap.get(key.toString().toLowerCase(Locale.ENGLISH));
return super.get(realKey);
}
@Override
public Object put(String key, Object value) {
Object oldKey = lowerCaseMap.put(key.toLowerCase(Locale.ENGLISH), key);
Object oldValue = super.remove(oldKey);
super.put(key, value);
return oldValue;
}
@Override
public void putAll(Map<? extends String, ?> m) {
for (Map.Entry<? extends String, ?> entry : m.entrySet()) {
String key = entry.getKey();
Object value = entry.getValue();
this.put(key, value);
}
}
@Override
public Object remove(Object key) {
Object realKey = lowerCaseMap.remove(key.toString().toLowerCase(Locale.ENGLISH));
return super.remove(realKey);
}
}
使用DBUtils获取多条数据
编写代码：
// 类hellodbutils.DBUtilsSelect2
package hellodbutils;
import java.util.Map;
import java.util.List;
import java.sql.*;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
public class DBUtilsSelect2 {
public static void main(String args[]) {
Connection conn;
QueryRunner queryRunner = new QueryRunner();
try {
Class.forName(DBConf.JDBC_DRIVER);
conn = DriverManager.getConnection(DBConf.DB_URL, DBConf.USER, DBConf.PASS);
String sql = "SELECT id, name, age FROM user";
List<Map<String, Object>> result = queryRunner.query(conn, sql, new MapListHandler());
System.out.println(result);
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
运行结果：
[{id=1, name=樂天, age=18}, {id=2, name=樂天天, age=19}]
源码分析：
先看一下类org.apache.commons.dbutils.handlers.MapListHandler：
// 类 org.apache.commons.dbutils.handlers.MapListHandler
public class MapListHandler extends AbstractListHandler<Map<String, Object>> {
// .... 删去了若干代码
public MapListHandler() {
this(ArrayHandler.ROW_PROCESSOR);
}
// .... 删去了若干代码
@Override
protected Map<String, Object> handleRow(ResultSet rs) throws SQLException {
return this.convert.toMap(rs);
}
}
handle(...)方法在MapListHandler的父类org.apache.commons.dbutils.handlers.AbstractListHandler中：
public abstract class AbstractListHandler<T> implements ResultSetHandler<List<T>> {
@Override
public List<T> handle(ResultSet rs) throws SQLException {
List<T> rows = new ArrayList<T>();
while (rs.next()) {
rows.add(this.handleRow(rs));
}
return rows;
}
protected abstract T handleRow(ResultSet rs) throws SQLException;
}
使用handleRow(...)处理ResultSet rs中当前指向的数据，并转换为Map；而handle()方法将获得ResultSet rs所能找到的所有数据对应的Map构成的ArrayList。
以上内容是否对您有帮助：
更多建议：