数据库连接池

自定义连接池

只是简化的说明原理，而不是真实上线使用

测试类：
@Test
    public void testPool() throws SQLException {
        Connection conn=null;
        PreparedStatement ps=null;
        MyDataSource dataSource=null;
        try {
         dataSource=new MyDataSource();
            conn = dataSource.getConnection();
             //关闭自动提交
             conn.setAutoCommit(false);
             String sql="update student set age=age-? where sex=?";
              ps = conn.prepareStatement(sql);
              ps.setInt(1, 1);
              ps.setInt(2, 31);
              ps.executeUpdate();
              ps.setInt(1, -1);
              ps.setInt(2, 2);
              ps.executeUpdate();
            //提交事务
              conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            //回滚事务
            conn.rollback();
        }finally {
            ps.close();
            JDBCUtil.release(conn, ps);
        }
    }

public class ConnectionWrap implements Connection{
    List<Connection> list=null;
    Connection conn=null;
    public ConnectionWrap(Connection conn,List<Connection> list) {
        super();
        this.conn=conn;
        this.list=list;
    }

    //只关注以下两个方法即可
    @Override
    public void close() throws SQLException {
              //利用装饰器模式，默认的关闭操作，现在改为访问数据库连接池
        list.add(conn);
    }
    @Override
    public PreparedStatement prepareStatement(String sql) throws SQLException {
        return conn.prepareStatement(sql);
    }
}

接口类:
public class MyDataSource implements DataSource{
    List<Connection>  list=new ArrayList<Connection>();
    public MyDataSource() {
        for (int i = 0; i < 10; i++) {
            Connection connection=JDBCUtil.getConn();
            list.add(connection);
        }
    }

    @Override
    public Connection getConnection() throws SQLException {
        if (list.size()==0) {
            for (int i = 0; i <3; i++) {
                Connection connection=JDBCUtil.getConn();
                list.add(connection);
            }
        }
        Connection conn = list.remove(0);
//这行代码就是利用装饰器模式，包装连接对象，实质作用就是把默认的连接对象变化为
//包装类，使test使用close时候，不是关闭，而是放回连接池
        Connection connection=new ConnectionWrap(conn, list);
        return connection;
    }
}

第三方连接池

常用连接池： DBCP C3P0

DBCP

代码形式

public void testDBCP01(){
        
            
            Connection conn = null;
            PreparedStatement ps = null;
            try {
                
                //1. 构建数据源对象
                BasicDataSource dataSource = new BasicDataSource();
                //连的是什么类型的数据库， 访问的是哪个数据库 ， 用户名， 密码。。
                //jdbc:mysql://localhost/bank 主协议：子协议 ://本地/数据库
                dataSource.setDriverClassName("com.mysql.jdbc.Driver");
                dataSource.setUrl("jdbc:mysql://localhost/bank");
                dataSource.setUsername("root");
                dataSource.setPassword("root");
                
                
                //2. 得到连接对象
                conn = dataSource.getConnection();
                String sql = "insert into account values(null , ? , ?)";
                ps = conn.prepareStatement(sql);
                ps.setString(1, "admin");
                ps.setInt(2, 1000);
                
                ps.executeUpdate();
                
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                JDBCUtil.release(conn, ps);
            }
            
        }

使用配置文件形式

配置文件路径.png

dbcp文件路径还可以修改，但是c3p0配置文件路径不可修改，因为使类加载器内部默认地址

jar包.png

DBCP：必须添加dbcp和pool两个jar，而且mysql驱动式必须的；
C3P0：需要mysql驱动和c3p0两个jar

配置文件：
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/java
username=root
password=zengqiang

#<!-- 初始化连接 -->
initialSize=10

#最大连接数量
maxActive=50

#<!-- 最大空闲连接 -->
maxIdle=20

#<!-- 最小空闲连接 -->
minIdle=5

#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000


#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;] 
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=gbk

#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

@Test
    public void testdbcp() {
        Connection connection=null;
        PreparedStatement pStatement=null;
        try {
            //1.构建 数据源
            BasicDataSourceFactory bsf=new BasicDataSourceFactory();
            Properties properties= new Properties();
            properties.load(new FileInputStream("src//dbcpconfig.properties"));
            DataSource bSource=bsf.createDataSource(properties);
            //2.获取连接对象
            connection=bSource.getConnection();
            String sql="select * from student";
            pStatement=connection.prepareStatement(sql);
            ResultSet executeQuery = pStatement.executeQuery();
            while (executeQuery.next()) {
                System.out.println(executeQuery.getString("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.release(connection, pStatement);
        }
        
    }

C3P0

代码形式

Connection conn = null;
        PreparedStatement ps = null;
        try {
            //1. 创建datasource
            ComboPooledDataSource dataSource = new ComboPooledDataSource();
            //2. 设置连接数据的信息
            dataSource.setDriverClass("com.mysql.jdbc.Driver");
            
            //忘记了---> 去以前的代码 ---> jdbc的文档
            dataSource.setJdbcUrl("jdbc:mysql://localhost/bank");
            dataSource.setUser("root");
            dataSource.setPassword("root");
            
            //2. 得到连接对象
            conn = dataSource.getConnection();
            String sql = "insert into account values(null , ? , ?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, "admi234n");
            ps.setInt(2, 103200);
            
            ps.executeUpdate();
            
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.release(conn, ps);
        }

配置文件
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>

    <!-- default-config 默认的配置，  -->
  <default-config>
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost/java</property>
    <property name="user">root</property>
    <property name="password">zengqiang</property>
    
    
    <property name="initialPoolSize">10</property>
    <property name="maxIdleTime">30</property>
    <property name="maxPoolSize">100</property>
    <property name="minPoolSize">10</property>
    <property name="maxStatements">200</property>
  </default-config>
  
   <!-- 配置多个数据库是使用，一般删除即可 -->
  <named-config name="oracle"> 
    <property name="acquireIncrement">50</property>
    <property name="initialPoolSize">100</property>
    <property name="minPoolSize">50</property>
    <property name="maxPoolSize">1000</property>

    <!-- intergalactoApp adopts a different approach to configuring statement caching -->
    <property name="maxStatements">0</property> 
    <property name="maxStatementsPerConnection">5</property>

    <!-- he's important, but there's only one of him -->
    <user-overrides user="master-of-the-universe"> 
      <property name="acquireIncrement">1</property>
      <property name="initialPoolSize">1</property>
      <property name="minPoolSize">1</property>
      <property name="maxPoolSize">5</property>
      <property name="maxStatementsPerConnection">50</property>
    </user-overrides>
  </named-config>
</c3p0-config>

@Test
    public void testc3p0() {
        Connection connection=null;
        PreparedStatement pStatement=null;
        try {
            //内部通过类加载器，自动加载配置文件，所以文件名称不能修改
            ComboPooledDataSource dataSource=new ComboPooledDataSource();
            //如果不写参数，默认加载配置文件configname为default的文件
//          ComboPooledDataSource dataSource=new ComboPooledDataSource("oracle");
            //2.获取连接对象
            connection=dataSource.getConnection();
            String sql="select * from student";
            pStatement=connection.prepareStatement(sql);
            ResultSet executeQuery = pStatement.executeQuery();
            while (executeQuery.next()) {
                System.out.println(executeQuery.getString("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.release(connection, pStatement);
        }
    }

DBUtil

使用前先要添加DBUtil的jar包，dbutils只是简化crud的操作，但是连接的创建和获取不是它负责

dbutils.png

@Test
    public void testdbutil() throws SQLException {
        ComboPooledDataSource dataSource=new ComboPooledDataSource();
        //dbutils只是简化crud的操作，但是连接的创建和获取不是它负责
        QueryRunner queryRunner=new QueryRunner(dataSource);
        //插入
//      int update = queryRunner.update("insert into student values (?,?,?)","非",12,30);
            //删除
//       queryRunner.update("delete from student where name=?","非");
        //更新
//      queryRunner.update("update student set age=? where sex=?",300,2);
        //基本查询查询
        Student student= queryRunner.query("SELECT * FROM student WHERE sex=?",new ResultSetHandler<Student>(){
            @Override
            public Student handle(ResultSet arg0) throws SQLException {
                Student student=new Student();
                while (arg0.next()) {
                    String name=arg0.getString("name");
                    int age=arg0.getInt("age");
                    int sex=arg0.getInt("sex");
                    student.setAge(age);
                    student.setName(name);
                    student.setSex(sex);
                }
                return student;
            }
            
        }, 2);
        System.out.println(student);
        
        //下面才是常用查询
        //查询结果是一个则使用ResultSetHandler的实现类,BeanHandler,多个则使用BeanListHandler<T>
        Student s1= queryRunner.query("SELECT * FROM student WHERE sex=?",new BeanHandler<Student>(Student.class), 2);
//补充：当然针对map，array等也有具体的实现类，具体问题具体实现
    }