目录

一、测试插入

二、测试查询 

查询单条记录

查询多条记录

查询单条记录并且封装为键值

 查询多条记录并且封装为键值

特殊查询：查询表中记录个数 

特殊查询：查询最大日期 

 三、自定义resultsethandler的实现类

四、关闭连接 

---

 

commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库,封装了针对于数据库的增删改查操作

首先导入我们所需要的Dbutils的jar包  

 

一、测试插入

//测试插入
	@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, "薛总司令","siling@126.com","1997-09-08");
			System.out.println("添加了" + insertCount + "条记录");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
 
			JDBCUtils.closeResource(conn, null);
		}
 
	}

 

二、测试查询 

查询单条记录

//测试查询
	/*
	 * BeanHander:是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 handler = new BeanHandler<>(Customer.class);
			Customer customer = runner.query(conn, sql, handler, 23);
			System.out.println(customer);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtils.closeResource(conn, 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  handler = new BeanListHandler<>(Customer.class);
 
			List list = runner.query(conn, sql, handler, 23);
			list.forEach(System.out::println);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
 
			JDBCUtils.closeResource(conn, null);
		}
 
	}

 

查询单条记录并且封装为键值

/*
	 * MapHander:是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 map = runner.query(conn, sql, handler, 23);
			System.out.println(map);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.closeResource(conn, null);
 
		}
 
	}

 查询多条记录并且封装为键值

/*
	 * MapListHander:是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> list = runner.query(conn, sql, handler, 23);
			list.forEach(System.out::println);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.closeResource(conn, null);
 
		}
 
	}

特殊查询：查询表中记录个数 

使用scalarHandler满足特殊查询需求

/*
	 * 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(count);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.closeResource(conn, 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 maxBirth = (Date) runner.query(conn, sql, handler);
			System.out.println(maxBirth);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.closeResource(conn, null);
 
		}
 
	}

 三、自定义resultsethandler的实现类

*
	 * 自定义ResultSetHandler的实现类
	 */
	@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 = ?";
            //我们自己定义了一个handler并且重写了其中的方法
			ResultSetHandler handler = new ResultSetHandler(){
 
				@Override
				public Customer handle(ResultSet rs) throws SQLException {
                    //其实下面的query的返回值，其实就是我们现在这个当前方法的返回值
//					System.out.println("handle");
//					return null;
//					return new Customer(12, "成龙", "Jacky@126.com", new Date(234324234324L));
 
					if(rs.next()){
						int id = rs.getInt("id");
						String name = rs.getString("name");
						String email = rs.getString("email");
						Date birth = rs.getDate("birth");
						Customer customer = new Customer(id, name, email, birth);
						return customer;
					}
					return null;
 
				}
 
			};
			Customer customer = runner.query(conn, sql, handler,23);
			System.out.println(customer);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.closeResource(conn, null);
 
		}
 
	}

四、关闭连接 

之前手动关闭数据库连接池的写法 

/**
	 * 关闭连接和Statement的操作
	 */
	public static void closeResource(Connection conn,Statement ps){
		try {
			if(ps != null)
				ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	/**
	 * 关闭资源操作
	 */
	public static void closeResource(Connection conn,Statement ps,ResultSet rs){
		try {
			if(ps != null)
				ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(rs != null)
				rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

 使用Dbutils工具类实现资源关闭的写法

/**
	 * 使用dbutils.jar中提供的DbUtils工具类，实现资源的关闭
	 */
	public static void closeResource1(Connection conn,Statement ps,ResultSet rs){
		//直接关闭
		try {
			DbUtils.close(conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			DbUtils.close(ps);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			DbUtils.close(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}
 
		//或者是悄无声息地关闭掉，也就是是将上面的try catch一并封装进去
		DbUtils.closeQuietly(conn);
		DbUtils.closeQuietly(ps);
		DbUtils.closeQuietly(rs);
	}