Tomcat MySQL datasource

This page describes the MySQL datasource. However, you can use it to install any JDBC datasource, as long as you adjust the driver and dialect.

Add MySQL datasource

Setup MySQL JDBC connector

1. Download MySQL JDBC driver http://dev.mysql.com/downloads/connector/j/

2. Decompress content and extract mysql-connector-java-XXX-bin.jar

3. Copy this file into $TOMCAT/libs

• Automatic install: /usr/share/tomcat7/lib
• Manual install, Tomcat instance: /opt/tomcat-base/lib/

Declare MySQL datasource

Server.xml

Edit the configuration file

• Automatic install: /etc/tomcat7/server.xml
• Manual install: /opt/tomcat-base/server.xml

vim $TOMCAT/server.xml

Add - replace myDataSource & mySchema by your settings:

<GlobalNamingResources>

<!-- ####################################################################### -->
<!--                              MySQL datasource                           -->
<!-- ####################################################################### -->
<!-- maxActive: Maximum number of database connections in pool. Set to -1 for no limit. -->
<!-- maxIdle: Maximum number of idle database connections to retain in pool. Set to -1 for no limit.  -->
<!-- maxWait: Maximum time to wait for a database connection to become available in ms. Set to -1 to wait indefinitely. -->
<!-- driverClassName: Class name for the official MySQL Connector/J driver is com.mysql.jdbc.Driver. -->

<Resource name="jdbc/myDataSource" 
	      auth="Container" type="javax.sql.DataSource"
	      username="user" 
              password="password" 
	      url="jdbc:mysql://localhost:3306/mySchema" 
	      maxActive="50" maxIdle="30" maxWait="10000"
	      driverClassName="com.mysql.jdbc.Driver"
              factory="org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory"
              removeAbandoned="true"
              validationQuery="select 1" validationInterval="30000"
              testOnBorrow="true" testWhileIdle="true" 
              timeBetweenEvictionRunsMillis="60000"
              numTestsPerEvictionRun="5"
              poolPreparedStatements="true"
/>

</GlobalNamingResources>

Technical note:

• Tomcat 8+: factory="org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory"
• Tomcat 7 >= 7.0.52 : factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
• Tomcat 6,7 < 7.0.52 : factory="org.apache.commons.dbcp.BasicDataSourceFactory"

Context.xml

Edit:

$TOMCAT/context.xml

Add - replace myDataSource by your setting, as earlier:

<!-- ####################################################################### -->
<!--                              MySQL datasource                           -->
<!-- ####################################################################### -->
<ResourceLink name="jdbc/myDataSource" 	
              global="jdbc/myDataSource"
              type="javax.sql.datasource" />

web.xml

Edit:

$TOMCAT/web.xml

Add ~line 33 (replace myDataSource by your setting, as earlier):

<!-- ####################################################################### -->
<!--                              MySQL datasource                           -->
<!-- ####################################################################### -->
    <resource-ref>
	    <description>My super application datasource</description>
	    <res-ref-name>jdbc/myDataSource</res-ref-name>
	    <res-type>javax.sql.DataSource</res-type>
	    <res-auth>Container</res-auth>
    </resource-ref>

All applications will then have access to jdbc/myDataSource

Take changes into account

Restart tomcat:

service tomcat7 restart

Check result: http://localhost:8080/manager/text/resources

Use datasource

To use the datasource with a JNDI name you must prefix it with:

• java:comp/env/

      java:comp/env/jdbc/myDataSource

Datasource tweak

You can tweak the datasource using some specific config parameters.

Edit:

$TOMCAT/server.xml

Edit your JDBC resource:

<Resource auth="Container"
   name="jdbc/myDataSource"
   username="user"
   password="password"
   type="javax.sql.DataSource"

   <!-- JDBC Driver -->
   url="jdbc:mysql://localhost:3306/rtd"		
   driverClassName="com.mysql.jdbc.Driver"

   <!-- To manage connection pool and close inactive connections -->
   maxActive="50" maxIdle="30" maxWait="10000"
   maxIdle="10"
   maxWait="5000"
   maxActive="30"   	             

   <!-- Log settings -->
   logAbandoned="true" 		     To report the stacktrace of the faulty code
   removeAbandoned="true"	     To remedy connection starvation while leaky code is not fixed
   removeAbandonedTimeout="60"	     Interval for fixing connection starvation

   <!-- custom query to perform regular checks. Interval in ms -->
   validationQuery="select 1 from dual"     
   validationInterval="30000"		    
   testOnBorrow="true"
   testOnReturn="false"
   testWhileIdle="true"
   timeBetweenEvictionRunsMillis="5000"
   numTestsPerEvictionRun="3"
   minEvictableIdleTimeMillis="30000"
/>

More tweaks: http://commons.apache.org/proper/commons-dbcp/configuration.html

Drivers

You can use the following JDBC drivers:

Edit:

$TOMCAT/server.xml

Adjust your datasource:

<Resource auth="Container"
   name="jdbc/myDataSource"
   username="user"
   password="password"
   type="javax.sql.DataSource"

   <!-- JDBC Driver -->
   <!-- MySQL -->
   url="jdbc:mysql://localhost:3306/rtd"		
   driverClassName="com.mysql.jdbc.Driver"

   <!-- Oracle -->
   url="jdbc:oracle:thin:@server.domain:1521:development"
   driverClassName="oracle.jdbc.driver.OracleDriver"
  
   ...
</Resource>