使用QueryRunner完成数据库的增删改查操作

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

  代码中Customer类的构造查看：针对单个表进行通用的查询操作_qq_46053741的博客-CSDN博客

代码中DBCPUtil类的构造查看：封装数据库的连接关闭操作_qq_46053741的博客-CSDN博客

对于增删改操作：

public void testInsert(){
		Connection conn=null;
		try {
			QueryRunner runner=new QueryRunner();
			conn = DBCPUtil.testConnection();
			String sql="insert into customers(name,email,birth)value(?,?,?)";
			int flag=runner.update(conn, sql, "蔡徐坤","caixukun@qq.com","2000-11-22");
			if(flag==1)
				System.out.println("操作成功");
			else
				System.out.println("操作失败");
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("操作失败");
		}finally{
			DBCPUtil.connectionClose(conn, null);
		}
		
	}

 2、 BeanHandler:是ResultSetHandler接口的实现类，用于封装表中的一条记录
     查询操作，返回一条结果

	public void Query1(){
		Connection conn=null;
		try {
			QueryRunner runner=new QueryRunner();
			conn=DBCPUtil.testConnection();
			String sql="select id,name,email,birth from customers where id=?";
			BeanHandler<Customer> handler=new BeanHandler<>(Customer.class);
			Customer cust=runner.query(conn, sql, handler, 10);
			if(cust!=null)
				System.out.println(cust.toString());
			else
				System.out.println("不存在");
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("操作失败");
		}finally{
			DBCPUtil.connectionClose(conn, null);
		}
	}

3、BeanListHandler:是ResultSetHandler接口的实现类，用于封装表中的多条记录构成的集合
    查询操作，返回多条结果

	public void Query2(){
		Connection conn=null;
		try {
			QueryRunner runner=new QueryRunner();
			conn=DBCPUtil.testConnection();
			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, 10);
			if(list!=null)
				list.forEach(System.out::print);
			else
				System.out.println("不存在");
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("操作失败");
		}finally{
			DBCPUtil.connectionClose(conn, null);
		}
	}

4、MapHandler:是ResultSetHandler接口的实现类，对应表中的一条记录
     将字段及相应字段的值作为map中的key和value
     查询操作，返回一条结果

	public void Query3(){
		Connection conn=null;
		try {
			QueryRunner runner=new QueryRunner();
			conn=DBCPUtil.testConnection();
			String sql="select id,name,email,birth from customers where id=?";
			MapHandler handler=new MapHandler();
			Map<String,Object> cust=runner.query(conn, sql, handler, 10);
			if(cust!=null)
				System.out.println(cust);
			else
				System.out.println("不存在");
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("操作失败");
		}finally{
			DBCPUtil.connectionClose(conn, null);
		}
	}

5、MapListHandler:是ResultSetHandler接口的实现类，对应表中的多条记录
     将字段及相应字段的值作为map中的key和value。将这些map添加到List中
     查询操作，返回多条结果

	public void Query4(){
		Connection conn=null;
		try {
			QueryRunner runner=new QueryRunner();
			conn=DBCPUtil.testConnection();
			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, 10);
			if(list!=null)
				list.forEach(System.out::println);
			else
				System.out.println("不存在");
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("操作失败");
		}finally{
			DBCPUtil.connectionClose(conn, null);
		}
	}

6、ScalarHandler :用于查询特殊值

	public void Query5(){
		Connection conn=null;
		long count=-1;
		try {
			QueryRunner runner=new QueryRunner();
			conn=DBCPUtil.testConnection();
			String sql="select count(*) from customers";
			ScalarHandler handler=new ScalarHandler();
			count=(long)runner.query(conn, sql, handler);
			if(count>=0)
				System.out.println("count:"+count);
			else
				System.out.println("不存在");
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("操作失败");
		}finally{
			DBCPUtil.connectionClose(conn, null);
		}
	}