JDBC：Apache-DBUtils实现CRUD操作

Apache-DBUtils实现CRUD操作

1 Apache-DBUtils简介

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

2.API介绍：

• org.apache.commons.dbutils.QueryRunner
• org.apache.commons.dbutils.ResultSetHandler
• 工具类：org.apache.commons.dbutils.DbUtils

2 QueryRunner类及测试

该类简单化了SQL查询，它与ResultSetHandler组合在一起使用可以完成大部分的数据库操作，能够大大减少编码量。

public class QueryRunnerTest {

	//测试插入
	@Test
	public void testInsert(){
		
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			
			conn = JDBCUtils.getConnection3();
			String sql = "insert into customers(name,email,birth) values(?,?,?)";
			int insertCount = runner.update(conn, sql, "蔡徐坤", "caixukun@126.com", "1997-09-08");
			System.out.println("添加了" + insertCount + "条记录");
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
		
			JDBCUtils.closeResource(conn, null, null);
		}
	}
	
	//测试查询
	/*
	 * BeanHandler:是ResultSetHandler接口的实现类，用于封装表中的一条记录。
	 */
	@Test
	public void testQuery1(){
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			
			conn = JDBCUtils.getConnection3();
			
			String sql = "select id,name,email,birth from customers where id = ?";
			BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
			Customer customer = runner.query(conn, sql, handler, 21);
			System.out.println(customer);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
		
			JDBCUtils.closeResource(conn, null, null);
		}
	}
	
	/*
	 * BeanListHandler:是ResultSetHandler接口的实现类，用于封装表中多条记录构成的集合
	 */
	@Test
	public void testQuery2(){
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			
			conn = JDBCUtils.getConnection3();
			
			String sql = "select id,name,email,birth from customers where id < ?";
			BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
			List<Customer> list = runner.query(conn, sql, handler, 21);
			list.forEach(System.out::println);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
				
			JDBCUtils.closeResource(conn, null, null);
		}
	}
	
	//测试查询
	/*
	 * MapHandler:是ResultSetHandler接口的实现类，对应表中的一条记录。
	 * 将字段及相应字段的值作为map中的key和value
	 */
	@Test
	public void testQuery3(){
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			
			conn = JDBCUtils.getConnection3();
			
			String sql = "select id,name,email,birth from customers where id = ?";
			MapHandler handler = new MapHandler();
			Map<String, Object> map = runner.query(conn, sql, handler, 21);
			System.out.println(map);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
		
			JDBCUtils.closeResource(conn, null, null);
		}
	}
	
	/*
	 * MapListHandler:是ResultSetHandler接口的实现类，对应表中的一组记录。
	 * 将字段及相应字段的值作为map中的key和value，将这些map添加到List中
	 */
	@Test
	public void testQuery4(){
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			
			conn = JDBCUtils.getConnection3();
			
			String sql = "select id,name,email,birth from customers where id < ?";
			MapListHandler handler = new MapListHandler();
			List<Map<String,Object>> list = runner.query(conn, sql, handler, 21);
			list.forEach(System.out::println);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
		
			JDBCUtils.closeResource(conn, null, null);
		}
	}
	
	/*
	 * ScalarHandler: 用于查询特殊值：比如表中数据的个数、最大生日等
	 */
	@Test
	public void testQuery5(){
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			
			conn = JDBCUtils.getConnection3();
			
			String sql = "select count(*) from customers";
			ScalarHandler handler = new ScalarHandler();
			Long count = (Long) runner.query(conn, sql, handler);
			System.out.println("customers表中的记录数为: " + count);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
		
			JDBCUtils.closeResource(conn, null, null);
		}
	}
	
	@Test
	public void testQuery6(){
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			
			conn = JDBCUtils.getConnection3();
			
			String sql = "select max(birth) from customers";
			ScalarHandler handler = new ScalarHandler();
			Date date = (Date) runner.query(conn, sql, handler);
			System.out.println("customers表中最大生日为: " + date);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
		
			JDBCUtils.closeResource(conn, null, null);
		}
	}
	
	/*
	 * 自定义ResultHandler实现类
	 */
	@Test
	public void testQuery7(){
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			
			conn = JDBCUtils.getConnection3();
			
			String sql = "select id,name,email,birth from customers where id = ?";
			ResultSetHandler<Customer> handler = new ResultSetHandler<Customer>() {

				@Override
				public Customer handle(ResultSet rs) throws SQLException {
					if(rs.next()){
						int id = rs.getInt("id");
						String name = rs.getString("name");
						String email = rs.getString("email");
						Date birth = rs.getDate("birth");
						
						Customer cust = new Customer(id, name, email, birth);
						return cust;
					}
					
					return null;
				}
			};
			
			Customer customer = runner.query(conn, sql, handler, 19);
			System.out.println(customer);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
		
			JDBCUtils.closeResource(conn, null, null);
		}
	}
	
}