[大数据]数据库连接池

C3P0

package com.sql.jbdc.datasource;

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

import java.beans.PropertyVetoException;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

public class C3P0_ {
    //方式1. 相关参数在程序中指定user,url,password等

    @Test
    public void testc3p0() throws IOException, PropertyVetoException, SQLException {
        //1.创建一个数据源对象
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
        //2.通过配置文件 mysql.properties 获取相关信息
        Properties properties = new Properties();

        properties.load(new FileInputStream("src\\mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");

        //给数据源 comboPooledDataSource 设置相关信息
        //注意：连接管理是由 comboPoolDateSource 来管理
        comboPooledDataSource.setDriverClass(driver);
        comboPooledDataSource.setJdbcUrl(url);
        comboPooledDataSource.setUser(user);
        comboPooledDataSource.setPassword(password);

        //设置初始化连接数
        comboPooledDataSource.setInitialPoolSize(10);
        //最大连接数
        comboPooledDataSource.setMaxPoolSize(50);
        //测试连接池效率
        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {
            Connection connection = comboPooledDataSource.getConnection();//核心方法 DataSource 接口实现
            System.out.println("连接成功");
            connection.close();
        }

        long end = System.currentTimeMillis();
        System.out.println(end-start);
    }
    //使用配置文件模板的方式
    //该文件制定了连接数据库和连接池的相关参数
    @Test
    public void testc3p0_() throws SQLException {
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("lixingrui");
        Connection connection = comboPooledDataSource.getConnection();
        System.out.println("连接成功");
        connection.close();
    }
}

Druid

package com.sql.jbdc.datasource;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.util.Properties;

public class Druid_ {
    @Test
    public void testDruid() throws Exception {
        //1.加入Druid jar包
        //2.加入配置文件 到src目录
        //3.创建Properties对象，读取配置文件
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\druid.properties"));

        //4.创建一个指定参数的数据库连接池
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);

        Connection connection = dataSource.getConnection();
        System.out.println("连接成功");
        connection.close();
    }
}

基于Druid数据库连接池工具类封装

package com.sql.jbdc.datasource;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

//基于druid数据库连接池工具类
public class JDBCUtilsByDruid {
    private static DataSource ds;
    //在静态代码块完成 ds初始化

    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src\\druid.properties"));
            ds = DruidDataSourceFactory.createDataSource(properties);

        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //编写getConnection方法
    public static Connection getConnection() throws SQLException {
            return ds.getConnection();
    }

    //关闭连接(这里是直接放回连接池)
    public static void close(ResultSet resultSet, Statement statement,Connection connection){
        try {
            if(resultSet != null){
                resultSet.close();
            }
            if(statement != null){
                statement.close();
            }
            if(connection != null){
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

}

Druid工具类的使用

package com.sql.jbdc.datasource;

import com.sql.jbdc.utils.JDBCUtils;
import org.junit.Test;

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

public class JDBCUtilsByDruid_Use {
    @Test
    public void testSelect(){
        //1.得到连接
        Connection connection = null;
        //2.组织一个sql
        String sql = "select * from actor";
        PreparedStatement preparedStatement = null;
        ResultSet set = null;
        //3.创建PreparedStatement 对象
        try {
            connection = JDBCUtilsByDruid.getConnection();
            preparedStatement = connection.prepareStatement(sql);

            //执行得到结果集
            set = preparedStatement.executeQuery();
            //遍历该结果集
            while(set.next()){
                int id = set.getInt("id");
                String name = set.getString("name");
                String phone = set.getString("phone");
                String sex = set.getString("sex");
                String borndate = set.getString("borndate");
                System.out.println(id + "\t" + name + "\t" + borndate  + "\t" + sex +"\t" + phone);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            JDBCUtilsByDruid.close(set, preparedStatement, connection);
        }
    }
}

ApDBUtils 使用

package com.sql.jbdc.datasource;

import java.util.Date;

public class Actor {




    private int id;
    private String name;
    private String sex;
    private Date borndate;
    private String phone;

    public Actor(int id, String name, String sex, Date borndate, String phone) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.borndate = borndate;
        this.phone = phone;
    }

    public Actor(){

    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Date getBorndate() {
        return borndate;
    }

    public void setBorndate(Date borndate) {
        this.borndate = borndate;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    @Override
    public String toString() {
        return "Actor{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", borndate=" + borndate +
                ", phone='" + phone + '\'' +
                '}';
    }
}

package com.sql.jbdc.datasource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

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

public class ApDBUtils_ {
    //使用apache-DBUtils 工具类 +druid 完成对表的crud操作
    @Test
    public void testQuerMany() throws SQLException {//返回结果是多行的情况

        //1.得到连接 druid
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2.使用 DBUtils 类和接口，先引入 DBUtils 相关的jar
        //3.创建一个QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        //4.就可以执行相关方法,返回ArrayList 结果集
        String sql = "select * from actor where id >= ?";

        //(1)query 方法就是执行sql 语句,得到resultest ---封装到 -->ArrayList 集合中
        //(2)返回集合
        //(3)connection:连接
        //(4)sql:执行的sql语句
        //(5)new BeanListHandler<>(Actor.class):在将resultset --> Actor 对象 -->封装到ArrayList
        //   底层使用反射机制 去获取Actor 类的属性，然后进行封装
        //(6)1 就是给 sql语句中的? 赋值，可以有多个值，因为是可变参数Object... params
        //(7)底层得到的resultset ,会在query 关闭, 关闭PreparedStatment
        List<Actor> list =
                queryRunner.query(connection,sql,new BeanListHandler<>(Actor.class),1);

        for(Actor actor : list){
            System.out.println(actor);
        }

        JDBCUtilsByDruid.close(null,null,connection);
    }
}