DBUtils 查询操作的九种结果集处理

DBUtils 查询操作的九种结果集处理

一.数据准备

1. 数据库data5

2. 数据表emp

 empno  ename      gender  job         sal  hiredate    deptno  
------  ---------  ------  ---------  ------  ----------  --------
     1  张三         男       程序员         35000  2017-07-11         1
     2  李四         男       程序员         50000  2014-05-12         1
     3  王小丫        女       会计           8000  2016-06-21         2
     4  大娟         女       人事          10000  2013-02-20         2

     5  小强         男       销售          15000  2010-07-06         3

3.c3p0Utils工具类(获取连接对象,数据源,提供关闭资源方法)

public class C3P0Utils {
	static ComboPooledDataSource dataSource = new ComboPooledDataSource();
	
	//返回DataSource接口实现类对象
	public static DataSource getDataSource() {
		return dataSource;
	}
	
	//获取连接对象conn
	public static Connection getConnection() throws SQLException {
		return dataSource.getConnection();
	}
	
	//关闭资源
	public static void close(Connection conn,Statement stat,ResultSet rs) {
		if(rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(stat != null) {
			try {
				stat.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}

           4.c3p0-config.xml 配置文件

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
	<default-config>
	    <!-- 
	                         配置数据库连接四大信息
	        property配置信息
	                        属性name,属性值,四大信息的键名
	                        标签体: 四大信息的值
	     -->
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/data5</property>
		<property name="user">root</property>
		<property name="password">1234</property>
	</default-config> 
</c3p0-confi>

           5.javaBean类 (Employee): 私有字段对应数据表,提供get/set方法,无参构造,重写toString(方便打印)

public class Employee {
	private int empno;
	private String ename;
	private String gender;
	private String job;
	private double sal;
	private Date hiredate;
	private int deptno;
	
	public int getEmpno() {
		return empno;
	}
	public void setEmpno(int empno) {
		this.empno = empno;
	}
	public String getEname() {
		return ename;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public String getGender() {
		return gender;
	}
	public void setGender(String gender) {
		this.gender = gender;
	}
	public String getJob() {
		return job;
	}
	public void setJob(String job) {
		this.job = job;
	}
	public double getSal() {
		return sal;
	}
	public void setSal(double sal) {
		this.sal = sal;
	}
	public Date getHiredate() {
		return hiredate;
	}
	public void setHiredate(Date hiredate) {
		this.hiredate = hiredate;
	}
	public int getDeptno() {
		return deptno;
	}
	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}
	@Override
	public String toString() {
		return "Employee [empno=" + empno + ", ename=" + ename + ", gender=" + gender + ", job=" + job + ", sal=" + sal
				+ ", hiredate=" + hiredate + ", deptno=" + deptno + "]";
	}
	
}

二.编写方法分别测试 ResultSetHandler 的九种实现类(对结果集的不同处理)

public class MainApp {
	/**
	 * c3p0_DBUtils的Query方法(九种)
	 * 
	 * ResultSetHandler结果集处理类
	 * @throws SQLException 
	 * */
	public static void main(String[] args) throws SQLException {
		KeyedHandler();
	}
	
	/**
	 * 第一种ResultSetHandler实现类:ArrayHandler 返回查询结果中的一行数据
	 * */
	public static void arrayHandler() throws SQLException {
		//创建QueryRunner对象,构造方法中传递DataSource实现类对象
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		//拼写SQL语句
		String sql = "SELECT * FROM emp";
		//调用query方法,放回结果的第一行数据
		Object[] objs = qr.query(sql, new ArrayHandler());
		for (Object obj : objs) {
			System.out.print(obj+"\t");
		}
	}
	
	/**
	 * 第二种实现类 : ArrayListHandler
	 * 		每行数据都对应存放在一个Object[] 数组中,多行数据的多个Object[]数组存放在集合中 
	 * @throws SQLException 
	 * */
	public static void arrayListHandler() throws SQLException {
		//创建QueryRunner 对象,构造方法中传入DataSource实现类对象
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		//拼写SQL语句
		String sql = "SELECT * FROM emp";
		//qr调用query方法执行SQL
		List<Object[]> list = qr.query(sql, new ArrayListHandler());
		
		//遍历集合打印数据
		for (Object[] objs : list) {
			for (Object obj : objs) {
				System.out.print(obj+"\t");
			}
			System.out.println();
		}
	}
	
	/**
	 * 第三种实现类:BeanHandler(重要)
	 * 		将查询结果的第一行存储到javaBean对象中
	 * @throws SQLException 
	 * */
	public static void beanHandler() throws SQLException {
		//创建QueryRunner对象
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		//拼写SQL语句
		String sql = "SELECT * FROM emp";
		//调用query方法执行SQL,BeanHandler的构造方法中传入javaBean类的Class对象
		Employee emp = qr.query(sql, new BeanHandler<Employee>(Employee.class));
		//打印数据
		System.out.println(emp);
	}
	
	/**
	 * 第四种实现类:BeanListHandler(非常重要)
	 * @throws SQLException 
	 * */
	public static void beanListHandler() throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		String sql = "SELECT * FROM emp";
		List<Employee> list = qr.query(sql, new BeanListHandler<Employee>(Employee.class));
		for (Employee emp : list) {
			System.out.println(emp);
		}
	}
	
	/**
	 * 第五种实现类:ScalarHandler(重要)
	 * 		适合单个查询,一个select查询的结果只有一个值
	 * @throws SQLException 
	 * */
	public static void scalarHandler() throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		String sql = "SELECT AVG(sal) as '平均工资' FROM emp";
		//ScalarHandler<返回值类型>(列名);
		Object obj = qr.query(sql, new ScalarHandler<Object>("平均工资"));
		System.out.println(obj);
	}
	
	/**
	 * 第六种实现类:ColumnListHandler
	 * 		用于返回一个列的信息,存储集合中
	 * @throws SQLException 
	 * */
	public static void columnListHandler() throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		String sql = "SELECT ename as '姓名' FROM emp";
		List<Object> list = qr.query(sql, new ColumnListHandler<Object>("姓名"));
		for (Object obj : list) {
			System.out.println(obj);
		}
	}

	/**
	 * 第七种实现类:MapHandler
	 * 		查询结果中的第一行,存储map集合
	 * 		Map<K,V>
	 * 		K:列名
	 * 		V:列中的值
	 * @throws SQLException 
	 * */
	public static void mapHandler() throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		String sql = "SELECT * FROM emp";
		Map<String, Object> map = qr.query(sql, new MapHandler());
		//遍历map
		for (Object key : map.keySet()) {
			System.out.println(key+ "\t" +map.get(key));
		}
	}

	/**
	 * 第八种实现类:MapListHandler
	 * 		将查询出的每一行数据存储到map集合中,再将每一个map集合存储list集合中
	 * @throws SQLException 
	 * */
	public static void mapListHandler() throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		String sql = "SELECT * FROM emp";
		List<Map<String,Object>> list = qr.query(sql, new MapListHandler());
		//遍历list,再遍历map
		for (Map<String, Object> map : list) {
			for (Object key : map.keySet()) {
				System.out.print(key+"\t"+map.get(key)+"\t");
			}
			System.out.println();
		}
	}
	
	/**
	 * 第九种实现类:KeyedHandler
	 * 		查询的每一行数据存储到map集合中,将多个map集合再存储到map中
	 * @throws SQLException 
	 * */
	public static void KeyedHandler() throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		String sql = "SELECT * FROM emp";
		Map<Object, Map<String, Object>> map = qr.query(sql, new KeyedHandler<>());
		//遍历map(两层)
		for(Object obj : map.keySet()) {
			for(String key : map.get(obj).keySet()) {
				System.out.print(key+"\t"+map.get(obj).get(key)+"\t");
			}
			System.out.println();
		}
	}
}