DbUtils并非是什么ORM框架，只是对原始的JDBC进行了一些封装，以便我们少写一些重复代码。就“用”而言，仅仅需要学习QueryRunner类和ResultSetHandler接口就可以了。它的显著特点就是超级轻量级，总代码量目测似乎还不到一万行。
通过一个简单的调用看整体结构
public class TestDbUtils {
private static final QueryRunner RUNNER = new QueryRunner(HikariCPUtils.getDs());
public static void main(String[] args) {
ResultSetHandler<SimpleUser> handler = new BeanHandler<>(SimpleUser.class);
SimpleUser user = null;
try {
user = RUNNER.query("SELECT * FROM simple_user WHERE username=?", handler, "admin123");
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println(user);
}
}
TestDbUtils.java
/**
* Calls query after checking the parameters to ensure nothing is null.
* @param conn The connection to use for the query call.
* @param closeConn True if the connection should be closed, false otherwise.
* @param sql The SQL statement to execute.
* @param params An array of query replacement parameters. Each row in
* this array is one set of batch replacement values.
* @return The results of the query.
* @throws SQLException If there are database or parameter errors.
*/
private <T> T query(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params)
throws SQLException {
if (conn == null) {
throw new SQLException("Null connection");
} if (sql == null) {
if (closeConn) {
close(conn);
}
throw new SQLException("Null SQL statement");
} if (rsh == null) {
if (closeConn) {
close(conn);
}
throw new SQLException("Null ResultSetHandler");
} PreparedStatement stmt = null;
ResultSet rs = null;
T result = null; try {
stmt = this.prepareStatement(conn, sql);
this.fillStatement(stmt, params);
rs = this.wrap(stmt.executeQuery());
result = rsh.handle(rs); } catch (SQLException e) {
this.rethrow(e, sql, params); } finally {
try {
close(rs);
} finally {
close(stmt);
if (closeConn) {
close(conn);
}
}
} return result;
}
private T query(Connection conn, boolean closeConn, String sql, ResultSetHandler rsh, Object... params) throws SQLException
AbstractQueryRunner、RowProcessor、ResultSetHandler<T>是Apache Commons DbUtils里的三大主角，通过继承上述抽象类/实现上述接口可以很方便地对API功能进行定制化。
详细类图参考：http://ju.outofmemory.cn/entry/143920
Examples
官方Examples：http://commons.apache.org/proper/commons-dbutils/examples.html
同步请求的代码在上面已经给出。异步请求的草稿代码如下：
public class DbUtilsSampleDAO {
private AsyncQueryRunner runner = new AsyncQueryRunner(Executors.newCachedThreadPool());
public Future<Integer> saveProfile(Profile profile) {
Future<Integer> future;
Connection conn = null;
try {
conn = ConnectionFactory.getConnection();
future = runner.update(conn,
"INSERT ignore INTO `profiles`.`profile` (`username`, `password`, `nickname`) " +
"VALUES (?, ?, ?)", profile.getUsername(), profile.getPassword(), profile.getNickname());
} catch (SQLException e) {
throw new DaoException(e);
} finally {
try {
DbUtils.close(conn);
} catch (SQLException e) {
throw new DaoException(e);
}
}
return future;
}
public static void main(String[] args) {
DbUtilsSampleDAO dao = new DbUtilsSampleDAO();
Profile profile = new Profile("myusername", "mypassword", "thisnickname");
Future<Integer> future = dao.saveProfile(profile);
try {
System.out.println(future.get() == 1 ? "更新成功" : "更新失败");
} catch (InterruptedException | ExecutionException e) {
e.printStackTrace();
}
}
}
修改JAVA连接数据库#03#中的代码
以下代码同步更新在GitHub：https://github.com/xkfx/web-test-01
主要的几个类如下：
① QueryRunnerProxy.java
package org.sample.webapp.db.queryrunner; import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.sample.webapp.db.connmanager.ConnectionFactory;
import org.sample.webapp.exception.DaoException; import java.sql.SQLException; /**
* 封装DAO层通用代码，不会关闭connection！！！
*/
public class QueryRunnerProxy { private QueryRunnerProxy() {
// Exists to defeat instantiation
} private static QueryRunner runner = new QueryRunner(); public static int update(String sql, Object... param) {
int updates = 0;
try {
updates = runner.update(ConnectionFactory.getConnection(), sql, param);
} catch (SQLException e) {
throw new DaoException(e);
}
return updates;
} public static <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) {
T result = null;
try {
result = runner.query(ConnectionFactory.getConnection(), sql, rsh, params);
} catch (SQLException e) {
throw new DaoException(e);
}
return result;
}
}
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
② RsHandlers.java
package org.sample.webapp.db.queryrunner; import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.sample.webapp.entity.Profile; /**
* 业务相关，保存各式各样的ResultSetHandler常量
*/
public interface RsHandlers { BeanListHandler<Profile> PROFILE_LIST = new BeanListHandler<>(Profile.class); BeanHandler<Profile> PROFILE = new BeanHandler<>(Profile.class);
}
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
③ ProfileDAOImpl.java 顺便改为enum了，仅是因为觉得这样写表示单例更清晰（后来证明这是一个意义非常有限、自寻麻烦的做法。。）
package org.sample.webapp.dao.impl; import org.sample.webapp.dao.ProfileDAO;
import org.sample.webapp.db.queryrunner.QueryRunnerProxy;
import org.sample.webapp.db.queryrunner.RsHandlers;
import org.sample.webapp.entity.Profile; import java.util.List; /**
* 该类方法统一抛出DaoException
*/
public enum ProfileDAOImpl implements ProfileDAO { INSTANCE; @Override
public int saveProfile(Profile profile) {
final String sql = "INSERT ignore INTO profile (username, password, nickname) " +
"VALUES (?, ?, ?)"; // 添加ignore出现重复不会抛出异常而是返回0
return QueryRunnerProxy.update(sql, profile.getUsername(), profile.getPassword(), profile.getNickname());
} @Override
public List<Profile> listByNickname(String nickname) {
final String sql = "SELECT profile_id AS id, username, password, nickname, last_online AS lastOnline, gender, birthday, location, joined " +
"FROM profile " +
"WHERE nickname=?";
return QueryRunnerProxy.query(sql, RsHandlers.PROFILE_LIST, nickname);
} @Override
public Profile getByUsername(String username) {
final String sql = "SELECT profile_id AS id, username, password, nickname, last_online AS lastOnline, gender, birthday, location, joined " +
"FROM profile " +
"WHERE username=?"; // TODO 该字符串会反复创建吗？
return QueryRunnerProxy.query(sql, RsHandlers.PROFILE, username);
} @Override
public int updateById(Profile profile) {
final String sql = "UPDATE profile " +
"SET nickname=?, gender=?, birthday=?, location=? " +
"WHERE profile_id=?";
return QueryRunnerProxy.update(sql, profile.getNickname(), profile.getGender() != null ? String.valueOf(profile.getGender()) : null,
profile.getBirthday(), profile.getLocation(), profile.getId());
} @Override
public int updatePassword(String username, String password) {
final String sql = "UPDATE profile " +
"SET password=? " +
"WHERE username=?";
return QueryRunnerProxy.update(sql, password, username);
} @Override
public int updateLastOnline(String username) {
final String sql = "UPDATE profile " +
"SET last_online=CURRENT_TIMESTAMP " +
"WHERE username=?";
return QueryRunnerProxy.update(sql, username);
}
}
后续：
1、把sql常量字符串集中放在一个Interface里。如下：
package org.sample.shop.db.queryrunner;
public interface SQLs {
// item
String ITEM_LIST_BY_UID_AND_STATUS = "SELECT id, user_id AS userId, name, price, status, quantity FROM item WHERE user_id=? AND status=?";
String ITEM_SAVE_ITEM = "INSERT INTO item(user_id, name, price, status, quantity) VALUES (?, ?, ?, ?, ?)";
String ITEM_REMOVE_BY_ID = "DELETE FROM item WHERE id=?";
String ITEM_UPDATE_BY_ID = "UPDATE item SET name=?, price=?, status=?, quantity=? WHERE id=?";
// order
String ORDER_GET_BY_UID = "SELECT id, user_id AS userId, total FROM simple_order WHERE user_id=?";
String ORDER_SAVE_ORDER = "INSERT INTO simple_order(user_id, total) VALUES(?, ?)";
String ORDER_SAVE_ORDER_DETAIL = "INSERT INTO order_detail(order_id, item_id, user_id, quantity, price, status) VALUES(?, ?, ?, ?, ?, ?)";
// order detail
String ORDER_DETAIL_GET_BY_ORDER_ID = "SELECT id, order_id AS orderId, item_id AS itemId, user_id AS userId, quantity, price, status FROM order_detail WHERE order_id=?";
String ORDER_DETAIL_GET_BY_UID = "SELECT id, order_id AS orderId, item_id AS itemId, user_id AS userId, quantity, price, status FROM order_detail WHERE user_id=?";
// user
String USER_GET_BY_USERNAME = "SELECT id, username, password, type FROM simple_user WHERE username=?";
String USER_SAVE_USER = "INSERT INTO simple_user(type, username, password) VALUES (?, ?, ?)";
// transport order
String TRANSPORT_ORDER_SAVE_ORDER = "INSERT INTO transport_order(user_id, order_detail_id, location, status) VALUES (?, ?, ?, ?)";
String TRANSPORT_ORDER_GET_BY_UID = "SELECT id, user_id AS userId, order_detail_id AS detailId, location, status FROM transport_order WHERE user_id=?";
String TRANSPORT_ORDER_GET_BY_DETAIL_ID = "SELECT id, user_id AS userId, order_detail_id AS detailId, location, status FROM transport_order WHERE order_detail_id=?";
String TRANSPORT_ORDER_UPDATE_BY_ID = "UPDATE transport_order SET location=?, status=? WHERE id=?";
}