java使用dbcp2數據庫連接池

在開發中中我們經常會使用到數據庫連接池，比如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; } }

9、基本的數據庫連接池就創建完畢了，之後就可以通過DatabaseHelper的update方法來模擬獲取數據庫連接進行數據庫的操作，可根據自己的需求進行數據的操作。

以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持武林網。