diff options
Diffstat (limited to 'src/com/nms/server/util/BoneCPPool.java')
| -rw-r--r-- | src/com/nms/server/util/BoneCPPool.java | 217 |
1 files changed, 217 insertions, 0 deletions
diff --git a/src/com/nms/server/util/BoneCPPool.java b/src/com/nms/server/util/BoneCPPool.java new file mode 100644 index 0000000..19b2745 --- /dev/null +++ b/src/com/nms/server/util/BoneCPPool.java @@ -0,0 +1,217 @@ +package com.nms.server.util; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.concurrent.TimeUnit; + +import org.apache.log4j.Logger; + +import com.jolbox.bonecp.BoneCP; +import com.jolbox.bonecp.BoneCPConfig; +import com.nms.server.common.Constants; + +/** + * JDBC操作数据库工具类 + * + * @date Jul 20, 2011 2:27:04 PM + * @author ZhangGang + * + */ +public class BoneCPPool { + + private static Logger logger = Logger.getLogger(BoneCPPool.class); + private static BoneCP connectionPool; + + /** + * 构造方法实现 加载驱动，创建连接 + */ + public static void initPool() throws ClassNotFoundException ,SQLException ,Exception{ + + //校验并关闭线程池 + shutdown(); + + Class.forName(Constants.DB_DRIVER); + BoneCPConfig config = new BoneCPConfig(); + config.setJdbcUrl(Constants.DB_URL); // jdbc url specific to your database, eg jdbc:mysql://127.0.0.1/yourdb + config.setUsername(Constants.DB_USER_NAME); + config.setPassword(Constants.DB_PASSWORD); + + /** BoneCP主要配置参数 */ + //设置分区个数 + config.setPartitionCount(Constants.DB_POOL_PARTITION_COUNT); + //设置每个分区中的最大连接数 + config.setMaxConnectionsPerPartition(Constants.DB_POOL_CONNECTIONS_PER_PARTITION_MAX); + //设置每个分区中的最小连接数 + config.setMinConnectionsPerPartition(Constants.DB_POOL_CONNECTIONS_PER_PARTITION_MIN); + //当连接池中的连接耗尽的时候 BoneCP一次同时获取的连接数 + config.setAcquireIncrement(Constants.DB_POOL_ACQUIRE_INCREMENT); +// config.setPoolAvailabilityThreshold(poolAvailabilityThreshold); //默认20 当空闲线程所占比例小于20%时，增加连接池中连接数 + //设置获取connection超时的时间。在调用getConnection获取connection时，获取时间超过了这个参数，就视为超时并报异常 +// config.setConnectionTimeout(20, TimeUnit.SECONDS); + + /** BoneCP线程配置参数 */ + //连接（Connection）回收助手数量 + config.setReleaseHelperThreads(Constants.DB_POOL_RELEASE_HELPER_THREADS); + //会话（Statement)回收助手数量 + config.setStatementReleaseHelperThreads(Constants.DB_POOL_STATEMENT_RELEASE_HELPER_THREADS); + //设置连接最大存活时间-当连接存在超过最大存活时间时，就会关闭连接 + config.setMaxConnectionAge(Constants.DB_POOL_MAX_CONNECTION_AGE, TimeUnit.MINUTES); + //设置连接空闲时间-当连接空闲时间达到设置的时间时，就会关闭连接 + config.setIdleMaxAge(Constants.DB_POOL_IDLE_MAX_AGE,TimeUnit.MINUTES); + //设置每15分钟检查数据库中的空闲连接数 依赖setIdleMaxAge + config.setIdleConnectionTestPeriod(Constants.DB_POOL_IDLE_CONNECTION_TEST_PERIOD,TimeUnit.MINUTES); + config.setConnectionTestStatement("select 1 from dual"); + config.setInitSQL("select 1 from dual"); + config.setResetConnectionOnClose(false);//当为true时mysql 关闭连接 时 会抛出异常 + /** BoneCP可选配置参数 ：之前设置的是1分钟，默认5次，超时时间为Long.max_value,就是会阻塞死掉 + * setConnectionTimeout:获取一次连接超时的时间 + * setAcquireRetryAttempts：获取连接尝试次数 + * setAcquireRetryDelay：两次获取连接的时间间隔 + * */ +// config.setAcquireRetryAttempts(3);//此参数默认为5次 + //设置重新获取连接的次数间隔时间。这个参数默认为7000，单位：毫秒。 + config.setAcquireRetryDelay(10, TimeUnit.SECONDS);// + // 2016-07-02 zbc 设置获取连接超时的时间 + // 在未设置获取连接超时时间情况下，尝试指定连接次数获取连接仍然失败后，可能继续获取连接直至获取连接成功或导致进程假死。（可能为连接池BUG） +// config.setConnectionTimeout(config.getAcquireRetryAttempts()* config.getAcquireRetryDelay(TimeUnit.MINUTES), +// TimeUnit.MINUTES); + config.setConnectionTimeout(Constants.DB_GET_CONNECTION_TIMEOUT,TimeUnit.SECONDS); + //设置连接池初始化功能。 + config.setLazyInit(true); + //缓存的statement数量 默认0 +// config.setStatementsCacheSize(0); + config.setCloseConnectionWatch(true); + //设置sql日志输出 + config.setLogStatementsEnabled(Constants.DB_POOL_LOG_STATEMENT_ENABLE); + //设置sql执行超时时间 + config.setQueryExecuteTimeLimit(Constants.DB_POOL_QUERY_EXECUTE_TIME_LIMIT, TimeUnit.SECONDS); +// config.setPoolName("DC连接池"); + config.setPoolName("DC connection pool"); + + //创建数据库连接 + connectionPool = new BoneCP(config); // setup the connection pool--多次initPool时会出现，多个对象赋值给一个全局静态变量，知道系统推出，才会关闭所有连接(虽然会shutdown，但是总的趋势还是不断的增加数据库连接) + logger.debug("BoneCP初始化连接池成功"); + } + + + + + public static Connection getConnection() throws SQLException{ + if(connectionPool != null ){ + logger.debug("获取连接中。。。"); + Connection connection = connectionPool.getConnection(); + logger.debug("获取连接成功"); + boolean closed = connection.isClosed(); + if( closed){ + logger.warn("connection.toString()->" + connection.toString()); + } + + int [] array = getDBLinkSize(connection); + logger.debug("closed("+closed+"),autoCommit="+connection.getAutoCommit()+" [dbpool:总数("+connectionPool.getTotalCreatedConnections() + +")空闲(" +connectionPool.getTotalFree() + +")已使用(" +connectionPool.getTotalLeased()+")]" + +"[oracle:总数("+array[0]+")活动数("+array[0]+")]"); + connection.setAutoCommit(true); + return connection; + }else{ + return null; + } + } + + /** + * 查询当前DC的数据库连接数 + * @time Jan 13, 2013-4:25:03 PM + * @param dao + * @return + * @throws Exception + */ + public static int[] getDBLinkSize(Connection connection){ + int [] linksArrays = new int[]{-1,0}; + PreparedStatement ps = null; + ResultSet rs = null; + try { + if(Constants.IS_MYSQL){ + + }else{ + /*String dbLinkSizeSql = "select count(1) dbLinks,count(decode(status,'ACTIVE',1,0,0)) activeLinks from v$session t where lower(PROGRAM)=lower('JDBC Thin Client') and lower(username) =lower(?) and lower(t.MACHINE) = lower(?)"; + String dbUserName = Constants.DB_USER_NAME; + String computerName = InetAddress.getLocalHost().getHostName(); + ps = connection.prepareStatement(dbLinkSizeSql); + ps.setString(1, dbUserName); + ps.setString(2, computerName); + rs = ps.executeQuery(); + if(rs.next()){ + linksArrays [0] = rs.getInt(1); + linksArrays [1] = rs.getInt(2); + }*/ + } + } catch (Exception e) { + logger.error("Query activity database connection information exception", e); + }finally{ + try { + if(rs!=null){rs.close();} + if(ps!=null){ps.close();} + } catch (Exception e) { + logger.error("Query activity database connection information exception", e); + } + } + + return linksArrays; + } + + public static void shutdown(){ + if(connectionPool != null){ + connectionPool.close(); + connectionPool.shutdown(); // shutdown connection pool. + connectionPool = null; + } + } + + /* + public static void main(String [] args) { + + try { + initPool(); + } catch (Exception e) { + e.getMessage(); + } + + Runnable runnable = new Runnable(){ + + @Override + public void run() { + CommonDao dao = null; + Map<String, String> info = new HashMap<String, String>(); + String value = Math.random()+""; + value = value.length()>19?value.substring(0,19):value; + info.put("test_value", value) ; + try { + dao = new CommonDao(); + dao.setAutoCommit(false); + dao.insertObj("TEST_BONECP_ZG", info); + ArrayList<String> fields = new ArrayList<String>(); + fields.add("counts"); + ArrayList<Map<String, String>> maps = dao.dbSelect("select count(1) counts from TEST_BONECP_ZG", fields); + if(maps!=null && maps.size()>0){ + String counts = maps.get(0).get("counts"); + logger.debug("counts "+counts); + } + dao.commit(); + } catch (SQLExecuteTimeoutException e) { + logger.error(e.getMessage()); + } catch (SQLException e) { + logger.error(e.getMessage()); + }finally{ + dao.close(); + } + } + + }; + + for (int i = 0; i < 8000; i++) { + Common.scheduled.scheduleAtFixedRate(runnable, 0, 500, TimeUnit.MILLISECONDS); + } + }*/ +} |