DBUtils 查询操作的九种结果集处理
一.数据准备
-
数据库data5
-
数据表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();
}
}
}