JDBC Data source

Overview

The content of the Jndi data source configuration is described in detail in the JNDI resource documentation. But from the feedback from Tomcat users, the details of some configurations are tricky.

For commonly used databases, we have provided some examples of configuration for Tomcat users, as well as some common techniques for database usage. These examples and techniques are shown in this chapter.

In addition, although some considerations come from the configuration and feedback provided by the user, you may also have different practices. If you have been tested and you find that certain configurations may have a wide range of benefits, or if you think they will make the content of this chapter more complete, please do not hesitate to advise.

Note that the comparison between Tomcat 7.x and Tomcat 8.x,jndi resource configuration is somewhat different because of the different versions of the Apache Commons DBCP libraries used. So, for use in Tomcat 8, you'd better modify the old version of the JNDI resource configuration so that it matches the format in the example below. Refer to the Tomcat migration documentation for details.

Also, it is important to note that, in general (especially for this tutorial), the JNDI data source configuration assumes that you have understood the context and Host configuration preferences, including those related to automatic deployment of applications in the latter configuration preferences.

DriverManager, service provider mechanisms, and memory leaks

java.sql.DriverManagerSupports the service provider mechanism. The practical effect of this feature is that for all available JDBC drivers, as long as they declare the provided META-INF/services/java.sql.Driver files, they are automatically discovered, loaded, and registered, thereby mitigating the burden of explicitly loading the database driver before creating the JDBC connection. But in all Java versions of the servlet container environment, this functionality is simply not possible. The problem is that java.sql.DriverManager only one drive is scanned.

Tomcat's own blocking JRE memory leak listener can solve this problem to some extent, triggering a driver scan when Tomcat boots. This listener is enabled by default. Only libraries that are visible to the listener, such as $CATALINA_BASE/lib libraries in, can be scanned by the database driver. If you want to disable this feature, be sure to remember that the first web app that uses JDBC will trigger the scan, causing an error when the app reloads, and an error for other web apps that depend on the feature.

So, if there is a database driver in the directory of the application, WEB-INF/lib then these applications cannot rely on the service provider mechanism, but should explicitly register the driver.

java.sql.DriverManagerThe driver in the drive has been considered a source of memory leaks. When the Web app stops running, any drivers it registers must be re-registered. When the web app stops running, Tomcat attempts to automatically find and re-register any JDBC drivers that are loaded by the Web App class loader. But it's best to do this by the app ServletContextListener .

Database connection pool (DBCP 2) configuration

Apache Tomcat's default database connection pool implementation is based on a library of Apache Commons projects, specifically these two libraries:

• Commons DBCP
• Commons Pool

Both libraries are located in a JAR file: $CATALINA_HOME/lib/tomcat-dbcp.jar . However, the file only includes the classes required for the connection pool, and the package name has changed to avoid conflicts with the application.

DBCP 2.0 supports JDBC 4.1.

Installation

Refer to the DBCP documentation for complete configuration parameters.

Prevent database connection pool leaks

The database connection pool creates and manages a number of connections to the database. It is more efficient to reclaim or reuse an existing database connection than to open a new connection.

There is also a problem with connection pooling. Web apps must explicitly turn off ResultSet, Statement, and Connection. If the Web app cannot shut down these resources, it can cause these resources to no longer be reused, causing the database connection pool to "leak". If no more connections are available, this will eventually cause the WEB application database connection to fail.

For this issue, there is a workaround: Log and restore these obsolete database connections by configuring Apache Commons DBCP. Not only can it restore these connections, but it can also generate stack traces for code that opens these connections and never shuts them down.

In order to configure the DBCP data source to remove and reclaim obsolete database connections, add the following attributes (one or all) to the configuration in your DBCP data source Resource :

removeAbandonedOnBorrow=true

removeAbandonedOnMaintenance=true

The above properties are by default false . Note that timeBetweenEvictionRunsMillis you can only take effect if a positive value is enabled for pool maintenance removeAbandonedOnMaintenance . For more information about these properties, you can view the DBCP documentation.

Use the removeAbandonedTimeout property to set the number of seconds that a database connection is idle, which is considered an obsolete connection.

removeAbandonedTimeout="60"

The default timeout for removing obsolete connections is 300 seconds.

will be logAbandoned set to true allow DBCP to record stack trace information for code that discards database connection resources.

logAbandoned="true"

The default is false .

MySQL DBCP Example 0. Brief introduction

The version numbers of MySQL and JDBC drivers that have been reported to work correctly are:

• MySQL 3.23.47, MySQL 3.23.47 with InnoDB, MySQL 3.23.58, and MySQL 4.0.1 Alpha
• Connector/j 3.0.11-stable (JDBC official driver)
• Mm.mysql 2.0.14 (an older JDBC third-party driver)

Never forget to copy the JDBC-driven JAR file to the next step $CATALINA_HOME/lib .

1. MySQL Configuration

Be sure to follow the instructions below, or there will be a problem.

Create a new test user, a new database, and a new test table. You must specify a password for the MySQL user. If the password is empty, it will not drive properly when connected.

mysql> GRANT ALL PRIVILEGES ON *.* TO [email protected]    ->   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: Once the test is finished, you should remove the user from the previous example!

testdataSome test data is inserted in the table below:

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

Add a resource declaration in the Context to configure the JNDI data source in Tomcat.

Examples are as follows:

<Context> <!--maxtotal:maximum number of database connections in pool. Make sure-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 database connections to retain in pool.  Set to-1 for No limit.         See also the DBCP documentation in this and the Minevictableidletimemillis configuration parameter.  -<!--maxwaitmillis:maximum time to wait for a database connection to become available in MS Example seconds.  An Exception are thrown if this timeout is exceeded.         Set to-1 to wait indefinitely. -<!--username and Password:mysql username and password for database connections-<!--DRIVERCLA Ssname:class name for the old Mm.mysql JDBC driver is org.gjt.mm.mysql.driver-we recommend using connector/j th         Ough. Class name for the Official MySQL connector/j driver is com.mysql.jdbc.Driver.         -<!--url:the JDBC connection URL for connecting to your MySQL database. --<resource name= "Jdbc/testdb" auth= "Container" type= "Javax.sql.DataSource" maxtotal= "" maxidle= "               "Maxwaitmillis=" "10000" username= "Javauser" password= "Javadude" driverclassname= "Com.mysql.jdbc.Driver" Url= "Jdbc:mysql://localhost:3306/javatest"/></context>

3. Web. XML configuration

Create a file for the test app 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/j2eehttp://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

Create a simple test.jsp page that will be used 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/TestDB">select id, foo, bar from testdata</sql:query>

The JSP page uses JSTL's SQL and Core taglibs. You can get it from the Apache Tomcat taglibs-standard Tag Library project, but be aware that it should be a 1.1.x or later version. After the JSTL is downloaded, it is jstl.jar standard.jar copied to the Web app's WEB-INF/lib directory.

Finally, deploy your app in $CATALINA_BASE/webapps two ways: either deploy the application in the DBTest.war form of a WAR file named, or put the app DBTest in a subdirectory called.

Once the deployment is complete, you can enter it in the browser http://localhost:8080/DBTest/test.jsp to see your first labor results.

Oracle 8i, 9i, and 10g0. Brief introduction

Oracle requires almost the same configuration as MySQL, but there are some common problems.

Drivers for previous versions of Oracle may be distributed in . zip format instead of. jar format. Tomcat uses only *.jar files, and they must also be installed in $CATALINA_HOME/lib . Therefore, classes111.zip or classes12.zip such a file suffix should be changed .jar . Because the jar file is inherently a zip file, you do not need to unzip the original zip file and create the appropriate jar file, just change the suffix name.

For versions after Oracle 9i, you should use oracle.jdbc.OracleDriver instead oracle.jdbc.driver.OracleDriver , because Oracle rules start to be deprecated oracle.jdbc.driver.OracleDriver , and the next major version will no longer support this driver class.

1. Context Configuration

As in front of MYSQL configuration, you also need to define the data source in the context. The following defines a data source called MyOracle, which is connected using the short driver described above (the user named Scott, the password is Tiger) to the SID named Mysid (the Oracle system ID, which identifies the unique identifier of a database). The schema that user Scott uses is the default schema.

With the OCI driver, you simply change the thin to OCI in the URL string.

<Resource name="jdbc/myoracle" auth="Container"              type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"              url="jdbc:oracle:thin:@127.0.0.1:1521:mysid"              username="scott" password="tiger" maxTotal="20" maxIdle="10"              maxWaitMillis="-1"/>

2. Web. XML configuration

When you create Web. xml files, be sure to follow the sequence of elements required by the DTD in the Web App deployment descriptor file.

<resource-ref> <description>Oracle Datasource example</description> <res-ref-name>jdbc/myoracle</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth></resource-ref>

3. Code examples

You can replace the data source code with the following code by using the example application listed above (if you created the DB instance and table you want, and so on):

Context initContext = new InitialContext();Context envContext  = (Context)initContext.lookup("java:/comp/env");DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle");Connection conn = ds.getConnection();//etc.

PostgreSQL0. Brief introduction

The PostgreSQL configuration is basically similar to Oracle.

1. Required Documents

Copy the Postgres JDBC jar file into the $CATALINA_HOME/lib . As with Oracle configuration, jar files must be placed in this directory, and the DBCP class loader can find them. No matter what happens next, this is the first thing you have to do.

2. Resource Configuration

There are currently two options: Define a data source that can be shared by all Tomcat apps, or define a data source that can be used only by a single application.

2a. Shared data Source Configuration

If you want to define a data source that can be shared by multiple Tomcat apps, or if you just want to define your own data source in a file, use the following configuration:

Although some user feedback said this was possible, the author of this document did not succeed, I hope someone can clarify.

<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" maxTotal="20" maxIdle="10" maxWaitMillis="-1"/>

2b. Application-Specific resource configuration

If you want to define a data source specifically for an app, other Tomcat apps cannot be used, and you can use the following configuration. This method is less damaging to the Tomcat installation.

Create a resource definition in the context of your app as follows:

<Context><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" maxTotal="20" maxIdle="10"maxWaitMillis="-1"/></Context>

3. Web. XML configuration

<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 database

When accessing a database using a program, remember to put java:/comp/env it in the front of your JNDI lookup method parameters, as shown in the following code. Alternatively, you can replace it with any value you want to use jdbc/postgres , but remember to use the same value to modify the resource definition file above.

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!");}

Non-DBCP Solutions

These scenarios either use a separate database connection (recommended for testing only!). ), or use some other pooled technology.

Introduction to Oracle 8i and OCI clients

While it is not possible to strictly address the issue of how to use the OCI client to create a JNDI data source, these considerations can be used in conjunction with the Oracle and DBCP solutions mentioned above.

In order to use the OCI driver, an Oracle customer should be installed first. You should have installed the Oracle 8i (8.1.7) client on CD-ROM and downloaded the applicable jdbc/oci driver (oracle8i 8.1.7.1 jdbc/oci driver) from otn.oracle.com.

After you rename classes12.zip classes12.jar it, copy it to $CATALINA_HOME/lib medium. Depending on the version of Tomcat and the JDK you are using, you may also have to delete the class in the file javax.sql.* .

Link up

Make sure that you $PATH LD_LIBRARY_PATH exist or file in or (possibly in $ORAHOME\bin ) the directory ocijdbc8.dll , and .so also verify that you can use System.loadLibrary("ocijdbc8"); such a simple test program to load the local library.

Below you should create a simple test with a servlet or JSP, which should contain the following key code:

DriverManager.registerDriver(neworacle.jdbc.driver.OracleDriver());conn =DriverManager.getConnection("jdbc:oracle:oci8:@database","username","password");

The current database is the host:port:SID form, if you try to access the test with servlet/jsp, then you will get an ServletException exception, the root cause of the exception lies in java.lang.UnsatisfiedLinkError:get_env_handle .

Analysis, the first UnsatisfiedLinkError shows:

• The JDBC class file does not match the Oracle client version. The message revealed that the required library file was not found. For example, you might use an Oracle 8.1.6 Class12.zip file, while the Oracle client version is 8.1.5. The Classexxxs.zip file must match the Oracle client file version.

• There was a $PATH, LD_LIBRARY_PATH problem.

• It is reported that ignoring the drivers downloaded from the OTN Web site, using $ORAHOME\jdbc\lib the Class12.zip file in the directory, will also work properly.

Next, you may also encounter another error message: ORA-06401 NETCMN: invalid driver designator .

The Oracle documentation says, "Exception reason: the login (connection) string contains an illegal driver identifier. Workaround: Modify the string and resubmit. "So, modify the database ( host:port:SID ) connection string as follows:

(description=(address=(host=myhost)(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))

Problems

Here are some of the problems that Web apps often encounter when working with databases, as well as some coping techniques.

Database Connection Intermittent Failure

Tomcat runs in the JVM. The JVM periodically performs garbage collection (GC) and clears Java objects that are no longer in use. When the JVM executes the GC, the execution of the code in Tomcat terminates. If the configured database connection is established for a maximum time less than the time of the garbage collection, the database connection fails.

When you start Tomcat, -verbose:gc Add the parameters to the CATALINA_OPTS environment variable to know how long the garbage collection takes. When enabled verbose:gc , the $CATALINA_BASE/logs/catalina.out log file can contain data for each garbage collection, including the time it takes.

After the JVM is properly tuned, garbage collection can take up to 99% of the time to occupy no more than 1 seconds. The remainder takes only a few seconds, and in rare cases the GC takes more than 10 seconds.

Ensure that the database connection timeout is set to 10-15 seconds. For DBCP, you can use maxWaitMillis parameters to set it.

Random Connection Close exception

Such an exception message often occurs when a request obtains a database connection from the connection pool and then closes it twice. When a connection pool is used, the connection is closed, and it is returned to the connection pool so that other requests can reuse the connection and not close the connection. Tomcat uses multiple threads to handle concurrent requests. The following example demonstrates that in Tomcat, a series of events caused this error.

You run request 1 in thread 1 to get a connection.

Request 1 closed the database connection.

The JVM switches the running thread to thread 2.

Request 2 that is running in thread 2 gets a database connection.
(The same database connection was just requested 1 off)

The JVM then switches the running thread back to thread 1.

Request 1 closed the database connection for the second time.

The JVM switches the running thread back to thread 2.

Request 2 and Thread 2 attempted to use a database connection, but failed. Because request 1 has 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;    }  }

Context and global naming resources

Note that although the JNDI declarations are placed inside a Context element in the instructions above, it is possible (and sometimes more necessary) to place these declarations in the Globalnamingresources area of the server configuration file. The resources placed in the Globalnamingresources area will be shared by the various contexts of the server.

JNDI resource naming and Realm interaction

In order for realm to work, realm must point to the data source defined in the <GlobalNamingResources> or <Context> region, not <ResourceLink> the renamed data source.

JDBC Data source