Using Connector/J with Tomcat

The following instructions are based on the instructions for Tomcat-5.x, available at http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html which is current at the time this document was written.

First, install the .jar file that comes with Connector/J in $CATALINA_HOME/common/lib so that it is available to all applications installed in the container.

Next, Configure the JNDI DataSource by adding a declaration resource to $CATALINA_HOME/conf/server.xml in the context that defines your web application:

 <Context ....>
 ...
 <Resource name='jdbc/MySQLDB'
 auth='Container'
 type='javax.sql.DataSource'/>
 <!-- The name you used above, must match _exactly_ here!
 The connection pool will be bound into JNDI with the name
 'java:/comp/env/jdbc/MySQLDB'
 -->
 <ResourceParams name='jdbc/MySQLDB'>
 <parameter>
 <name>factory</name>
 <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
 </parameter>
 <!-- Don't set this any higher than max_connections on your
 MariaDB server, usually this should be a 10 or a few 10's
 of connections, not hundreds or thousands -->
 <parameter>
 <name>maxActive</name>
 <value>10</value>
 </parameter>
 <!-- You don't want to many idle connections hanging around
 if you can avoid it, only enough to soak up a spike in
 the load -->
 <parameter>
 <name>maxIdle</name>
 <value>5</value>
 </parameter>
 <!-- Don't use autoReconnect=true, it's going away eventually
 and it's a crutch for older connection pools that couldn't
 test connections. You need to decide whether your application
 is supposed to deal with SQLExceptions (hint, it should), and
 how much of a performance penalty you're willing to pay
 to ensure 'freshness' of the connection -->
 <parameter>
 <name>validationQuery</name>
 <value>SELECT 1</value> <-- See discussion below for update to this option -->
 </parameter>
 <!-- The most conservative approach is to test connections
 before they're given to your application. For most applications
 this is okay, the query used above is very small and takes
 no real server resources to process, other than the time used
 to traverse the network.
 If you have a high-load application you'll need to rely on
 something else. -->
 <parameter>
 <name>testOnBorrow</name>
 <value>true</value>
 </parameter>
 <!-- Otherwise, or in addition to testOnBorrow, you can test
 while connections are sitting idle -->
 <parameter>
 <name>testWhileIdle</name>
 <value>true</value>
 </parameter>
 <!-- You have to set this value, otherwise even though
 you've asked connections to be tested while idle,
 the idle evicter thread will never run -->
 <parameter>
 <name>timeBetweenEvictionRunsMillis</name>
 <value>10000</value>
 </parameter>
 <!-- Don't allow connections to hang out idle too long,
 never longer than what wait_timeout is set to on the
 server...A few minutes or even fraction of a minute
 is sometimes okay here, it depends on your application
 and how much spikey load it will see -->
 <parameter>
 <name>minEvictableIdleTimeMillis</name>
 <value>60000</value>
 </parameter>
 <!-- Username and password used when connecting to MariaDB -->
 <parameter>
 <name>username</name>
 <value>someuser</value>
 </parameter>
 <parameter>
 <name>password</name>
 <value>somepass</value>
 </parameter>
 <!-- Class name for the Connector/J driver -->
 <parameter>
 <name>driverClassName</name>
 <value>com.mysql.jdbc.Driver</value>
 </parameter>
 <!-- The JDBC connection url for connecting to MySQL, notice
 that if you want to pass any other MySQL-specific parameters
 you should pass them here in the URL, setting them using the
 parameter tags above will have no effect, you will also
 need to use &amp; to separate parameter values as the
 ampersand is a reserved character in XML -->
 <parameter>
 <name>url</name>
 <value>jdbc:mysql://localhost:3306/test</value>
 </parameter>
 </ResourceParams>
</Context>

Note that Connector/J 5.1.3 introduced a facility whereby, rather than use a validationQuery value of SELECT 1, it is possible to use validationQuery with a value set to /* ping */. This sends a ping to the server which then returns a fake result set. This is a lighter weight solution. It also has the advantage that if using ReplicationConnection or LoadBalancedConnection type connections, the ping will be sent across all active connections. The following XML snippet illustrates how to select this option:

<parameter>
 <name>validationQuery</name>
 <value>/* ping */</value>
</parameter>

Note that /* ping */ has to be specified exactly.

In general, follow the installation instructions that come with your version of Tomcat, as the way you configure datasources in Tomcat changes from time-to-time, and if you use the wrong syntax in your XML file, you will most likely end up with an exception similar to the following:

Error: java.sql.SQLException: Cannot load JDBC driver class 'null ' SQL state: null

Note that the auto-loading of drivers having the META-INF/service/java.sql.Driver class in JDBC 4.0 causes an improper undeployment of the Connector/J driver in Tomcat on Windows. Namely, the Connector/J jar remains locked. This is an initialization problem that is not related to the driver. The possible workarounds, if viable, are as follows: use 'antiResourceLocking=true' as a Tomcat Context attribute, or remove the META-INF/ directory.

Using Connector/J with Tomcat

Retornar