1. Apache-DBUtils简介

*commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。

*API介绍：

org.apache.commons.dbutils.QueryRunner

org.apache.commons.dbutils.ResultSetHandler

工具类：org.apache.commons.dbutils.DbUtils

*jar包

2.使用QueryRunner添加数据

 public void testInsert(){Connection conn = null;try {QueryRunner runner = new QueryRunner();conn = JDBCUtiles3.getConnection();String sql="insert into customers(name,email,birth)values(?,?,?)";int insertcount = runner.update(conn, sql, "翟天临", "zhaitianlin@163.com", "1995-01-23");System.out.println("添加了"+insertcount+"条记录");} catch (SQLException e) {e.printStackTrace();} finally {JDBCUtiles.closeResource(conn,null);}}

 3.使用QueryRunner查询操作

@Testpublic void testQuery1(){Connection conn = null;try {QueryRunner runner = new QueryRunner();conn = JDBCUtiles3.getConnection();String sql="select * from customers where id=?";//BeanHandler:是ResultSetHandler接口的实现类，用于封装表中的一条记录BeanHandler handler = new BeanHandler(Customer.class);Customer customer = runner.query(conn, sql, handler, 25);System.out.println(customer);} catch (SQLException e) {e.printStackTrace();} finally {JDBCUtiles.closeResource(conn,null);}}@Testpublic void testQuery2(){Connection conn = null;try {QueryRunner runner = new QueryRunner();conn = JDBCUtiles3.getConnection();String sql="select * from customers where id listHandler = new BeanListHandler<>(Customer.class);List customerList = runner.query(conn, sql, listHandler, 25);System.out.println(customerList);} catch (SQLException e) {e.printStackTrace();} finally {JDBCUtiles.closeResource(conn,null);}}

4.使用QueryRunner查询特殊值

public void testQuery3(){Connection conn = null;try {QueryRunner runner = new QueryRunner();conn = JDBCUtiles3.getConnection();String sql="select count(1) from customers";ScalarHandler handler = new ScalarHandler();Object query = runner.query(conn, sql, handler);System.out.println(query);} catch (SQLException e) {e.printStackTrace();} finally {JDBCUtiles.closeResource(conn,null);}}

4.自定义ResultSetHandler

*使用ResultSetHandler的匿名实现类实现

public void testQuery4(){Connection conn = null;try {QueryRunner runner = new QueryRunner();conn = JDBCUtiles3.getConnection();String sql="select * from customers where id handler = new ResultSetHandler(){//方法重写@Overridepublic Customer handle(ResultSet resultSet) throws SQLException {//resultSet就是结果集//在重写方法中写入对结果集的操作System.out.println("handler");return null;}};Customer query = runner.query(conn, sql, handler, 25);System.out.println(query);} catch (SQLException e) {e.printStackTrace();} finally {JDBCUtiles.closeResource(conn,null);}}

5. 使用DbUtils工具类实现资源的关闭

package utils;import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.commons.dbutils.DbUtils;import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;public class JDBCUtiles3 {private static DataSource source;static {try {Properties pros=new Properties();InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");pros.load(is);source= DruidDataSourceFactory.createDataSource(pros);} catch (Exception e) {e.printStackTrace();}}public static Connection getConnection() throws SQLException {return source.getConnection();}//使用DbUtils工具类实现资源的关闭public static void closeResource(Connection conn, Statement ps, ResultSet rs){DbUtils.closeQuietly(conn);DbUtils.closeQuietly(ps);DbUtils.closeQuietly(rs);}}