Druid连接池工具类，连接多个不同数据库需求

一、获取不同数据库连接

1.需要连接一系列的数据库

2.数据库会动态改变，比如新增和删除数据库

代码如下：

 package com.chinaoly.utils;
 
 import com.alibaba.druid.pool.DruidDataSource;
 import com.alibaba.druid.pool.DruidDataSourceFactory;
 import com.chinaoly.database.entity.DataBase;
 import com.chinaoly.database.service.DataBaseService;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.time.LocalDate;
 import java.time.LocalDateTime;
 import java.time.ZoneId;
 import java.util.HashMap;
 import java.util.List;
 import java.util.Map;
 import java.util.Properties;
 import javax.annotation.Resource;
 import javax.sql.DataSource;
 import java.sql.Connection;
 import org.joda.time.LocalTime;
 import org.springframework.stereotype.Component;
 
 
 public class DruidUtil {
 
   private static DruidUtil single = null;
 
   public static Map<String,DruidDataSource> map = new HashMap<>();
 
   public DruidUtil() {
   }
 
   public DruidUtil(List<DataBase> allDBs) {
     for (DataBase db:allDBs) {
       Properties prop = new Properties();
       if("Mysql".equalsIgnoreCase(db.getType())){
         prop.setProperty("driver","com.mysql.jdbc.Driver");
         prop.setProperty("url","jdbc:mysql://"+db.getIpAddress()+":"+db.getPort()+"/"+db.getName());
         prop.setProperty("connectionProperties","useUnicode=true;characterEncoding=UTF8");
       }else if ("Oracle".equalsIgnoreCase(db.getType())){
         prop.setProperty("driver","oracle.jdbc.driver.OracleDriver");
         prop.setProperty("url","jdbc:oracle:thin:@"+db.getIpAddress()+":"+db.getPort()+":"+db
             .getName());
       }else if("Hive".equalsIgnoreCase(db.getType())){
         prop.setProperty("driver","org.apache.hive.jdbc.HiveDriver");
         prop.setProperty("url","jdbc:hive2://"+db.getIpAddress()+":"+db.getPort()+"/"+db.getName());
       }else {
         throw new RuntimeException("连接池目前只支持Mysql、Oracle、Hive三种数据库类型！");
       }
       prop.setProperty("username",db.getUserName());
       String passwd = EncryptUtil.aesDecrypt(db.getPasswd());
       prop.setProperty("password",passwd);
       prop.setProperty("initialSize","3");
       prop.setProperty("maxActive","6");
       prop.setProperty("minIdle","1");
       prop.setProperty("maxWait","60000");
       prop.setProperty("filters","stat");
       prop.setProperty("timeBetweenEvictionRunsMillis","35000");
       prop.setProperty("minEvictableIdleTimeMillis","30000");
       prop.setProperty("testWhileIdle","true");
       prop.setProperty("testOnBorrow","false");
       prop.setProperty("testOnReturn","false");
       prop.setProperty("poolPreparedStatements","false");
       prop.setProperty("maxPoolPreparedStatementPerConnectionSize","200");
       prop.setProperty("removeAbandoned","true");
       try {
         DruidDataSource druidDataSource = (DruidDataSource) DruidDataSourceFactory
             .createDataSource(prop);
         map.put(db.getId(),druidDataSource);
       } catch (Exception e) {
         e.printStackTrace();
         System.out.println("初始化创建连接池失败！");
       }
     }
   }
 
   /**
    * 获取实例
    * @return
    */
   public static DruidUtil getInstance(){
     if (single == null) {
       synchronized (DruidUtil.class) {
         if (single == null) {
           single = new DruidUtil();
         }
       }
     }
     return single;
   }
 
   public Connection getConnection(String id) throws SQLException {
     DruidDataSource source = map.get(id);
     return source.getConnection();
   }
 
   public void addDataBaseIn(DataBase db){
     Properties prop = new Properties();
     if("Mysql".equalsIgnoreCase(db.getType())){
       prop.setProperty("driverClassName","com.mysql.jdbc.Driver");
       prop.setProperty("validationQuery","SELECT 1 FROM DUAL");
       prop.setProperty("url","jdbc:mysql://"+db.getIpAddress()+":"+db.getPort()+"/"+db.getName());
       prop.setProperty("connectionProperties","useUnicode=true;characterEncoding=UTF8");
     }else if ("Oracle".equalsIgnoreCase(db.getType())){
       prop.setProperty("driverClassName","oracle.jdbc.driver.OracleDriver");
       prop.setProperty("validationQuery","SELECT 1 FROM DUAL");
       prop.setProperty("url","jdbc:oracle:thin:@"+db.getIpAddress()+":"+db.getPort()+":"+db
           .getName());
     }else if("Hive".equalsIgnoreCase(db.getType())){
       prop.setProperty("driverClassName","org.apache.hive.jdbc.HiveDriver");
       prop.setProperty("validationQuery","SELECT 1");
       prop.setProperty("url","jdbc:hive2://"+db.getIpAddress()+":"+db.getPort()+"/"+db.getName());
     }else {
       throw new RuntimeException("连接池目前只支持Mysql、Oracle、Hive三种数据库类型！");
     }
     prop.setProperty("username",db.getUserName());
     String passwd = EncryptUtil.aesDecrypt(db.getPasswd());
     prop.setProperty("password",passwd);
     prop.setProperty("initialSize","3");
     prop.setProperty("maxActive","10");
     prop.setProperty("minIdle","3");
     prop.setProperty("maxWait","60000");
     prop.setProperty("filters","stat");
     prop.setProperty("timeBetweenEvictionRunsMillis","35000");
     prop.setProperty("minEvictableIdleTimeMillis","30000");
     prop.setProperty("testWhileIdle","true");
     prop.setProperty("testOnBorrow","false");
     prop.setProperty("testOnReturn","false");
     prop.setProperty("poolPreparedStatements","false");
     prop.setProperty("maxPoolPreparedStatementPerConnectionSize","200");
     prop.setProperty("removeAbandoned","true");
     try {
       DruidDataSource druidDataSource = (DruidDataSource) DruidDataSourceFactory
           .createDataSource(prop);
       map.put(db.getId(),druidDataSource);
     } catch (Exception e) {
       e.printStackTrace();
       System.out.println("新增数据库创建连接池失败！");
     }
   }
 
   public void removeDataBaseOut(DataBase db){
     DruidDataSource source = map.get(db.getId());
     source.close();
     map.remove(db.getId());
   }
 
   public boolean containsId(String id){
     return map.containsKey(id);
   }
 }

 

 

 

 二、获取数据库表或表字段类型、注释等信息

 public class DatabaseUtilTest   {    
     private static final Logger logger = LoggerFactory.getLogger(DatabaseUtilTest.class);    
     private static final String DRIVER = "com.mysql.jdbc.Driver";    
     private static final String DATABASE_URL = "jdbc:mysql://ip:3306/databasename?useUnicode=true&characterEncoding=utf8";    
     private static final String USERNAME = "xxx";    
     private static final String PASSWORD = "xxx";    
     private static final String SQL = "SELECT * FROM ";// 数据库操作    
     static {    
         try {    
             Class.forName(DRIVER);    
         } catch (ClassNotFoundException e) {    
             logger.error("can not load jdbc driver", e);    
         }    
     }    
     /**    
      * 获取数据库连接    
      *    
      * @return    
      */    
     public static Connection getConnection() {    
         Connection conn = null;    
         try {    
             conn = DriverManager.getConnection(DATABASE_URL, USERNAME, PASSWORD);    
         } catch (SQLException e) {    
             logger.error("get connection failure", e);    
         }    
         return conn;    
     }    
     /**    
      * 关闭数据库连接    
      * @param conn    
      */    
     public static void closeConnection(Connection conn) {    
         if(conn != null) {    
             try {    
                 conn.close();    
             } catch (SQLException e) {    
                 logger.error("close connection failure", e);    
             }    
         }    
     }    
     /**    
      * 获取数据库下的所有表名    
      */    
     public static List<String> getTableNames() {    
         List<String> tableNames = new ArrayList<>();    
         Connection conn = getConnection();    
         ResultSet rs = null;    
         try {    
             //获取数据库的元数据    
             DatabaseMetaData db = conn.getMetaData();    
             //从元数据中获取到所有的表名    
             rs = db.getTables(null, null, null, new String[] { "TABLE" });    
             while(rs.next()) {    
                 tableNames.add(rs.getString(3));    
             }    
         } catch (SQLException e) {    
             logger.error("getTableNames failure", e);    
         } finally {    
             try {    
                 rs.close();    
                 closeConnection(conn);    
             } catch (SQLException e) {    
                 logger.error("close ResultSet failure", e);    
             }    
         }    
         return tableNames;    
     }    
     /**    
      * 获取表中所有字段名称    
      * @param tableName 表名    
      * @return    
      */    
     public static List<String> getColumnNames(String tableName) {    
         List<String> columnNames = new ArrayList<>();    
         //与数据库的连接    
         Connection conn = getConnection();    
         PreparedStatement pStemt = null;    
         String tableSql = SQL + tableName;    
         try {    
             pStemt = conn.prepareStatement(tableSql);    
             //结果集元数据    
             ResultSetMetaData rsmd = pStemt.getMetaData();    
             //表列数    
             int size = rsmd.getColumnCount();    
             for (int i = 0; i < size; i++) {    
                 columnNames.add(rsmd.getColumnName(i + 1));    
             }    
         } catch (SQLException e) {    
             logger.error("getColumnNames failure", e);    
         } finally {    
             if (pStemt != null) {    
                 try {    
                     pStemt.close();    
                     closeConnection(conn);    
                 } catch (SQLException e) {    
                     logger.error("getColumnNames close pstem and connection failure", e);    
                 }    
             }    
         }    
         return columnNames;    
     }    
     /**    
      * 获取表中所有字段类型    
      * @param tableName    
      * @return    
      */    
     public static List<String> getColumnTypes(String tableName) {    
         List<String> columnTypes = new ArrayList<>();    
         //与数据库的连接    
         Connection conn = getConnection();    
         PreparedStatement pStemt = null;    
         String tableSql = SQL + tableName;    
         try {    
             pStemt = conn.prepareStatement(tableSql);    
             //结果集元数据    
             ResultSetMetaData rsmd = pStemt.getMetaData();    
             //表列数    
             int size = rsmd.getColumnCount();    
             for (int i = 0; i < size; i++) {    
                 columnTypes.add(rsmd.getColumnTypeName(i + 1));    
             }    
         } catch (SQLException e) {    
             logger.error("getColumnTypes failure", e);    
         } finally {    
             if (pStemt != null) {    
                 try {    
                     pStemt.close();    
                     closeConnection(conn);    
                 } catch (SQLException e) {    
                     logger.error("getColumnTypes close pstem and connection failure", e);    
                 }    
             }    
         }    
         return columnTypes;    
     }    
     /**    
      * 获取表中字段的所有注释    
      * @param tableName    
      * @return    
      */    
     public static List<String> getColumnComments(String tableName) {    
         List<String> columnTypes = new ArrayList<>();    
         //与数据库的连接    
         Connection conn = getConnection();    
         PreparedStatement pStemt = null;    
         String tableSql = SQL + tableName;    
         List<String> columnComments = new ArrayList<>();//列名注释集合    
         ResultSet rs = null;    
         try {    
             pStemt = conn.prepareStatement(tableSql);    
             rs = pStemt.executeQuery("show full columns from " + tableName);    
             while (rs.next()) {    
                 columnComments.add(rs.getString("Comment"));    
             }    
         } catch (SQLException e) {    
             e.printStackTrace();    
         } finally {    
             if (rs != null) {    
                 try {    
                     rs.close();    
                     closeConnection(conn);    
                 } catch (SQLException e) {    
                     logger.error("getColumnComments close ResultSet and connection failure", e);    
                 }    
             }    
         }    
         return columnComments;    
     }    
     public static void main(String[] args) {    
         List<String> tableNames = getTableNames();    
         System.out.println("tableNames:" + tableNames);    
         for (String tableName : tableNames) {    
             System.out.println("================start==========================");    
             System.out.println("==============================================");    
             System.out.println("ColumnNames:" + getColumnNames(tableName));    
             System.out.println("ColumnTypes:" + getColumnTypes(tableName));    
             System.out.println("ColumnComments:" + getColumnComments(tableName));    
             System.out.println("==============================================");    
             System.out.println("=================end=======================");    
         }    
     }    
 }