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;}//获取连接对象connpublic 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 blocke.printStackTrace();}}if(stat != null) {try {stat.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if(conn != null) {try {conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.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;}@Overridepublic 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方法执行SQLList<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());//遍历mapfor (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,再遍历mapfor (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();}}}