DBCP数据库连接池及Apache-DBUtils实现CRUD操作

DBCP数据库连接池

Druid（德鲁伊）数据库连接池
Druid是阿里巴巴开源平台上一个数据库连接池实现，它结合了C3P0、DBCP、Proxool等DB池的优点，同时加入了
日志监控，可以很好的监控DB池连接和SQL的执行情况，可以说是针对监控而生的DB连接池，可以说是目前最好的
连接池之一。

//使用druid数据库连接池获取数据库连接
@Test
	public void testGetConnection() throws Exception {
		
		Properties pros = new Properties();
//		//方式1
//		InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
//		pros.load(is);
		//方式2
		FileInputStream is = new FileInputStream(new File("src/druid.properties"));
		pros.load(is);
		
		DataSource ds = DruidDataSourceFactory.createDataSource(pros);
		Connection conn = ds.getConnection();
		System.out.println(conn);
	}
	

Apache-DBUtils实现CRUD操作

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

//测试使用QueryRunner插入数据
	@Test
	public void testInsert()  {
		
		 Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			 
			 conn = JDBCUtil.getConnection3();
			 
			 String sql = "insert into customers(name,email,birth)values(?,?,?)";
			 int update = runner.update(conn,sql,"蔡徐坤","caixukun@qq.com","1997-01-24");
			 
			 System.out.println("插入了" + update +"条数据");
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			
			JDBCUtil.closeResourse(conn, null);
		}
		
	}
	
	
	//测试查询
	/*
	 * BeanHandler:是ResultSetHandler接口的实现类，用于封装表中的一条记录
	 */
	@Test
	public void testQuery1() {
		
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			
			conn = JDBCUtil.getConnection3();
			String sql = "select id,name,email,birth from customers where id = ?";
			
			BeanHandler<Customer> handler = new BeanHandler<Customer>(Customer.class);
			Customer cust = runner.query(conn, sql, handler, "20");

			System.out.println(cust);
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.closeResourse(conn, null);
		}
		
	}
	
	/*
	 * BeanListHandler:是ResultSetHandler接口的实现类，用于封装多条记录组成的集合
	 */
	@Test
	public void testQuery2() {
		
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			
			conn = JDBCUtil.getConnection3();
			String sql = "select id,name,email,birth from customers where id < ?";
			
			BeanListHandler<Customer> handler = new BeanListHandler<Customer>(Customer.class);
			List<Customer> list = runner.query(conn, sql, handler, "20");

			list.forEach(System.out :: println);
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.closeResourse(conn, null);
		}
		
	}
	
	/*
	 * MapHandler:是ResultSetHandler接口的实现类，对应表中的一条记录
	 * 将字段及相应字段的值作为map中的key和value
	 */
	@Test
	public void testQuery3() {
		
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			
			conn = JDBCUtil.getConnection3();
			String sql = "select id,name,email,birth from customers where id = ?";
			
			MapHandler handler = new MapHandler();
			Map<String, Object> query = runner.query(conn, sql, handler, "20");

			System.out.println(query);
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.closeResourse(conn, null);
		}
		
	}
	
	
	/*
	 * MapHandler:是ResultSetHandler接口的实现类，对应表中的多条记录
	 * 将字段及相应字段的值作为map中的key和value封装在list中
	 */
	@Test
	public void testQuery4() {
		
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			
			conn = JDBCUtil.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, "20");

			list.forEach(System.out :: println);
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.closeResourse(conn, null);
		}
		
	}

使用DBUtils实现资源的关闭

public static void closeResourse2(Connection conn, PreparedStatement ps,ResultSet rs) {
		
		DbUtils.closeQuietly(conn);
		DbUtils.closeQuietly(ps);
		DbUtils.closeQuietly(rs);
		
		
	}