龙空技术网

贡献自己写数据库连接池(mysql,java)

坏桃子 896

前言:

当前我们对“mysql数据连接池”大概比较关注,小伙伴们都想要分析一些“mysql数据连接池”的相关知识。那么小编同时在网络上搜集了一些关于“mysql数据连接池””的相关知识,希望大家能喜欢,姐妹们快快来学习一下吧!

这里和大家分享一个我自己项目中使用了mysql数据库连接池类,这个类自动管理所有数据库连接对象,自动实现心跳维持,容错处理,读写分离等。

配置文件(conf.java):

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; //连接线程的最大值数量

核心类(DBConnectO.java):

package GamePlatformServer.classFile;import GamePlatformServer.conf.Setup;import java.sql.*;import java.util.NoSuchElementException;import java.util.Timer;import java.util.TimerTask;import java.util.concurrent.ArrayBlockingQueue;/** * Created by lt on 2017/2/24. */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){ //当队列中没有时,创建一条连接对象出去 Log.errorLog("初始化连接数据库线程不够!!!!"); 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){ //表示心跳线死亡需要重启心跳 Log.errorLog("监听到数据库连接池心跳死亡,重启一下心跳 上次最后一下心跳"+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); Log.errorLog("数据库连接线程心跳线启动,时间:"+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){ Log.errorLog(e.getMessage()); Log.errorLog(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) { Log.errorLog(e.getMessage()); Log.errorLog(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){ Log.errorLog(e.getMessage()); Log.errorLog(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) { Log.errorLog(e.getMessage()); Log.errorLog(e.getStackTrace()); } Log.errorLog("数据库连接线程心跳结束,时间:"+System.currentTimeMillis()+" 修改对象池:"+DBConnectO.ConnectionQueue.size()+" 读取对象池:"+DBConnectO.ReadConnectionQueue.size()); }}

使用方法:

初始化:

//开始启动数据池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("服务器异常");}

最后说一下,为啥我要自己写一个数据库连接池,第一:减少一个jar引用,第二:增加了解整个架构的具体情况,第三:代码短小精干,实用就好。

最后感谢和我一样的梦想追梦人们。

标签: #mysql数据连接池