连接池（C3P0，DBCP，Druid）-蒲公英云

一、C3P0连接池

c3p0-config.xml（放在resources资源里）

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>  
   <!--配置连接池mysql-->
    <named-config name="mysql">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/db1?characterEncoding=UTF-8</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">10</property>
    </named-config>
    <!--配置连接池2,可以配置多个-->
</c3p0-config>

C3P0工具类（放在utils包下）

public class C3P0Utils {
//    //1.创建连接池对象
    //使用指定的配置
    public static ComboPooledDataSource dataSource=new ComboPooledDataSource("mysql");
    //获取连接的方法
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    //释放资源的方法
    public static void close(Connection connection, Statement statement) throws SQLException {
        if(connection!=null &&statement!=null){
            statement.close();
            statement.close();
        }
    }
    public static void close(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
        if(connection!=null &&statement!=null && resultSet!=null){
            resultSet.close();
            statement.close();
            statement.close();
        }
    }
}

通过C3P0连接池向数据库插入数据

public class Test01 {
    public static void main(String[] args) throws SQLException {
        //1.从连接池拿到对象
        Connection connection= DBCPUtils.getConnection();
        //2.获取statement对象
        String sql="insert into user values (?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //从用户那里拿到输入的语句
        Scanner s=new Scanner(System.in);
        System.out.println("请输入要插入的用户名");
        String name=s.next();
        System.out.println("请输入差的用户名的密码：");
        String password=s.next();
        // 设置参数
        preparedStatement.setString(1,name);
        preparedStatement.setString(2,password);
        //执行sql语句
        preparedStatement.executeUpdate();
        //关闭流操作
        DBCPUtils.close(connection,preparedStatement);
    }
}

二、DBCP连接池

DBCP工具类

public class DBCPUtils {
    //1. 定义常量
    public static final String DRIVERNAME="com.mysql.jdbc.Driver";
    public static final String URL="jdbc:mysql://localhost:3306/db1?characterEncoding=UTF-8";
    public static final String USER="root";
    public static final String PASSWORD="root";
    //2.创建连接池对象
    public static BasicDataSource dataSource=new BasicDataSource();
    //3.使用静态代码块进行配置
    static{
        dataSource.setDriverClassName(DRIVERNAME);
        dataSource.setUrl(URL);
        dataSource.setUsername(USER);
        dataSource.setPassword(PASSWORD);
    }
    //4.连接
    public static Connection getConnection() throws SQLException {
        Connection connection=dataSource.getConnection();
        return connection;
    }
    //5.关闭流
    public static void close(Connection connection, Statement statement) throws SQLException {
        if(connection!=null && statement!=null){
            statement.close();
            connection.close();
        }
    }
    public static void close(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
        if(connection!=null && statement!=null && resultSet!=null){
            resultSet.close();
            statement.close();
            connection.close();
        }
    }
}

通过DBCP连接池登录数据库

public class DBCPUtils {
    //1. 定义常量
    public static final String DRIVERNAME="com.mysql.jdbc.Driver";
    public static final String URL="jdbc:mysql://localhost:3306/db1?characterEncoding=UTF-8";
    public static final String USER="root";
    public static final String PASSWORD="root";
    //2.创建连接池对象
    public static BasicDataSource dataSource=new BasicDataSource();
    //3.使用静态代码块进行配置
    static{
        dataSource.setDriverClassName(DRIVERNAME);
        dataSource.setUrl(URL);
        dataSource.setUsername(USER);
        dataSource.setPassword(PASSWORD);
    }
    //4.连接
    public static Connection getConnection() throws SQLException {
        Connection connection=dataSource.getConnection();
        return connection;
    }
    //5.关闭流
    public static void close(Connection connection, Statement statement) throws SQLException {
        if(connection!=null && statement!=null){
            statement.close();
            connection.close();
        }
    }
    public static void close(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
        if(connection!=null && statement!=null && resultSet!=null){
            resultSet.close();
            statement.close();
            connection.close();
        }
    }
}

三、Druid连接池

druid.properties(放在resources里面)

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8
username=root
password=root

Druid工具类

public class DruidUtils {
    //定义成员变量
    public static DataSource dataSource;
    //静态代码块
    static{
        try {
            //3. 创建属性集对象
            Properties properties = new Properties();
            //4.加载配置文件 Druid 连接池不能够主动加载配置文件，需要指定文件
            InputStream inputStream=DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
            //5. Properties对象的Load方法 从字节流中读取配置信息
            properties.load(inputStream);
            //6. 通过工厂类获取连接池对象
            dataSource= DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接对象
    public static Connection getConnection(){
        try {
            return dataSource.getConnection();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return null;
        }
    }
    //关闭连接方法
    public static  void close(Connection connection, Statement statement) throws SQLException {
        if(connection!=null&&statement!=null){
            statement.close();
            connection.close();
        }
    }
    public static  void close(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
        if(connection!=null&&statement!=null && resultSet!=null){
            resultSet.close();
            statement.close();
            connection.close();
        }
    }
}