前言
之前开发一个系统，需要连接sql server 的多个数据库，且具体会有多少数据库是未知的，要连接的数据库是动态变化的。无论是网上的资源，jar包还是框架里提供的现有的方法和我的业务需求不太相符（不排除本人才疏学浅，不知道有比较适合我的业务需求的解决方案的可能性）。反正我就自己实现了一个数据库连接池来适配了自己的需求。
代码结构
没啥好说的，就是一个类表示数据库连接池，一个类封装了数据库连接。
代码
直接上代码吧
池内连接
package com.springboot.finance.SqlserverConnectionPool;
import com.springboot.finance.domain.MyResponseEnums;
import com.springboot.finance.domain.MyRuntimeException;
import java.util.Date;
import java.sql.*;
/**
* @program: finance
* @description: 记录每个连接及其状态
* @author: William Munch
* @create: 2019-10-02 14:12
**/
public class ConnectionInPool implements Comparable<ConnectionInPool>{
private static String driver ="com.microsoft.sqlserver.jdbc.SQLServerDriver"; // 数据库驱动
private static String url = "jdbc:sqlserver://localhost:1433;databasename="; // 数据 URL
private String database = ""; // 数据库名
private String username = "sa"; // 数据库用户名
private String password = "sa"; // 数据库用户密码
private Connection conn = null; // 数据库连接
private Statement stmt = null;
private ResultSet rs = null;
private String sql="";
private boolean busy = false;// 此连接是否正在使用的标志，默认没有正在使用
private Date lasttime=null;//上次使用的时间
public ConnectionInPool(String database,String username,String password) {
this.database=database;
this.username=username;
this.password=password;
this.busy=false;
lasttime=new Date();
}
public boolean isEqual(ConnectionInPool pCon)
{
if (this.database.equals(pCon.getDatabase())&&this.username.equals(pCon.getUsername()))
return true;
else
return false;
}
public String getDatabase() {
return database;
}
public Date getLasttime() {
return lasttime;
}
public void setLasttime(Date lasttime) {
this.lasttime = lasttime;
}
public String getUsername() {
return username;
}
public Connection getConnection() {
return conn;
}
public void setConnection() {
try {
Class.forName( driver );
this.conn = DriverManager.getConnection(url+database,username,password);
} catch (Exception e) {
System.out.println( MyResponseEnums.DATABASE_CONNECTION_ERROR.getMsg());
throw new MyRuntimeException(MyResponseEnums.DATABASE_CONNECTION_ERROR);
}
}
//执行不产生ResultSet的语句
public void executeSp(String sql) {
try {
setStmt();
setSql(sql);
this.stmt.execute(this.sql);
}catch (MyRuntimeException e) {
throw e;
} catch (Exception e) {
System.out.println( MyResponseEnums.DATABASE_RESULTSET_ERROR.getMsg());
throw new MyRuntimeException(MyResponseEnums.DATABASE_RESULTSET_ERROR);
}
}
public boolean isBusy() {
return busy;
}
public void setBusy(boolean busy) {
this.busy = busy;
}
public void setStmt() {
try {
setConnection();
this.stmt = this.conn.createStatement();
} catch (MyRuntimeException e) {
throw e;
}catch (Exception e) {
System.out.println( MyResponseEnums.DATABASE_STATEMENT_ERROR.getMsg());
throw new MyRuntimeException(MyResponseEnums.DATABASE_STATEMENT_ERROR);
}
}
public void setSql(String sql) {
this.sql = sql;
}
public ResultSet getRs() {
return rs;
}
public void setRs(String sql) {
try {
setStmt();
setSql(sql);
this.rs= this.stmt.executeQuery(this.sql);
}catch (MyRuntimeException e) {
throw e;
} catch (Exception e) {
System.out.println( MyResponseEnums.DATABASE_RESULTSET_ERROR.getMsg());
throw new MyRuntimeException(MyResponseEnums.DATABASE_RESULTSET_ERROR);
}
}
public void closeConnection()
{
try {
if (rs!=null)
rs.close();
if (stmt!=null)
stmt.close();
if (conn!=null)
conn.close();
} catch (Exception e) {
System.out.println( MyResponseEnums.DATABASE_CLOSE_ERROR.getMsg());
throw new MyRuntimeException(MyResponseEnums.DATABASE_CLOSE_ERROR);
}
}
@Override
public int compareTo(ConnectionInPool o) {
return lasttime.compareTo(o.getLasttime());
}
}
连接池
package com.springboot.finance.SqlserverConnectionPool;
import com.springboot.finance.domain.MyResponseEnums;
import com.springboot.finance.domain.MyRuntimeException;
import java.sql.*;
import java.util.Collections;
import java.util.Date;
import java.util.Enumeration;
import java.util.Iterator;
import java.util.Vector;
/**
* @program: finance
* @description: 数据库连接池
* @author: William Munch
* @create: 2019-10-02 14:14
**/
public class ConnectionPool {
private Vector<ConnectionInPool> connections = null; // 存放连接池中数据库连接的向量
private int MaxConnectionNum=50;
private int cleanConnectionNum=5;
private int FreeConnectionNum=0;
private int BusyConnectionNum=0;
// 设置成单例模式 防止多次实例化连接池
private static ConnectionPool instance = new ConnectionPool();
private ConnectionPool(){};
public static ConnectionPool getInstance(){
return instance;
}
// //检测该连接是否可用
// public boolean testConnection(ConnectionInPool pConn)
// {
// Statement stmt = null;
// String sql="";
// ResultSet rs=null;
// try {
// stmt = pConn.getConnection().createStatement();
// sql = "SELECT @@VERSION";
// rs = stmt.executeQuery(sql);
//
// if (rs!=null)
// rs.close();
// if (stmt!=null)
// stmt.close();
// } catch (SQLException e) {
// return false;
// }
// return true;
// }
public int getFreeConnectionNum() {
return FreeConnectionNum;
}
public int getBusyConnectionNum() {
return BusyConnectionNum;
}
//创建连接池
public synchronized void createPool()
{
if (connections != null)
{
return; // 假如己经创建，则返回
}
connections = new Vector();
System.out.println("【创建连接池阶段】数据库连接池创建成功！ ");
System.out.println("【创建连接池阶段】连接池内连接数为"+connections.size());
}
//连接数大于最大数的时候，就清除一些
public void refreshConnectionPool()
{
Collections.sort(connections);
Iterator it = connections.iterator();
ConnectionInPool TEMPpConn=null;
int freenum=0;
while(it.hasNext()) {
TEMPpConn=(ConnectionInPool)it.next();
if (!TEMPpConn.isBusy())
{
freenum++;
try {
TEMPpConn.closeConnection();
}
catch (MyRuntimeException e) {
System.out.println("【清理连接池阶段】池内连接关闭失败，直接移除");
}
it.remove();
}
if (freenum>=cleanConnectionNum)
break;
}
}
//遍历数据池得到连接的细节（是否空闲）
public void getConnectionDetail()
{
// 获得连接池向量中所有的对象
Enumeration enumerate = connections.elements();
ConnectionInPool TEMPpConn=null;
int freenum=0;
int busynum=0;
while (enumerate.hasMoreElements())
{
TEMPpConn = (ConnectionInPool) enumerate.nextElement();
if (!TEMPpConn.isBusy())
freenum++;
else
busynum++;
}
FreeConnectionNum=freenum;
BusyConnectionNum=busynum;
}
//查找连接指定数据库的空闲的连接，并返回
public synchronized ConnectionInPool getFreeConnectionFromPool(String databasename,String username,String password)
{
getConnectionDetail();
System.out.println("【查找可用连接阶段】正在寻找可用连接，池内连接数为"+connections.size());
System.out.println("【查找可用连接阶段】空闲个数为"+getFreeConnectionNum()+"个，忙碌个数为"+getBusyConnectionNum()+"个");
//如果连接数过多，就清理
if(connections.size()>=MaxConnectionNum)
{
System.out.println("【查找可用连接阶段】池内连接过多，开始清理连接池");
refreshConnectionPool();
System.out.println("【查找可用连接阶段】完成清理连接池");
}
ConnectionInPool pConn = null;
Iterator it = connections.iterator();//删除的时候不会有元素前移，enumerate遍历删除会有元素前移
ConnectionInPool TEMPpConn=null;
while (it.hasNext())
{
TEMPpConn = (ConnectionInPool)it.next();
if (TEMPpConn.getDatabase().equals(databasename)&&TEMPpConn.getUsername().equals(username)&&!TEMPpConn.isBusy()) //是业务所需连接的数据库且非busy
{
try {
if (TEMPpConn.getConnection()==null||!TEMPpConn.getConnection().isValid(1) )
{
// 假如此连接不可再用了，则删除
TEMPpConn.closeConnection();
it.remove();
System.out.println("【查找可用连接阶段】找到的连接异常，已清除连接。池内连接数为"+connections.size());
}
else
{
TEMPpConn.setBusy(true);
pConn=TEMPpConn;
System.out.println("【查找可用连接阶段】己经找到一个可用的连接。池内连接数为"+connections.size());
break;
}
}catch (MyRuntimeException e) {
System.out.println("【查找可用连接阶段】"+ e.getMsg());
it.remove();
System.out.println("【查找可用连接阶段】已清除异常连接。池内连接数为"+connections.size());
throw e;
}
catch (Exception e)
{
System.out.println("【查找可用连接阶段】"+ MyResponseEnums.DATABASE_CONNECTION_CHECK_ERROR.getMsg());
it.remove();
System.out.println("【查找可用连接阶段】已清除异常连接。池内连接数为"+connections.size());
throw new MyRuntimeException(MyResponseEnums.DATABASE_CONNECTION_CHECK_ERROR);
}
}
}
if (pConn==null)
{
System.out.println("【查找可用连接阶段】未找到可用连接，正在创建新的连接。池内连接数为"+connections.size());
ConnectionInPool pConnNew=new ConnectionInPool(databasename,username,password);
pConnNew.setBusy(true);
pConn=pConnNew;
connections.addElement(pConnNew);
System.out.println("【查找可用连接阶段】完成创建新的连接。池内连接数为"+connections.size());
}
getConnectionDetail();
System.out.println("【查找可用连接阶段】空闲个数为"+getFreeConnectionNum()+"个，忙碌个数为"+getBusyConnectionNum()+"个");
return pConn; // 返回找到到的可用连接
}
public synchronized void returnConnectionToPool( ConnectionInPool pConn)
{
// 确保连接池存在，假如连接没有创建（不存在），直接返回
if (connections == null)
{
System.out.println("【返还连接至池阶段】连接池不存在，无法返回此连接到连接池中 !");
System.out.println("###############################################################################");
return;
}
if (pConn == null)
{
System.out.println("【返还连接至池阶段】连接为空，无法返回此连接到连接池中 !");
System.out.println("###############################################################################");
return;
}
boolean findflag=false;
getConnectionDetail();
System.out.println("【返还连接至池阶段】归还连接前，连接池内连接数为"+connections.size());
System.out.println("【返还连接至池阶段】空闲个数为"+getFreeConnectionNum()+"个，忙碌个数为"+getBusyConnectionNum()+"个");
Enumeration enumerate = connections.elements();
while (enumerate.hasMoreElements())
{
ConnectionInPool TEMPpConn = (ConnectionInPool) enumerate.nextElement();
// 先找到连接池中的要返回的连接对象
if (TEMPpConn == pConn)
{
// 找到了 , 设置此连接为空闲状态
TEMPpConn.setLasttime(new Date());
TEMPpConn.setBusy(false);
findflag=true;
break;
}
}
getConnectionDetail();
if (findflag)
System.out.println("【返还连接至池阶段】归还连接后，连接池内连接数为"+connections.size());
else
System.out.println("【返还连接至池阶段】未找到连接，归还失败。连接池内连接数为"+connections.size());
System.out.println("【返还连接至池阶段】空闲个数为"+getFreeConnectionNum()+"个，忙碌个数为"+getBusyConnectionNum()+"个");
System.out.println("###############################################################################");
}
public synchronized void closePool()
{
// 确保连接池存在，假如不存在，返回
if (connections == null)
{
System.out.println("【销毁连接池阶段】连接池不存在，无法销毁 !");
throw new MyRuntimeException(MyResponseEnums.NO_POOL_ERROR);
}
ConnectionInPool pConn = null;
Iterator it = connections.iterator();
InterruptedException ie=null;
MyRuntimeException me=null;
while (it.hasNext())
{
pConn = (ConnectionInPool)it.next();
// 假如忙，等 5 秒
try{ if (pConn.isBusy())
Thread.sleep(5000);
}
catch (InterruptedException e)
{
System.out.println("【销毁连接池阶段】有连接正在使用，线程等待五秒失败");
ie=e;
}
//5 秒后直接关闭它
try {
pConn.closeConnection();
}
catch (MyRuntimeException e) {
System.out.println("【销毁连接池阶段】池内连接关闭失败");
me=e;
}
it.remove();
}
// 置连接池为空
connections = null;
if (me!=null)
throw me;
if (ie!=null)
throw new MyRuntimeException(MyResponseEnums.THREAD_SLEEP_ERROR);
}
}
使用
public void connectToDatabase(String databasename,String username,String password, String sql)
{
ConnectionPool pool = ConnectionPool.getInstance();
pool.createPool();
ConnectionInPool pConn=pool.getFreeConnectionFromPool( databasename, username, password);
pConn.setRs(sql);
ResultSet rs =pConn.getRs();
pool.returnConnectionToPool(pConn);
}辅助类
package com.springboot.finance.domain;
/**
* @program: finance
* @description: 友好提示枚举
* @author: William Munch
* @create: 2019-08-20 13:06
**/
public enum MyResponseEnums {
//Config Error 1xxx 基础配置异常
CONNECTION_ERROR("1000", "网络连接请求失败"),
DATABASE_ERROR("1001", "数据库异常"),
DATABASE_CONNECTION_ERROR("1002", "数据库连接失败"),
DATABASE_STATEMENT_ERROR("1003", "实例化STATEMENT失败"),
DATABASE_RESULTSET_ERROR("1004", "实例化RESULTSET失败"),
DATABASE_CLOSE_ERROR("1005", "数据库关闭失败"),
DATABASE_CONNECTION_CHECK_ERROR("1006", "数据库连接校验失败"),
RESULTSET_TO_JSON_ERROR("1007", "RESULTSET转换JSON失败"),
THREAD_SLEEP_ERROR("1008", "线程等待失败"),
NO_POOL_ERROR("1009", "无数据库连接池，无从销毁"),
//Success 2xxx 业务逻辑成功
REGISTER_SUCCESS("2000", "注册成功"),
LOGIN_SUCCESS("2001", "登陆成功"),
LOGOUT_SUCCESS("2002", "已退出登录"),
CALL_SUCCESS("2003", "调用成功"),
SEND_EMAIL_SUCCESS("2004", "邮件已发送，请注意查收"),
EDIT_PWD_SUCCESS("2005", "修改密码成功"),
UPLOAD_FILE_SUCCESS("2006", "上传成功"),
OPERATE_SUCCESS("2007", "操作成功"),
//Error 3xxx 业务逻辑失败
REPEAT_REGISTER("3001", "重复注册"),
NO_USER_EXIST("3002", "用户不存在"),
INVALID_PASSWORD("3003", "密码错误"),
NO_LOGIN("3004", "未登陆"),
NO_FILE("3005", "无该文件（夹）"),
ERROR_IDCODE("3006", "验证码不正确"),
NO_RECORD("3007", "没有查到相关记录"),
REPEAT_MOBILE("3008", "已存在此手机号"),
REPEAT_EMAIL("3009", "已存在此邮箱地址"),
INVALID_MOBILE("3010", "无效的手机号码"),
INVALID_EMAIL("3011", "无效的邮箱"),
INVALID_GENDER("3012", "无效的性别"),
MORE_THAN_ONE_RECORD("3013", "查到多条相关记录"),
REPEAT_CONTRACT("3014", "已存在此合同编号"),
NO_USER_TO_COMPANY("3015", "没有用户与该公司名关联"),
MORE_THAN_ONE_USER_TO_COMPANY("3016", "查到多个用户与该公司名关联"),
REPEAT_COMPANY("3017", "已存在用户与此公司对应"),
//Client Error 4xxx 客户端错误 仿照4xx的http错误
BAD_REQUEST("4000", "错误的请求参数"),
UNAUTHORIZED("4001", "未经授权"),
PAYMENT_REQUIRED("4002", "付费请求"),
FORBIDDEN("4003", "资源不可用"),
NOT_FOUND("4004", "无效的访问路径"),
METHOD_NOT_ALLOWED("4005", "不合法的请求方式"),
NOT_ACCEPTABLE("4006", "不可接受"),
PROXY_AUTHENTICATION_REQUIRED("4007", "需要代理身份验证"),
REQUEST_TIMEOUT("4008", "请求超时"),
CONFLICT("4009", "指令冲突"),
GONE("4010", "文档永久地离开了指定的位置"),
LENGTH_REQUIRED("4011", "需要CONTENT-LENGTH头请求"),
PRECONDITION_FAILED("4012", "前提条件失败"),
REQUEST_ENTITY_TOO_LARGE("4013", "请求实体太大"),
REQUEST_URI_TOO_LONG("4014", "请求URI太长"),
UNSUPPORTED_MEDIA_TYPE("4015", "不支持的媒体类型"),
REQUESTED_RANGE_NOT_SATISFIABLE("4016", "请求的范围不可满足"),
EXPECTATION_FAILED("4017", "期望失败"),
//Server Error 5xxx 服务器错误 仿照5xx的http错误
INTERNAL_SERVER_ERROR("5000", "内部服务器错误"),
NOT_IMPLEMENTED("5001", "未实现"),
BAD_GATEWAY("5002", "错误的网关"),
SERVICE_UNAVAILABLE("5003", "服务不可用"),
GATEWAY_TIMEOUT("5004", "网关超时"),
HTTP_VERSION_NOT_SUPPORTED("5005", "HTTP版本不支持"),
//终极赖皮手段
UNKNOWN_ERROR("0000", "未知错误");
private String code;
private String msg;
private MyResponseEnums(String code, String msg) {
this.code = code;
this.msg = msg;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
public String getMsg() {
return msg;
}
public void setMsg(String msg) {
this.msg = msg;
}
}
package com.springboot.finance.domain;
/**
* @program: finance
* @description: 自定义异常 比如普通用户的越权行为，管理员修改超级管理员的信息，查找不存在的人，等等，系统会报告异常并提示信息
* @author: William Munch
* @create: 2019-08-20 13:07
**/
public class MyRuntimeException extends RuntimeException {
private static final long serialVersionUID = 6863853281199294164L;
protected String code;
protected String msg;
public MyRuntimeException() {
super();
}
public MyRuntimeException(MyResponseEnums enums) {
super();
this.code = enums.getCode();
this.msg = enums.getMsg();
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
public String getMsg() {
return msg;
}
public void setMsg(String msg) {
this.msg = msg;
}
}
872
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
