week09_day06_数据库连接池

最新推荐文章于 2022-07-28 17:41:36 发布

-玫瑰少年-

最新推荐文章于 2022-07-28 17:41:36 发布

阅读量239

点赞数

分类专栏：数据结构与数据库

本文链接：https://blog.csdn.net/shihao1998/article/details/106589276

版权

数据结构与数据库专栏收录该内容

30 篇文章 0 订阅

订阅专栏

数据库连接池

所谓的数据库连接池技术，就是用来分配，管理，释放数据库连接的。我们以前用 JDBC 操作数据库的时候，每次操作完都会将连接关闭。数据库连接是极其宝贵的资源，频繁的创建和销毁会极大地降低服务器的性能。因此，我们可以利用池化技术，重复利用数据库资源，避免没必要的开销。

1. 手写数据库连接池
首先，我们来手写一个基本的数据库连接池，加深对数据库连接池原理的理解。

MyConnectionPool：

package com.cskaoyan.DataSource;

import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.*;
import java.util.LinkedList;
import java.util.Map;
import java.util.Properties;
import java.util.Queue;
import java.util.concurrent.Executor;
import java.util.concurrent.LinkedBlockingDeque;

/**
 * @author shihao
 * @create 2020-06-06 16:17
 */
public class MyConnectionPool {
    //属性
    //MAX_SIZE、INIT_SIZE这两个属性通过pool.properties配置文件传入
    //最大连接数目
    private static int MAX_SIZE;
    //初始连接数目
    private static int INIT_SIZE;
    //配置文件
    private static Properties info = new Properties();
    //实际连接的数目
    private int size;
    //用队列存放空闲连接，使用时来这里拿，用完了放回来
    private Queue<Connection> queue = new LinkedList<>();

    static {
        try (Reader reader = new FileReader("pool.properties")) {
            info.load(reader);
            MAX_SIZE = Integer.parseInt(info.getProperty("MAX_SIZE"));
            INIT_SIZE = Integer.parseInt(info.getProperty("INIT_SIZE"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //构造方法
    //我们希望在创建对象之前就对MAX_SIZE和INIT_SIZE赋值，所以应在静态代码块中对这两个参数初始化
    public MyConnectionPool() {
        //创建INIT_SIZE个空闲连接，并放在队列中
        for (int i = 0; i < INIT_SIZE; i++) {
            Connection connec = createConnection();
            queue.offer(connec);
        }
    }

    //方法
    //获取连接
    public Connection getConnection(){
        //如果队列非空，就从队列中取出一个连接
        if (!queue.isEmpty()){
            return queue.poll();
        }
        //走到这就说明没有空闲的连接了
        //如果没有达到最大值，就创建一个连接给外界使用
        if (size<MAX_SIZE){
            return createConnection();
        }
        //走到这说明达到最大值了，就抛出异常
        throw new RuntimeException("连接数目已经达到最大值！");
    }



    //我们希望这些连接在没有用的时候把它放回到池子中，调用close方法后，让连接回到连接池中，而不是把它销毁
    private Connection createConnection() {
        String url = info.getProperty("url");
        String user = info.getProperty("user");
        String password = info.getProperty("password");
        Connection conn = null;
        try {
            conn = new WorkConnection(DriverManager.getConnection(url, user, password));
            size++;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    //我们希望WorkConnection可以访问私有成员queue等，就需要写一个内部类
    //其实我们只需要重写其close方法
    private class WorkConnection extends ConnectionWrapper {
        WorkConnection(Connection connec) {
            super(connec);
        }

        @Override
        public void close() throws SQLException {
            //用完这条连接后就放到队列中去，而不是销毁
            queue.offer(this);
        }
    }

}

装饰者模式在流里面也经常用，比如说，对一个流进行包装就会得到另一个流。

package com.cskaoyan.DataSource;

import java.sql.*;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;

/**
 * @author shihao
 * @create 2020-06-06 17:08
 */
//装饰者模式
public abstract class ConnectionWrapper implements Connection {

    //这些方法我们都不会重写，我们让connec来帮我们做事情
    //组合
    //组合定义
    //在新类里面创建原有类的对象，重复利用已有类的功能。(has-a关系)
    protected Connection connec;

    ConnectionWrapper(Connection connec) {
        this.connec = connec;
    }

    @Override
    public Statement createStatement() throws SQLException {
        return connec.createStatement();
    }

    @Override
    public PreparedStatement prepareStatement(String sql) throws SQLException {
        return connec.prepareStatement(sql);
    }

    @Override
    public CallableStatement prepareCall(String sql) throws SQLException {
        return connec.prepareCall(sql);
    }

    @Override
    public String nativeSQL(String sql) throws SQLException {
        return connec.nativeSQL(sql);
    }

    @Override
    public void setAutoCommit(boolean autoCommit) throws SQLException {
        connec.setAutoCommit(autoCommit);
    }

    @Override
    public boolean getAutoCommit() throws SQLException {
        return connec.getAutoCommit();
    }

    @Override
    public void commit() throws SQLException {
        connec.commit();
    }

    @Override
    public void rollback() throws SQLException {
        connec.rollback();
    }

    //这个方法让继承者们实现,因为要重写的只有这有方法
    @Override
    public abstract void close() throws SQLException;

    @Override
    public boolean isClosed() throws SQLException {
        return connec.isClosed();
    }

    @Override
    public DatabaseMetaData getMetaData() throws SQLException {
        return connec.getMetaData();
    }

    @Override
    public void setReadOnly(boolean readOnly) throws SQLException {
        connec.setReadOnly(readOnly);
    }

    @Override
    public boolean isReadOnly() throws SQLException {
        return connec.isReadOnly();
    }

    @Override
    public void setCatalog(String catalog) throws SQLException {
        connec.setCatalog(catalog);
    }

    @Override
    public String getCatalog() throws SQLException {
        return connec.getCatalog();
    }

    @Override
    public void setTransactionIsolation(int level) throws SQLException {
        connec.setTransactionIsolation(level);
    }

    @Override
    public int getTransactionIsolation() throws SQLException {
        return connec.getTransactionIsolation();
    }

    @Override
    public SQLWarning getWarnings() throws SQLException {
        return connec.getWarnings();
    }

    @Override
    public void clearWarnings() throws SQLException {
        connec.clearWarnings();
    }

    @Override
    public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
        return connec.createStatement(resultSetType, resultSetConcurrency);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
        return connec.prepareCall(sql, resultSetType, resultSetConcurrency);
    }

    @Override
    public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
        return connec.prepareCall(sql, resultSetType, resultSetConcurrency);
    }

    @Override
    public Map<String, Class<?>> getTypeMap() throws SQLException {
        return connec.getTypeMap();
    }

    @Override
    public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
        connec.setTypeMap(map);
    }

    @Override
    public void setHoldability(int holdability) throws SQLException {
        connec.setHoldability(holdability);
    }

    @Override
    public int getHoldability() throws SQLException {
        return connec.getHoldability();
    }

    @Override
    public Savepoint setSavepoint() throws SQLException {
        return connec.setSavepoint();
    }

    @Override
    public Savepoint setSavepoint(String name) throws SQLException {
        return connec.setSavepoint(name);
    }

    @Override
    public void rollback(Savepoint savepoint) throws SQLException {
        connec.rollback(savepoint);
    }

    @Override
    public void releaseSavepoint(Savepoint savepoint) throws SQLException {
        connec.releaseSavepoint(savepoint);
    }

    @Override
    public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
        return connec.createStatement(resultSetType, resultSetConcurrency, resultSetHoldability);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
        return connec.prepareStatement(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
    }

    @Override
    public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
        return connec.prepareCall(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
        return connec.prepareStatement(sql, autoGeneratedKeys);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
        return connec.prepareStatement(sql,columnIndexes);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
        return connec.prepareStatement(sql,columnNames);
    }

    @Override
    public Clob createClob() throws SQLException {
        return connec.createClob();
    }

    @Override
    public Blob createBlob() throws SQLException {
        return connec.createBlob();
    }

    @Override
    public NClob createNClob() throws SQLException {
        return connec.createNClob();
    }

    @Override
    public SQLXML createSQLXML() throws SQLException {
        return connec.createSQLXML();
    }

    @Override
    public boolean isValid(int timeout) throws SQLException {
        return connec.isValid(timeout);
    }

    @Override
    public void setClientInfo(String name, String value) throws SQLClientInfoException {
        connec.setClientInfo(name,value);
    }

    @Override
    public void setClientInfo(Properties properties) throws SQLClientInfoException {
        connec.setClientInfo(properties);
    }

    @Override
    public String getClientInfo(String name) throws SQLException {
        return connec.getClientInfo(name);
    }

    @Override
    public Properties getClientInfo() throws SQLException {
        return connec.getClientInfo();
    }

    @Override
    public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
        return connec.createArrayOf(typeName,elements);
    }

    @Override
    public Struct createStruct(String typeName, Object[] attributes) throws SQLException {
        return connec.createStruct(typeName,attributes);
    }

    @Override
    public void setSchema(String schema) throws SQLException {
        connec.setSchema(schema);
    }

    @Override
    public String getSchema() throws SQLException {
        return connec.getSchema();
    }

    @Override
    public void abort(Executor executor) throws SQLException {
        connec.abort(executor);
    }

    @Override
    public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {
        connec.setNetworkTimeout(executor, milliseconds);
    }

    @Override
    public int getNetworkTimeout() throws SQLException {
        return connec.getNetworkTimeout();
    }

    @Override
    public <T> T unwrap(Class<T> iface) throws SQLException {
        return connec.unwrap(iface);
    }

    @Override
    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        return connec.isWrapperFor(iface);
    }
}

2. 优秀的数据库连接池
Sun 公司规定：Java 中所有的数据库连接池都需要实现 DataSource 接口。该接口定义如下：
其中，定义了两个获取连接的方法。

2.1 DBCP
DBCP 是由 Apache 开发的一个 Java 数据库连接池项目。Tomcat 内置的连接池组件就是 DBCP。单独使用 DBCP需要3个包：common-dbcp.jar,common-pool.jar,common-logging.jar。它预先将数据库连接放在内存中，应用程序需要建立数据库连接时直接到连接池中申请一个就行，用完再放回。单线程，并发量低，性能不好，适用于小型系统。

使用的方式主要有两种，一种是硬编码方式，就是在代码中设置各种参数 (不推荐) 。
第二种方式就是配置相应的配置文件 (推荐)。

package com.cskaoyan.DataSource;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
import org.junit.Assert;
import org.junit.Test;

import java.io.FileReader;
import java.io.Reader;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @author shihao
 * @create 2020-06-06 22:05
 */
public class DBCPDemo {
    //硬编码方式
    @Test
    public void testGetConnection() throws SQLException {
        BasicDataSource ds = new BasicDataSource();
        ds.setInitialSize(3);
        ds.setMaxTotal(10);
        ds.setMaxIdle(1800);
        ds.setUrl("jdbc:mysql://localhost:3306/jdbc_db");
        ds.setUsername("root");
        ds.setPassword("069583");

        Connection conn = ds.getConnection();
        Assert.assertNotNull(conn);
    }

    //配置文件方式
    @Test
    public void testGetConnection2() {
        Properties info = new Properties();
        Connection conn = null;
        try (Reader reader = new FileReader("dbcp.properties")) {
            info.load(reader);
            BasicDataSource ds = BasicDataSourceFactory.createDataSource(info);
            conn = ds.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        Assert.assertNotNull(conn);
    }
}

2.2 C3P0
开源的 JDBC 连接池，支持 JDBC3 规范和 JDBC2 的标准扩展。目前使用它的开源项目有Hibernate、Spring等。单线程，性能较差，适用于小型系统。使用 C3P0 需要 c3p0-0.9.5.2.jar 和 mchangecommons-java-0.2.11.jar。

c3p0 会默认从 src 根目录下读取文件名为 c3p0-config.xml 的文件。在该文件中，我们可以配置我们自己需要的参数，如下所示，更多的参数可以自己查阅相关参数。

package com.cskaoyan.DataSource;

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

import java.sql.Connection;
import java.sql.SQLException;

/**
 * @author shihao
 * @create 2020-06-06 22:22
 */
public class C3P0Demo {
    @Test
    public void testGetConnection() {
        ComboPooledDataSource ds = new ComboPooledDataSource();
        Connection conn = null;
        try {
            conn = ds.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Assert.assertNotNull(conn);
    }
}

2.3 Druid

Druid 是 Java 语言中最好的数据库连接池，Druid 能够提供强大的监控和扩展功能，是一个可用于大数据实时查询和分析的高容错、高性能的开源分布式系统，尤其是当发生代码部署、机器故障以及其他产品系统遇到宕机等情况时，Druid 仍能够保持100% 正常运行。

主要特色: 为分析监控设计；快速的交互式查询；高可用；可扩展；

package com.cskaoyan.DataSource;

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

import javax.sql.DataSource;
import java.io.FileReader;
import java.io.Reader;
import java.sql.Connection;
import java.util.Properties;

/**
 * @author shihao
 * @create 2020-06-06 22:22
 */
public class DruidDemo {
    @Test
    public void testGetConnection() {
        Properties info = new Properties();
        Connection conn = null;
        try (Reader reader = new FileReader("druid.properties")) {
            info.load(reader);
            DataSource ds = DruidDataSourceFactory.createDataSource(info);
            conn = ds.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        Assert.assertNotNull(conn);
    }
}

数据库连接池的优点：

数据库连接得以重用，避免了频繁创建和销毁引起的大量性能开销，同时也增加了系统的稳定性。
数据库连接池在初始化过程中，往往就已经创建了若干个连接。因此可以提高系统的反应速度。
如果有多个应用共用一个 DBMS，由于 DBMS 的连接是有限的，因此我们可以通过数据库连接池
限制一个应用最大的连接数目，避免某一应用独占所有的连接。
在较为完善的数据库连接池中，可以设置占用超时，强制回收被占用的连接，从而避免了常规数据
库连接操作中可能出现的资源泄露问题。

···················································································································································································································
作业：
. 用Druid数据库连接池创建连接。在test数据库中，创建user表(id, username, password, balance).
批量插入10000条记录, 然后查询 balance>10000 的用户有哪些？将username和balance输出到test.txt文件中。

package com.cskaoyan.Homework;

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

import javax.sql.DataSource;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.PrintWriter;
import java.io.Reader;
import java.math.BigDecimal;
import java.sql.*;
import java.util.Properties;
import java.util.Random;

/**
 * @author shihao
 * @create 2020-06-06 22:41
 * <p>
 * 2. 用Druid数据库连接池创建连接。
 * 在test数据库中，创建user表(id, username, password, balance).
 * 批量插入10000条记录, 然后查询 balance>10000 的用户有哪些？
 * 将username和balance输出到test.txt文件中。
 */
public class Homework {

    public static void main(String[] args) throws SQLException, FileNotFoundException {
        // 获取连接
        Connection conn = GetConnection();
        //获取执行平台
        Statement stmt = conn.createStatement();

        //创建user表
        String sql = "create table user(id int primary key auto_increment, " +
                "username varchar(255) not null, password varchar(255) not null, " +
                "balance decimal(18,2) default 0)";
        stmt.execute(sql);

        //插入1000条数据
        sql = "insert into user(username,password,balance) values (?,?,?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        Random r = new Random();
        for (int i = 0; i < 100; i++) {
            pstmt.setString(1, "user" + i);
            pstmt.setString(2, "pwd" + i);
            pstmt.setBigDecimal(3, new BigDecimal(r.nextInt(15000)));
            pstmt.addBatch();
        }
        pstmt.executeBatch();

        // 查询balance>10000的记录，并写入到test.txt文件中
        sql = "select * from user where balance > 10000";
        ResultSet rs = pstmt.executeQuery(sql);
        PrintWriter pw = new PrintWriter("homework.txt");
        pw.println("id" + "\t" + "username" + "\t" + "balance");
        while (rs.next()) {
            int id = rs.getInt("id");
            String username = rs.getString("username");
            BigDecimal balance = rs.getBigDecimal("balance");
            pw.println(id + "\t" + username + "\t" + balance);
        }
        pw.close();
        conn.close();
    }


    //连接数据库
    public static Connection GetConnection() {
        Properties info = new Properties();
        Connection conn = null;
        try (Reader reader = new FileReader("druid.properties")) {
            info.load(reader);
            DataSource ds = DruidDataSourceFactory.createDataSource(info);
            conn = ds.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
}

-玫瑰少年-

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
week09_day06_数据库连接池

···················································································································································································································...
复制链接

扫一扫