（五）JDBC学习笔记——开源数据库连接池

一、C3P0

首先下载C3P0。
然后将两个jar包导入到工程：
导入配置文件c3p0-config.xml到src目录。
然后使用即可：

public class C3P0Test {
    public static void main(String[] args) throws SQLException {
        //1.创建c3p0数据库连接池对象
        DataSource ds = new ComboPooledDataSource();//不指定名字则使用默认配置
        //2.通过连接池对象获取数据库连接
        Connection connection = ds.getConnection();
        //查询
        //3.查询学生表的信息
        String sql = "SELECT * FROM student";
        PreparedStatement pst = connection.prepareStatement(sql);

        //4.执行sql语句 接收结果集
        ResultSet resultSet = pst.executeQuery();
        while (resultSet.next()){
            System.out.println(resultSet.getInt("sid") + "\t" + resultSet.getString("name") + "\t" + resultSet.getString("age") );
        }

        //5.释放资源
        resultSet.close();
        pst.close();
        connection.close();
    }
}

二、Druid

Druid是阿里的，目前应用范围很广。首先下载Druid的jar包，导入到工程中。然后在src目录下新建druid.properties文件配置druid，文件名任意，需要手动加载配置文件。

public class DruidTest {
    public static void main(String[] args) throws Exception {
        //1.通过Properties集合加载配置文件
        InputStream is = DruidTest.class.getClassLoader().getResourceAsStream("druid.properties");
        Properties prop = new Properties();
        prop.load(is);

        //2.通过Druid连接池工厂类获取数据库连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //3.通过连接池对象获取数据库连接并使用
        Connection connection = dataSource.getConnection();
        //4.使用：
        //查询学生表的信息
        String sql = "SELECT * FROM student";
        PreparedStatement pst = connection.prepareStatement(sql);
        //执行sql语句 接收结果集
        ResultSet resultSet = pst.executeQuery();
        while (resultSet.next()){
            System.out.println(resultSet.getInt("sid") + "\t" + resultSet.getString("name") + "\t" + resultSet.getString("age") );
        }

        //5.释放资源
        resultSet.close();
        pst.close();
        connection.close();
    }
}

三、抽取连接池工具类

将连接池的加载、获取、释放等功能抽象出来：

//数据库连接池工具类
public class DataSourceUtils {
    //1.私有构造方法
    private DataSourceUtils() {}

    //2.声明数据池对象变量
    private static DataSource dataSource;

    //3.提供静态代码块，完成配置文件的加载、获取数据库连接池对象
    static{
        InputStream is = DataSourceUtils.class.getClassLoader().getResourceAsStream("druid.properties");
        Properties prop = new Properties();
        try {
            prop.load(is);
            dataSource = DruidDataSourceFactory.createDataSource(prop);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //4.提供一个获取数据库连接的方法
    public static Connection getConnection(){
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return connection;
    }

    //5.提供一个获取数据库连接池对象（非连接对象）的方法，自定义框架用得到
    public static DataSource getDataSource(){
        return dataSource;
    }

    //6.提供释放资源方法
    public static void close(Connection connection, Statement statement, ResultSet resultSet){
        if(connection != null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(statement != null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    //增删改查调用这个
    public static void close(Connection connection, Statement statement){
        if(connection != null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(statement != null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

然后进行测试：

public class DruidTest2 {
    public static void main(String[] args) throws Exception {
        Connection connection = DataSourceUtils.getConnection();
        String sql = "SELECT * FROM student";
        PreparedStatement pst = connection.prepareStatement(sql);
        ResultSet resultSet = pst.executeQuery();
        while (resultSet.next()){
            System.out.println(resultSet.getInt("sid") + "\t" + resultSet.getString("name") + "\t" + resultSet.getString("age") );
        }
        DataSourceUtils.close(connection, pst, resultSet);
    }
}