官方网站tomcat连接Mysql连接池配置

MySQL DBCP Example

MySQL DBCP Example

用红字标出各配置名称的不同，以便理解。

官网地址：http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html

0. Introduction

Versions of MySQL and JDBC drivers that have been reported to work:

• MySQL 3.23.47, MySQL 3.23.47 using InnoDB,, MySQL 3.23.58, MySQL 4.0.1alpha
• Connector/J 3.0.11-stable (the official JDBC Driver)
• mm.mysql 2.0.14 (an old 3rd party JDBC Driver)

Before you proceed, don't forget to copy the JDBC Driver's jar into $CATALINA_HOME/lib .

1. MySQL configuration

Ensure that you follow these instructions as variations can cause problems.

Create a new test user, a new database and a single test table. Your MySQL user must have a password assigned. The driver will fail if you try to connect with an empty password.

	mysql> GRANT ALL PRIVILEGES ON *.* TO javauser@localhost -> IDENTIFIED BY 'javadude' WITH GRANT OPTION; mysql> create database javatest ; mysql> use javatest ; mysql> create table testdata ( -> id int not null auto_increment primary key, -> foo varchar(25), -> bar int);

Note: the above user should be removed once testing is complete!

Next insert some test data into the testdata table.

	mysql> insert into testdata values(null, 'hello', 12345); Query OK, 1 row affected (0.00 sec) mysql> select * from testdata; +----+-------+-------+ | ID | FOO | BAR | +----+-------+-------+ | 1 | hello | 12345 | +----+-------+-------+ 1 row in set (0.00 sec) mysql>

2. Context configuration

Configure the JNDI DataSource in Tomcat by adding a declaration for your resource to your Context .

For example:

	<Context path="/DBTest " docBase="DBTest " debug="5" reloadable="true" crossContext="true"> <!-- maxActive: Maximum number of dB 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 dB connections to retain in pool. Set to -1 for no limit. See also the DBCP documentation on this and the minEvictableIdleTimeMillis configuration parameter. --> <!-- maxWait: Maximum time to wait for a dB 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 dB username and password for dB 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 dB. The autoReconnect=true argument to the url makes sure that the mm.mysql JDBC Driver will automatically reconnect if mysqld closed the connection. mysqld by default closes idle connections after 8 hours. --> <Resource name="jdbc/TestDB " auth="Container" type="javax.sql.DataSource" maxActive="100" maxIdle="30" maxWait="10000" username="javauser" password="javadude" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/javatest ?autoReconnect=true"/> </Context>

自已加入说明: 例：Context path="/x" docBase="F:/我的文档/jsp" debug="0" / 怎么理解？

path的值是用于浏览器防问的URL，比如上例中就可以通过/x访问，完全URL：http://localhost:8080/x   docBase值指硬盘上的真实文件地址，本例中是F:/我的文档/jsp，建议不要指向含有中文的目录，可能会出错的。debug的值表示是否调试。

3. web.xml configuration

Now create a WEB-INF/web.xml for this test application.

	<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"> <description>MySQL Test App</description> <resource-ref> <description>DB Connection</description> <res-ref-name>jdbc/TestDB </res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> </resource-ref> </web-app>

4. Test code

Now create a simple test.jsp page for use later.

	<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <sql:query var="rs" dataSource="jdbc/T estDB "> select id, foo, bar from testdata </sql:query> <html> <head> <title>DB Test</title> </head> <body> <h2>Results</h2> <c:forEach var="row" items="${rs.rows}"> Foo ${row.foo}<br/> Bar ${row.bar}<br/> </c:forEach> </body> </html>

That JSP page makes use of JSTL 's SQL and Core taglibs. You can get it from Sun's Java Web Services Developer Pack or Jakarta Taglib Standard 1.1 project - just make sure you get a 1.1.x release. Once you have JSTL, copy jstl.jar and standard.jar to your web app's WEB-INF/lib directory.

Finally deploy your web app into $CATALINA_BASE/webapps either as a warfile called DBTest.war or into a sub-directory called DBTest

Once deployed, point a browser at http://localhost:8080/DBTest /test.jsp to view the fruits of your hard work.

 

===========================

 

从上例中可以看到：

     1.真实的连接数据库，在context.xml中的<Resource url="jdbc:mysql://localhost:3306/javatest ?autoReconnect=true>中只写一次就行了！

     2.在context.xml中的<Resource name="jdbc/TestDB ".....> 要和web.xml中的<res-ref-name>jdbc/TestDB </res-ref-name>相对即可。在使用时，也用 dataSource="jdbc/T estDB " 就行了，而不是使用真实的连接 jdbc/javatest

     3. 在 <Context path="/DBTest " docBase="DBTest " debug="5" reloadable="true" crossContext="true">中，path的值是用于浏览器防问的URL，比如上例中就可以通过/x访问，完全URL：http://localhost:8080/DBTest   docBase值指硬盘上的真实文件地址。

 

     有一点不是很明白，如果docBase不写目录，直接写名字代表什么呢？从网上搜索了一下：

     如：
<Host name="www.domain.com" debug="0" appBase="/home/www/html" uppackWars="true" autoDeploy="true">
<Context path="" docBase="ROOT" debug="0"/ >
</Host>

<Context path="" docBase="ROOT" debug="0"/ >

 

    ROOT 是相对于前面 <Host ... appBase="/home/www/html" ...> 中的 appBase 的，即它应该是

/home/www/html 中的一个目录。当然你可以用完全的目录名，这样就不是相对 appBase 而言的了，比如说：
<Context path="" docBase="/home/www/html/ROOT" debug="0"/>
这个配置与你的配置效果一样。