多数据源连接池

来源：互联网发布：传奇3 网络传输异常编辑：程序博客网时间：2024/06/05 01:51

做元数据管理，需要查询多个数据源的元数据，不用orm框架，直接用java实现。如下

主要有几个重要的类：MultiPool (连接池) ConnectionManager (连接管理器) DBConenction (数据库连接对象)

1. MultiPool 和 ConnectionManager 都使用单例模式

2. 数据库连接参数如driverClassName, url 使用properties配置

3. 经测试，mysql,oracle, hive2测试通过

4. 添加动态和静态管理连接池个数(还未实现，后续补充)

MultiPool代码

package wuyx.multipool;

import java.sql.Connection;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;
import java.util.Vector;

/***
* 多数据源链接池
* @author wuyx
*
*/
public class MultiPool {
   private static MultiPool instance=null;

   private static Map<String, List<Connection>> pool;
   private static Map<String, List<Connection>> free = null;

   static{
       free = new Hashtable<String, List<Connection>>();
       pool = new Hashtable<String, List<Connection>>();
   }

   /**
   * 获取实例
   * @return
   */
   public static MultiPool getInstance(){
       if(instance == null){
           instance = new MultiPool();
       }
       return instance;
   }

   /**
   * 获取一个指定库的连接
   * @param dbType
   * @return
   */
   public Connection get(String dbType){
       return first(dbType);
   }

   /**
   * 返回第一个空闲的连接
   * @param dbType
   * @return
   */
   synchronized Connection first(String dbType){
       Connection ret = free.get(dbType).get(0);
       free.remove(0);
       return ret;
   }

   /**
   * 释放连接
   * @param dbType
   * @param conn
   */
   public synchronized void release(String dbType, Connection conn){
       free.get(dbType).add(conn);
   }

   /**
   * 往连接池中增加一个连接
   * @param dbType
   * @param conn
   */
   public synchronized void add(String dbType, Connection conn){
       if(pool.get(dbType) == null){
           List<Connection> list = new Vector<Connection>();
           pool.put(dbType, list);
       }
       if(free.get(dbType) == null){
           List<Connection> fs = new Vector<Connection>();
           free.put(dbType, fs);
       }

       pool.get(dbType).add(conn); //连接池增加一个连接
       free.get(dbType).add(conn); //同时往空闲池增加一个连接

       System.out.println("add");
   }

   /***
   * 连接池是否活动
   * @return
   */
   public boolean isAlive(){
       if(pool.size() == 0) return false;
       return true;
   }

   /**
   * 关闭连接池
   */
   public void close(){
       pool.clear();
       free.clear();
   }

}

ConnectionManager代码

package wuyx.multipool;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

/***
* 连接管理器
* @author wuyx
*
*/
public class ConnectionManager {

   private static ConnectionManager instance = null;//单例模式
   private static MultiPool pool = MultiPool.getInstance();

   /***
   * 获取实例
   * @return
   */
   public static synchronized ConnectionManager getInstance(){
       if(instance == null){
           instance = new ConnectionManager();
       }
       return instance;
   }

   /**
   * 获取连接
   * @param dbType 数据库类型
   * @return
   */
   public static Connection getConnection(String dbType){
       return pool.get(dbType);
   }

   public static void closeConnection(String dbType, Connection conn){
       pool.release(dbType, conn);
   }

   /***
   * 初始化连接池
   * @param conns
   */
   public void init(List<DBConnection> conns){
       if(pool.isAlive()){
           System.out.println("connection pool has been init");
           return;
       }

       for(DBConnection db:conns){
           for(int i=0;i<db.getInitialSize();i++){
               String dbType = db.getDbType();
               Connection conn = connection(db.getDriverClassName(), db.getUrl(), db.getUsername(), db.getPassword());
               pool.add(dbType, conn);
           }
       }
       System.out.println("complete");
   }

   /**
   * 摧毁连接池
   */
   public void destroy(){
       pool.close();
   }

   /**
   * 连接数据库
   * @param driverClass
   * @param url
   * @param username
   * @param password
   * @return
   */
   private Connection connection(String driverClass, String url, String username, String password){

       try {
           Class.forName(driverClass);
           Connection conn = DriverManager.getConnection(url, username, password);
           System.out.println("connect success");
           return conn;
       } catch (SQLException e) {
           e.printStackTrace();
           return null;
       } catch (ClassNotFoundException e1){
           e1.printStackTrace();
           return null;
       }
   }
}

DBConnection代码

package wuyx.multipool;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Properties;

public class DBConnection {

   private static Properties props = null;
   static{//加载配置
       props = new Properties();
       try {
           props.load(new FileInputStream("./src/conf/jdbc.properties"));
       } catch (FileNotFoundException e) {
           e.printStackTrace();
       } catch (IOException e) {
           e.printStackTrace();
       }
   }

   /**
   * 获取驱动类名
   */
   private String buildDriverClassName(String dbType){
       String key = new StringBuilder().append(dbType).append(".").append("driverClassName").toString();
       return props.getProperty(key);
   }

   /**
   * 拼接url
   */
   private String buildUrl(String dbType, String ip, String port, String db){
       String key = new StringBuilder().append(dbType).append(".").append("url").toString();
       String value = props.getProperty(key);
       return urlReplace(value, new String[]{ip, port, db});
   }
   /**
   * 替换url
   */
   private String urlReplace(String url, String[] args){
       url = url.replace("[ip]", args[0]);
       url = url.replace("[port]", args[1]);
       url = url.replace("[db]", args[2]);
       return url;
   }

   public DBConnection(String dbType, String driverClassName, String url, String username,
           String password) {
       super();
       this.dbType = dbType;
       this.driverClassName = driverClassName;
       this.url = url;
       this.username = username;
       this.password = password;
   }
   public DBConnection(String dbType, String ip, String port, String db, String username, String password) {
       super();
       this.dbType = dbType;
       this.driverClassName = buildDriverClassName(dbType);
       this.url = buildUrl(dbType, ip, port, db);
       this.username = username;
       this.password = password;
   }


   public String getDriverClassName() {
       return driverClassName;
   }
   public void setDriverClassName(String driverClassName) {
       this.driverClassName = driverClassName;
   }
   public String getUrl() {
       return url;
   }
   public void setUrl(String url) {
       this.url = url;
   }
   public String getUsername() {
       return username;
   }
   public void setUsername(String username) {
       this.username = username;
   }
   public String getPassword() {
       return password;
   }
   public void setPassword(String password) {
       this.password = password;
   }
   public int getMinIdle() {
       return minIdle;
   }
   public void setMinIdle(int minIdle) {
       this.minIdle = minIdle;
   }
   public int getMaxIdle() {
       return maxIdle;
   }
   public void setMaxIdle(int maxIdle) {
       this.maxIdle = maxIdle;
   }
   public int getInitialSize() {
       return initialSize;
   }
   public void setInitialSize(int initialSize) {
       this.initialSize = initialSize;
   }
   public int getMaxActive() {
       return maxActive;
   }
   public void setMaxActive(int maxActive) {
       this.maxActive = maxActive;
   }
   public int getMaxWait() {
       return maxWait;
   }
   public void setMaxWait(int maxWait) {
       this.maxWait = maxWait;
   }
   public String getDbType() {
       return dbType;
   }
   public void setDbType(String dbType) {
       this.dbType = dbType;
   }

   private String dbType;
   private String driverClassName;
   private String url;
   private String username;
   private String password;
   private int minIdle = 1; //最小个数
   private int maxIdle = 10; //最大个数
   private int initialSize = 3; //初始化大小
   private int maxActive = 5; //最大活跃数
   private int maxWait = 3000000; //最大等待时间, 毫秒

}

jdbc.properties配置

#oracle
oracle.driverClassName=oracle.jdbc.driver.OracleDriver
oracle.url=jdbc:oracle:thin:@//[ip]:[port]/[db]

#mysql
mysql.driverClassName=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://[ip]:[port]/[db]

#db2
db2.driverClassName=com.ibm.db2.jdbc.app.DB2Driver
db2.url=jdbc:db2://[ip]:[port]/[db]

#sqlserver
sqlserver.driverClassName=com.microsoft.jdbc.sqlserver.SQLServerDriver
sqlserver.url=jdbc:microsoft:sqlserver://[ip]:[port];DatabaseName=[db]

#hive2
hive2.driverClassName=org.apache.hive.jdbc.HiveDriver
hive2.url=jdbc:hive2://[ip]:[port]/db

#mongodb

测试代码

package wuyx.multipool;

import java.util.ArrayList;
import java.util.List;

public class TestMultiPool {
   public static void main(String[] args){
//       DBConnection mysql = new DBConnection("mysql", "com.mysql.jdbc.Driver", "jdbc:mysql://192.168.229.123:3306/cpbs", "root", "yanfei");
//       DBConnection oracle = new DBConnection("oracle", "oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@//192.168.229.235:1521/ORCL_LG", "DBDC01", "DBDC01");

       DBConnection mysql = new DBConnection("mysql", "192.168.229.123", "3306", "cpbs", "root", "yanfei");
       DBConnection oracle = new DBConnection("oracle", "192.168.229.235", "1521", "ORCL_LG", "DBDC01", "DBDC01");
       DBConnection hive2 = new DBConnection("hive2", "192.168.229.124", "10000", "default", "hadoop", "hadoop");
       List<DBConnection> conns = new ArrayList<DBConnection>();
       conns.add(mysql);
       conns.add(oracle);
       conns.add(hive2);

       ConnectionManager manager = ConnectionManager.getInstance();
       manager.init(conns);

       System.out.println("init complete");

   }
}

0 0