DBCP连接池

连接池：是用来管理数据库连接的一种技术。在做数据库操作时，反复的创建连接和关闭连接耗费时间。

连接池的工作过程：

创建连接池时，在连接池中初始化一定数量的连接，当应用程序获得连接时，某个连接变为忙状态，根据配置的最 小空闲连接数，连接池创建新的连接，但连接数量不能超过配置的最大连接数。当应用程序用完连接后，连接变为 空闲状态，当所有的连接都是忙状态，应用程序获得连接时将等待。

常用的连接池有：

1. dbcp连接池：老牌的连接池

2. c3p0连接池：先对dbcp连接池稳定，但速度慢。

3. hirake连接池：springboot框架默认使用这个连接池，速度比druid快

4. druid德鲁伊连接池：阿里巴巴开发的连接池，对SQL语句进行监控、拦截的功能。

当然也可以自己写连接池，但通常不这么做，用写好的成熟的连接池

DBCP连接池：

public class Test {
    public static void main(String[] args) {
        BasicDataSource pool = new BasicDataSource();
        pool.setDriverClassName("com.mysql.jdbc.Driver");
        pool.setUrl("jdbc:mysql://localhost:3306/java2113?characterEncoding=utf8");
        pool.setUsername("root");
        pool.setPassword("root");
        pool.setInitialSize(3);
        pool.setMinIdle(3);
        pool.setMaxActive(5);
        try {
            Connection conn = pool.getConnection();
            System.out.println(conn.isClosed());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

package com.oracle.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.ResourceBundle;
import org.apache.commons.dbcp.BasicDataSource;
import com.oracle.vo.Student;
//通用的数据库操作类
public class BaseDao {
    Connection conn;
    PreparedStatement stmt;
    ResultSet rs;
    ResultSetMetaData rsmd;
    static String driver ;
    static String url;
    static String user;
    static String password;
    static String initSize;
    static String minIdle;
    static String maxActive;
    static BasicDataSource pool;
    static {
        ResourceBundle bundle = ResourceBundle.getBundle("db");
        driver = bundle.getString("driver");
        url = bundle.getString("url");
        user = bundle.getString("user");
        password = bundle.getString("password");
        initSize = bundle.getString("initSize");
        minIdle = bundle.getString("minIdle");
        maxActive = bundle.getString("maxActive");
        pool = new BasicDataSource();
        pool.setDriverClassName(driver);
        pool.setUrl(url);
        pool.setUsername(user);
        pool.setPassword(password);
        pool.setInitialSize(Integer.parseInt(initSize));
        pool.setMinIdle(Integer.parseInt(minIdle));
        pool.setMaxActive(Integer.parseInt(maxActive));
    }
    public int insert(String sql,Object[] values) {
        getConnection();
        int result = 0;
        try {
            stmt = conn.prepareStatement(sql);
            for(int i = 0;i<values.length;i++) {
                stmt.setObject(i+1, values[i]);
            }
            result = stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
    }
        return result;
        }
    public int update(String sql,Object[] values) {
        return insert(sql,values);
        }
    public int delete(String sql,Object[] values) {
        return insert(sql,values);
        }
    //按条件查询
    //结果集的数据向 vo中填充属性 通过接口 描述填充的过程
    public <T> List<T> select(String sql,Object[] values,Convert<T> convert){
        List<T> list = new ArrayList<T>();
        getConnection();
        try {
        stmt = conn.prepareStatement(sql);
        for(int i = 0;i<values.length;i++) {
        stmt.setObject(i+1, values[i]);
        }
        rs = stmt.executeQuery();
        while(rs.next()) {
        T t = convert.convert(rs);
        list.add(t);
        }
        } catch (SQLException e) {

        e.printStackTrace();
        } finally {
        try {
        conn.close();
        } catch (SQLException e) {

        e.printStackTrace();
        }
        }
        return list;
        }
    //按id查询
    public <T> T selectById(String sql,Object[] values,Convert<T> convert) {
        getConnection();
        try {
        stmt = conn.prepareStatement(sql);
        for(int i = 0;i<values.length;i++) {
        stmt.setObject(i+1, values[i]);
        }
        rs = stmt.executeQuery();
        if(rs.next()) {
        return convert.convert(rs);
        }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
        try {
        conn.close();
        } catch (SQLException e) {
        e.printStackTrace();
        }
        }
        return null;
        }
    //获得连接
    public Connection getConnection() {
        try {
        conn = pool.getConnection();
        } catch (SQLException e) {

        e.printStackTrace();
        }
        return conn;
        }
    public static void main(String[] args) {
    //向学生表插入一条记录
    /*String sql = "insert into student(sname,tel,amt) values(?,?,?)";
    Object[] values = new Object[] {"abc","13111111111",1000};
    CommonData cdata = new CommonData();
        System.out.println(cdata.insert(sql, values));*/
    //id是35的学生电话修改为11111111111
    /*String sql = "update student set tel=? where id=?";
    Object[] values = new Object[] {"11111111111",35};
    CommonData cdata = new CommonData();
        System.out.println(cdata.update(sql, values));*/
        String sql = "delete from student where id = ?";
        Object[] values = new Object[] {35};
        BaseDao cdata = new BaseDao();
            System.out.println(cdata.delete(sql, values));
        //显示所有学生的姓名和电话号
        List<Student> list = cdata.select("select * from student", new Object[] {}, new
        StudentConvert());
        for(Student s:list) {
            System.out.println(s.getSname());
        }
        Student s = cdata.selectById("select * from student where id=?", new Object[] {21}, new
        StudentConvert());
        System.out.println(s.getSname());
    /* List<Student> list = cdata.select("select * from student", new Object[] {},(rs)->            {
    Student s = new Student();
    try {
        s.setId(rs.getInt("id"));
        s.setAmt(rs.getInt("amt"));
        s.setSname(rs.getString("sname"));
    } catch (SQLException e) {
            e.printStackTrace();
        }
        return s;
        });*/
        }
    }
class StudentConvert implements Convert<Student> {
    @Override
    public Student convert(ResultSet rs) {
        Student s = new Student();
        try {
            s.setId(rs.getInt("id"));
            s.setAmt(rs.getInt("amt"));
            s.setSname(rs.getString("sname"));
            s.setBirthday(rs.getDate("birthday"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return s;
    }
}