1 package com.chinaoly.utils;
  2 
  3 import com.alibaba.druid.pool.DruidDataSource;
  4 import com.alibaba.druid.pool.DruidDataSourceFactory;
  5 import com.chinaoly.database.entity.DataBase;
  6 import com.chinaoly.database.service.DataBaseService;
  7 import java.sql.PreparedStatement;
  8 import java.sql.ResultSet;
  9 import java.sql.SQLException;
 10 import java.time.LocalDate;
 11 import java.time.LocalDateTime;
 12 import java.time.ZoneId;
 13 import java.util.HashMap;
 14 import java.util.List;
 15 import java.util.Map;
 16 import java.util.Properties;
 17 import javax.annotation.Resource;
 18 import javax.sql.DataSource;
 19 import java.sql.Connection;
 20 import org.joda.time.LocalTime;
 21 import org.springframework.stereotype.Component;
 22 
 23 
 24 public class DruidUtil {
 25 
 26   private static DruidUtil single = null;
 27 
 28   public static Map<String,DruidDataSource> map = new HashMap<>();
 29 
 30   public DruidUtil() {
 31   }
 32 
 33   public DruidUtil(List<DataBase> allDBs) {
 34     for (DataBase db:allDBs) {
 35       Properties prop = new Properties();
 36       if("Mysql".equalsIgnoreCase(db.getType())){
 37         prop.setProperty("driver","com.mysql.jdbc.Driver");
 38         prop.setProperty("url","jdbc:mysql://"+db.getIpAddress()+":"+db.getPort()+"/"+db.getName());
 39         prop.setProperty("connectionProperties","useUnicode=true;characterEncoding=UTF8");
 40       }else if ("Oracle".equalsIgnoreCase(db.getType())){
 41         prop.setProperty("driver","oracle.jdbc.driver.OracleDriver");
 42         prop.setProperty("url","jdbc:oracle:thin:@"+db.getIpAddress()+":"+db.getPort()+":"+db
 43             .getName());
 44       }else if("Hive".equalsIgnoreCase(db.getType())){
 45         prop.setProperty("driver","org.apache.hive.jdbc.HiveDriver");
 46         prop.setProperty("url","jdbc:hive2://"+db.getIpAddress()+":"+db.getPort()+"/"+db.getName());
 47       }else {
 48         throw new RuntimeException("连接池目前只支持Mysql、Oracle、Hive三种数据库类型！");
 49       }
 50       prop.setProperty("username",db.getUserName());
 51       String passwd = EncryptUtil.aesDecrypt(db.getPasswd());
 52       prop.setProperty("password",passwd);
 53       prop.setProperty("initialSize","3");
 54       prop.setProperty("maxActive","6");
 55       prop.setProperty("minIdle","1");
 56       prop.setProperty("maxWait","60000");
 57       prop.setProperty("filters","stat");
 58       prop.setProperty("timeBetweenEvictionRunsMillis","35000");
 59       prop.setProperty("minEvictableIdleTimeMillis","30000");
 60       prop.setProperty("testWhileIdle","true");
 61       prop.setProperty("testOnBorrow","false");
 62       prop.setProperty("testOnReturn","false");
 63       prop.setProperty("poolPreparedStatements","false");
 64       prop.setProperty("maxPoolPreparedStatementPerConnectionSize","200");
 65       prop.setProperty("removeAbandoned","true");
 66       try {
 67         DruidDataSource druidDataSource = (DruidDataSource) DruidDataSourceFactory
 68             .createDataSource(prop);
 69         map.put(db.getId(),druidDataSource);
 70       } catch (Exception e) {
 71         e.printStackTrace();
 72         System.out.println("初始化创建连接池失败！");
 73       }
 74     }
 75   }
 76 
 77   /**
 78    * 获取实例
 79    * @return
 80    */
 81   public static DruidUtil getInstance(){
 82     if (single == null) {
 83       synchronized (DruidUtil.class) {
 84         if (single == null) {
 85           single = new DruidUtil();
 86         }
 87       }
 88     }
 89     return single;
 90   }
 91 
 92   public Connection getConnection(String id) throws SQLException {
 93     DruidDataSource source = map.get(id);
 94     return source.getConnection();
 95   }
 96 
 97   public void addDataBaseIn(DataBase db){
 98     Properties prop = new Properties();
 99     if("Mysql".equalsIgnoreCase(db.getType())){
100       prop.setProperty("driverClassName","com.mysql.jdbc.Driver");
101       prop.setProperty("validationQuery","SELECT 1 FROM DUAL");
102       prop.setProperty("url","jdbc:mysql://"+db.getIpAddress()+":"+db.getPort()+"/"+db.getName());
103       prop.setProperty("connectionProperties","useUnicode=true;characterEncoding=UTF8");
104     }else if ("Oracle".equalsIgnoreCase(db.getType())){
105       prop.setProperty("driverClassName","oracle.jdbc.driver.OracleDriver");
106       prop.setProperty("validationQuery","SELECT 1 FROM DUAL");
107       prop.setProperty("url","jdbc:oracle:thin:@"+db.getIpAddress()+":"+db.getPort()+":"+db
108           .getName());
109     }else if("Hive".equalsIgnoreCase(db.getType())){
110       prop.setProperty("driverClassName","org.apache.hive.jdbc.HiveDriver");
111       prop.setProperty("validationQuery","SELECT 1");
112       prop.setProperty("url","jdbc:hive2://"+db.getIpAddress()+":"+db.getPort()+"/"+db.getName());
113     }else {
114       throw new RuntimeException("连接池目前只支持Mysql、Oracle、Hive三种数据库类型！");
115     }
116     prop.setProperty("username",db.getUserName());
117     String passwd = EncryptUtil.aesDecrypt(db.getPasswd());
118     prop.setProperty("password",passwd);
119     prop.setProperty("initialSize","3");
120     prop.setProperty("maxActive","10");
121     prop.setProperty("minIdle","3");
122     prop.setProperty("maxWait","60000");
123     prop.setProperty("filters","stat");
124     prop.setProperty("timeBetweenEvictionRunsMillis","35000");
125     prop.setProperty("minEvictableIdleTimeMillis","30000");
126     prop.setProperty("testWhileIdle","true");
127     prop.setProperty("testOnBorrow","false");
128     prop.setProperty("testOnReturn","false");
129     prop.setProperty("poolPreparedStatements","false");
130     prop.setProperty("maxPoolPreparedStatementPerConnectionSize","200");
131     prop.setProperty("removeAbandoned","true");
132     try {
133       DruidDataSource druidDataSource = (DruidDataSource) DruidDataSourceFactory
134           .createDataSource(prop);
135       map.put(db.getId(),druidDataSource);
136     } catch (Exception e) {
137       e.printStackTrace();
138       System.out.println("新增数据库创建连接池失败！");
139     }
140   }
141 
142   public void removeDataBaseOut(DataBase db){
143     DruidDataSource source = map.get(db.getId());
144     source.close();
145     map.remove(db.getId());
146   }
147 
148   public boolean containsId(String id){
149     return map.containsKey(id);
150   }
151 }