1、DBCP连接池QueryRunner的10种用法：ArrayHandler、ArrayListHandler、BeanHandler、BeanListHandler、ColumnListHandler、MapHandler、MapListHandler、BeanMapHandler、KeyedHandler、ScalarHandler。
2、DatabaseHelper.java
package mvc.framework.helper;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.BeanMapHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import mvc.web.model.User;
// 数据库操作助手类
public final class DatabaseHelper {
private static final Logger LOGGER = LoggerFactory.getLogger(DatabaseHelper.class);
private static final QueryRunner QUERY_RUNNER;
private static final BasicDataSource DATA_SOURCE;
static {
QUERY_RUNNER = new QueryRunner();
DATA_SOURCE = new BasicDataSource();
// 基本配置
// 数据库驱动
DATA_SOURCE.setDriverClassName(ConfigHelper.getJdbcDriver());
// 数据库的URL
DATA_SOURCE.setUrl(ConfigHelper.getJdbcUrl());
// 指定数据库的用户名
DATA_SOURCE.setUsername(ConfigHelper.getJdbcUsername());
// 指定数据库的密码
DATA_SOURCE.setPassword(ConfigHelper.getJdbcPassword());
// 连接池配置
// 指定数据库的最大连接数
DATA_SOURCE.setMaxTotal(ConfigHelper.getMaxTotal());
// 指定数据库的初始连接数
DATA_SOURCE.setInitialSize(ConfigHelper.getInitialSize());
// 指定数据库的最大空闲连接数(没有人用连接的时候，最大闲置的连接个数)
DATA_SOURCE.setMaxIdle(ConfigHelper.getMaxIdle());
// 指定数据库的最小空闲连接数(没有人用连接的时候，最小闲置的连接个数)
DATA_SOURCE.setMinIdle(ConfigHelper.getMinIdle());
// 指定数据库的最大等待数(以毫秒为单位)
DATA_SOURCE.setMaxWaitMillis(ConfigHelper.getMaxWaitMillis());
// 指定数据库的默认递交 // true：sql命令的提交（commit）由驱动程序负责。false：sql命令的提交由应用程序负责，程序必须调用commit或者rollback方法。
DATA_SOURCE.setDefaultAutoCommit(true);
// 指定数据库的连接超时时是否自动启动删除
DATA_SOURCE.setRemoveAbandonedOnMaintenance(true);
// 指定数据库的删除数据库连接的超时时长(以秒为单位)
DATA_SOURCE.setRemoveAbandonedTimeout(60);
// 是否记录中断事件
DATA_SOURCE.setLogAbandoned(true);
// 数据库重连设置，打开检查，用异步线程evict进行检查
DATA_SOURCE.setTestWhileIdle(true); // 指明对象是否需要通过对象驱逐者进行校验（如果有的话），假如一个对象校验失败，则对象将被从池中释放。
DATA_SOURCE.setTestOnBorrow(true); // 指明在从池中租借对象时是否要进行校验，如果对象校验失败，则对象将从池子释放，然后我们将尝试租借另一个。
DATA_SOURCE.setTestOnReturn(true); // 指明在将对象归还给连接池前是否需要校验。
DATA_SOURCE.setValidationQuery("select 1"); // 在连接池返回连接给调用者前用来进行连接校验的查询sql。
DATA_SOURCE.setValidationQueryTimeout(3); // 确认连接有效SQL的执行查询超时时间（秒）。
DATA_SOURCE.setTimeBetweenEvictionRunsMillis(60000); // 每60秒运行一次空闲连接回收器
DATA_SOURCE.setNumTestsPerEvictionRun(20); // 设定在进行后台对象清理时，每次检查几个链接。默认值是3，如果numTestsPerEvictionRun>=0, 则取numTestsPerEvictionRun 和池内的链接数 的较小值 作为每次检测的链接数。
String msg = "dbcp init: [" + ConfigHelper.DBCP_MaxIdle + ":" + ConfigHelper.getMaxTotal()
+ "-" + ConfigHelper.DBCP_InitialSize + ":" + ConfigHelper.getInitialSize()
+ "-" + ConfigHelper.DBCP_MaxIdle + ":" + ConfigHelper.getMaxIdle()
+ "-" + ConfigHelper.DBCP_MinIdle + ":" + ConfigHelper.getMinIdle()
+ "-" + ConfigHelper.DBCP_MaxWaitMillis + ":" + ConfigHelper.getMaxWaitMillis()
+ "]";
LOGGER.debug(msg);
}
// 获取数据库连接
public static Connection getConnection() {
Connection conn = null;
try {
conn = DATA_SOURCE.getConnection();
} catch (SQLException e) {
LOGGER.error("get connection failure", e);
}
return conn;
}
// ①ArrayHandler 将查询结果的第一行数据，保存到Object数组中
public static Object[] queryArray(String sql, Object... params) {
Object[] resultArray;
try {
Connection conn = getConnection();
resultArray = QUERY_RUNNER.query(conn, sql, new ArrayHandler(), params);
conn.close();
} catch (SQLException e) {
LOGGER.error("query array failure", e);
throw new RuntimeException(e);
}
return resultArray;
}
// ②ArrayListHandler 将查询的结果，每一行先封装到Object数组中，然后将数据存入List集合
public static List<Object[]> queryArrayList(String sql, Object... params) {
List<Object[]> resultArrayList;
try {
Connection conn = getConnection();
resultArrayList = QUERY_RUNNER.query(conn, sql, new ArrayListHandler(), params);
conn.close();
} catch (SQLException e) {
LOGGER.error("query array list failure", e);
throw new RuntimeException(e);
}
return resultArrayList;
}
// ③BeanHandler 将查询结果的第一行数据，封装到entityClass对象
public static <T> T queryBean(Class<T> entityClass, String sql, Object... params) {
T entity;
try {
Connection conn = getConnection();
entity = QUERY_RUNNER.query(conn, sql, new BeanHandler<T>(entityClass), params);
conn.close();
} catch (SQLException e) {
LOGGER.error("query entity failure", e);
throw new RuntimeException(e);
}
return entity;
}
// ④BeanListHandler 将查询结果的每一行封装到entityClass对象，然后再存入List集合
public static <T> List<T> queryBeanList(Class<T> entityClass, String sql, Object... params) {
List<T> entityList;
try {
Connection conn = getConnection();
entityList = QUERY_RUNNER.query(conn, sql, new BeanListHandler<T>(entityClass), params);
conn.close();
} catch (SQLException e) {
LOGGER.error("query entity list failure", e);
throw new RuntimeException(e);
}
return entityList;
}
// ⑤ColumnListHandler 将查询结果的指定列的数据封装到List集合中
public static <T> List<T> queryColumnList(String sql, Object... params) {
List<T> list;
try {
Connection conn = getConnection();
list = QUERY_RUNNER.query(conn, sql, new ColumnListHandler<T>(), params);
conn.close();
} catch (SQLException e) {
LOGGER.error("query list failure", e);
throw new RuntimeException(e);
}
return list;
}
// ⑥MapHandler 将查询结果的第一行数据封装到map结合（key==列名，value==列值）
public static Map<String, Object> queryMap(String sql, Object... params) {
Map<String, Object> resultMap;
try {
Connection conn = getConnection();
resultMap = QUERY_RUNNER.query(conn, sql, new MapHandler(), params);
conn.close();
} catch (SQLException e) {
LOGGER.error("query map failure", e);
throw new RuntimeException(e);
}
return resultMap;
}
// ⑦MapListHandler 将查询结果的每一行封装到map集合（key==列名，value==列值），再将map集合存入List集合
public static List<Map<String, Object>> queryMapList(String sql, Object... params) {
List<Map<String, Object>> resultMapList;
try {
Connection conn = getConnection();
resultMapList = QUERY_RUNNER.query(conn, sql, new MapListHandler(), params);
conn.close();
} catch (SQLException e) {
LOGGER.error("query map list failure", e);
throw new RuntimeException(e);
}
return resultMapList;
}
// ⑧BeanMapHandler 将查询结果的每一行数据，封装到entityClass对象，再存入map集合中（key==列名，value==列值）
public static <T> Map<Integer, T> queryBeanMap(Class<T> entityClass, String sql, Object... params) {
Map<Integer, T> resultMap;
try {
Connection conn = getConnection();
resultMap = QUERY_RUNNER.query(conn, sql, new BeanMapHandler<Integer, T>(entityClass, 1), params);
conn.close();
} catch (SQLException e) {
LOGGER.error("query map failure", e);
throw new RuntimeException(e);
}
return resultMap;
}
// ⑨KeyedHandler 将查询的结果的每一行数据，封装到map1（key==列名，value==列值 ），再为其指定key添加到存入map2集合（只有一个）
public static <T> Map<Integer, Map<String, Object>> queryKeyed(Class<T> entityClass, String sql, Object... params) {
Map<Integer, Map<String, Object>> resultMap;
try {
Connection conn = getConnection();
resultMap = QUERY_RUNNER.query(conn, sql, new KeyedHandler<Integer>(1), params);
conn.close();
} catch (SQLException e) {
LOGGER.error("query map failure", e);
throw new RuntimeException(e);
}
return resultMap;
}
// ⑩ScalarHandler 封装类似count、avg、max、min、sum......函数的执行结果
public static <T> T queryScalar(String sql, Object... params) {
T obj;
try {
Connection conn = getConnection();
obj = QUERY_RUNNER.query(conn, sql, new ScalarHandler<T>(), params);
conn.close();
} catch (SQLException e) {
LOGGER.error("query failure", e);
throw new RuntimeException(e);
}
return obj;
}
// 执行更新语句（包括：update、insert、delete）
public static int update(String sql, Object... params) {
int rows;
try {
Connection conn = getConnection();
rows = QUERY_RUNNER.update(conn, sql, params);
conn.close();
} catch (SQLException e) {
LOGGER.error("execute update failure", e);
throw new RuntimeException(e);
}
return rows;
}
// 测试
public static void main(String[] args) {
System.out.println("①queryArray----------------------");
Object[] result = DatabaseHelper.queryArray("select * from app_users");
System.out.println(result);
System.out.println("----------------------");
System.out.println(Arrays.toString(result));
System.out.println("②queryArrayList----------------------");
List<Object[]> list = DatabaseHelper.queryArrayList("select * from app_users");
for (Object[] obj : list) {
System.out.println(Arrays.toString(obj));
}
System.out.println("③queryBean----------------------");
User user = DatabaseHelper.queryBean(User.class, "select * from app_users where user_id = 1");
System.out.println(user.getUser_code() + " " + user.getUser_name());
System.out.println("④queryBeanList----------------------");
List<User> entityList = DatabaseHelper.queryBeanList(User.class, "select * from app_users");
for (User uu : entityList) {
System.out.println(uu.getUser_code());
}
System.out.println("⑤queryColumnList----------------------");
List<String> columnList = DatabaseHelper.queryColumnList("select user_code from app_users");
for (String name : columnList) {
System.out.println(name.toString());
}
System.out.println("⑥queryMap----------------------");
Map<String, Object> data = DatabaseHelper.queryMap("select * from app_users");
for (String key : data.keySet()) {
System.out.println(data.get(key));
}
System.out.println("⑦queryMapList----------------------");
List<Map<String, Object>> dataList = DatabaseHelper.queryMapList("select * from app_users");
for (Map<String, Object> map : dataList) {
for (String key : map.keySet()) {
System.out.println(map.get(key));
}
}
System.out.println("⑧queryBeanMap----------------------");
Map<Integer, User> resultMap = DatabaseHelper.queryBeanMap(User.class, "select * from app_users");
for (Integer key : resultMap.keySet()) {
User u = resultMap.get(key);
System.out.println(u.getUser_code() + " " + u.getUser_name());
}
System.out.println("⑨KeyedHandler----------------------");
Map<Integer, Map<String, Object>> resultKeyed = DatabaseHelper.queryKeyed(User.class,
"select * from app_users");
for (Integer key : resultKeyed.keySet()) {
User u = resultMap.get(key);
System.out.println(u.getUser_code() + " " + u.getUser_name());
}
System.out.println("⑩queryScalar----------------------");
long num = DatabaseHelper.queryScalar("select count(*) from app_users");
System.out.println(num);
}
}