初创原生Java数据库连接池

爱被打了一巴掌 2022-04-25 06:40 121阅读 0赞

前言：  
项目中使用了mysql数据库连接池类，这个类自动管理所有数据库连接对象，自动实现心跳维持，容错处理，读写分离等。  
这篇文章是转载，并加上了自己的见解，能力有限，学习收藏罢了。

代码结构预览  
![在这里插入图片描述][20190530092859242.png]  
配置文件：

public class Setup {
    
        public final static String DBurl="127.0.0.1"; //数据库地址
        public final static String DBurlS="127.0.0.1"; //数据库地址(副)
        public final static String DBport="3306"; //端口
        public final static String DBuser="root"; //数据库的名字
        public final static String DBpassword="123456"; //数据库密码
        public final static String DBtable="game"; //表名
        public final static int DBpoolSUM=5; //连接线程的最大值数量
    }

核心类：  
不依赖除第三方类

import java.sql.*;
    import java.util.NoSuchElementException;
    import java.util.Timer;
    import java.util.TimerTask;
    import java.util.concurrent.ArrayBlockingQueue;
    
    
    public class DBConnectO {
        public static int DBConnectOKeepTimeInt;
        public static ArrayBlockingQueue<Connection> ConnectionQueue =new ArrayBlockingQueue<Connection>(Setup.DBpoolSUM);
        public static ArrayBlockingQueue<Connection> ReadConnectionQueue =new ArrayBlockingQueue<Connection>(Setup.DBpoolSUM);
        public static void Initialization(){
            System.out.println("DBConnectO 数据库连接池开始启动......");
            try {
                Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
            } catch (Exception ex) {
                System.out.println(">>>>>>>>>>>>>"+ex.getMessage());
            }
            int row;
            try {
                for (row=1; row <= Setup.DBpoolSUM; row++) {
                    //主
                    Connection connection= DriverManager.getConnection("jdbc:mysql://"+ Setup.DBurl+":"+Setup.DBport+"/"+Setup.DBtable+"?characterEncoding=utf8&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=GMT%2B8",Setup.DBuser,Setup.DBpassword);
                    ConnectionQueue.add(connection);
                    //副
                    Connection readConnection= DriverManager.getConnection("jdbc:mysql://"+ Setup.DBurl+":"+Setup.DBport+"/"+Setup.DBtable+"?characterEncoding=utf8&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=GMT%2B8",Setup.DBuser,Setup.DBpassword);
                    ReadConnectionQueue.add(readConnection);
                }
                //启动数据库连接心跳线
                new DBConnectKeepWorkerIni().start();
            }catch (SQLException SqlE){
                System.out.println("地址"+Setup.DBurl+" 端口"+Setup.DBport+" 表"+Setup.DBtable+ " 用户名"+Setup.DBuser+" 密码" +Setup.DBpassword);
                System.out.println("DBConnectO :: "+SqlE.getMessage());
                SqlE.printStackTrace();
                System.out.println(SqlE);
            }
        }
        //state 1 表示有写入权限 2表示只读取
        public static Connection getCon(int state) throws SQLException{
            Connection redConnection;
            try {
                if (state == 2) {
                    if( ReadConnectionQueue.size()>0) {
                        redConnection = ReadConnectionQueue.remove();
                    }else {
                        redConnection = DriverManager.getConnection("jdbc:mysql://" + Setup.DBurlS + ":" + Setup.DBport + "/" + Setup.DBtable + "?characterEncoding=utf8&autoReconnect=true&useSSL=false", Setup.DBuser, Setup.DBpassword);
                    }
                } else {
                    if(ConnectionQueue.size()>0) {
                        redConnection = ConnectionQueue.remove();
                    }else {
                        redConnection = DriverManager.getConnection("jdbc:mysql://" + Setup.DBurl + ":" + Setup.DBport + "/" + Setup.DBtable + "?characterEncoding=utf8&autoReconnect=true&useSSL=false", Setup.DBuser, Setup.DBpassword);
                    }
                }
            }catch (NoSuchElementException NoSuchE){
                //当队列中没有时，创建一条连接对象出去
                System.out.println("初始化连接数据库线程不够！！！！");
                if (state == 2) {
                    redConnection = DriverManager.getConnection("jdbc:mysql://" + Setup.DBurlS + ":" + Setup.DBport + "/" + Setup.DBtable + "?characterEncoding=utf8&autoReconnect=true&useSSL=false", Setup.DBuser, Setup.DBpassword);
                } else {
                    redConnection = DriverManager.getConnection("jdbc:mysql://" + Setup.DBurl + ":" + Setup.DBport + "/" + Setup.DBtable + "?characterEncoding=utf8&autoReconnect=true&useSSL=false", Setup.DBuser, Setup.DBpassword);
                }
            }
            return redConnection;
        }
        public static void redCon(int state,Connection connection){
            try {
                if(state==2){
                    if(ReadConnectionQueue.size()<Setup.DBpoolSUM) {
                        ReadConnectionQueue.add(connection);
                    }else {
                        connection.close();
                    }
                }else {
                    if(ConnectionQueue.size()<Setup.DBpoolSUM) {
                        ConnectionQueue.add(connection);
                    }else {
                        connection.close();
                    }
                }
            }catch (Exception e){
                try {
                    connection.close();
                }catch (SQLException sqlE){
                    //不做任何处理
                }
            }
        }
    }
    class DBConnectKeepWorkerIni extends Thread {
        public static int keepTime=120000;
        public static int manageKeepTime=10000;
        public void run() {
            //检测心跳线 运行情况
            new Timer().schedule(new KeepWorkerManange(), manageKeepTime, manageKeepTime);
            //启动数据库连接心跳线
            new Timer().schedule(new KeepWorker(), keepTime, keepTime);
        }
    }
    class KeepWorkerManange extends TimerTask{
        @Override
        public void run() {
            int nowTime=(int)(System.currentTimeMillis()/1000);
            if( nowTime -DBConnectO.DBConnectOKeepTimeInt>DBConnectKeepWorkerIni.keepTime*3){ //表示心跳线死亡需要重启心跳
                System.out.println("监听到数据库连接池心跳死亡，重启一下心跳 上次最后一下心跳"+DBConnectO.DBConnectOKeepTimeInt+" 本次启动时间:"+nowTime);
                new Timer().schedule(new KeepWorker(), DBConnectKeepWorkerIni.keepTime, DBConnectKeepWorkerIni.keepTime);
            }
        }
    }
    class KeepWorker extends TimerTask {
        public KeepWorker(){
            DBConnectO.DBConnectOKeepTimeInt=(int)(System.currentTimeMillis()/1000);
        }
        public void run() {
            //喂狗
            DBConnectO.DBConnectOKeepTimeInt=(int)(System.currentTimeMillis()/1000);
            System.out.println("数据库连接线程心跳线启动,时间："+System.currentTimeMillis());
            try {
                for (int row = 1; row <= Setup.DBpoolSUM; row++) {
                    try {
                        Connection con = DBConnectO.ConnectionQueue.remove();
                        Statement st = con.createStatement();
                        ResultSet resultSet =st.executeQuery("show tables");
                        resultSet.close();
                        st.close();
                        DBConnectO.ConnectionQueue.add(con);
                    }catch (Exception e){
                        System.out.println(e.getMessage());
                        System.out.println(e.getStackTrace());
                        Connection upCon = DriverManager.getConnection("jdbc:mysql://" + Setup.DBurl + ":" + Setup.DBport + "/" + Setup.DBtable + "?characterEncoding=utf8&autoReconnect=true&useSSL=false", Setup.DBuser, Setup.DBpassword);
                        DBConnectO.ConnectionQueue.add(upCon);
                    }
                }
            }catch(SQLException e) {
                System.out.println(e.getMessage());
                System.out.println(e.getStackTrace());
            }
            try {
                for (int row = 1; row <= Setup.DBpoolSUM; row++) {
                    try {
                        Connection readCon =DBConnectO.ReadConnectionQueue.remove();
                        Statement st = readCon.createStatement();
                        st.executeQuery("show tables");
                        st.close();
                        DBConnectO.ReadConnectionQueue.add(readCon);
                    }catch (Exception e){
                        System.out.println(e.getMessage());
                        System.out.println(e.getStackTrace());
                        Connection readCon = DriverManager.getConnection("jdbc:mysql://" + Setup.DBurl + ":" + Setup.DBport + "/" + Setup.DBtable + "?characterEncoding=utf8&autoReconnect=true&useSSL=false", Setup.DBuser, Setup.DBpassword);
                        DBConnectO.ReadConnectionQueue.add(readCon);
                    }
                }
            }catch(SQLException e) {
                System.out.println(e.getMessage());
                System.out.println(e.getStackTrace());
            }
            System.out.println("数据库连接线程心跳结束,时间："+System.currentTimeMillis()+" 修改对象池："+DBConnectO.ConnectionQueue.size()+" 读取对象池："+DBConnectO.ReadConnectionQueue.size());
        }
    }

项目中使用

import java.sql.Connection;
    
    // 使用方法：
    public class Test01 {
    
        public static void main(String[] args) {
    
            //  初始化：
            //开始启动数据池
            DBConnectO.Initialization();
    
            //开始启动数据池
            try {
                Connection con =DBConnectO.getCon(1); //获取一个主数据库连接对象（主数据库负责写入，当然可以读）
                try{
    
                /**
                 * 这里就是你实际的逻辑部分，注意不能调用con.close(),
                 * 中途使用过的的PreparedStatement或者ResultSet对象注意使用close()关闭调用，
                 *  利于服务长期运行。
                 */
    
                }catch (Exception e){
    //                message.setState(1);
    //                message.setEmsg("服务器异常");
                }finally {
                    DBConnectO.redCon(1,con); //放回到连接池中，让其它线程继续使用
                }
            }catch (Exception e){
    //            message.setState(1);
    //            message.setEmsg("服务器异常");
            }
    
        }
    }

[20190530092859242.png]: /images/20220126/f2725dd2d8814f178f088a1c054d7fc6.png