DBUtils学习笔记

基本介绍

 Commons DbUtils是Apache组织提供的一个对JDBC进行简单封装的开源工具类库，使用它能够简化JDBC应用程序的开发，同时也不会影响程序的性能。

     DBUtils是java编程中的数据库操作实用工具，小巧简单实用，

1. 对于数据表的读操作，他可以把结果转换成List，Array，Set等java集合，便于程序员操作；
2. 对于数据表的写操作，也变得很简单（只需写sql语句）
3. 可以使用数据源，使用JNDI，数据库连接池(dbcp,c3p0)等技术来优化性能--重用已经构建好的数据库连接对象，而不像php，asp那样，费时费力的不断重复的构建和析构这样的对象。

DbUtils的类和接口API

1. QueryRunner查询执行类

Constructors

QueryRunner() Constructor for QueryRunner.
QueryRunner(boolean pmdKnownBroken) Constructor for QueryRunner that controls the use of ParameterMetaData.
QueryRunner(DataSource ds) Constructor for QueryRunner that takes a DataSource to use.
QueryRunner(DataSource ds, boolean pmdKnownBroken) Constructor for QueryRunner that takes a DataSource and controls the use of ParameterMetaData.
QueryRunner(DataSource ds, boolean pmdKnownBroken,StatementConfiguration stmtConfig) Constructor for QueryRunner that takes a DataSource, a StatementConfiguration, and controls the use of ParameterMetaData.
QueryRunner(DataSource ds, StatementConfiguration stmtConfig) Constructor for QueryRunner that takes a DataSource to use and a StatementConfiguration.
QueryRunner(StatementConfiguration stmtConfig) Constructor for QueryRunner that takes a StatementConfiguration to configure statements when preparing them.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Method Summary

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 Connection, avoid closing if null.
static void	close(ResultSet rs) Close a ResultSet, avoid closing if null.
static void	close(Statement stmt) Close a Statement, avoid closing if null.
static void	closeQuietly(Connection conn) Close a Connection, avoid closing if null and hide any SQLExceptions that occur.
static void	closeQuietly(Connection conn,Statement stmt, ResultSet rs) Close a Connection, Statement and ResultSet.
static void	closeQuietly(ResultSet rs) Close a ResultSet, avoid closing if null and hide any SQLExceptions that occur.
static void	closeQuietly(Statement stmt) Close a Statement, avoid closing if null and hide any SQLExceptions that occur.
static void	commitAndClose(Connection conn) Commits a Connection then closes it, avoid closing if null.
static void	commitAndCloseQuietly(Connection conn) Commits a Connection then closes it, avoid closing if null and hide any SQLExceptions that occur.
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 Connection then closes it, avoid closing if null.
static void	rollbackAndCloseQuietly(Connection conn) Performs a rollback on the Connection then closes it, avoid closing if null and hide any SQLExceptions that occur.

测试实例

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("********************************************************");
	}

}