dbUtils结果集处理器

1，首先要有c3p0-0.9.2-pre1.jar,commons-dbutils-1.4jar,mcchange-commons-0.2jar,mysql-connector-java-5.1.28-bin.jar包

2,在src同级目录下添加c3p0-config.xml文件 ：

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!-- 这是默认配置信息 -->
    <default-config> 
        <!-- 连接四大参数配置 -->
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="user">root</property>
        <property name="password">abcd@1234</property>
        <!-- 池参数配置 -->
        <property name="acquireIncrement">3</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">2</property>
        <property name="maxPoolSize">10</property>
    </default-config>
    
    <!-- 专门为oracle提供的配置信息 -->
    <named-config name="oracle-config"> 
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/mydb1</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="user">root</property>
        <property name="password">123</property>
        <property name="acquireIncrement">3</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">2</property>
        <property name="maxPoolSize">10</property>
    </named-config>

</c3p0-config>

3，写实体类：person的set，get方法，构造函数，等

4,引入jdbcUtils类：

package cn.itcast.utils;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class JdbcUtils {
    // 配置文件的默认配置！要求你必须给出c3p0-config.xml！！！
    private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
    
    /**
     * 使用连接池返回一个连接对象
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    
    /**
     * 返回连接池对象！
     * @return
     */
    public static DataSource getDataSource() {
        return dataSource;
    }
}

5：结果集处理的几种方法，非常简便，

package cn.itcast.dbutils;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import cn.itcast.jdbc.JdbcUtils;

public class Demo3 {
    @Test
    public void fun1() throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "insert into t_stu values(?,?,?,?)";
        Object[] params = {1002, "liSi", 88, "female"};
        
        qr.update(sql, params);
    }
    
    @Test
    public void fun2() throws SQLException {
        // 创建QueryRunner，需要提供数据库连接池对象
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        // 给出sql模板
        String sql = "select * from t_stu where sid=?";
        // 给出参数
        Object[] params = {1001};
        
//        ResultSetHandler<Stu> rsh = new ResultSetHandler<Stu>() {
//
//            @Override
//            public Stu handle(ResultSet rs) throws SQLException {
//                // TODO Auto-generated method stub
//                return null;
//            }
//        };
        // 执行query()方法，需要给出结果集处理器，即ResultSetHandler的实现类对象
        // 我们给的是BeanHandler，它实现了ResultSetHandler
        // 它需要一个类型，然后它会把rs中的数据封装到指定类型的javabean对象中，然后返回javabean
        Stu stu = qr.query(sql, new BeanHandler<Stu>(Stu.class), params);
        System.out.println(stu);
    }
    
    /**
     * BeanListHandler的应用，它是多行处理器
     * 每行对象一个Stu对象！
     * @throws Exception
     */
    @Test
    public void fun3() throws Exception {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select * from t_stu";
        List<Stu> stuList = qr.query(sql, new BeanListHandler<Stu>(Stu.class));
        
        System.out.println(stuList);
    }
    
    /**
     * MapHandler的应用，它是单行处理器，把一行转换成一个Map对象
     * @throws SQLException 
     */
    @Test
    public void fun4() throws SQLException  {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select * from t_stu where sid=?";
        Object[] params = {1001};
        Map map = qr.query(sql, new MapHandler(), params);
        
        System.out.println(map);
    }
    
    /**
     * MapListHandler，它是多行处理器，把每行都转换成一个Map，即List<Map>
     * @throws SQLException
     */
    @Test
    public void fun5() throws SQLException  {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select * from t_stu";
        List<Map<String,Object>> mapList = qr.query(sql, new MapListHandler());
        
        System.out.println(mapList);
    }
    
    /**
     * ScalarHandler，它是单行单列时使用，最为合适！
     * @throws SQLException
     */
    @Test
    public void fun6() throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select count(*) from t_stu";
        /*
         * Integer、Long、BigInteger
         */
        Number cnt = (Number)qr.query(sql, new ScalarHandler());
        
        long c = cnt.longValue();
        System.out.println(c);
    }
}