目前比較常見的連接池實現有DBCP、C3P0,Tomcat_JDBC等。 本文使用的連接池是DBCP。 進入[http://commons.apache.org/proper/commons-dbcp/download_dbcp.cgi](http://commons.apache.org/proper/commons-dbcp/download_dbcp.cgi)下載`Apache Commons DBCP for JDBC`，[http://commons.apache.org/proper/commons-pool/download_pool.cgi](http://commons.apache.org/proper/commons-pool/download_pool.cgi)中下載`Apache Commons Pool`，[http://dev.mysql.com/downloads/connector/j/](http://dev.mysql.com/downloads/connector/j/)下載MySQL的JDBC驅動。 若下載出現問題，可以到一些Maven倉庫中下載。例如[http://mvnrepository.com/](http://mvnrepository.com/)、[http://maven.oschina.net](http://maven.oschina.net/)。 ## [](https://github.com/someus/another-tutorial-about-java-web/blob/master/00-07.md#數據庫準備)數據庫準備 MySQL 5.6。 ~~~ --創建數據庫 CREATE DATABASE IF NOT EXISTS `test` DEFAULT CHARSET utf8 COLLATE utf8_general_ci; USE `test`; --創建table CREATE TABLE IF NOT EXISTS user ( `id` int AUTO_INCREMENT, `name` varchar(255), `email` varchar(255), `age` varchar(255), `passwd` varchar(255), PRIMARY KEY (`id`), UNIQUE KEY (`name`), UNIQUE KEY (`email`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ---插入若干數據 INSERT INTO user (`name`, `email`, `age`, `passwd`) VALUES ('user01', 'user01@163.com', 20, password('123')); INSERT INTO user (`name`, `email`, `age`, `passwd`) VALUES ('user02', 'user02@163.com', 20, password('456')); ~~~ ## [](https://github.com/someus/another-tutorial-about-java-web/blob/master/00-07.md#示例1)示例1 目錄結構如下： [](https://github.com/someus/another-tutorial-about-java-web/blob/master/img/00-07/01.png) **web.xml源碼：** ~~~ <?xml version="1.0" encoding="UTF-8"?> <web-app version="3.1" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"> <servlet-mapping> <servlet-name>default</servlet-name> <url-pattern>*.jpg</url-pattern> </servlet-mapping> <servlet-mapping> <servlet-name>default</servlet-name> <url-pattern>*.png</url-pattern> </servlet-mapping> <servlet-mapping> <servlet-name>default</servlet-name> <url-pattern>*.js</url-pattern> </servlet-mapping> <servlet-mapping> <servlet-name>default</servlet-name> <url-pattern>*.css</url-pattern> </servlet-mapping> <session-config> <session-timeout> 30 </session-timeout> </session-config> </web-app> ~~~ **dbcp.properties源碼：** ~~~ driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/test username=root password=123456 initialSize=2 maxActive=15 maxIdle=2 minIdle=1 maxWait=30000 ~~~ 這些配置的解釋請見[BasicDataSource Configuration Parameters](http://commons.apache.org/proper/commons-dbcp/configuration.html)。 **HelloServlet.java源碼：** ~~~ package me.letiantian.servlet; import java.io.IOException; import java.io.PrintWriter; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.Properties; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.sql.DataSource; import org.apache.commons.dbcp.BasicDataSourceFactory; @WebServlet(name = "HelloServlet", urlPatterns = {"/hello"}) public class HelloServlet extends HttpServlet { protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws IOException { response.setContentType("text/html;charset=UTF-8"); PrintWriter out = response.getWriter(); try{ Properties properties=new Properties(); properties.load(getServletContext().getResourceAsStream("/WEB-INF/dbcp.properties")); DataSource dataSource = BasicDataSourceFactory.createDataSource(properties); Connection conn = dataSource.getConnection(); String sql = "select 1+1 as result;"; PreparedStatement pstmt = conn.prepareStatement(sql); ResultSet rs = pstmt.executeQuery(); if (rs.next()) { int result = rs.getInt("result"); out.println("result: " + result); } rs.close(); pstmt.close(); conn.close(); } catch (Exception ex) { out.println(ex.getMessage()); } } @Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException { processRequest(request, response); } @Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException { processRequest(request, response); } } ~~~ 運行項目，瀏覽器訪問`http://localhost:8084/Project_0007_DBCP/hello`： [](https://github.com/someus/another-tutorial-about-java-web/blob/master/img/00-07/02.png) ## [](https://github.com/someus/another-tutorial-about-java-web/blob/master/00-07.md#改進將初始化的連接池放到servlet上下文中)改進：將初始化的連接池放到Servlet上下文中 上面代碼中是再servlet中初始化連接池，更好的方法是再Listener中初始化，并將連接池作為屬性放入servlet上下文中。 源文件以及代碼有所變化，項目結構如下： [](https://github.com/someus/another-tutorial-about-java-web/blob/master/img/00-07/03.png) DBCPListener.java內容如下： ~~~ package me.letiantian.listener; import java.util.Properties; import javax.servlet.ServletContext; import javax.servlet.ServletContextEvent; import javax.servlet.ServletContextListener; import javax.servlet.annotation.WebListener; import javax.sql.DataSource; import org.apache.commons.dbcp.BasicDataSourceFactory; @WebListener public class DBCPListener implements ServletContextListener{ // 應用啟動時，該方法被調用 @Override public void contextInitialized(ServletContextEvent sce) { try { System.out.println("設置數據庫連接池"); ServletContext application = sce.getServletContext(); Properties properties=new Properties(); properties.load(application.getResourceAsStream("/WEB-INF/dbcp.properties")); DataSource dataSource = BasicDataSourceFactory.createDataSource(properties); application.setAttribute("dataSource", dataSource); } catch(Exception ex) { System.err.println("數據庫連接池設置出現異常：" + ex.getMessage()); } } // 應用關閉時，該方法被調用 @Override public void contextDestroyed(ServletContextEvent sce) { } } ~~~ HelloServlet.java內容如下： ~~~ package me.letiantian.servlet; import java.io.IOException; import java.io.PrintWriter; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.sql.DataSource; @WebServlet(name = "HelloServlet", urlPatterns = {"/hello"}) public class HelloServlet extends HttpServlet { protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws IOException { response.setContentType("text/html;charset=UTF-8"); PrintWriter out = response.getWriter(); try { DataSource dataSource = (DataSource) getServletContext().getAttribute("dataSource"); Connection conn = dataSource.getConnection(); String sql = "select name from user;"; PreparedStatement pstmt = conn.prepareStatement(sql); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { String name = rs.getString("name"); out.println("result: " + name + "</br>"); } rs.close(); pstmt.close(); conn.close(); } catch (Exception ex) { out.println(ex.getMessage()); } } @Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException { processRequest(request, response); } @Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException { processRequest(request, response); } } ~~~ 啟動項目，可以看到Tomcat輸出： ~~~ 設置數據庫連接池 ~~~ 瀏覽器輸出： [](https://github.com/someus/another-tutorial-about-java-web/blob/master/img/00-07/04.png) 查看一下mysql的連接： ~~~ mysql> show processlist; +----+------+-----------------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+------+---------+------+-------+------------------+ | 45 | root | localhost | test | Query | 0 | init | show processlist | | 77 | root | localhost:41770 | test | Sleep | 300 | | NULL | | 78 | root | localhost:41771 | test | Sleep | 300 | | NULL | | 83 | root | localhost:41790 | test | Sleep | 274 | | NULL | | 84 | root | localhost:41791 | test | Sleep | 69 | | NULL | +----+------+-----------------+------+---------+------+-------+------------------+ 5 rows in set (0.00 sec) ~~~ 關閉Tomcat，查看數據庫連接： ~~~ mysql> show processlist; +----+------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+------------------+ | 45 | root | localhost | test | Query | 0 | init | show processlist | +----+------+-----------+------+---------+------+-------+------------------+ 1 row in set (0.00 sec) ~~~ ## [](https://github.com/someus/another-tutorial-about-java-web/blob/master/00-07.md#dbutils)DBUtils 使用DBUtils可以更加方便的操作數據庫，可以參考[DBUtils簡明教程](http://www.letiantian.me/2015-03-09-apache-dbutils/)。 ## [](https://github.com/someus/another-tutorial-about-java-web/blob/master/00-07.md#資料)資料 官網 [DBCP,C3P0,Tomcat_JDBC 性能及穩定性測試](http://www.open-open.com/lib/view/open1329182303124.html) [數據連接池DBCP參數介紹](http://my.oschina.net/robinsonlu/blog/77759) [DBCP數據庫連接池的使用](http://my.oschina.net/donghongyu/blog/190494)