我们先来看看经典的JDBC操作数据库
- 加载数据库驱动
- 创建数据库连接
- 创建一个Statement
- 执行SQL语句
- 处理返回结果
关闭连接
为了提高性能,利用连接池,很好的解决了获取数据库连接,复用数据库连接,但是还有执行SQL,处理返回结果还是没有抽离出来,为了让代码更好的解耦,我们来看看DBUtils是怎么做的!DBUtils主要做了两件事,一件是填充执行SQL语句参数,另外一件是处理返回结果,封装从对象实体。
只看主要代码,从QueryRunner.query()入口方法开始
private <T> T query(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params)
throws SQLException {
//省略不重要的代码
PreparedStatement stmt = null;
ResultSet rs = null;
T result = null;
try {
stmt = this.prepareStatement(conn, sql);
//封装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;
}
fillStatement方法操作逻辑如下:
- 通过Statement.getParameterMetaData() 获取SQL参数实体对象ParameterMetaData
- 赋值变量params数组,遍历顺序需要跟SQL参数顺序一致
//比如如下两个参数,需要按顺序填充
String sql = "select * from user where groupId=? and sex=?";
Object[] params = new Object[2];
params[0] = groupId;
params[1] = sex;
fillStatement方法代码如下:
public void fillStatement(PreparedStatement stmt, Object... params)
throws SQLException {
// check the parameter count, if we can
ParameterMetaData pmd = null;
if (!pmdKnownBroken) {
try {
pmd = stmt.getParameterMetaData();
if (pmd == null) { // can be returned by implementations that don't support the method
pmdKnownBroken = true;
} else {
int stmtCount = pmd.getParameterCount();
int paramsCount = params == null ? 0 : params.length;
if (stmtCount != paramsCount) {
throw new SQLException("Wrong number of parameters: expected "
+ stmtCount + ", was given " + paramsCount);
}
}
} catch (SQLFeatureNotSupportedException ex) {
pmdKnownBroken = true;
}
// TODO see DBUTILS-117: would it make sense to catch any other SQLEx types here?
}
// nothing to do here
if (