基本介绍
Commons DbUtils是Apache组织提供的一个对JDBC进行简单封装的开源工具类库,使用它能够简化JDBC应用程序的开发,同时也不会影响程序的性能。
DBUtils是java编程中的数据库操作实用工具,小巧简单实用,
- 对于数据表的读操作,他可以把结果转换成List,Array,Set等java集合,便于程序员操作;
- 对于数据表的写操作,也变得很简单(只需写sql语句)
- 可以使用数据源,使用JNDI,数据库连接池(dbcp,c3p0)等技术来优化性能--重用已经构建好的数据库连接对象,而不像php,asp那样,费时费力的不断重复的构建和析构这样的对象。
DbUtils的类和接口API
- QueryRunner查询执行类
QueryRunner() Constructor for QueryRunner. |
---|
QueryRunner(boolean pmdKnownBroken) Constructor for QueryRunner that controls the use of |
QueryRunner(DataSource ds) Constructor for QueryRunner that takes a |
QueryRunner(DataSource ds, boolean pmdKnownBroken) Constructor for QueryRunner that takes a |
QueryRunner(DataSource ds, boolean pmdKnownBroken,StatementConfiguration stmtConfig) Constructor for QueryRunner that takes a |
QueryRunner(DataSource ds, StatementConfiguration stmtConfig) Constructor for QueryRunner that takes a |
QueryRunner(StatementConfiguration stmtConfig) Constructor for QueryRunner that takes a |
Modifier and Type | Method and Description |
int[] | batch(Connection conn, String sql, Object[][] params) Execute a batch of SQL INSERT, UPDATE, or DELETE queries. |
int[] | batch(String sql, Object[][] params) Execute a batch of SQL INSERT, UPDATE, or DELETE queries. |
int | execute(Connection conn, String sql, Object... params) Execute an SQL statement, including a stored procedure call, which does not return any result sets. |
<T> List<T> | execute(Connection conn, String sql,ResultSetHandler<T> rsh, Object... params) Execute an SQL statement, including a stored procedure call, which returns one or more result sets. |
int | execute(String sql, Object... params) Execute an SQL statement, including a stored procedure call, which does not return any result sets. |
<T> List<T> | execute(String sql, ResultSetHandler<T> rsh,Object... params) Execute an SQL statement, including a stored procedure call, which returns one or more result sets. |
<T> T | insert(Connection conn, String sql,ResultSetHandler<T> rsh) Execute an SQL INSERT query without replacement parameters. |
<T> T | insert(Connection conn, String sql,ResultSetHandler<T> rsh, Object... params) Execute an SQL INSERT query. |
<T> T | insert(String sql, ResultSetHandler<T> rsh) Executes the given INSERT SQL without any replacement parameters. |
<T> T | insert(String sql, ResultSetHandler<T> rsh,Object... params) Executes the given INSERT SQL statement. |
<T> T | insertBatch(Connection conn, String sql,ResultSetHandler<T> rsh, Object[][] params) Executes the given batch of INSERT SQL statements. |
<T> T | insertBatch(String sql, ResultSetHandler<T> rsh, Object[][] params) Executes the given batch of INSERT SQL statements. |
<T> T | query(Connection conn, String sql,ResultSetHandler<T> rsh) Execute an SQL SELECT query without any replacement parameters. |
<T> T | query(Connection conn, String sql,ResultSetHandler<T> rsh, Object... params) Execute an SQL SELECT query with replacement parameters. |
<T> T | query(String sql, ResultSetHandler<T> rsh) Executes the given SELECT SQL without any replacement parameters. |
<T> T | query(String sql, ResultSetHandler<T> rsh,Object... params) Executes the given SELECT SQL query and returns a result object. |
int | update(Connection conn, String sql) Execute an SQL INSERT, UPDATE, or DELETE query without replacement parameters. |
int | update(Connection conn, String sql, Object... params) Execute an SQL INSERT, UPDATE, or DELETE query. |
int | update(Connection conn, String sql, Object param) Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement parameter. |
int | update(String sql) Executes the given INSERT, UPDATE, or DELETE SQL statement without any replacement parameters. |
int | update(String sql, Object... params) Executes the given INSERT, UPDATE, or DELETE SQL statement. |
int | update(String sql, Object param) Executes the given INSERT, UPDATE, or DELETE SQL statement with a single replacement parameter. |
2. ResultSetHandler结果集处理接口
ResultSetHandler用于处理从ResultSet中获取数据库数据,然后按照一定方法,进行二次封装。DBUtils已经提供了一些实现类,我们可可以直接使用这些实现类,当然也可以通过实现ResultSetHandler接口自定义结果集处理方式。
ArrayHandler | 将结果集中的第一条记录封装到一个Object[]数组中, 数组中的每一个元素就是这条记录中的每一个字段的值。 |
ArrayListHandler | 将结果集中的每一条记录都封装到一个Object[]数组中,将这些数组在封装到List集合中。 |
BeanHandler(常用) | 将结果集中第一条记录封装到一个指定的javaBean中。 |
BeanListHandler(常用) | 将结果集中每一条记录封装到指定的javaBean中,将这些javaBean再封装到List集合中。 |
ColumnListHandler | 将结果集中指定的列的字段值,封装到个List集合中。 |
KeyedHandler | 将结果集中每条记录封装到Map<String,Object> ,在将这个map集合做为另一个Map 的value, 另一个Map集合的key是指定的字段值。 |
KeyedHandler | 将结果集中每条记录封装到Map<String,Object> ,在将这个map集合做为另一个Map 的value, 另一个Map集合的key是指定的字段值。 |
MapHandler | 将结果集中第一条记录封装到了Map<String,Object>集合中,key就是字段名称,value就是字段值。 |
MapListHandler | 将结果集中每条记录封装到了Map<String,Object>集合中,key就是字段名称,value就是字段值,在将这些Map封装到List集合中。 |
ScalarHandler | 它是用于单数据。 例如select count(*) from表操作。 |
DbUtils数据库工具类
DBUtils提供了close(关闭)、closeQuielty(关闭不抛异常)、commitAndClose(提交并关闭)、comitAndCloseQuietly(提交并关闭不抛异常)、loadDriver(加载驱动)、printStackTrace(打印异常栈追踪消息)、printWarnings(打印警告)、rollback(回滚)、rollbackAndClose(回滚并关闭)、rollbackAndCloseQuietly(回滚并关闭不抛异常)。
static void | close(Connection conn) Close a |
static void | close(ResultSet rs) Close a |
static void | close(Statement stmt) Close a |
static void | closeQuietly(Connection conn) Close a |
static void | closeQuietly(Connection conn,Statement stmt, ResultSet rs) Close a |
static void | closeQuietly(ResultSet rs) Close a |
static void | closeQuietly(Statement stmt) Close a |
static void | commitAndClose(Connection conn) Commits a |
static void | commitAndCloseQuietly(Connection conn) Commits a |
static boolean | loadDriver(ClassLoader classLoader,String driverClassName) Loads and registers a database driver class. |
static boolean | loadDriver(String driverClassName) Loads and registers a database driver class. |
static void | printStackTrace(SQLException e) Print the stack trace for a SQLException to STDERR. |
static void | printStackTrace(SQLException e,PrintWriter pw) Print the stack trace for a SQLException to a specified PrintWriter. |
static void | printWarnings(Connection conn) Print warnings on a Connection to STDERR. |
static void | printWarnings(Connection conn,PrintWriter pw) Print warnings on a Connection to a specified PrintWriter. |
static void | rollback(Connection conn) Rollback any changes made on the given connection. |
static void | rollbackAndClose(Connection conn) Performs a rollback on the |
static void | rollbackAndCloseQuietly(Connection conn) Performs a rollback on the |
测试实例
package com.test;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Set;
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.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import com.alibaba.druid.pool.DruidDataSource;
import com.utils.ConnectionManager;
import com.utils.Dep;
public class TestDbutils {
public static QueryRunner run = null;
public static void init()
{
//druid 的 连接池
DruidDataSource dds = ConnectionManager.ds;
//获取查询对象
run = new QueryRunner(dds);
}
// 测试查询所有部门(使用BeanListHandler)
public static void getAllUsersByBeanListHandler()
{
try {
// 创建SQL语句
String sql = "select *from dep";
// 创建Bean列表处理器
BeanListHandler<Dep> beanlist = new BeanListHandler<>(Dep.class);
// 执行查询
List<Dep> deps = run.query(sql, beanlist);
for (Dep dep : deps) {
System.out.println(dep);
}
} catch (Exception e) {
e.printStackTrace();
}
}
// 测试查询指定ID的部门(使用BeanHandler)
public static void getUserByIdByBeanHandler()
{
try {
String sql = "select *from dep where id=?";
BeanListHandler<Dep> beanHandler = new BeanListHandler<Dep>(Dep.class);
Object[] params = {1};
List<Dep> deps = run.query(sql, beanHandler,params);
for (Dep dep : deps) {
System.out.println(dep);
}
} catch (Exception e) {
e.printStackTrace();
}
}
// 测试查询所有部门(使用MapListHandler)
public static void getAllUsersByMapListHandler()
{
try {
// 创建SQL语句
String sql = "select *from dep";
// 创建Bean列表处理器
MapListHandler maplist = new MapListHandler();
// 执行查询
List<Map<String,Object>> deps = run.query(sql, maplist);
for (Map<String,Object> dep : deps) {
System.out.println(dep);
}
} catch (Exception e) {
e.printStackTrace();
}
}
// 测试查询所有部门的姓名(使用ArrayListHandler)
public static void getAllUserByArrayListHandler()
{
try {
String sql = "select *from dep";
List<Object[]> deps = run.query(sql, new ArrayListHandler());
for (Object[] dep : deps) {
System.out.println(dep[1]);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
// 测试查询指定ID的用户(使用ArrayHandler)
public static void getUserByIdByArrayHandler()
{
try {
String sql = "select *from dep where id=?";
Object[] deps = run.query(sql, new ArrayHandler(),1);
System.out.println(deps[0]+"----"+deps[1]);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
// 测试查询所有用户的姓名(使用ColumnHandler)
public static void getAllUserNameByColumnHandler()
{
try {
String sql = "select *from dep";
// 列列表处理器,指定相应列,返回列表
ColumnListHandler columnList = new ColumnListHandler("dname");
List<Object> names = (List<Object>) run.query(sql,columnList);
for (Object object : names)
{
System.out.println(object);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
// 测试查询所有用户的姓名(使用KeyedHandler)
public static void getAllUserByKeyedHandler()
{
try {
String sql = "select *from dep";
KeyedHandler keyedHandler = new KeyedHandler("id");
Map<Object,Map<String,Object>> deps = (Map<Object, Map<String, Object>>) run.query(sql, keyedHandler);
Set<Object> it = deps.keySet();
for (Object key : it)
{
System.out.println(deps.get(key));
for (String str : deps.get(key).keySet()) {
System.out.println(deps.get(key).get(str));
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
// 测试查询一共有多少用户(使用ScalarHandler)
public static void getAllUserNumberBy()
{
try {
String sql = "select count(*) from dep";
// 返回单行单列
ScalarHandler scalarHandler = new ScalarHandler();
Object number = run.query(sql, scalarHandler);
System.out.println(number);
} catch (SQLException e) {
e.printStackTrace();
}
}
// 测试插入一个用户
public void insertUser() {
try {
String sql = "insert into dep(dname) values(?)";
int updateNum = run.update(sql, "人力");
System.out.println(updateNum);
} catch (SQLException e) {
e.printStackTrace();
}
}
// 测试更新一个用户
public void updateUserById() {
try {
String sql = "update dep set dname=? where id=?";
Object[] params = {"技术2", "4"};
int updateNum = run.update(sql, params);
System.out.println(updateNum);
} catch (SQLException e) {
e.printStackTrace();
}
}
// 测试删除一个用户
public void deleteUserById() {
try {
String sql = "delete from dep where id=?";
Object[] params = {"4"};
int updateNum = run.update(sql, params);
System.out.println(updateNum);
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
System.out.println("********************************************************");
init();
System.out.println("********************************************************");
getAllUsersByBeanListHandler();
System.out.println("********************************************************");
getUserByIdByBeanHandler();
System.out.println("********************************************************");
getAllUsersByMapListHandler();
System.out.println("********************************************************");
getAllUserByArrayListHandler();
System.out.println("********************************************************");
getUserByIdByArrayHandler();
System.out.println("********************************************************");
getAllUserNameByColumnHandler();
System.out.println("********************************************************");
getAllUserByKeyedHandler();
System.out.println("********************************************************");
getAllUserNumberBy();
System.out.println("********************************************************");
}
}