JDBC-自定义数据库连接池

最新推荐文章于 2022-05-27 17:57:09 发布

Fly_Fly_Zhang

最新推荐文章于 2022-05-27 17:57:09 发布

阅读量307

点赞数

分类专栏： JDBC

本文链接：https://blog.csdn.net/Fly_Fly_Zhang/article/details/95026522

版权

JDBC 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

本文介绍了如何创建自定义的数据库连接池，包括配置文件的设置、连接池代码的实现以及模拟连接和测试的过程，展示了从零开始构建数据库连接池的详细步骤。

摘要由CSDN通过智能技术生成

配置文件：

#驱动路径
driver=com.mysql.cj.jdbc.Driver
#JDBC连接URL
url=jdbc:mysql://localhost:3306/sqldemo?useSSL=false&serverTimezone=UTC
#账号
username=root
#密码
password=123456
#初始连接池大小
initPoolSize=3
#最大空闲时间
maxIdleTime=20
#最长连接等待时间
maxCreateTime=20
#最大连接池数
maxPoolSize=6

连接池代码：


package com.JDBC.CreateConnectionPool;

import javax.sql.DataSource;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.ArrayList;
import java.util.logging.Logger;

/**
 * @Created with IntelliJ IDEA
 * @Description:
 * @Package: com.JDBC.CreateConnectionPool
 * @author: FLy-Fly-Zhang
 * @Date: 2019/7/7
 * @Time: 16:20
 */
class run implements Runnable{
    private DataSourceDemo dataSourceDemo;
    public run(DataSourceDemo dataSourceDemo){
        this.dataSourceDemo=dataSourceDemo;
    }
    @Override
    public void run() {
        dataSourceDemo.release();
    }
}
public class DataSourceDemo implements DataSource{
    private String driverclassName;
    private String url;
    private String username;
    private String password;
    private int initialSize;
    private long maxWait;
    private long maxCreateTime;//获取连接最大等待时间
    private int maxActive; //最大连接数
    private ArrayList<Connection> list;//已有连接容器
    private  int size;//当前连接数
    private long lastTime; //上一次获取线程时间
    public DataSourceDemo(){
        Thread thread=new Thread(new run(this));
        //thread.setDaemon(true);
        thread.start();
    }
    public  void  release(){
        for (;  ; ) {
            if(this.size>this.initialSize&&(System.currentTimeMillis()>this.lastTime+this.maxWait)){
                synchronized (list){
                    if(!list.isEmpty()){
                        try {
                            list.remove(0).close();
                            this.size--;
                            this.lastTime=System.currentTimeMillis();
                            System.out.println("当前剩余连接数1 "+this.size);
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }
        }
    }
    public synchronized void  release(Connection connection) throws SQLException {
        //当前连接数多于核心连接数，并且已经有maxWait时间没有服务获取连接。关闭此连接。
        if(this.size>this.initialSize&&(System.currentTimeMillis()>this.lastTime+this.maxWait)){
            connection.close();
            this.size--;
            System.out.println("当前剩余连接数："+(this.size));
        }
        if(null!=connection){
            list.add(connection);
        }
    }
    /**
     * 得到连接
     * @return
     * @throws SQLException
     */
    @Override
    public Connection getConnection() throws SQLException {
            if(this.list==null){
                init();
            }else{
                long future=System.currentTimeMillis()+this.maxCreateTime;
                long remaining=this.maxCreateTime;
                while(list.isEmpty()&&remaining>0){
                    createConnection();
                    remaining=future-System.currentTimeMillis();
                }
            }
            if(!list.isEmpty()){
                synchronized (list){
                    if(!list.isEmpty()){
                        this.lastTime=System.currentTimeMillis();
                        System.out.println("当前剩余连接数 get："+(this.size));
                        return list.remove(0);
                    }
                }
            }
        this.lastTime=System.currentTimeMillis();
        return null;
    }

    /**
     * 创建连接
     * @throws SQLException
     */
    private synchronized void createConnection() throws SQLException {
        if(this.list==null){
            list=new ArrayList<>(maxActive);
        }
        try {
            if(this.size<this.maxActive){
                Class.forName(driverclassName);
                list.add(DriverManager.getConnection(url,username,password));
                this.size++;
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 初始化连接
     * @throws SQLException
     */
    private void init() throws SQLException {
        if(initialSize>0&&initialSize<=maxActive){
            for (int i = 0; i < initialSize; i++) {
                createConnection();
            }
        }
    }

    public void setMaxCreateTime(long maxCreateTime) {
        this.maxCreateTime = maxCreateTime;
    }

    public void setDriverclassName(String driverclassName) {
        this.driverclassName = driverclassName;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public void setInitialSize(int initialSize) {
        this.initialSize = initialSize;
    }

    public void setMaxWait(long maxWait) {
        this.maxWait = maxWait;
    }

    public void setMaxActive(int maxActive) {
        this.maxActive = maxActive;
    }



    @Override
    public Connection getConnection(String username, String password) throws SQLException {
        return null;
    }

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

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

    @Override
    public PrintWriter getLogWriter() throws SQLException {
        return null;
    }

    @Override
    public void setLogWriter(PrintWriter out) throws SQLException {

    }

    @Override
    public void setLoginTimeout(int seconds) throws SQLException {

    }

    @Override
    public int getLoginTimeout() throws SQLException {
        return 0;
    }

    @Override
    public Logger getParentLogger() throws SQLFeatureNotSupportedException {
        return null;
    }
}

模拟连接代码：

package com.JDBC.CreateConnectionPool;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @Created with IntelliJ IDEA
 * @Description:
 * @Package: com.JDBC.CreateConnectionPool
 * @author: FLy-Fly-Zhang
 * @Date: 2019/7/7
 * @Time: 17:38
 */
class rundemo implements Runnable{
    DataSourceDemo dataSource;
    public  rundemo(DataSourceDemo dataSource){
        this.dataSource=dataSource;
    }
    @Override
    public void run()  {
        for (int i = 0; i < 1; i++) {
            Connection connection= null;
            try {
                while(connection==null){
                    connection = dataSource.getConnection();
                }
                String sql="select * from Student,SC where Student.SID=SC.SID and Student.Ssex=?";
                PreparedStatement statement=connection.prepareStatement(sql);
                statement.setString(1,"男");
                //提交Sql,并获取返回结果
                ResultSet resultSet=statement.executeQuery();
                while(resultSet.next()){
                    //Thread.sleep(20);
                    String name=resultSet.getString("Sname");
                    int age=resultSet.getInt("Sage");
                    int score=resultSet.getInt("score");
                    //System.out.println("Sname: "+name+" Sage: "+age+" score: "+score);
                }
                Thread.sleep(20);
                statement.close();
                dataSource.release(connection);//回收连接。
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
    }
}
public class DataSourcesDemoTest {

    public static void main(String[] args) {
        Properties properties=new Properties();
        try {
            properties.load(DataSourcesDemoTest.class.getClassLoader().getResourceAsStream("datasourceconfig.properties"));
            final DataSourceDemo dataSource=new DataSourceDemo();
            //读取配置文件中数据库驱动路径
            dataSource.setDriverclassName(properties.getProperty("driver"));
            //读取配置文件中url
            dataSource.setUrl(properties.getProperty("url"));
            //读取配置文件中数据库用户名
            dataSource.setUsername(properties.getProperty("username"));
            //读取配置文件中用户名对应密码
            dataSource.setPassword(properties.getProperty("password"));
            //读取配置文件中初始化连接数量
            dataSource.setInitialSize(Integer.parseInt(properties.getProperty("initPoolSize")));
            //配置最大空闲时间
            dataSource.setMaxWait(Long.parseLong(properties.getProperty("maxIdleTime")));
            //配置获取连接最大时间
            dataSource.setMaxCreateTime(Long.parseLong(properties.getProperty("maxCreateTime")));
            //配置最大并发连接数
            dataSource.setMaxActive(Integer.parseInt(properties.getProperty("maxPoolSize")));
            for (int i = 0; i <8; i++) {
                new Thread(new rundemo(dataSource)).start();

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

测试结果：

当前剩余连接数 get：3
当前剩余连接数 get：4
当前剩余连接数 get：5
当前剩余连接数 get：6
当前剩余连接数 get：6
当前剩余连接数 get：6
当前剩余连接数 get：6
当前剩余连接数 get：6
当前剩余连接数 get：6
当前剩余连接数 get：6
当前剩余连接数：5
当前剩余连接数：4
当前剩余连接数：3