Java EE开发第八章：JDBC开发之dbutils的使用

前言：通过前几章对jdbc有个大概的了解，但是我们在做项目的时候，我们是不是想有没有更好更强大的工具类呢，这个类既包括了连接池，优化查询的方法，返回处理的结果。ok，今天就来介绍一个jdbc常用的一个强大的工具类dbutils类，他是apache组织的一个工具类,jdbc的框架,更方便我们使用！

---------使用步骤------

1.导入jar包(commons-dbutils-1.4.jar)。注意：是在c3p0类的基础上的。

2.创建一个queryrunner类，queryrunner作用:操作sql语句，构造方法:new QueryRunner(Datasource ds);

3.编写sql

4.执行sql，query(..):执行r操作，update(...):执行cud操作

---------代码Demo------

1.封装一个简单的DataSourceUtils类：

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DataSourceUtils {
	private static ComboPooledDataSource ds = new ComboPooledDataSource();

	/**
	 * 获取数据源
	 * 
	 * @return连接池
	 */
	public static DataSource getDataSource() {
		return ds;
	}

	/**
	 * 获取连接
	 * 
	 * @return
	 * @throws SQLException
	 */
	public static Connection getConnection() throws SQLException {
		return ds.getConnection();
	}

	public static void closeResource(Connection conn, Statement st, ResultSet rs) {
		closeResultSet(rs);
		closeStatement(st);
		closeConn(conn);
	}

	private static void closeConn(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conn = null;
		}
	}

	private static void closeStatement(Statement st) {
		if (st != null) {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			st = null;
		}

	}

	private static void closeResultSet(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs = null;
		}
	}

}

2.配置文件：

3.CURDDemo：

import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;

import com.fly.utils.DataSourceUtils;

public class CURDDemo {

	@Test
	public void insert() throws SQLException {
		// 1创建queryrunner类
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		// 2编写sql
		String sql = "insert into category values(?,?);";
		// 执行sql
		qr.update(sql, "c2025", "厨房电气25");
	}

	public void update() throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());

		String sql = "update category set cname = ? where cid = ?";

		qr.update(sql, "达电器", "c001");
	}
	
	
}

4.运行效果自行查看。

----------QueryRunner类------------

1.作用：操作sql语句

2.构造器：new QueryRunner(Datasource ds);

3.注意：底层帮我们创建连接,创建语句执行者 ,释放资源.

4.常用方法：query(..)、update(..):

---------DbUtils------------

1.释放资源,控制事务 类

2.closeQuietly(conn):内部处理了异常

3.commitAndClose(Connection conn):提交事务并释放连接

---------ResultSetHandler:封装结果集 接口--------

0.javabean：

public class Category {
	private String cid;
	private String cname;

	public String getCid() {
		return cid;
	}

	public void setCid(String cid) {
		this.cid = cid;
	}

	public String getCname() {
		return cname;
	}

	public void setCname(String cname) {
		this.cname = cname;
	}

	@Override
	public String toString() {
		return "Category [cid=" + cid + ", cname=" + cname + "]";
	}
}

1.(了解)ArrayHandler, 将查询结果的第一条记录封装成数组,返回

	@Test
	public void arrayHandler() throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select * from category";
//		String sql = "select * from user";
		Object[] query = qr.query(sql, new ArrayHandler());
		System.out.println(Arrays.toString(query));
	}

2.(了解)ArrayListHandler, 将查询结果的每一条记录封装成数组,将每一个数组放入list中返回

@Test
	public void arrayListHandler() throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select * from category";
		List<Object[]> list = qr.query(sql, new ArrayListHandler());
		for (Object[] obj : list) {
			System.out.println(Arrays.toString(obj));
		}
	}

3.★★BeanHandler, 将查询结果的第一条记录封装成指定的bean对象,返回

@Test
	public void beanHandler() throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select * from category";
		Category bean = qr.query(sql, new BeanHandler<>(Category.class));
		System.out.println(bean);
	}

4. ★★BeanListHandler, 将查询结果的每一条记录封装成指定的bean对象,将每一个bean对象放入list中 返回.

@Test
	public void beanListHandler() throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select * from category";
		List<Category> list = qr.query(sql, new BeanListHandler<>(
				Category.class));
		for (Category bean : list) {
			System.out.println(bean);
		}
	}

5. (了解)ColumnListHandler, 将查询结果的指定一列放入list中返回 

6. (了解)MapHandler, 将查询结果的第一条记录封装成map,字段名作为key,值为value 返回

@Test
	public void mapHandler() throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());

		String sql = "select * from category";

		Map<String, Object> map = qr.query(sql, new MapHandler());
		System.out.println(map);
	}

7. ★MapListHandler, 将查询结果的每一条记录封装map集合,将每一个map集合放入list中返回

@Test
	public void mapListHandler() throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());

		String sql = "select * from category";

		List<Map<String, Object>> list = qr.query(sql, new MapListHandler());
		for (Map<String, Object> map : list) {
			System.out.println(map);
		}
	}

8.★ScalarHandler,针对于聚合函数 例如:count(*) 返回的是一个Long值

@Test
	public void scalarHandler() throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());

		String sql = "select count(*) from category";

		Object obj = qr.query(sql, new ScalarHandler());
		System.out.println(obj.getClass().getName());
	}

--------完--------