利用Tomcat1.8实现Mysql数据库连接池

为了提高数据库连接的使用效率，可以使用数据库连接池，避免重复的创建和关闭连接。

它的具体配置步骤如下：

1.将数据库驱动文件放到tomcat目录的lib下，我使用的是mysql-connector-java-5.1.34-bin.jar。

2.配置tomcat_home/conf下的context.xml文件。在文件中添加如下内容

<Resource auth="Container" driverClassName="com.mysql.jdbc.Driver" maxIdle="30" maxTotal="100" maxWaitMillis="10000" name="jdbc/CloudStorage" password="数据库密码" type="javax.sql.DataSource" url="jdbc:mysql://192.168.2.160:3306/数据库名称" username="数据库用户名"/>

各个属性的解释如下，摘自apache网站的文档。

<!-- maxTotal: Maximum number of database connections in pool. Make sure you
         configure your mysqld max_connections large enough to handle
         all of your db connections. Set to -1 for no limit.
         -->

    <!-- maxIdle: Maximum number of idle database connections to retain in pool.
         Set to -1 for no limit.  See also the DBCP documentation on this
         and the minEvictableIdleTimeMillis configuration parameter.
         -->

    <!-- maxWaitMillis: Maximum time to wait for a database connection to become available
         in ms, in this example 10 seconds. An Exception is thrown if
         this timeout is exceeded.  Set to -1 to wait indefinitely.
         -->

    <!-- username and password: MySQL username and password for database connections  -->

    <!-- driverClassName: Class name for the old mm.mysql JDBC driver is
         org.gjt.mm.mysql.Driver - we recommend using Connector/J though.
         Class name for the official MySQL Connector/J driver is com.mysql.jdbc.Driver.
         -->

    <!-- url: The JDBC connection url for connecting to your MySQL database.
         -->

防止数据库连接池泄漏，可以配置如下的参数。

Preventing database connection pool leaks

A database connection pool creates and manages a pool of connectionsto a database. Recycling and reusing already existing connectionsto a database is more efficient than opening a new connection.

There is one problem with connection pooling. A web application hasto explicitly close ResultSet's, Statement's, and Connection's.Failure of a web application to close these resources can result inthem never being available again for reuse, a database connection pool "leak".This can eventually result in your web application database connections failingif there are no more available connections.

There is a solution to this problem. The Apache Commons DBCP can beconfigured to track and recover these abandoned database connections. Notonly can it recover them, but also generate a stack trace for the codewhich opened these resources and never closed them.

To configure a DBCP DataSource so that abandoned database connections areremoved and recycled, add one or both of the following attributes to theResource configuration for your DBCP DataSource:

removeAbandonedOnBorrow=true

removeAbandonedOnMaintenance=true

The default for both of these attributes is false. Note thatremoveAbandonedOnMaintenance has no effect unless poolmaintenance is enabled by setting timeBetweenEvictionRunsMillisto a positive value. See theDBCP documentation for full documentation on these attributes.

Use the removeAbandonedTimeout attribute to set the numberof seconds a database connection has been idle before it is considered abandoned.

removeAbandonedTimeout="60"

The default timeout for removing abandoned connections is 300 seconds.

The logAbandoned attribute can be set to trueif you want DBCP to log a stack trace of the code which abandoned thedatabase connection resources.

logAbandoned="true"

The default is false

3.修改项目的web.xml中，添加如下的内容

<resource-ref>
    <description>DataBase Connection Pool</description>
    <res-ref-name>jdbc/CloudStorage</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref>

4.可以使用如下的代码来测试一下数据库连接池是否成功

package com.cyber_space.MysqlManager;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import java.sql.Statement;;

public class DataResource {
	
	public static void createConnection() throws NamingException, SQLException{
		Context context = new InitialContext();		
		DataSource dataSource = (DataSource) context.lookup("java:comp/env/jdbc/CloudStorage");
		Connection connection = dataSource.getConnection();
		Statement statement = connection.createStatement();
		String sqlString = "select * from user;";
		ResultSet resultSet = statement.executeQuery(sqlString);
		
		while(resultSet.next()){
			System.out.println(resultSet.getString("ID") + " ");
		}
		resultSet.close();
		statement.close();
		connection.close();
	}
}

5.可能遇到的问题

错误1:javax.naming.NameNotFoundException: Name [mysql] is not bound in this Contex

这个错误的可能原因是：context.xml中的name属性必须和web.xml中的res-ref-name结点值不一致造成的，因此需要注意查找错误。

改正错误后重启tomcat或者clean一下项目应该就好了。

错误2:数据库权限错误，例如Access Deny，这就需要在Mysql中调整权限了