前言
之前开发一个系统，需要连接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: -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="; // 数据 URLprivate 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;elsereturn 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);}}@Overridepublic int compareTo(ConnectionInPool o) {return pareTo(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: -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++;elsebusynum++;}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());elseSystem.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: -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("", "调用成功"),SEND_EMAIL_SUCCESS("", "邮件已发送，请注意查收"),EDIT_PWD_SUCCESS("", "修改密码成功"),UPLOAD_FILE_SUCCESS("", "上传成功"),OPERATE_SUCCESS("", "操作成功"),//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: -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;}}
