apachecommon连接mysql_Java连接数据库 #04# Apache Commons DbUtils

DbUtils并非是什么ORM框架，只是对原始的JDBC进行了一些封装，以便我们少写一些重复代码。就“用”而言，仅仅需要学习QueryRunner类和ResultSetHandler接口就可以了。它的显著特点就是超级轻量级，总代码量目测似乎还不到一万行。

通过一个简单的调用看整体结构

public classTestDbUtils {private static final QueryRunner RUNNER = newQueryRunner(HikariCPUtils.getDs());public static voidmain(String[] args) {

ResultSetHandler 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.

*@paramconn The connection to use for the query call.

*@paramcloseConn True if the connection should be closed, false otherwise.

*@paramsql The SQL statement to execute.

*@paramparams An array of query replacement parameters. Each row in

* this array is one set of batch replacement values.

*@returnThe results of the query.

*@throwsSQLException If there are database or parameter errors.*/

private T query(Connection conn, boolean closeConn, String sql, ResultSetHandlerrsh, Object... params)throwsSQLException {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);

}

}

}returnresult;

}

private T query(Connection conn, boolean closeConn, String sql, ResultSetHandler rsh, Object... params) throws SQLException

AbstractQueryRunner、RowProcessor、ResultSetHandler是Apache Commons DbUtils里的三大主角，通过继承上述抽象类/实现上述接口可以很方便地对API功能进行定制化。

Examples

同步请求的代码在上面已经给出。异步请求的草稿代码如下：

public classDbUtilsSampleDAO {private AsyncQueryRunner runner = newAsyncQueryRunner(Executors.newCachedThreadPool());public FuturesaveProfile(Profile profile) {

Futurefuture;

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 newDaoException(e);

}finally{try{

DbUtils.close(conn);

}catch(SQLException e) {throw newDaoException(e);

}

}returnfuture;

}public static voidmain(String[] args) {

DbUtilsSampleDAO dao= newDbUtilsSampleDAO();

Profile profile= new Profile("myusername", "mypassword", "thisnickname");

Future future =dao.saveProfile(profile);try{

System.out.println(future.get()== 1 ? "更新成功" : "更新失败");

}catch (InterruptedException |ExecutionException e) {

e.printStackTrace();

}

}

}

主要的几个类如下：

① QueryRunnerProxy.java

packageorg.sample.webapp.db.queryrunner;importorg.apache.commons.dbutils.QueryRunner;importorg.apache.commons.dbutils.ResultSetHandler;importorg.sample.webapp.db.connmanager.ConnectionFactory;importorg.sample.webapp.exception.DaoException;importjava.sql.SQLException;/*** 封装DAO层通用代码，不会关闭connection！！！*/

public classQueryRunnerProxy {privateQueryRunnerProxy() {//Exists to defeat instantiation

}private static QueryRunner runner = newQueryRunner();public static intupdate(String sql, Object... param) {int updates = 0;try{

updates=runner.update(ConnectionFactory.getConnection(), sql, param);

}catch(SQLException e) {throw newDaoException(e);

}returnupdates;

}public static T query(String sql, ResultSetHandlerrsh, Object... params) {

T result= null;try{

result=runner.query(ConnectionFactory.getConnection(), sql, rsh, params);

}catch(SQLException e) {throw newDaoException(e);

}returnresult;

}

}

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

② RsHandlers.java

packageorg.sample.webapp.db.queryrunner;importorg.apache.commons.dbutils.handlers.BeanHandler;importorg.apache.commons.dbutils.handlers.BeanListHandler;importorg.sample.webapp.entity.Profile;/*** 业务相关，保存各式各样的ResultSetHandler常量*/

public interfaceRsHandlers {

BeanListHandler PROFILE_LIST = new BeanListHandler<>(Profile.class);

BeanHandler PROFILE = new BeanHandler<>(Profile.class);

}

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

③ ProfileDAOImpl.java 顺便改为enum了，仅是因为觉得这样写表示单例更清晰(后来证明这是一个意义非常有限、自寻麻烦的做法。。)

packageorg.sample.webapp.dao.impl;importorg.sample.webapp.dao.ProfileDAO;importorg.sample.webapp.db.queryrunner.QueryRunnerProxy;importorg.sample.webapp.db.queryrunner.RsHandlers;importorg.sample.webapp.entity.Profile;importjava.util.List;/*** 该类方法统一抛出DaoException*/

public enum ProfileDAOImpl implementsProfileDAO {

INSTANCE;

@Overridepublic intsaveProfile(Profile profile) {final String sql = "INSERT ignore INTO profile (username, password, nickname) " +

"VALUES (?, ?, ?)"; //添加ignore出现重复不会抛出异常而是返回0

returnQueryRunnerProxy.update(sql, profile.getUsername(), profile.getPassword(), profile.getNickname());

}

@Overridepublic ListlistByNickname(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=?";returnQueryRunnerProxy.query(sql, RsHandlers.PROFILE_LIST, nickname);

}

@OverridepublicProfile 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 该字符串会反复创建吗？

returnQueryRunnerProxy.query(sql, RsHandlers.PROFILE, username);

}

@Overridepublic intupdateById(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());

}

@Overridepublic intupdatePassword(String username, String password) {final String sql = "UPDATE profile " +

"SET password=? " +

"WHERE username=?";returnQueryRunnerProxy.update(sql, password, username);

}

@Overridepublic intupdateLastOnline(String username) {final String sql = "UPDATE profile " +

"SET last_online=CURRENT_TIMESTAMP " +

"WHERE username=?";returnQueryRunnerProxy.update(sql, username);

}

}

后续：

1、把sql常量字符串集中放在一个Interface里。如下：

packageorg.sample.shop.db.queryrunner;public interfaceSQLs {//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=?";

}