使用數(shù)據(jù)庫(kù)連接池

使用數(shù)據(jù)庫(kù)連接池

目前比較常見的連接池實(shí)現(xiàn)有DBCP、C3P0,Tomcat_JDBC等。

本文使用的連接池是DBCP。

進(jìn)入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中下載Apache Commons Pool，http://dev.mysql.com/downloads/connector/j/下載MySQL的JDBC驅(qū)動(dòng)。

數(shù)據(jù)庫(kù)準(zhǔn)備

MySQL 5.6。

--創(chuàng)建數(shù)據(jù)庫(kù)
CREATE DATABASE IF NOT EXISTS `test` DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
USE `test`;
--創(chuàng)建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;
---插入若干數(shù)據(jù)
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'));

示例1

目錄結(jié)構(gòu)如下：

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

這些配置的解釋請(qǐng)見BasicDataSource Configuration Parameters。

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);
    }

}

運(yùn)行項(xiàng)目，瀏覽器訪問(wèn)http://localhost:8084/Project_0007_DBCP/hello：

改進(jìn)：將初始化的連接池放到Servlet上下文中

上面代碼中是再servlet中初始化連接池，更好的方法是再Listener中初始化，并將連接池作為屬性放入servlet上下文中。

源文件以及代碼有所變化，項(xiàng)目結(jié)構(gòu)如下：

DBCPListener.java內(nèi)容如下：

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{

    // 應(yīng)用啟動(dòng)時(shí)，該方法被調(diào)用
    @Override
    public void contextInitialized(ServletContextEvent sce) {
        try {
            System.out.println("設(shè)置數(shù)據(jù)庫(kù)連接池");
            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("數(shù)據(jù)庫(kù)連接池設(shè)置出現(xiàn)異常：" + ex.getMessage());
        }
    }

    // 應(yīng)用關(guān)閉時(shí)，該方法被調(diào)用
    @Override
    public void contextDestroyed(ServletContextEvent sce) {

    }

}

HelloServlet.java內(nèi)容如下：

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);
    }

}

啟動(dòng)項(xiàng)目，可以看到Tomcat輸出：

設(shè)置數(shù)據(jù)庫(kù)連接池

瀏覽器輸出：

查看一下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)

關(guān)閉Tomcat，查看數(shù)據(jù)庫(kù)連接：

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)

DBUtils

使用DBUtils可以更加方便的操作數(shù)據(jù)庫(kù)，可以參考DBUtils簡(jiǎn)明教程。