Jdbc 链接数据库（含连接池）

发布时间：2020-07-15 19:43:05 作者：终极码农
来源：网络阅读：324

/*
* Copyright 2000-2020 YGSoft.Inc All Rights Reserved.
*/
package com.ygsoft.gris.mapp.materialmanage.impl.util;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.ygsoft.ecp.service.tool.StringUtil;
import com.ygsoft.gris.mapp.materialmanage.service.model.EngineeringMaterialsOutVO;
import com.ygsoft.gris.mapp.materialmanage.service.model.QueryConditionsVO;
import com.ygsoft.gris.mapp.materialmanage.service.model.SupplierInvoiceInfoVO;
import com.ygsoft.gris.mapp.materialmanage.service.model.SupplierZanguSupplies;
import com.ygsoft.gris.mapp.materialmanage.service.model.Tm0001CatalogsVO;

/**
* sqlService数据库连接. 
*
* @author mapengfei 
* @version 1.0.0 2016年10月13日 
* @see
* @since JDK 1.5.0
*/
public class SqlServerDbConnectionUtil {

 private static boolean ISTESTDATA = true;

 /**
 * 内部内部懒加载类. 
 *
 * @author mapengfei 
 * @version 1.0.0 2016年5月31日 
 * @see
 * @since JDK 1.5.0
 */
 private static class LazyHolder {
 private static final SqlServerDbConnectionUtil INSTANCE = new SqlServerDbConnectionUtil();
 }

 /**
 * 单例新实现
 *
 * @return
 */
 public static final SqlServerDbConnectionUtil getInstance() {
 return LazyHolder.INSTANCE;
 }

 /**
 * @param args
 */
 public static void main(final String[] args) {
 // TODO Auto-generated method stub
 // SqlServerDbConnectionUtil util = new SqlServerDbConnectionUtil();
 // util.dbConnection();
 // List<EngineeringMaterialsOutVO> list =
 // SqlServerDbConnectionUtil.findAllMaterialsOutByCondition(null);

 List<ExcelModel> list = findExcelExportByCondition(null, "ZGWZ");

 System.out.println(list);

 }

 /**
 * 获取数据库链接
 *
 * @return
 */
 private static Connection getCurrentConnecton() {
 // 初始化连接池
 ConnectionPool pool = getPool();
 // 取得当前链接
 Connection conn = pool.getCurrentConnecton();
 if (conn != null) {
 return conn;
 }
 return null;
 }

 /**
 * 单例模式初始化一个连接池
 *
 * @return
 */
 public static ConnectionPool getPool() {
 return ConnectionPoolManager.getInstance().getPool("sqllitePool");
 }

 /**
 * 根据条件查询
 *
 * @return
 */
 public static List<EngineeringMaterialsOutVO> findAllMaterialsOutByCondition(
 final QueryConditionsVO queryCondition) {
 if (ISTESTDATA) {
 return TestData.findAllMaterialsOutByCondition(queryCondition);
 }
 List<EngineeringMaterialsOutVO> list = new ArrayList<EngineeringMaterialsOutVO>();
 Connection conn = null;
 try {
 conn = getCurrentConnecton();
 Statement stat = conn.createStatement();

 String sql = setQueryConditionsSql(queryCondition, "CLCK", 1000);
 ResultSet rs = stat.executeQuery(sql);
 EngineeringMaterialsOutVO out = null;
 int i = 1;
 while (rs.next()) {
 out = new EngineeringMaterialsOutVO();
 out.setNo("" + i);
 out.setTotal(rs.getBigDecimal(7));
 out.setProDefinition(!StringUtil.isNotEmptyString(rs.getString(3)) ? "" : rs.getString(3).toString()); // 工程界定
 out.setNotTaxAmount(BigDecimal.valueOf(rs.getDouble(4)));
 out.setProName(!StringUtil.isNotEmptyString(rs.getString(2)) ? "" : rs.getString(2).toString()); // 名称
 out.setProNumber(!StringUtil.isNotEmptyString(rs.getString(1)) ? "" : rs.getString(1).toString()); // 编码
 out.setTax(rs.getDouble(6));
 out.setTaxRate(rs.getDouble(5));
 list.add(out);
 i++;
 }
 rs.close();
 stat.close();

 } catch (SQLException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 } finally {
 closeConnection(conn);
 }
 return list;
 }

 /**
 * 暂估物资汇总
 *
 * @return
 */
 public static List<SupplierZanguSupplies> findAllSupplierZanguSuppliesByCondition(
 final QueryConditionsVO queryCondition) {
 if (ISTESTDATA) {
 return TestData.findAllSupplierZanguSuppliesByCondition(queryCondition);
 }
 List<SupplierZanguSupplies> list = new ArrayList<SupplierZanguSupplies>();
 Connection conn = null;
 try {
 conn = getCurrentConnecton();
 Statement stat = conn.createStatement();
 String sql = setQueryConditionsSql(queryCondition, "ZGWZ", 1000);
 ResultSet rs = stat.executeQuery(sql);
 SupplierZanguSupplies out = null;
 int i = 0;
 while (rs.next()) {
 out = new SupplierZanguSupplies();
 out.setNo("" + i);
 out.setInStoragePrice(rs.getBigDecimal(3)); // 入库价款
 out.setInvoicePrice(rs.getBigDecimal(7)); // 发票价款
 // out.setNo(rs.getString(1));
 out.setNotInvoicePrice(rs.getBigDecimal(8)); // 未×××价格
 out.setPurAmount(rs.getBigDecimal(2)); // 采购金额
 out.setSupplier(rs.getString(1));
 out.setTax(rs.getBigDecimal(5));
 out.setTaxRate(rs.getBigDecimal(4));
 out.setTotalPrice(rs.getBigDecimal(6)); // 入库价税合计
 list.add(out);
 i++;
 }
 rs.close();
 stat.close();

 } catch (SQLException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 } finally {
 closeConnection(conn);
 }
 return list;
 }

 /**
 * 导出数据获取
 *
 * @param supplier
 * @param startTime
 * @param endTime
 * @return
 */
 public static List<ExcelModel> findExcelExportByCondition(final QueryConditionsVO queryCondition,
 final String type) {
 if (ISTESTDATA) {
 return TestData.findExcelExportByCondition(queryCondition, type);
 }

 List<ExcelModel> list = new ArrayList<ExcelModel>();
 List<Tm0001CatalogsVO> tm001s = SqlServerDbConnectionUtil.findProductTypeAll();
 Connection conn = null;
 try {
 conn = getCurrentConnecton();
 Statement stat = conn.createStatement();
 String sql = setQueryConditionsSql(queryCondition, type, 100000);
 ResultSet rs = stat.executeQuery(sql);
 ExcelModel out = null;
 int col = rs.getMetaData().getColumnCount();
 int row = 1;
 while (rs.next()) {
 out = new ExcelModel();
 List<ExcelModel> suns = new ArrayList<ExcelModel>();
 for (int i = 1; i < col; i++) { // X轴
 out = new ExcelModel();
 Object obj = rs.getObject(i) != null ? rs.getObject(i) : "";
 if ("FPXX".equals(type)) { // 发票
 switch (i) {
 case 8:
 case 10:
 case 12:
 case 13:
 case 15:
 case 17:
 case 18:
 case 19:
 obj = formatMoney(obj.toString(), 2, true);
 break;
 case 11:
 obj = formatMoney(obj.toString(), 0, false) + "%";
 break;
 case 9:
 case 14:
 case 16:
 obj = 0;
 if (StringUtil.isNotEmptyString(obj.toString())) {
 long num = Long.parseLong(obj.toString());
 obj = num;
 }
 break;
 default:
 break;
 }
 out.setData(obj);
 } else if ("ZGWZ".equals(type)) { // 暂估物资
 if (i == 1) {
 ExcelModel out_no = new ExcelModel();
 out_no.setData(row);
 suns.add(out_no);
 }
 switch (i) {
 case 2:
 case 3:
 case 5:
 case 6:
 case 7:
 case 8:
 obj = formatMoney(obj.toString(), 2, true);
 break;
 case 4:
 obj = formatMoney(obj.toString(), 0, false) + "%";
 break;
 default:
 break;
 }

 out.setData(obj);
 } else {
 switch (i) {
 case 5: // 税率
 obj = formatMoney(obj.toString(), 0, false) + "%";
 break;
 case 3:
 String newProtype = "";
 Object proType = rs.getString("proType");
 List<String> protypes = new ArrayList<String>();
 for (Tm0001CatalogsVO c : tm001s) {
 if (proType.equals(c.getID())) { // 找到节点
 newProtype += c.getDisplayName() + "-";
 searchProType(tm001s, newProtype, c, protypes);
 }
 }
 obj = protypes.get(0);
 break;
 case 4:
 case 6:
 case 7:
 obj = (Object) formatMoney(obj.toString(), 2, true);
 break;
 default:
 out.setData(obj);
 break;
 }
 out.setData(obj);
 }

 suns.add(out);
 }
 out.setDataList(suns);
 list.add(out);
 row++;
 }
 rs.close();
 stat.close();

 } catch (SQLException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 } finally {
 closeConnection(conn);
 }
 return list;

 }

 /**
 * 查询所有的项目分类
 *
 * @return
 */
 public static List<Tm0001CatalogsVO> findProductTypeAll() {
 if (ISTESTDATA) {
 return TestData.findProductTypeAll();
 }
 // 获得测试数据
 List<Tm0001CatalogsVO> list = new ArrayList<Tm0001CatalogsVO>();
 Connection conn = null;
 try {
 conn = getCurrentConnecton();
 Statement stat = conn.createStatement();
 StringBuffer sql = new StringBuffer(" SELECT * FROM TM0001_Catalogs WHERE FieldID=109 ");
 ResultSet rs = stat.executeQuery(sql.toString());
 Tm0001CatalogsVO out = null;
 while (rs.next()) {
 out = new Tm0001CatalogsVO();
 out.setID(rs.getString(1));
 out.setName(rs.getString(2));
 out.setDisplayName(rs.getString(3));
 out.setParentID(rs.getString(4));
 out.setFieldID(rs.getString(5));
 out.setObjectID(rs.getString(6));
 out.setLevel(rs.getString(7));
 out.setComment(rs.getString(8));
 list.add(out);
 }
 rs.close();
 stat.close();

 } catch (SQLException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 } finally {
 closeConnection(conn);
 }

 return list;

 }

 /**
 * 查询登录信息的方法
 *
 * @return
 */
 public Map<String, Object> getLoginNameAndStatus() {
 Connection conn = null;
 try {
 conn = getCurrentConnecton();
 Statement stat = conn.createStatement();
 StringBuffer sql = new StringBuffer(" SELECT * FROM TM0001_Catalogs WHERE FieldID=109 ");
 ResultSet rs = stat.executeQuery(sql.toString());
 while (rs.next()) {

 }
 rs.close();
 stat.close();

 } catch (SQLException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 } finally {
 closeConnection(conn);
 }

 return null;

 }

 /**
 *
 * @param newNoInvoiceTax
 * @return
 */
 public static List<SupplierInvoiceInfoVO> findAllSupplierInvoiceInfoVOByCondition(
 final QueryConditionsVO queryCondition) {
 if (ISTESTDATA) {
 return TestData.findAllSupplierInvoiceInfoVOByCondition(queryCondition);
 }
 List<SupplierInvoiceInfoVO> list = new ArrayList<SupplierInvoiceInfoVO>();
 Connection conn = null;
 try {
 conn = getCurrentConnecton();
 Statement stat = conn.createStatement();
 String sql = setQueryConditionsSql(queryCondition, "FPXX", 1000);
 ResultSet rs = stat.executeQuery(sql);
 SupplierInvoiceInfoVO out = null;
 int i = 1;
 while (rs.next()) {
 out = new SupplierInvoiceInfoVO();
 out.setNo("" + i);
 out.setEngineeringCode(rs.getString(5)); // 工程编码
 out.setEngineeringName(rs.getString(6)); // 工程名称
 out.setInStorageAmount(rs.getBigDecimal(13)); // 入库金额
 out.setInStorageNum(rs.getLong(9)); // 入库数量
 out.setInStoreAmount(rs.getBigDecimal(10)); // 入库价款
 out.setInvoiceAmount(rs.getBigDecimal(15)); // 发票金额
 out.setInvoiceNum(rs.getLong(14)); // 发票数量
 out.setMaterialCode(rs.getString(2)); // 物资编码
 out.setMaterialName(rs.getString(3));
 out.setNoInvoiceAmount(rs.getBigDecimal(17)); // 未开票金额
 out.setNoInvoiceNum(rs.getLong(16)); // 未开票数量
 out.setNoInvoiceTax(rs.getBigDecimal(18));
 out.setPurAmount(rs.getBigDecimal(8)); // 采购金额
 out.setPurQuantity(rs.getLong(7)); // 采购数量
 out.setSpecificationsModel(rs.getString(4)); // 规格型号
 out.setSupplier(rs.getString(1)); // 供应商
 out.setTax(rs.getBigDecimal(12)); // 税款
 out.setTaxRate(rs.getBigDecimal(11)); // 税率
 out.setTotalPrice(rs.getBigDecimal(19)); // 价税合计
 list.add(out);
 i++;
 }
 rs.close();
 stat.close();

 } catch (SQLException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 } finally {
 closeConnection(conn);
 }
 return list;
 }

 /**
 * 测试连接
 */
 @SuppressWarnings("unused")
 private void dbConnection() {
 Connection conn = null;
 try {
 // Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
 // String url =
 // "jdbc:sqlserver://192.168.0.2:1433;DatabaseName=msdb";
 // conn = DriverManager.getConnection(url, "sa", "lxm226855");
 conn = getCurrentConnecton();
 Statement stat = conn.createStatement();
 // String sql = "call
 // cq_gcclckhzb('1321','17','2013-10-21','2016-10-21')";

 CallableStatement proc = conn.prepareCall("{call cq_gcclckhzb(?,?,?,?)}");
 proc.setString(1, "1321");
 proc.setString(2, "17");
 proc.setString(3, "2013-10-21");
 proc.setString(4, "2016-10-21");
 // proc.registerOutParameter(5, Types.VARCHAR);
 proc.execute();

 ResultSet rs = stat.executeQuery("select * from temp_cq_gcclckhzb");
 int col = rs.getMetaData().getColumnCount();
 while (rs.next()) {
 for (int i = 1; i <= col; i++) {
 System.out.print(rs.getString(i) + "\t");
 if ((i == 2) && (rs.getString(i).length() < 8)) {
 System.out.print("\t");
 }
 }
 System.out.println("");
 }
 rs.close();
 stat.close();

 } catch (SQLException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 } finally {
 closeConnection(conn);
 }
 }

 /**
 * 删除一个连接池
 *
 * @param conn
 */
 private static void closeConnection(final Connection conn) {
 ConnectionPoolManager.getInstance().closeConnection("sqllitePool", conn);
 }

 /**
 * 构造查询条件
 *
 * @param conditions
 * @return
 */
 private static String setQueryConditionsSql(final QueryConditionsVO conditions, final String type, final int top) {
 StringBuffer sql = new StringBuffer();
 if ("ZGWZ".equals(type)) { // 暂估物资
 sql.append("SELECT TOP " + top + " * FROM CQ_GYSZGWZHZB WHERE 1=1 ");
 } else if ("FPXX".equals(type)) { // 发票信息
 sql.append("SELECT TOP " + top + " * FROM CQ_GYSFPXXTJB WHERE 1=1 ");
 } else {
 sql.append("SELECT TOP " + top + " * FROM CQ_GCCLCKHZB WHERE 1=1 ");
 }
 if (conditions != null) {
 if (StringUtil.isNotEmptyString(conditions.getSupplier())) {
 String[] supplier = conditions.getSupplier().split("，");
 sql.append(" and (");
 for (int i = 0; i < supplier.length; i++) {
 sql.append(" supplier like '%" + supplier[i] + "%'");
 if (i != supplier.length - 1) {
 sql.append(" or ");
 }
 }
 sql.append(" )");
 }
 if (StringUtil.isNotEmptyString(conditions.getMaterials())) { // 物资
 String[] materials = conditions.getMaterials().split("，");
 sql.append(" and (");
 for (int i = 0; i < materials.length; i++) {
 sql.append(" materials like '%" + materials[i] + "%'");
 if (i != materials.length - 1) {
 sql.append(" or ");
 }
 }
 sql.append(" )");
 }
 if (StringUtil.isNotEmptyString(conditions.getProject())) { // 项目工程
 String[] projects = conditions.getProject().split("，");
 sql.append(" and (");
 for (int i = 0; i < projects.length; i++) {
 sql.append(" project like '%" + projects[i] + "%' ");
 if (i != projects.length - 1) {
 sql.append(" or ");
 }
 }
 sql.append(" )");
 }
 if (StringUtil.isNotEmptyString(conditions.getTaxRate())) { // 税率
 String[] taxRates = conditions.getTaxRate().split(",");
 sql.append(" and taxRate in ( ");
 for (int i = 0; i < taxRates.length; i++) {
 int taxRate = Integer.valueOf(taxRates[i].indexOf("%") > -1
 ? taxRates[i].substring(0, taxRates[i].length() - 1) : taxRates[i]);
 if (i != taxRates.length - 1) {
 sql.append(taxRate + ",");
 } else {
 sql.append(taxRate);
 }
 }
 sql.append(" )");
 }
 if (StringUtil.isNotEmptyString(conditions.getProType())) { // 分类
 String[] proTypes = conditions.getProType().split(",");
 sql.append(" and proType in ( ");
 for (int i = 0; i < proTypes.length; i++) {
 if (i != proTypes.length - 1) {
 sql.append("'" + proTypes[i] + "',");
 } else {
 sql.append("'" + proTypes[i] + "'");
 }
 }
 sql.append(" )");
 }

 if (StringUtil.isNotEmptyString(conditions.getStartTime())) { // 起始日期
 sql.append(" and BusinessDate >='" + conditions.getStartTime() + "'");
 }
 if (StringUtil.isNotEmptyString(conditions.getEndTime())) { // 结束日期
 sql.append(" and BusinessDate <= '" + conditions.getEndTime() + "'");
 }
 }
 sql.append(" order by rand()");
 return sql.toString();

 }

 /**
 * 格式化金额类型
 *
 * @param s
 * 带格式化字符串
 * @param len
 * 小数点保留长度
 * @return
 */
 public static String formatMoney(final String s, final int len, final boolean isEndTag) {
 if (!StringUtil.isNotEmptyString(s) || s.length() < 1) {
 return "";
 }
 NumberFormat formater = null;
 double num = Double.parseDouble(s);
 if (len == 0) {
 formater = new DecimalFormat("###,###,###");
 } else {
 StringBuffer buff = new StringBuffer();
 buff.append("###,###,###.");
 for (int i = 0; i < len; i++) {
 buff.append("#");
 }
 formater = new DecimalFormat(buff.toString());
 }
 String result = formater.format(num);
 if (result.indexOf(".") == -1 && isEndTag) {
 result = result + ".00";
 }
 return result;

 }

 private static void searchProType(final List<Tm0001CatalogsVO> list, String newProtype, final Tm0001CatalogsVO c,
 final List<String> protypes) {
 for (Tm0001CatalogsVO b : list) {
 if (c.getParentID().equals(b.getID())) {
 newProtype += b.getDisplayName() + "-";
 if (Integer.valueOf(b.getLevel()) > 1) {
 searchProType(list, newProtype, b, protypes);
 } else {
 String[] oldProTypes = newProtype.split("-");
 StringBuffer proType = new StringBuffer();
 for (int i = oldProTypes.length - 1; i >= 0; i--) {
 if (i != 0) {
 proType.append(oldProTypes[i] + "-");
 } else {
 proType.append(oldProTypes[i]);
 }
 }
 protypes.add(proType.toString());
 break;
 }
 }
 }
 }

}

/**************************************8ConnectionPool************/

/*
* Copyright 2000-2020 YGSoft.Inc All Rights Reserved.
*/
package com.ygsoft.gris.mapp.materialmanage.impl.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import java.util.Timer;
import java.util.TimerTask;
import java.util.Vector;

import com.ygsoft.ecp.service.log.EcpLogFactory;
import com.ygsoft.ecp.service.log.IEcpLog;

/**
* 连接池基本处理. 
*
* @author mapengfei 
* @version 1.0.0 2016年5月12日 
* @see
* @since JDK 1.5.0
*/
public class ConnectionPool {

 // 连接池配置属性
 private DBbean dbBean;
 // 连接池活动状态
 private boolean isActive = false;
 // 记录创建的总的连接数
 private int contActive = 0;
 // 空闲连接
 private List<Connection> freeConnection = new Vector<Connection>();
 // 活动连接
 private List<Connection> activeConnection = new Vector<Connection>();
 // 将线程和连接绑定，保证事务能统一执行
 private static ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();
 /**
 * 日志
 */
 private static final IEcpLog LOG = EcpLogFactory.getLog(ConnectionPool.class);

 /**
 * 构造函数
 *
 * @param dbBean
 */
 public ConnectionPool(final DBbean dbBean) {
 super();
 // 取得配置参数
 this.dbBean = dbBean;
 // 初始化链接池
 if (contActive == 0) {
 init();
 } else {
 if (LOG.isInfoEnabled()) {
 LOG.info("连接池中已有链接，不能再初始化");
 }
 }
 // 连接池校验
 cheackPool();
 }

 // 初始化
 public void init() {
 // Class.forName(dbBean.getDriverName());
 if (LOG.isInfoEnabled()) {
 LOG.info("取得足够数量的连接数");
 }
 // 取得足够数量的连接数
 for (int i = 0; i < dbBean.getInitConnections(); i++) {
 // 获得新的链接
 Connection conn = newConnection();
 // 初始化最小连接数
 if (conn != null) {
 if (LOG.isInfoEnabled()) {
 LOG.info("写入空闲的连接池集合表");
 }
 freeConnection.add(conn); // 写入空闲的连接池集合表
 // 连接池计数器
 contActive++;
 }
 }
 if (LOG.isInfoEnabled()) {
 LOG.info("本次总共初始化" + contActive + "个链接.");
 }
 /**
 * 设置连接池活动状态
 */
 isActive = true;
 }

 /**
 * 获得当前连接当前链接可以在线程中拿
 *
 * @return
 */
 public Connection getCurrentConnecton() {
 // 默认线程里面取
 Connection conn = threadLocal.get();
 if (!isValid(conn)) {
 conn = getConnection();
 }
 return conn;
 }

 /**
 * 获得连接
 *
 * @return
 */
 public synchronized Connection getConnection() {
 Connection conn = null;
 try {
 // 判断是否超过最大连接数限制
 if (contActive < this.dbBean.getMaxActiveConnections()) {
 if (freeConnection.size() > 0) { // 空线程有没被占用的
 if (LOG.isInfoEnabled()) {
 LOG.info("获得空线程中的第一个链接给");
 }
 conn = freeConnection.get(0); // 获得空线程中的第一个链接给
 if (conn != null) {
 if (LOG.isInfoEnabled()) {
 LOG.info("将获取到的链接赋给线程（线程是安全带锁的");
 }
 threadLocal.set(conn); // 将获取到的链接赋给线程（线程是安全带锁的）
 }
 if (LOG.isInfoEnabled()) {
 LOG.info("将线程池中的链接从空链接池中析出");
 }
 freeConnection.remove(0); // 将线程池中的链接从空链接池中析出
 } else {
 conn = newConnection(); // 如果没有了空的链接数，则重新创建一个链接
 }

 } else {
 // 继续获得连接,直到从新获得连接
 if (LOG.isInfoEnabled()) {
 LOG.info("由于链接已达到或超过设定的总的连接数上限故等待链接被定时器回收");
 }
 wait(this.dbBean.getConnTimeOut()); // 等待链接被定时器
 conn = getConnection();
 }
 // 校验链接是否可用
 if (isValid(conn)) {
 activeConnection.add(conn); // 将链接添加到活动的连接池中
 contActive++; // 链接池
 }
 } catch (InterruptedException e) {
 e.printStackTrace();
 }
 return conn;
 }

 /**
 * 获得新连接
 *
 * @return
 * @throws ClassNotFoundException
 * @throws SQLException
 */
 private synchronized Connection newConnection() {
 Connection conn = null;
 if (dbBean != null) {
 try {
 Class.forName(dbBean.getDriverName()).newInstance();
 conn = DriverManager.getConnection(dbBean.getUrl(), dbBean.getUserName(), dbBean.getPassword());
 } catch (ClassNotFoundException e) {
 if (LOG.isInfoEnabled()) {
 LOG.info("连接池驱动获取失败");
 }
 e.printStackTrace();
 } catch (SQLException e) {
 if (LOG.isInfoEnabled()) {
 LOG.info("DriverManager 链接失败");
 }
 e.printStackTrace();
 } catch (InstantiationException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 } catch (IllegalAccessException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 }
 }
 return conn;
 }

 /**
 * 释放连接
 *
 * @param conn
 * @throws SQLException
 */
 public synchronized void releaseConn(final Connection conn) throws SQLException {
 // 当前空闲链接数大于设置的最大连接池数时释放部分连接池
 if (LOG.isInfoEnabled()) {
 LOG.info("当前空闲链接数大于设置的最大连接池数时释放部分连接池");
 }
 if (isValid(conn) && !(freeConnection.size() > dbBean.getMaxConnections())) {
 freeConnection.add(conn);
 activeConnection.remove(conn);
 contActive--;
 threadLocal.remove();
 // 唤醒所有正待等待的线程，去抢连接
 if (LOG.isInfoEnabled()) {
 LOG.info("调用notifyAll()方法唤醒所有正待等待的线程，去抢连接");
 }
 notifyAll();
 } else {
 if (LOG.isInfoEnabled()) {
 LOG.info("由于该链接已被关闭或空闲连接池数>设定的最大数,这里采取强制关闭");
 }
 conn.close();
 }
 }

 /**
 * 判断连接是否可用
 *
 * @param conn
 * @return
 */
 private boolean isValid(final Connection conn) {
 try {
 if (conn == null || conn.isClosed()) {
 return false;
 }
 } catch (SQLException e) {
 if (LOG.isInfoEnabled()) {
 LOG.info("判断连接是否可用时出现异常");
 }
 e.printStackTrace();
 }
 return true;
 }

 /**
 * 销毁连接池
 */
 public synchronized void destroy() {
 /**
 * 销毁空闲连接池
 */
 for (Connection conn : freeConnection) {
 try {
 if (isValid(conn)) {
 conn.close();
 }
 } catch (SQLException e) {
 e.printStackTrace();
 }
 }
 /**
 * 销毁自定义连接池
 */
 for (Connection conn : activeConnection) {
 try {
 if (isValid(conn)) {
 conn.close();
 }
 } catch (SQLException e) {
 e.printStackTrace();
 }
 }
 isActive = false;
 contActive = 0;
 }

 /**
 * 连接池状态
 *
 * @return
 */
 public boolean isActive() {
 return isActive;
 }

 /**
 * 定时检查连接池情况
 */
 public void cheackPool() {
 if (dbBean.isCheakPool()) {
 if (LOG.isInfoEnabled()) {
 LOG.info("开启连接池定时检查任务,当前设置的检查频率为" + dbBean.getPeriodCheck() + ",检查间歇时间为" + dbBean.getLazyCheck());
 }
 // 启动一个定时器类的时钟任务，用于检测当前使用的线程情况
 new Timer().schedule(new TimerTask() {
 @Override
 public void run() {
 // 1.对线程里面的连接状态
 // 2.连接池最小最大连接数
 // 3.其他状态进行检查，因为这里还需要写几个线程管理的类，暂时就不添加了
 if (LOG.isInfoEnabled()) {
 LOG.info("空线池连接数：" + freeConnection.size());
 LOG.info("活动连接数：：" + activeConnection.size());
 LOG.info("总的连接数：" + contActive);
 }
 }
 }, dbBean.getLazyCheck(), dbBean.getPeriodCheck());
 }
 }
}

/***********************************ConnectionPoolManager**********/

/*
* Copyright 2000-2020 YGSoft.Inc All Rights Reserved.
*/
package com.ygsoft.gris.mapp.materialmanage.impl.util;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Hashtable;

import com.ygsoft.ecp.service.log.EcpLogFactory;
import com.ygsoft.ecp.service.log.IEcpLog;

/**
* 连接池管理. 
*
* @author mapengfei 
* @version 1.0.0 2016年5月12日 
* @see
* @since JDK 1.5.0
*/
public class ConnectionPoolManager {

 /**
 * 连接池存放
 */
 public Hashtable<String, ConnectionPool> pools = new Hashtable<String, ConnectionPool>();
 /**
 * 日志
 */
 private static final IEcpLog LOG = EcpLogFactory.getLog(ConnectionPoolManager.class);

 /**
 * 构造函数
 */
 private ConnectionPoolManager() {
 init();
 }

 /**
 * 单例实现
 *
 * @return
 */
 public static ConnectionPoolManager getInstance() {
 return Singtonle.INSTANCE; // 启动单例模式
 }

 /**
 *
 * 启用单例 
 *
 * @author mapengfei 
 * @version 1.0.0 2016年5月12日 
 * @see
 * @since JDK 1.5.0
 */
 private static class Singtonle {
 private static ConnectionPoolManager INSTANCE = new ConnectionPoolManager();
 }

 /**
 * 初始化所有的连接池
 */
 public void init() {
 /**
 * 将连接池放入管理
 */
 for (int i = 0; i < DBInitInfo.beans.size(); i++) {
 DBbean bean = DBInitInfo.beans.get(i);
 ConnectionPool pool = new ConnectionPool(bean);
 if (pool != null) {
 pools.put(bean.getPoolName(), pool); // 写入集合
 if (LOG.isInfoEnabled()) {
 LOG.info("信息:初始化连接池成功 ->" + bean.getPoolName());
 }
 }
 }
 }

 /**
 * 获得连接,根据连接池名字获得连接
 *
 * @param poolName
 * 连接池名称
 * @return
 */
 public Connection getConnection(final String poolName) {
 Connection conn = null;
 if (pools.size() > 0 && pools.containsKey(poolName)) {
 conn = getPool(poolName).getConnection();
 } else {
 if (LOG.isInfoEnabled()) {
 LOG.info("Error:没有找到该链接 ->" + poolName);
 }
 }
 return conn;
 }

 /**
 * 关闭，回收连接
 *
 * @param poolName
 * 连接池名称
 * @param conn
 * 链接
 */
 public void closeConnection(final String poolName, final Connection conn) {
 if (LOG.isInfoEnabled()) {
 LOG.info("启动链接关闭回收机制来关闭名为"+poolName+"的连接池的链接"+conn);
 }
 ConnectionPool pool = getPool(poolName);
 try {
 if (pool != null) {
 pool.releaseConn(conn);
 }
 } catch (SQLException e) {
 if (LOG.isInfoEnabled()) {
 LOG.info("连接池已经销毁");
 }
 e.printStackTrace();
 }
 }

 /**
 * 清空连接池
 *
 * @param poolName
 * 连接池名称
 */
 public void destroy(final String poolName) {
 ConnectionPool pool = getPool(poolName);
 if (pool != null) {
 pool.destroy();
 }
 }

 /**
 * 获得连接池
 *
 * @param poolName
 * 连接池名称
 * @return
 */
 public ConnectionPool getPool(final String poolName) {
 ConnectionPool pool = null;
 if (pools.size() > 0) {
 pool = pools.get(poolName);
 }
 return pool;
 }

}

/****************************DBbean******************/

package com.ygsoft.gris.mapp.materialmanage.impl.util;
/**
* 这是外部可以配置的连接池属性
* 可以允许外部配置，拥有默认值
* @author Ran
*
*/
public class DBbean {
   // 连接池属性
   private String driverName;
   private String url;
   private String userName;
   private String password;
   private String poolName; // 链接池的名称
   private int minConnections = 1; // 空闲池，最小连接数
   private int maxConnections = 10; // 空闲池，最大连接数
   private int initConnections = 5;// 初始化连接数
   private long connTimeOut = 1000;// 重复获得连接的频率
   private int maxActiveConnections = 10;// 最大允许的连接数，和数据库对应
   private long connectionTimeOut = 1000*60;// 连接超时时间，默认1分钟
   private boolean isCurrentConnection = true; // 是否获得当前连接，默认true
   private boolean isCheakPool = true; // 是否定时检查连接池
   private long lazyCheck = 1000*60;// 延迟多少时间后开始检查
   private long periodCheck = 1000*60;// 检查频率
   /**
   *
   * @param driverName 驱动名称
   * @param url
   * @param userName
   * @param password
   * @param poolName 连接池名称
   */
   public DBbean(final String driverName, final String url, final String userName,final String password, final String poolName) {
       super();
       this.driverName = driverName;
       this.url = url;
       this.userName = userName;
       this.password = password;
       this.poolName = poolName;
   }
   public DBbean() {
   }
   public String getDriverName() {
       if(driverName == null){
           driverName = this.getDriverName()+"_"+this.getUrl();
       }
       return driverName;
   }
   /**
   * 获取url.
   * @return the url
   */
   public String getUrl() {
       return url;
   }
   /**
   * 设置url.
   * @param newUrl the url to set
   */
   public void setUrl(final String newUrl) {
       url = newUrl;
   }
   /**
   * 获取userName.
   * @return the userName
   */
   public String getUserName() {
       return userName;
   }
   /**
   * 设置userName.
   * @param newUserName the userName to set
   */
   public void setUserName(final String newUserName) {
       userName = newUserName;
   }
   /**
   * 获取password.
   * @return the password
   */
   public String getPassword() {
       return password;
   }
   /**
   * 设置password.
   * @param newPassword the password to set
   */
   public void setPassword(final String newPassword) {
       password = newPassword;
   }
   /**
   * 获取poolName.
   * @return the poolName
   */
   public String getPoolName() {
       return poolName;
   }
   /**
   * 设置poolName.
   * @param newPoolName the poolName to set
   */
   public void setPoolName(final String newPoolName) {
       poolName = newPoolName;
   }
   /**
   * 获取minConnections.
   * @return the minConnections
   */
   public int getMinConnections() {
       return minConnections;
   }
   /**
   * 设置minConnections.
   * @param newMinConnections the minConnections to set
   */
   public void setMinConnections(final int newMinConnections) {
       minConnections = newMinConnections;
   }
   /**
   * 获取maxConnections.
   * @return the maxConnections
   */
   public int getMaxConnections() {
       return maxConnections;
   }
   /**
   * 设置maxConnections.
   * @param newMaxConnections the maxConnections to set
   */
   public void setMaxConnections(final int newMaxConnections) {
       maxConnections = newMaxConnections;
   }
   /**
   * 获取initConnections.
   * @return the initConnections
   */
   public int getInitConnections() {
       return initConnections;
   }
   /**
   * 设置initConnections.
   * @param newInitConnections the initConnections to set
   */
   public void setInitConnections(final int newInitConnections) {
       initConnections = newInitConnections;
   }
   /**
   * 获取connTimeOut.
   * @return the connTimeOut
   */
   public long getConnTimeOut() {
       return connTimeOut;
   }
   /**
   * 设置connTimeOut.
   * @param newConnTimeOut the connTimeOut to set
   */
   public void setConnTimeOut(final long newConnTimeOut) {
       connTimeOut = newConnTimeOut;
   }
   /**
   * 获取maxActiveConnections.
   * @return the maxActiveConnections
   */
   public int getMaxActiveConnections() {
       return maxActiveConnections;
   }
   /**
   * 设置maxActiveConnections.
   * @param newMaxActiveConnections the maxActiveConnections to set
   */
   public void setMaxActiveConnections(final int newMaxActiveConnections) {
       maxActiveConnections = newMaxActiveConnections;
   }
   /**
   * 获取connectionTimeOut.
   * @return the connectionTimeOut
   */
   public long getConnectionTimeOut() {
       return connectionTimeOut;
   }
   /**
   * 设置connectionTimeOut.
   * @param newConnectionTimeOut the connectionTimeOut to set
   */
   public void setConnectionTimeOut(final long newConnectionTimeOut) {
       connectionTimeOut = newConnectionTimeOut;
   }
   /**
   * 获取isCurrentConnection.
   * @return the isCurrentConnection
   */
   public boolean isCurrentConnection() {
       return isCurrentConnection;
   }
   /**
   * 设置isCurrentConnection.
   * @param newIsCurrentConnection the isCurrentConnection to set
   */
   public void setCurrentConnection(final boolean newIsCurrentConnection) {
       isCurrentConnection = newIsCurrentConnection;
   }
   /**
   * 获取isCheakPool.
   * @return the isCheakPool
   */
   public boolean isCheakPool() {
       return isCheakPool;
   }
   /**
   * 设置isCheakPool.
   * @param newIsCheakPool the isCheakPool to set
   */
   public void setCheakPool(final boolean newIsCheakPool) {
       isCheakPool = newIsCheakPool;
   }
   /**
   * 获取lazyCheck.
   * @return the lazyCheck
   */
   public long getLazyCheck() {
       return lazyCheck;
   }
   /**
   * 设置lazyCheck.
   * @param newLazyCheck the lazyCheck to set
   */
   public void setLazyCheck(final long newLazyCheck) {
       lazyCheck = newLazyCheck;
   }
   /**
   * 获取periodCheck.
   * @return the periodCheck
   */
   public long getPeriodCheck() {
       return periodCheck;
   }
   /**
   * 设置periodCheck.
   * @param newPeriodCheck the periodCheck to set
   */
   public void setPeriodCheck(final long newPeriodCheck) {
       periodCheck = newPeriodCheck;
   }
   /**
   * 设置driverName.
   * @param newDriverName the driverName to set
   */
   public void setDriverName(final String newDriverName) {
       driverName = newDriverName;
   }



}

/********************************************DBInitInfo********************/

/*
* Copyright 2000-2020 YGSoft.Inc All Rights Reserved.
*/
package com.ygsoft.gris.mapp.materialmanage.impl.util;

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

import com.ygsoft.gris.mapp.materialmanage.impl.ext.config.BaseConfig;

/**
* 初始化，模拟加载所有的配置文件 . 
* @author mapengfei 
* @version 1.0.0 2016年5月12日 
* @see
* @since JDK 1.5.0
*/
public class DBInitInfo {
 public static List<DBbean> beans = null;
 static{
 beans = new ArrayList<DBbean>();
 // 这里数据可以从xml 等配置文件进行获取
 // 为了测试，这里我直接写死
 DBbean beanOracle = new DBbean();
 beanOracle.setDriverName(BaseConfig.get("DriverName"));
 beanOracle.setUrl(BaseConfig.get("JdbcUrl"));
 beanOracle.setMinConnections(Integer.valueOf(BaseConfig.get("MinConnections")));
 beanOracle.setMaxConnections(Integer.valueOf(BaseConfig.get("MaxConnections")));
 beanOracle.setPoolName(BaseConfig.get("PoolName"));
 beanOracle.setUserName(BaseConfig.get("dbusername"));
 beanOracle.setPassword(BaseConfig.get("dbpassword"));

 beans.add(beanOracle);
 }
}

Jdbc 链接数据库（含连接池）

相关阅读