tomcat数据库连接池配置

tomcat数据库连接池配置

辛苦一晚上，终于将tomcat/MySQL的数据库连接池搞定，写出来给大家，好让大家不再象我一样辛苦。同时记录一下，以后就不用再去麻烦了
tomcat 5.0.28
mysql 4.0.20

首先，建一个数据库，dstest
建表test
-- 表的结构 `test`
--

CREATE TABLE `test` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(30) NOT NULL default '',
`address` varchar(50) default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=20 ;

--
-- 表中的数据 `test`
--

INSERT INTO `test` (`id`, `name`, `address`) VALUES (1, 'tom', '123');
INSERT INTO `test` (`id`, `name`, `address`) VALUES (2, 'tom', '123');
INSERT INTO `test` (`id`, `name`, `address`) VALUES (3, 'tom', '123');
INSERT INTO `test` (`id`, `name`, `address`) VALUES (4, 'tom', '123');
INSERT INTO `test` (`id`, `name`, `address`) VALUES (5, 'tom', '123');
INSERT INTO `test` (`id`, `name`, `address`) VALUES (6, 'tom', '123');
INSERT INTO `test` (`id`, `name`, `address`) VALUES (7, 'tom', '123');
INSERT INTO `test` (`id`, `name`, `address`) VALUES (8, 'tom', '123');
INSERT INTO `test` (`id`, `name`, `address`) VALUES (9, 'tom', '123');
INSERT INTO `test` (`id`, `name`, `address`) VALUES (10, 'tom', '123');
INSERT INTO `test` (`id`, `name`, `address`) VALUES (11, 'tom', '123');
INSERT INTO `test` (`id`, `name`, `address`) VALUES (12, 'tom', '123');
INSERT INTO `test` (`id`, `name`, `address`) VALUES (13, 'tom', '123');
INSERT INTO `test` (`id`, `name`, `address`) VALUES (14, 'tom', '123');
INSERT INTO `test` (`id`, `name`, `address`) VALUES (15, 'tom', '123');
INSERT INTO `test` (`id`, `name`, `address`) VALUES (16, 'tom', '123');
INSERT INTO `test` (`id`, `name`, `address`) VALUES (17, 'tom', '123');
INSERT INTO `test` (`id`, `name`, `address`) VALUES (18, 'tom', '123');
INSERT INTO `test` (`id`, `name`, `address`) VALUES (19, 'tom', '123');

将MySQL的JDBC驱动mysql-connector-java-bin.jar放到Tomcat\common\lib下面

配置tomcat数据源

编辑tomcat\conf\server.xml
在<host></host>之间加入以下内容
<!-- DataSource pools config begin-->
<Context path="/dstest" docBase="dstest" debug="5" reloadable="true">
<!--dstest为你的应用所在的目录名称，即webapps\dstest,根据你的情况更改-->
<Resource name="jdbc/testpool" auth="Container" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/testpool">
<!--jdbc/testpool为数据源名称，testpool可根据你的喜好更改-->
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>username</name>
<value>root</value>
<!--MySQL用户名-->
</parameter>
<parameter>
<name>password</name>
<value>root</value>
<!--MySQL密码-->
</parameter>
<parameter>
<name>driverClassName</name>
<value>org.gjt.mm.mysql.Driver</value>
<!--jdbc驱动-->
</parameter>
<parameter>
<name>url</name>
<value>jdbc:mysql://localhost:3306/dstest</value>
<!--连接URL，dstest为数据库名-->
</parameter>
<parameter>
<name>maxActive</name>
<value>8</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>4</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>10000</value>
</parameter>
</ResourceParams>
</Context>
<!-- DataSource pools config finished-->
切记要加在<host></host>之间，我辛苦的搞了一个晚上，就是这个没加对地方，郁闷了一个晚上(:
后来在tomcat\webapps\tomcat-docs下面发现了jndi-resources-howto.html，上面说
To configure Tomcat's resource factory, add an elements like this to the $CATALINA_HOME/conf/server.xml file, nested inside the Context element for this web application (or nested inside a DefaultContext element for the surrounding <Host> or <Engine> element.
终于一个一个的试了出来

在tomcat\webapps建一个目录dstest（同server.xml的中<Context path="/dstest" docBase="dstest" debug="5" reloadable="true">对应）
dstest\WEB-INF
dstest\WEB-INF\classes
在dstest\WEB-INF下建web.xml
内容如下：
<?xml version="1.0" encoding="UTF-8"?>

<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
version="2.4">
<session-config>
<session-timeout>
30
</session-timeout>
</session-config>

<resource-ref>
<description>
datasource jdbc/testpool
</description>
<res-ref-name>
jdbc/testpool
<!--对应server.xml中的数据源名称-->
</res-ref-name>
<res-type>
javax.sql.DataSource
</res-type>
<res-auth>
Container
</res-auth>
</resource-ref>
<welcome-file-list>
<welcome-file>
index.jsp
</welcome-file>
<welcome-file>
index.html
</welcome-file>
<welcome-file>
index.htm
</welcome-file>
</welcome-file-list>
</web-app>

测试
dstest.jsp

<%@ page language="java"%>
<%@ page import="java.util.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="javax.naming.*" %>

<html>
<head>
<title>Test Database </title>
</head>
<body >
<center>Test Database Source Pools</center>
<table border="0" align="center">
<%
String jndi_name="java:comp/env/jdbc/testpool";
String select_user_sql="select * from test";

Context ctx = new InitialContext();
if(ctx == null )
throw new Exception("No Context");

DataSource ds = (DataSource)ctx.lookup(jndi_name);

Connection conn = ds.getConnection();

try {
PreparedStatement psPreparedStatement=conn.prepareStatement(select_user_sql);
ResultSet resultSet = psPreparedStatement.executeQuery();
while(resultSet.next()){
out.print("<tr>");
out.print("<td>"+resultSet.getInt(1)+"</td>");
out.print("<td>"+resultSet.getString(2)+"</td>");
out.print("<td>"+resultSet.getString(3)+"</td>");
//out.print("<td>KK</td>");
out.print("</tr>");
}
}
catch(SQLException e){
e.printStackTrace();
}
finally {
conn.close();
}
%>
</table>
</body>
</html>

OK，all done！
good luck！