package jdbc.connection_pool.c3p0;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class C3P0Demo {
    //手动配置C3P0连接池参数
    @Test
    public void manual() throws SQLException, PropertyVetoException {
        //创建线程池数据源对象
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        //设置四大连接参数
        dataSource.setDriverClass("com.mysql.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/jdbc");
        dataSource.setUser("root");
        dataSource.setPassword("root");

        //每次的增量为5
        dataSource.setAcquireIncrement(5);
        //初始化连接数
        dataSource.setInitialPoolSize(20);
        //最少连接数
        dataSource.setMinPoolSize(2);
        //最多连接数
        dataSource.setMaxPoolSize(50);

        //获得Connection数据库连接对象
        Connection con = dataSource.getConnection();

        //执行一系列操作
        Statement stmt = con.createStatement();
        String sql = "SELECT *  FROM nametb";
        ResultSet rs = stmt.executeQuery(sql);
        //获取列数
        int count = rs.getMetaData().getColumnCount();
        while (rs.next()) {//遍历行
            for (int i = 1; i <= count; i++) {//遍历列
                System.out.print(rs.getString(i));
                if (i < count) {
                    System.out.print("\t");
                }
            }
            System.out.println();
        }

        //释放结果集
        rs.close();
        //关闭用来执行SQL语句的声明
        stmt.close();
        //把连接归还给池
        con.close();
    }

    //自动配置C3P0连接池参数，利用c3p0-config.xml配置文件
    @Test
    public void automatic() throws SQLException, PropertyVetoException {
        //创建连接池对象
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        //获得Connection数据库连接对象
        Connection con = dataSource.getConnection();

        //执行一系列操作
        Statement stmt = con.createStatement();
        String sql = "SELECT *  FROM team";
        ResultSet rs = stmt.executeQuery(sql);
        //获取列数
        int count = rs.getMetaData().getColumnCount();
        while (rs.next()) {//遍历行
            for (int i = 1; i <= count; i++) {//遍历列
                System.out.print(rs.getString(i));
                if (i < count) {
                    System.out.print("\t");
                }
            }
            System.out.println();
        }

        //释放结果集
        rs.close();
        //关闭用来执行SQL语句的声明
        stmt.close();
        //把连接归还给池
        con.close();
    }

    //指定命名配置元素名称（连接其他数据库），自动配置C3P0连接池参数，利用c3p0-config.xml配置文件
    @Test
    public void automaticByName() throws SQLException {
        //创建连接Oracle的连接池对象
        ComboPooledDataSource dataSource = new ComboPooledDataSource("oracle-config");
        //获得Connection数据库连接对象
        Connection con = dataSource.getConnection();

        System.out.println(con);

        //把连接归还给池
        con.close();
    }
}