为了提高数据库连接的使用效率,可以使用数据库连接池,避免重复的创建和关闭连接。
它的具体配置步骤如下:
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 timeBetweenEvictionRunsMillis
to 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 true
if 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中调整权限了