java使用dbcp2数据库连接池

Z时代
2024-01-10
分类：IT

在开发中中我们经常会使用到数据库连接池" title="数据库连接池">数据库连接池，比如dbcp数据库连接池，本章将讲解java连接dbcp数据库库连接池的简单使用。

开发工具myeclipse2014

1、首先创建一个web项目，我把项目名取名为testjdbc，需要带有web.xml的配置文件，进行servlet的配置，创建完成以后的项目结构如下：

2、创建包，我创建的包名是com.szkingdom.db

3、创建帮助类CastUtil，代码如下：


package com.szkingdom.db; 
/** 
 * Created by jack on 2015/12/26. 
 * 转型操作工具类 
 */ 
public class CastUtil { 
  /* 
  * 转为String型 
  * */ 
  public static String castString(Object obj) { 
   return CastUtil.castString(obj, ""); 
  } 
  /* 
  * 转为String型（提供默认值） 
  * */ 
  public static String castString(Object obj, String defaultValue) { 
   return obj != null ? String.valueOf(obj) : defaultValue; 
  } 
  /* 
  * 转为double型 
  * */ 
  public static double castDouble(Object obj) { 
   return castDouble(obj, (double)0); 
  } 
  /* 
  * 转为double型（提供默认值） 
  * */ 
  public static double castDouble(Object obj, Double defaultValue) { 
   double doubleValue = defaultValue; 
   if (obj != null) { 
    String strValue = castString(obj); 
    if (StringUtil.isNotEmpty(strValue)) { 
     try { 
      doubleValue = Double.parseDouble(strValue); 
     } catch (NumberFormatException e) { 
      defaultValue = defaultValue; 
     } 
    } 
   } 
   return doubleValue; 
  } 
  /* 
  * 转为long型 
  * */ 
  public static long castLong(Object obj) { 
   return castLong(obj, 0); 
  } 
  /* 
   * 转为long型（提供默认值） 
   * */ 
  public static long castLong(Object obj, long defaultValue) { 
   long longValue = defaultValue; 
   if (obj != null) { 
    String strValue = castString(obj); 
    if (StringUtil.isNotEmpty(strValue)) { 
     try { 
      longValue = Long.parseLong(strValue); 
     }catch (NumberFormatException e){ 
      longValue=defaultValue; 
     } 
    } 
   } 
   return longValue; 
  } 
  /* 
  * 转为int型 
  * */ 
  public static int castInt(Object obj){ 
   return castInt(obj,0); 
  } 
  /* 
  * 转为int型(提供默值) 
  * */ 
  public static int castInt(Object obj,int defaultValue){ 
   int intValue=defaultValue; 
   if (obj!=null){ 
    String strValue=castString(obj); 
    if(StringUtil.isNotEmpty(strValue)){ 
     try { 
      intValue=Integer.parseInt(strValue); 
     }catch (NumberFormatException e){ 
      intValue=defaultValue; 
     } 
    } 
   } 
   return intValue; 
  } 
  /* 
  * 转为boolean型 
  * */ 
  public static boolean castBoolean(Object obj){ 
   return castBoolean(obj,false); 
  } 
  /* 
  * 转为boolean型(提供默认值) 
  * */ 
  public static boolean castBoolean(Object obj,boolean defaultValue){ 
   boolean booleanValue=defaultValue; 
   if(obj!=null){ 
    booleanValue=Boolean.parseBoolean(castString(obj)); 
   } 
   return booleanValue; 
  } 
}

4、创建属性文件读取帮助类PropsUtil，代码如下：


package com.szkingdom.db; 
import java.io.FileNotFoundException; 
import java.io.IOException; 
import java.io.InputStream; 
import java.util.Properties; 
/** 
 * Created by jack on 2015/12/26. 
 * 属性文件工具类 
 */ 
public class PropsUtil { 
  //private static final Logger LOGGER = LoggerFactory.getLogger(PropsUtil.class); 
  /* 
  * 加载属性文件 
  * 
  * */ 
  public static Properties loadProps(String fileName) { 
   Properties properties = null; 
   InputStream inputStream = null; 
   try { 
    inputStream = Thread.currentThread().getContextClassLoader().getResourceAsStream(fileName); 
    if (inputStream == null) { 
     throw new FileNotFoundException(fileName + " file is not found!"); 
    } 
    properties = new Properties(); 
    properties.load(inputStream); 
   } catch (IOException e) { 
    //LOGGER.error("load properties file failure", e); 
    System.out.println("load properties file failure:"+e); 
   } finally { 
    if (inputStream != null) { 
     try { 
      inputStream.close(); 
     } catch (IOException e) { 
      //LOGGER.error("close input stream failure", e); 
      System.out.println("close input stream failure:"+e); 
     } 
    } 
   } 
   return properties; 
  } 
  /* 
  * 获取字符型属性（默认为空字符串） 
  * 
  * */ 
  public static String getString(Properties props, String key) { 
   return getString(props, key, ""); 
  } 
  /* 
  * 获取字符型属性（可指定默认值） 
  * */ 
  public static String getString(Properties props, String key, String 
    defaultValue) { 
   String value = defaultValue; 
   if (props.containsKey(key)) { 
    value = props.getProperty(key); 
   } 
   return value; 
  } 
  /* 
  * 获取数值类型属性（默认为0） 
  * */ 
  public static int getInt(Properties props, String key) { 
   return getInt(props, key, 0); 
  } 
  /* 
  * 获取数值类型属性（可指定默认值） 
  * */ 
  public static int getInt(Properties props, String key, int defaultValue) { 
   int value = defaultValue; 
   if (props.containsKey(key)) { 
    value = CastUtil.castInt(props.getProperty(key)); 
   } 
   return value; 
  } 
  /* 
  * 获取布尔型属性（默认值为false） 
  * */ 
  public static boolean getBoolean(Properties props, String key) { 
   return getBoolean(props, key, false); 
  } 
  /* 
  * 获取布尔型属性（可指定默认值） 
  * */ 
  public static boolean getBoolean(Properties props, String key, Boolean defaultValue) { 
   boolean value = defaultValue; 
   if (props.containsKey(key)) { 
    value = CastUtil.castBoolean(props.getProperty(key)); 
   } 
   return value; 
  } 
}

5、创建一个字符串帮助类StringUtil，代码如下：


package com.szkingdom.db; 
/** 
 * Created by jack on 2015/12/26. 
 * 字符串工具类 
 */ 
public class StringUtil { 
 /* 
  * 判断字符串是否为空 
  * */ 
  public static boolean isEmpty(String str){ 
   if(str != null){ 
    str=str.trim(); 
   } 
   //return StringUtils.isEmpty(str); 
   return "".equals(str); 
  } 
  /* 
  * 判断字符串是否非空 
  * */ 
  public static boolean isNotEmpty(String str){ 
   return !isEmpty(str); 
  } 
}

6、在src目录下创建一个数据库连接的属性文件dbconfig.properties


<span style="color:#333333;">jdbc.driver=com.mysql.jdbc.Driver 
jdbc.url=jdbc:mysql://</span><span style="color:#ff6666;background-color: rgb(255, 0, 0);">127.0.0.1:3306/****</span><span style="color:#333333;"> 
jdbc.username=**** 
jdbc.password=****</span>

7、把必备的jar包放到lib目录下：

8、使用dbcp创建数据库帮助类


package com.szkingdom.db; 
import java.io.ByteArrayInputStream; 
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.PreparedStatement; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
import java.util.Properties; 
import org.apache.commons.dbcp2.BasicDataSource; 
/** 
 * Created by jack on 2015/12/26. 数据库操作助手类 
 */ 
public class DatabaseHelper { 
 // private static final Logger LOGGER= 
 // LoggerFactory.getLogger(DatabaseHelper.class); 
 private static final String DRIVER; 
 private static final String URL; 
 private static final String USERNAME; 
 private static final String PASSWORD; 
 //保证一个线程一个Connection，线程安全 
 private static final ThreadLocal<Connection> CONNECTION_HOLDER ; 
 //线程池 
 private static final BasicDataSource DATA_SOURCE; 
 static { 
   CONNECTION_HOLDER = new ThreadLocal<Connection>(); 
  Properties conf = PropsUtil.loadProps("dbconfig.properties"); 
  DRIVER = conf.getProperty("jdbc.driver"); 
  URL = conf.getProperty("jdbc.url"); 
  USERNAME = conf.getProperty("jdbc.username"); 
  PASSWORD = conf.getProperty("jdbc.password"); 
  String driver = conf.getProperty("jdbc.driver"); 
  String url = conf.getProperty("jdbc.url"); 
  String username = conf.getProperty("jdbc.username"); 
  String passwrod = conf.getProperty("jdbc.password"); 
  DATA_SOURCE=new BasicDataSource(); 
  DATA_SOURCE.setDriverClassName(driver); 
  DATA_SOURCE.setUrl(url); 
  DATA_SOURCE.setUsername(username); 
  DATA_SOURCE.setPassword(passwrod); 
  //数据库连接池参数配置：http://www.cnblogs.com/xdp-gacl/p/4002804.html 
  //http://greemranqq.iteye.com/blog/1969273 
  //http://blog.csdn.net/j903829182/article/details/50190337 
  //http://blog.csdn.net/jiutianhe/article/details/39670817 
  //http://bsr1983.iteye.com/blog/2092467 
  //http://blog.csdn.net/kerafan/article/details/50382998 
  //http://blog.csdn.net/a9529lty/article/details/43021801 
  ///设置空闲和借用的连接的最大总数量，同时可以激活。 
  DATA_SOURCE.setMaxTotal(60); 
  //设置初始大小 
  DATA_SOURCE.setInitialSize(10); 
  //最小空闲连接 
  DATA_SOURCE.setMinIdle(8); 
  //最大空闲连接 
  DATA_SOURCE.setMaxIdle(16); 
  //超时等待时间毫秒 
  DATA_SOURCE.setMaxWaitMillis(2*10000); 
  //只会发现当前连接失效，再创建一个连接供当前查询使用 
  DATA_SOURCE.setTestOnBorrow(true); 
  //removeAbandonedTimeout ：超过时间限制，回收没有用(废弃)的连接（默认为 300秒，调整为180） 
  DATA_SOURCE.setRemoveAbandonedTimeout(180); 
  //removeAbandoned ：超过removeAbandonedTimeout时间后，是否进 行没用连接（废弃）的回收（默认为false，调整为true) 
  //DATA_SOURCE.setRemoveAbandonedOnMaintenance(removeAbandonedOnMaintenance); 
  DATA_SOURCE.setRemoveAbandonedOnBorrow(true); 
  //testWhileIdle 
  DATA_SOURCE.setTestOnReturn(true); 
  //testOnReturn 
  DATA_SOURCE.setTestOnReturn(true); 
  //setRemoveAbandonedOnMaintenance 
  DATA_SOURCE.setRemoveAbandonedOnMaintenance(true); 
  //记录日志 
  DATA_SOURCE.setLogAbandoned(true); 
  //设置自动提交 
  DATA_SOURCE.setDefaultAutoCommit(true); 
  // DATA_SOURCE.setEnableAutoCommitOnReturn(true); 
  System.out.println("完成设置数据库连接池DATA_SOURCE的参数！！"); 
  /*try { 
   Class.forName(DRIVER); 
   System.out.println("load jdbc driver success"); 
  } catch (ClassNotFoundException e) { 
   // LOGGER.error("can not load jdbc driver",e); 
   System.out.println("can not load jdbc driver:" + e); 
  }finally{ 
  }*/ 
 } 
 //private static final ThreadLocal<Connection> CONNECTION_HOLDER = new ThreadLocal<Connection>(); 
 /** 
  * 获取数据库连接 
  */ 
 public static Connection getConnection() { 
  Connection conn = CONNECTION_HOLDER.get();// 1 
  if (conn == null) { 
   try { 
    //conn = DriverManager.getConnection(URL, USERNAME, PASSWORD); 
    conn = DATA_SOURCE.getConnection(); 
    System.out.println("get connection success"); 
   } catch (SQLException e) { 
    // LOGGER.error("get connection failure", e); 
    System.out.println("get connection failure:" + e); 
   } finally { 
    /*System.out.println(" 最小空闲连接MinIdle="+DATA_SOURCE.getMinIdle()); 
    System.out.println(" 最大空闲连接MaxIdle="+DATA_SOURCE.getMaxIdle()); 
    System.out.println(" 最大连接数量MaxTotal="+DATA_SOURCE.getMaxTotal()); 
    System.out.println(" 初始大小InitialSize="+DATA_SOURCE.getInitialSize()); 
    System.out.println(" 超时等待时间MaxWaitMillis="+(DATA_SOURCE.getMaxWaitMillis()/1000)); 
    System.out.println(" 获取活动的连接数getNumActive()="+DATA_SOURCE.getNumActive()); 
    System.out.println(" 获取连接数getNumIdle="+DATA_SOURCE.getNumIdle());*/ 
    CONNECTION_HOLDER.set(conn); 
   } 
  } 
  return conn; 
 } 
 /** 
  * 关闭数据库连接 
  */ 
 public static void closeConnection() { 
  Connection conn = CONNECTION_HOLDER.get();// 1 
  if (conn != null) { 
   try { 
    conn.close(); 
    System.out.println("close connection success"); 
   } catch (SQLException e) { 
    // LOGGER.error("close connection failure", e); 
    System.out.println("close connection failure:" + e); 
    throw new RuntimeException(e); 
   } finally { 
    CONNECTION_HOLDER.remove(); 
   } 
  } 
 } 
 //进行数据库操作 
 public static synchronized void update(int thlsh,String ltnr) { 
  Connection conn = getConnection(); 
  if(conn==null){ 
   System.out.println("update方法里面的()connection为null!!"); 
  } 
  PreparedStatement pstmt=null; 
  System.out.println("update开始!"); 
  int ltlsh=0; 
  try { 
   //String sql="update message set CONTENT = ? where id=?"; 
   //String sql1="select ltlsh from t_zxthlsk where lsh = ?"; 
   String sql="update t_wx_ltnrk b set b.LTNR = ? where b.lsh = "+ 
      "( select a.ltlsh from t_zxthlsk a where a.lsh = ? )"; 
   System.out.println("更新的sql语句为：sql->"+sql); 
   pstmt = conn.prepareStatement(sql); 
   pstmt.setBlob(1, new ByteArrayInputStream(ltnr.getBytes())); 
   pstmt.setInt(2, thlsh); 
   /*pstmt.setString(1, "this is dbcp2 test 2222"); 
   pstmt.setInt(2, 6);*/ 
   if(pstmt.executeUpdate()>0){ 
    //System.out.println("更新id=1的数据成功!"); 
    System.out.println("更新thlsh="+thlsh+"的聊天内容数据成功!\n聊天内容为："+ltnr); 
   } 
   //conn.commit(); 
   /*while(rs1.next()){ 
    ltlsh = rs1.getInt("ltlsh"); 
    System.out.println("查询聊天流水号成功，聊天流水号为ltlsh->"+ltlsh); 
   }*/ 
   //pstmt.setString(1, "精彩内容update1"); 
   //pstmt.setInt(2, 1); 
   //pstmt.setBlob(1, new ByteArrayInputStream("12345中国".getBytes())); 
   //pstmt.setInt(2, 76732); 
   /*if(pstmt.executeUpdate()>0){ 
    //System.out.println("更新id=1的数据成功!"); 
    System.out.println("更新id=76732的数据成功!"); 
   } 
   conn.commit();*/ 
   System.out.println("update t_wx_ltnrk success"); 
  } catch (SQLException e) { 
   //LOGGER.error("query entity list failure", e); 
   System.out.println("更新数据异常connection="+conn); 
   System.out.println("update t_wx_ltnrk failure:" + e); 
   throw new RuntimeException(e); 
  } finally { 
   //closeConnection(); 
   //closeConnection(); 
   if(pstmt!=null){ 
    try { 
     pstmt.close(); 
    } catch (SQLException e) { 
     // TODO Auto-generated catch block 
     e.printStackTrace(); 
     System.out.println("PreparedStatement失败"); 
    } 
   } 
   if(conn!=null){ 
    try { 
     conn.close(); 
    } catch (SQLException e) { 
     // TODO Auto-generated catch block 
     e.printStackTrace(); 
    } 
   } 
   //移除线程里面的Connection，不移除会导致connection关闭以后，获取的connection是 关闭状态，不能进行数据操作 
   CONNECTION_HOLDER.remove(); 
   //closeConnection(); 
  } 
  //return entityList; 
 } 
}