Tomcat4 + Oracle database connection pool Configuration

Jin this article mainly introduces the configuration of the database connection pool in tomcat4.0.6 and tomcat4.1.18, and the process for searching the JNDI of the connection pool, and provides the corresponding test code. At last, it points out the common problems and solutions in the configuration and application process.

1. Tomcat Introduction
Tomcat is a sub-project of Apache Jakarta and a JSP and Servlet Container recommended by Sun. As an excellent application server, Tomcat provides database connection pool, SSL, proxy, and many other common component functions. Among them, the connection pool is a new feature in version 4.0 and is widely used.

Ii. Tomcat 4 connection pool
Tomcat4 development can be divided into two stages. 4.0.6 is the most recommended release version in the first stage. The built-in database connection pool is tyrex 0.9.7.0 and tyrex is developed by exolab.org. For more information, see www. exolab.org. Later, Tomcat developers reconstructed Tomcat on the basis of 4.0.x, And the reconstructed release version recommended 4.1.18. At this time, the built-in connection pool was changed to DBCP, which is also a subproject of Jakarta commons.
Next, we will take 4.0.6 and 4.1.18 as examples to introduce the two connection pool configurations for oracle8.1.7.

3. Configure tyrex for tomcat4.0.6

For convenience, set the connection pool to root, the JNDI name to JDBC/oracledb, the database server IP address to 192.168.0.50, And the SID to oradb. The configuration steps are as follows.

Step 1: configure Server. xml

In the server. xml file, find
<! -- Tomcat root context --> <! -- <Context Path = "" docbase = "root" DEBUG = "0"/> -->
Change it
<! -- Tomcat root context --> <context Path = "" docbase = "root" DEBUG = "0"> <Resource Name = "JDBC/oracledb" auth = "Container" type =" javax. SQL. datasource "/> <resourceparams name =" JDBC/oracledb "> <parameter> <Name> User </Name> <value> holen </value> </parameter> <parameter> <Name> password </Name> <value> holen </value> </parameter> <Name> driverclassname </Name> <value> oracle. JDBC. driver. oracledriver </value> </parameter> <Name> drivername </Name> <value> JDBC: oracle: thin: @ 192.168.0.50: 1521: oradb </value> </parameter> </resourceparams> </context>

Note: remove the root context from the comment and define the resource item as follows:

<Resource Name = "JDBC/oracledb" auth = "Container" type = "javax. SQL. datasource"/>

Resource (the datasource object of the Connection Pool) has three attributes: name, auth, and type. The name is the name definition of JNDI, and this object can be found by the program through JNDI, the name is JDBC/oracledb. The auth item is the management attribute of the connection pool. The Container value is the container management item. The type item is the object type. The javax value is used here. SQL. datasource, declared as a database connection pool. tyrex can be used not only as a database connection pool, but also has many other functions. If you are interested, you can open the jar package of tyrex to see or access www. exolab.org.
The following <resourceparams> domain contains four parameters: User, password, driverclassname, and drivername, which are the database username, password, JDBC driver, and database address.
The user name and password are prepared for accessing the database. The value here is holen.
Driverclassname is the JDBC driver name of the database. For example, the JDBC driver package of oracle8.1.7 is named classes. jar, which is generally located in the ora81/jdbc/libdirectory under the Oracle installation directory. The extension name is zip, and the classes.zip name is manually changed to classes. jar and put it under common/lib. Here, the value is Oracle. JDBC. Driver. oracledriver, which is provided by classes. jar.

<Parameter> <Name> driverclassname </Name> <value> oracle. JDBC. Driver. oracledriver </value> </parameter>

For other databases, such as MySQL, The driverclassname is org. gjt. Mm. MySQL. Driver.

The last parameter is drivername, which is the address of the database. (To be precise, it should be called URL, and it should be changed to URL in 4.1.18)
<Parameter> <Name> drivername </Name> <value> JDBC: oracle: thin :@192.168.0.50: 1521: oradb </value> </parameter>

Enter the Oracle access address here. If it is MySQL, DB2, SQL Server, or other databases, enter the corresponding address.

Step 2: copy the oracle JDBC driver classes12.jar to common/lib in the tomcat installation directory. The same applies to other databases. Place the corresponding JDBC driver package in common/lib, for example, MySQL JDBC driver package mm. mysql-2.0.14.jar.

Now, the configuration is complete and the test code will be provided later.

4. Configure tomcat4.1.18 DBCP

The configuration method is slightly different from 4.0.6. The configuration procedure is as follows:

Step 1: configure Server. xml
In the server. xml file, find
<! -- Tomcat root context --> <! -- <Context Path = "" docbase = "root" DEBUG = "0"/> -->
Change it
<! -- Tomcat root context --> <context Path = "" docbase = "root" DEBUG = "0"> <Resource Name = "JDBC/oracledb" auth = "Container" type =" javax. SQL. datasource "/> <resourceparams name =" JDBC/oracledb "> <parameter> <Name> factory </Name> <value> Org. apache. commons. DBCP. basicdatasourcefactory </value> </parameter> <Name> driverclassname </Name> <value> oracle. JDBC. driver. oracledriver </value> </parameter> <Name> URL </Name> <value> JDBC: oracle: thin: @ 192.168.0.50: 1521: oradb </value> </parameter> <Name> username </Name> <value> holen </value> </parameter> <Name> password </Name> <value> holen </value> </parameter> <Name> maxactive </Name> <value> 20 </value> </parameter> <Parameter> <Name> maxidle </Name> <value> 10 </value> </parameter> <Name> maxwait </Name> <value>-1 </value> </parameter> </resourceparams> </context>
Note: The configuration file shows that the configuration of DBCP is similar to that of tyrex, but the function is richer. The same is not much said, but it focuses on different places.

Factory parameters:

<Parameter> <Name> factory </Name> <value> org. Apache. commons. DBCP. basicdatasourcefactory </value> </parameter>

That is, the basic object factory. Here, the value is org. Apache. commons. DBCP. basicperformancefactory, that is, the factory that comes with DBCP. You can also use something else.
Although DBCP is used as the connection pool in 4.1.18, tyrex can still be used as the connection pool. At this time, tyrex has been upgraded from 0.9.7.0 to 1.0, and JTA/JCA objects are supported, the object is still obtained through JNDI. For specific configuration methods, see the Tomcat documentation.

URL parameters:
<Parameter> <Name> URL </Name> <value> JDBC: oracle: thin :@192.168.0.50: 1521: oradb </value>

The URL is the database access address, as mentioned before.

The following three parameters are related to the number of connections:
<Parameter> <Name> maxactive </Name> <value> 20 </value> </parameter> <Name> maxidle </Name> <value> 10 </ value> </parameter> <Name> maxwait </Name> <value>-1 </value> </parameter>

Maxactive is the maximum number of activated connections. The value is 20, indicating a maximum of 20 connections to the database.
Maxidle is the maximum number of idle connections. The value is 10, indicating that 10 idle connections can be maintained even if no connection request is sent, and the connections are not cleared. The connections are always on standby. If you are interested in the object status, you can check the EJB information.
Maxwait is the maximum number of waiting seconds. Here, the value-1 indicates infinite waiting until the timeout period ends. It can also be set to 9000, indicating timeout after 9 seconds.
For some suggestions on maxactive and maxidle, for enterprise-level applications, the values of the two are generally close or the same. The key is to analyze the application size.

Step 2: Configure web. xml
Open Web. xml under webapps/root/WEB-INF and add the following content:

<Resource-ref> <description> Oracle datasource example </description> <res-ref-Name> JDBC/oracledb </RES-ref-Name> <res-type> javax. SQL. datasource </RES-type> <res-auth> container </RES-auth> </resource-ref>

Note: This step can be omitted, that is, you can use the connection pool without configuring web. xml. However, we recommend that you add it to the official project application.

Step 3: copy the JDBC driver classes12.jar of Oracle to common/lib in the tomcat installation directory.

Now, the configuration is complete and the test code will be provided later.

V. Test code

Write a JSP file testdb. jsp and place testdb. jsp in the webapps/root directory to test whether the configuration is correct. This test is suitable for both versions.

The database is as follows:

Create Table Test (ID varchar2 (12), name varchar2 (30 ))

The content of testdb. jsp is as follows:

<% @ Page contenttype = "text/html; charset = GBK" %> <% @ page import = "Java. SQL. * "%> <% @ page import =" javax. naming. * "%> <% Try {context initctx = new initialcontext (); Context CTX = (context) initctx. lookup ("Java: COMP/ENV"); // obtain the connection pool object OBJ = (object) CTX. lookup ("JDBC/oracledb"); // type conversion javax. SQL. datasource DS = (javax. SQL. datasource) OBJ; connection conn = Ds. getconnection (); statement stmt = Conn. createstatement (); string strsql = "insert into test (ID, name) values ('000000', ''hollen'')"; stmt.exe cuteupdate (strsql ); strsql = "select ID, name from test"; resultset rs = stmt.exe cutequery (strsql); If (RS. next () {out. println (RS. getstring (1); out. println (RS. getstring (2) ;}} catch (exception ex) {ex. printstacktrace (); throw new sqlexception ("cannot get connection pool. ") ;}%> <HR>

Note: Find the JDBC/oracledb object through JNDI, which can be completed in two steps, for example, object OBJ = (object) CTX. lookup ("Java: COMP/ENV/jdbc/oracledb ");
Then, convert the obtained object to the datasource type to obtain the connection. After the connection is obtained, the corresponding database operations can be performed.
Here we have performed two operations on the database. The first step is to insert a record, the second step is to retrieve the record from the database and display the content of the first record.

Open the webpage and enter http: // localhost: 8080/testdb. jsp in the address bar. If everything is normal, "00001 holen" is displayed ".

Vi. Summary
The built-in connection pools of tomcat4.0.6 and tomcat4.1.18 are configured above. The configuration methods for other versions of tomcat4 are similar, and the call methods are the same. The connection pool configuration mainly involves modifying server. XML and Web. xml.
It should be noted that tomcat4.1.18 has a management interface through which you can configure the connection pool and other functions, but the connection pool configured in this way is unavailable (during the test, the program runs slowly, the reason is that JNDI can be found, but connection cannot be established). This is a bug in the management tool and has been reported to the Tomcat bug list. For more details, see the tomcat-user email list.

VII. References
L Tomcat Documentation
Http://www.mail-archive.com/tomcat-dev@jakarta.apache.org/
Http://www.mail-archive.com/tomcat-user@jakarta.apache.org/