使用DBCP创建MySQL数据库连接池
程序员文章站
2022-04-22 08:32:54
...
DBCP(DataBase connection pool),数据库连接池。是 apache 上的一个 java 连接池项目,也是 tomcat 使用的连接池组件。单独使用dbcp需要3个包:commons-dbcp.jar,commons-pool.jar,commons-collections.jar。
由于建立数据库连接是一个非常耗时耗资源的行为,所以通过连接池预先同数据库建立一些连接,放在内存中,应用程序需要建立数据库连接时直接到连接池中申请一个就行,用完后再放回去。
【1】配置文件
#mysql db.className=com.mysql.jdbc.Driver #mysql to write wdb.url=jdbc:mysql:// wdb.username=root wdb.pwd=123456 wdb.maxActive=30 wdb.maxIdle=10 wdb.maxWait=3600000 #mysql common config info common.db.removeAbandoned=false common.db.removeAbandonedTimeout=120 common.db.testOnBorrow=true common.db.logAbandoned=true
【2】创建数据库连接池并获得数据库连接
package hhf.dbcp.util; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Properties; import org.apache.commons.dbcp2.BasicDataSource; import org.apache.commons.dbcp2.BasicDataSourceFactory; /** * Get the connection from writing DB */ public class WDBConnUtils { private static BasicDataSource dataSource = null; public static void init() { if (dataSource != null) { try { dataSource.close(); } catch (Exception e) { e.printStackTrace(); } dataSource = null; } try { Properties p = new Properties(); p.setProperty("driverClassName", SystemConstants.DB_CLASS_NAME); p.setProperty("url", SystemConstants.W_DB_URL); p.setProperty("username", SystemConstants.W_DB_USER_NAME); p.setProperty("password", SystemConstants.W_DB_USER_PWD); p.setProperty("maxActive", SystemConstants.W_DB_MAX_ACTIVE); p.setProperty("maxIdle", SystemConstants.W_DB_MAX_IDLE); p.setProperty("maxWait", SystemConstants.W_DB_MAX_WAIT); p.setProperty("removeAbandoned", SystemConstants.DB_REMOVE_ABANDONED); p.setProperty("removeAbandonedTimeout", SystemConstants.DB_REMOVE_ABANDONED_TIMEOUT); p.setProperty("testOnBorrow", SystemConstants.DB_TEST_ONBORROW); p.setProperty("logAbandoned", SystemConstants.DB_LOG_ABANDONED); dataSource = (BasicDataSource) BasicDataSourceFactory.createDataSource(p); } catch (Exception e) { e.printStackTrace(); } } public static synchronized Connection getConnection() throws SQLException { if (dataSource == null) { init(); } Connection conn = null; if (dataSource != null) { conn = dataSource.getConnection(); conn.setAutoCommit(false); } return conn; } /** * 关闭资源 * @param conn * @param pstmt * @param rs */ public static void closeResources(Connection conn, PreparedStatement pstmt, ResultSet rs) { if (null != rs) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } finally { if (null != pstmt) { try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } finally { if (null != conn) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } } } } } } /** * 关闭资源 * @param conn * @param pstmt */ public static void closeResources(Connection conn, PreparedStatement pstmt) { if (null != pstmt) { try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } finally { if (null != conn) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } } } } }
【3】执行插入和查询操作
package hhf.dbcp.throughput; import hhf.dbcp.util.WDBConnUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; /** * 数据库操作 * @author HHF * 2014年12月29日 */ public class InsertQueryDao { /** * 插入数据 * @param num */ public void save(String num) { String NEWS_INFO_INSERT = "INSERT INTO table(num) values(?)"; Connection conn = null; PreparedStatement pst = null; try { conn = WDBConnUtils.getConnection(); pst = conn.prepareStatement(NEWS_INFO_INSERT); pst.setString(1, num); pst.execute(); conn.commit(); } catch (SQLException e) { e.printStackTrace(); } finally { WDBConnUtils.closeResources(conn, pst); } } /** * 查询数据 * @param num * @return */ public String query(String num) { String NEWS_INFO_QUERY_LIST = "SELECT news_id FROM table num=? "; Connection conn = null; PreparedStatement pst = null; ResultSet rs = null; String result = null; try { conn = WDBConnUtils.getConnection(); pst = conn.prepareStatement(NEWS_INFO_QUERY_LIST); pst.setString(1, num); rs = pst.executeQuery(); while(rs.next()){ result = rs.getString("news_id"); } } catch (Exception e) { e.printStackTrace(); }finally{ WDBConnUtils.closeResources(conn, pst, rs); } return result; } }
【4】测试
public class Main { private static final Log log = LogFactory.getLog(Main.class); public void doProcess(String processTime1, String processTime2, int runMode) { InsertQueryDao dao = new InsertQueryDao(); String num = dao.query("0");//获得查询结果 log.info("num " + num); } }
(PS:附上项目测试源码和相关的jar包)
上一篇: 数据库分页
推荐阅读