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.
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.
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:
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>
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>
Oracle requires minimal changes from the MySQL configuration except for the usual gotchas :-)
Drivers for older Oracle versions may be distributed as *.zip files rather than *.jar files. Tomcat will only use *.jar files installed in $CATALINA_HOME/common/lib. Therefore classes111.zip or classes12.zip will need to be renamed with a .jar extension. Since jarfiles are zipfiles, there is no need to unzip and jar these files - a simple rename will suffice.
Some early versions of Tomcat 4.0 when used with JDK 1.4 will not load classes12.zip unless you unzip the file, remove the javax.sql.* class heirarchy and rejar.
For Oracle 9i onwards you should use oracle.jdbc.OracleDriver rather than oracle.jdbc.driver.OracleDriver as Oracle have stated that oracle.jdbc.driver.OracleDriver is deprecated and support for this driver class will be discontinued in the next major release.
server.xml 配置
In a similar manner to the mysql config above, you will need to define your Datasource in your server.xml file. Here we define a Datasource called myoracle using the thin driver to connect as user scott, password tiger to the sid called mysid. (Note: with the thin driver this sid is not the same as the tnsname). The schema used will be the default schema for the user scott.
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.
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.
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文件说明会说:"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 :-)
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;
}
}