In development, we often use database connection pools, such as dbcp database connection pools. This chapter will explain the simple use of Java connection dbcp database library connection pools.
Development Tools myeclipse2014
1. First create a web project. I named the project name testjdbc. I need a configuration file with web.xml to configure the servlet. After the creation is completed, the project structure is as follows:
2. Create a package. The package name I created is com.szkingdom.db
3. Create the help class CastUtil, the code is as follows:
package com.szkingdom.db; /** * Created by jack on 2015/12/26. * Transformation operation tool class*/ public class CastUtil { /* * Convert to String type* */ public static String castString(Object obj) { return CastUtil.castString(obj, ""); } /* * Convert to String type (providing default value) * */ public static String castString(Object obj, String defaultValue) { return obj != null ? String.valueOf(obj) : defaultValue; } /* * Convert to double type* */ public static double castDouble(Object obj) { return castDouble(obj, (double)0); } /* * Convert to double type (provided default value) * */ 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; } /* * Convert to long type* */ public static long castLong(Object obj) { return castLong(obj, 0); } /* * Convert to long type (provided default value) * */ 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; } /* * Convert to int type* */ public static int castInt(Object obj){ return castInt(obj,0); } /* * Convert to int type (providing the default value) * */ 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; } /* * Convert to boolean type* */ public static boolean castBoolean(Object obj){ return castBoolean(obj,false); } /* * Convert to boolean type (provides default value) * */ public static boolean castBoolean(Object obj,boolean defaultValue){ boolean booleanValue=defaultValue; if(obj!=null){ booleanValue=Boolean.parseBoolean(castString(obj)); } return booleanValue; } } 4. Create a property file to read the help class PropsUtil, the code is as follows:
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. * Properties file tool class*/ public class PropsUtil { //private static final Logger LOGGER = LoggerFactory.getLogger(PropsUtil.class); /* * Loading property file* * */ 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; } /* * Get character attributes (default is an empty string) * * */ public static String getString(Properties props, String key) { return getString(props, key, ""); } /* * Get character-type attributes (the default value can be specified) * */ public static String getString(Properties props, String key, String defaultValue) { String value = defaultValue; if (props.containsKey(key)) { value = props.getProperty(key); } return value; } /* * Get numerical type attributes (default is 0) * */ public static int getInt(Properties props, String key) { return getInt(props, key, 0); } /* * Get the numerical type attribute (the default value can be specified) * */ 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; } /* * Get the boolean property (the default value is false) * */ public static boolean getBoolean(Properties props, String key) { return getBoolean(props, key, false); } /* * Get boolean property (the default value can be specified) * */ 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. Create a string help class StringUtil, the code is as follows:
package com.szkingdom.db; /** * Created by jack on 2015/12/26. * String tool class */ public class StringUtil { /* * Determine whether the string is empty * */ public static boolean isEmpty(String str){ if(str != null){ str=str.trim(); } //return StringUtils.isEmpty(str); return "".equals(str); } /* * Determine whether the string is not empty * */ public static boolean isNotEmpty(String str){ return !isEmpty(str); } } 6. Create a database connection property file dbconfig.properties in the src directory
<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. Put the necessary jar packages in the lib directory:
8. Create a database help class using 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. Database Operation Assistant Class*/ 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; // Ensure one thread and one connection, thread-safe private static final ThreadLocal<Connection> CONNECTION_HOLDER ; // Thread pool 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); //Database connection pool parameter configuration: 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 ///Set the maximum total number of idle and borrowed connections, and can be activated at the same time. DATA_SOURCE.setMaxTotal(60); //Set the initial size DATA_SOURCE.setInitialSize(10); //Minimum idle connection DATA_SOURCE.setMinIdle(8); //Maximum idle connection DATA_SOURCE.setMaxIdle(16); //Timeout waiting time milliseconds DATA_SOURCE.setMaxWaitMillis(2*10000); //Only the current connection is invalid, create another connection for the current query to use DATA_SOURCE.setTestOnBorrow(true); //removeAbandonedTimeout : Recycling unused (absent) connections (default is 300 seconds, adjusted to 180) DATA_SOURCE.setRemoveAbandonedTimeout(180); //removeAbandoned: After the time of removeAbandonedTimeout exceeds the time of removeAbandonedTimeout, whether to recycle unused (absent) connections (default is false, adjusted to true) //DATA_SOURCE.setRemoveAbandonedOnMaintenance(removeAbandonedOnMaintenance); DATA_SOURCE.setRemoveAbandonedOnBorrow(true); //testWhileIdle DATA_SOURCE.setTestOnReturn(true); //testOnReturn DATA_SOURCE.setTestOnReturn(true); //setRemoveAbandonedOnMaintenance DATA_SOURCE.setRemoveAbandonedOnMaintenance(true); //Record log DATA_SOURCE.setLogAbandoned(true); //SetAddress automatic submission DATA_SOURCE.setDefaultAutoCommit(true); // DATA_SOURCE.setEnableAutoCommitOnReturn(true); System.out.println("Complete the parameter setting of the database connection pool 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>(); /** * Get database 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(" Minimum idle connection MinIdle="+DATA_SOURCE.getMinIdle()); System.out.println(" MaxIdle connection MaxIdle="+DATA_SOURCE.getMaxIdle()); System.out.println(" Maximum number of connections MaxTotal="+DATA_SOURCE.getMaxTotal()); System.out.println(" Initial size InitialSize="+DATA_SOURCE.getInitialSize()); System.out.println(" Timeout waiting time MaxWaitMillis="+(DATA_SOURCE.getMaxWaitMillis()/1000)); System.out.println(" Get active connections getNumActive()="+DATA_SOURCE.getNumActive()); System.out.println(" Get the number of connections getNumIdle="+DATA_SOURCE.getNumIdle());*/ CONNECTION_HOLDER.set(conn); } } return conn; } /** * Close the database connection*/ 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(); } } } // Perform database operations public static synchronized void update(int thlsh,String ltnr) { Connection conn = getConnection(); if(conn==null){ System.out.println("The ()connection in the update method is null!!"); } PreparedStatement pstmt=null; System.out.println("update start!"); 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("The updated sql statement is: 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("Update the data with id=1 successfully!"); System.out.println("Update the chat content data of thlsh="+thlsh+" successfully!/n Chat content is: "+ltnr); } //conn.commit(); /*while(rs1.next()){ ltlsh = rs1.getInt("ltlsh"); System.out.println("Query the chat flow number successfully, the chat flow number is ltlsh->"+ltlsh); }*/ //pstmt.setString(1, "Excellent content update1"); //pstmt.setInt(2, 1); //pstmt.setBlob(1, new ByteArrayInputStream("12345 China".getBytes())); //pstmt.setInt(2, 76732); /*if(pstmt.executeUpdate()>0){ //System.out.println("Update data with id=1 successful!"); System.out.println("Update data with id=76732 successful!"); } conn.commit();*/ System.out.println("update t_wx_ltnrk success"); } catch (SQLException e) { //LOGGER.error("query entity list failure", e); System.out.println("Update data exception 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 failed"); } } if(conn!=null){ try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //Remove the Connection in the thread. If the connection is not removed, the connection obtained will be closed. The data operation cannot be performed. CONNECTION_HOLDER.remove(); //closeConnection(); } //return entityList; } } 9. The basic database connection pool is created. After that, you can simulate the database connection through the update method of DatabaseHelper to simulate the operation of obtaining database connections and perform data operations according to your own needs.
The above is all the content of this article. I hope it will be helpful to everyone's learning and I hope everyone will support Wulin.com more.