使用数据源C3P0-config链接数据库，完成java-web开发

1、创建c3p0文件 （此文件必须为c3p0-config.xml）且目录放在resource下面

<?xml version="1.0" encoding="utf-8" ?>
<c3p0-config>
    <default-config>
        <property name="user">nest</property>
        <property name="password">nest</property>
        <property name="jdbcUrl">jdbc:oracle:thin:@10.25.186.217:1521:orcl</property>
        <property name="driverClass">oracle.jdbc.driver.OracleDriver</property>
        ---下边的可以省略--
        <!--初始化连接数-->
        <property name="initialPoolSize">3</property>
        <!--最大连接数-->
        <property name="maxPoolSize">50</property>
        <!--最小连接数-->
        <property name="minPoolSize">2</property>
        <!--最大闲置时间-->
        <property name="maxIdelTime">30</property>
    </default-config>
</c3p0-config>

2、创建获取c3p0的DButil

package com.neu.commons.util;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/*ComboPooledDataSource c3p0链接的专用类*/
public class DBUtil {
    private static ComboPooledDataSource dataSource = new ComboPooledDataSource();

    //返回数据源的方法
    public static DataSource getDataSource() {
        return dataSource;
    }

    //返回Connection链接的方法
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    //定义一个关闭连接的方法
    public static void close(Connection conn, PreparedStatement ps, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                rs = null;
            }
        }
        if (ps!=null){
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                ps=null;
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                conn = null;
            }
        }
    }
}

3、创建BaseDao 实例化DButil类 与数据库完成交互

package com.neu.dao;

import com.neu.commons.util.DBUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class BaseDao {

    protected Connection conn;
    protected PreparedStatement ps;
    protected ResultSet  rs;
    protected  final void getConnection() throws SQLException {
        conn=DBUtil.getConnection();
    }
    protected final void close(){
        DBUtil.close(conn,ps,rs);
    }

    /**
     * 定义一个更新的方法
     */
    protected final int executeUpdate(String sql,Object...params) {
        int row = 0;
        QueryRunner queryRunner=new QueryRunner(DBUtil.getDataSource());
        try {
            row = queryRunner.update(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }
    /**
     * 定义一个查询的方法
     */
    protected final List<Map<String,Object>> executeQuery(String sql,Object...params){
        QueryRunner queryRunner=new QueryRunner(DBUtil.getDataSource());
        List<Map<String,Object>> list=null;
        try {
            list=queryRunner.query(sql,new MapListHandler(),params);

        } catch (SQLException e) {
            throw  new RuntimeException(e);
        }
        return  list;
    }
    //支持批处理的操作，使用同一条语句多次插入数据时，特别是从excel文件中导入数据时
    protected  final int executeBatch(String sql,List<List<String>> list){
        int row=0;
        try {
            getConnection();
            ps=conn.prepareStatement(sql);
            if (list != null) {
                //获取要插入的每一行中的数据
                for (List<String> item : list) {
                    //获取每一行中的每一个？号的值
                    for (int i = 0; i < item.size(); i++) {
                        ps.setObject(i + 1, item.get(i));
                    }
                    //把该赋值后的sql添加批处理中,等待处理
                    ps.addBatch();
                }
            }
            ps.executeBatch();
            row = 1;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close();
        }
        return row;
    }
/*    public static void main(String[] args) {

        String sql = "select * from mgr_info";

        BaseDao baseDao = new BaseDao();

        System.out.println(baseDao.executeQuery(sql));

        System.out.println("==============================wwww");
    }*/
}