Apache Tomcat 5.5 Servlet/JSP 容器JNDI Datasource HOW-TO |  |
| Introduction |
JNDI数据源配置在JNDI-Resources-HOWTO里面有详尽的说明，tomcat用户反馈回来的信息显示个别的特殊配置可能会很易出错。
这里为tomcat用户展示了一些通用数据库配置的例子，以及数据库使用的一些通常的提示。
因为这些注解是从tomcat用户反馈的配置信息派生而来，所以如果你有其他测试过的配置并且觉得能对更多其他用户会有用处，或者你觉得我们可以对这一部分有所改进，请让我们知道。
Please note that JNDI resource configuration has changed somewhat between
Tomcat 5.0.x and Tomcat 5.5.x. You will most likely need to modify your JNDI
resource configurations to match the syntax in the example below in order
to make them work in Tomcat 5.5.x.
Also, please note that JNDI DataSource configuration in general, and this
tutorial in particular, assumes that you have read and understood the
Context and
Host configuration references, including
the section about Automatic Application Deployment in the latter reference.
|
| Database Connection Pool (DBCP) Configurations |
DBCP对JDBC 2.0提供支持。 使用1.4 JVM DBCP的系统会支持JDBC 3.0。如果你已经使用过1.4 JVM DBCP以及JDBC 3.0特性，请让我们知道。
See the
DBCP documentation for a complete list of configuration parameters.
| Installation |
DBCP uses the Jakarta-Commons Database Connection Pool. It relies on
number of Jakarta-Commons components:
- Jakarta-Commons DBCP 1.0
- Jakarta-Commons Collections
- Jakarta-Commons Pool
These libraries are located in a single JAR at
$CATALINA_HOME/common/lib/naming-factory-dbcp.jar. However,
only the classes needed for connection pooling have been included, and the
packages have been renamed to avoid interfering with applications.
|
| Preventing dB connection pool leaks |
一个数据库连接池产生并管理一系列与数据库的连接。再回收和再利用现存的数据库连接比打开一个新的连接要有效率的多。
连接池也有一个问题。那就是网络程序必须专门关闭ResultSet's, Statement's, 和 Connection's 。 网络程序若没有关闭这些资源，会造成这些资源再也不能被重新使用，产生数据库连接池“泄露”的后果。如果没有别的连接可被使用，最终会造成你的网络程序数据库连接失败。
对于这个问题有一个解决办法。Jakarta-Commons DBCP可以被配置来追踪和修复这些被遗弃的数据库连接。它不仅可以修复它们，而且可以对那些打开这些资源但又从来没有关闭它们的代码展开一层层追踪。
To configure a DBCP DataSource so that abandoned dB connections are
removed and recycled add the following attribute to the
Resource configuration for your DBCP DataSource:
当可被利用的数据库连接降到很低，DBCP会修复并回收它能发现的被放弃的数据库连接。默认值是false 。
Use the removeAbandonedTimeout attribute to set the number
of seconds a dB connection has been idle before it is considered abandoned.
| | | |
removeAbandonedTimeout="60"
| | | | |
The logAbandoned attribute can be set to true
if you want DBCP to log a stack trace of the code which abandoned the
dB connection resources.
默认值是false 。
|
| MySQL DBCP Example |
简介
下面这些的MySQL和JDBC驱动的版本可以运作：
- 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)
在你开始之前，别忘记复制一份JDBC驱动的jar文件到$CATALINA_HOME/common/lib里面。
MySQL 配置
你一定要遵循这些指导说明，因为变动会产生问题。
产生一个新的测试用户，一个新的数据库和一个测试表格。你的MySQL用户必须要有个指派的密码。如果你试图用空密码连接，驱动会失败。 | | | | 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); | | | | |
注意：当测试完成以后，上面的用户应该被删除掉。
下一步，在testdata表格中加入一些测试数据。 | | | | 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> | | | | |
server.xml 配置
通过向$CATALINA_HOME/conf/server.xml里面加入一个声明来在Tomcat里配置JNDI DataSource。
Add this in between the </Context> tag of the examples
context and the </Host> tag closing the localhost definition.
If there is no such tag, you can add one as illustrated in the
Context and
Host configuration references, and repeated below
for your convenience.
| | | |
<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 0 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>
| | | | |
web.xml 配置
现在为这个测试程序产生一个WEB-INF/web.xml 文件。 | | | |
<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>
| | | | |
测试代码
现在产生一个简单的test.jsp页面供以后使用。 | | | | <%@ 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/TestDB">
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> | | | | |
JSP页面利用JSTL 's SQL 和 Core taglibs 。你可以从Sun's Java Web Services Developer Pack 得到它，或者从Jakarta Taglib Standard 1.1 project 得到它——确保你拿到的是1.1.x发行版。当你有了JSTL以后，把jstl.jar和standard.jar复制到你的网络程序的WEB-INF/lib目录里。
最后把你的网络程序作为DBTest.war$CATALINA_HOME/webapps 里面。
当部署完成后，在一个浏览器里指向http://localhost:8080/DBTest/test.jsp就可以看到你辛勤劳动的成果了。
|
| PostgreSQL |
简介
PostgreSQL is configured in a similar manner to Oracle.
1. Required files
Copy the Postgres JDBC jar to $CATALINA_HOME/common/lib. As with Oracle, the
jars need to be in this directory in order for DBCP's Classloader to find
them. This has to be done regardless of which configuration step you take next.
2. Resource configuration
You have two choices here: define a datasource that is shared across all Tomcat
applications, or define a datasource specifically for one application.
2a. Shared resource configuration
Use this option if you wish to define a datasource that is shared across
multiple Tomcat applications, or if you just prefer defining your datasource
in this file.
This author has not had success here, although others have reported so.
Clarification would be appreciated here.
| | | |
<Resource name="jdbc/postgres" auth="Container"
type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
url="jdbc:postgresql://127.0.0.1:5432/mydb"
username="myuser" password="mypasswd" maxActive="20" maxIdle="10" maxWait="-1"/>
| | | | |
2b. Application-specific resource configuration
Use this option if you wish to define a datasource specific to your application,
not visible to other Tomcat applications. This method is less invasive to your
Tomcat installation.
Create a resource definition file for your application defining the
datasource. This file must have the same name as your application, so if
your application deploys as someApp.war, this filename must
be someApp.xml. This file should look something like the following.
| | | |
<Context path="/someApp" docBase="someApp"
crossContext="true" reloadable="true" debug="1">
<Resource name="jdbc/postgres" auth="Container"
type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
url="jdbc:postgresql://127.0.0.1:5432/mydb"
username="myuser" password="mypasswd" maxActive="20" maxIdle="10"
maxWait="-1"/>
</Context>
| | | | |
web.xml 配置
| | | | <resource-ref>
<description>postgreSQL Datasource example</description>
<res-ref-name>jdbc/postgres</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref> | | | | |
4. Accessing the datasource
When accessing the datasource programmatically, remember to prepend
java:/comp/env to your JNDI lookup, as in the following snippet of
code. Note also that "jdbc/postgres" can be replaced with any value you prefer, provided
you change it in the above resource definition file as well.
| | | |
InitialContext cxt = new InitialContext();
if ( cxt == null ) {
throw new Exception("Uh oh -- no context!");
}
DataSource ds = (DataSource) cxt.lookup( "java:/comp/env/jdbc/postgres" );
if ( ds == null ) {
throw new Exception("Data source not found!");
}
| | | | |
|
|
| Oracle 8i with OCI client |
| Introduction |
虽然没有严格强调使用OCI client来产生JNDI DataSource，这里的这些注解可以和上面所讲的Oracle and DBCP方法合在一起。
为了使用OCI驱动，你必须要安装一个Oracle client。你应该从cd安装Oracle8i(8.1.7) client ，然后再从otn.oracle.com下载相适宜的JDBC/OCI
driver(Oracle8i 8.1.7.1 JDBC/OCI Driver)。
在把classes12.zip文件重新命名为classes12.jar以后，把它复制到Tomcat的$CATALINA_HOME/common/lib里面。根据你使用的Tomcat和JDK 版本，你还可能必须从这个文件删除javax.sql.* 类。
|
| Putting it all together |
确保在你的$PATH or LD_LIBRARY_PATH
(possibly in $ORAHOME\bin ) 有ocijdbc8.dll or .so ，并且确定使用一个简单的测试程序System.loadLibrary("ocijdbc8")可以装载native library。
你下一步应该产生一个简单的测试servlet或jsp，要有下面这些关键行：
| | | | DriverManager.registerDriver(new
oracle.jdbc.driver.OracleDriver());
conn =
DriverManager.getConnection("jdbc:oracle:oci8:@database","username","password"); | | | | |
这里数据库是host:port:SID的形式。现在如果你想试图访问你测试的servlet/jsp 的URL，你得到的是一个ServletException，造成它的根本原因在于java.lang.UnsatisfiedLinkError:get_env_handle 。
首先，这个UnsatisfiedLinkError表明你有下列问题：
- JDBC类文件和Oracle client版本不相配。发给你的消息会说明是因为一个必须的library不能被找到。例如，你可能把Oracle Version 8.1.6里边的一个classes12.zip文件与与Version 8.1.5 Oracle client 一起使用。classeXXXs.zip文件与Oracle client软件的版本必须相配。
- 一个$PATH ,
LD_LIBRARY_PATH 问题。
- 据报道，忽略你从otn下载的驱动，使用$ORAHOME\jdbc\lib目录里的classes12.zip文件也行得通。
下一步，你可能会遇到错误ORA-06401 NETCMN: invalid driver designator
Oracle文件说明会说："Cause: The login (connect) string contains an invalid driver designator. Action: Correct the string and re-submit." 把数据库的连接字串( host:port:SID 的形式)改变成这个样：(description=(address=(host=myhost)(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))
Ed. Hmm, I don't think this is really needed if you sort out your TNSNames - but I'm not an Oracle DBA :-)
|
|
| Common Problems |
这里是一些网络程序中使用数据库常常遇到的问题，以及怎样解决它们的提示。
| Intermittent dB Connection Failures |
Tomcat在一个JVM里运行。JVM定期地执行垃圾收集工作(GC)来删除不再被使用的对象。当JVM执行GC时，Tomcat暂停。如果配置的连接数据库的最长时间少于垃圾收集所花费的时间，你就可能得到一个数据库连接失败。
要收集关于垃圾收集所花费的时间，在启动Tomcat时，把-verbose:gc加入到你的CATALINA_OPTS环境变量中。当垃圾收集(gc)工作时，你的$CATALINA_BASE/logs/catalina.out日志文件会记录所有的关于垃圾收集的数据，包括垃圾收集需要多长时间。
当你的JVM调试正确率为99%时，垃圾收集工作花费的时间比一秒钟还少。收尾工作仅仅花几秒钟。垃圾收集很少用10秒钟以上的时间。
确保数据库连接时间设置为10-15秒。对于DBCP来说，用maxWait参数来设置。
|
| Random Connection Closed Exceptions |
当一个请求从连接池得到一个数据库连接，然后关闭它两次，就会产生这些异常。当使用一个连接池时，关闭连接仅仅是把它返回到池中再被另外的请求使用，而不时把这个连接断掉。Tomcat使用多线程来处理同时发生的请求。这里的例子是关于事件(events)顺序怎样在Tomcat里造成这类错误： Request 1 running in Thread 1 gets a db connection.
Request 1 closes the db connection.
The JVM switches the running thread to Thread 2
Request 2 running in Thread 2 gets a db connection
(the same db connection just closed by Request 1).
The JVM switches the running thread back to Thread 1
Request 1 closes the db connection a second time in a finally block.
The JVM switches the running thread back to Thread 2
Request 2 Thread 2 tries to use the db connection but fails
because Request 1 closed it. 这里的例子是一个写得不错的关于使用从连接池获得的数据库连接的代码。Connection conn = null;
Statement stmt = null; // Or PreparedStatement if needed
ResultSet rs = null;
try {
conn = ... get connection from connection pool ...
stmt = conn.createStatement("select ...");
rs = stmt.executeQuery();
... iterate through the result set ...
rs.close();
rs = null;
stmt.close();
stmt = null;
conn.close(); // Return to connection pool
conn = null; // Make sure we don't close it twice
} catch (SQLException e) {
... deal with errors ...
} finally {
// Always make sure result sets and statements are closed,
// and the connection is returned to the pool
if (rs != null) {
try { rs.close(); } catch (SQLException e) { ; }
rs = null;
}
if (stmt != null) {
try { stmt.close(); } catch (SQLException e) { ; }
stmt = null;
}
if (conn != null) {
try { conn.close(); } catch (SQLException e) { ; }
conn = null;
}
}
|
|
|
