Difference between revisions of "Tomcat MySQL datasource"

(Created page with "Category:Linux Category:Development This page describes the MySQL datasource. However, you can use it to install any JDBC datasource, as long as you adjust the ''driv...")
 
(web.xml)
 
(One intermediate revision by the same user not shown)
Line 12: Line 12:
 
http://dev.mysql.com/downloads/connector/j/  
 
http://dev.mysql.com/downloads/connector/j/  
  
2. Decompress content and extract mysql-connector-java-XXX-bin.jar  
+
2. Decompress content and extract <code>mysql-connector-java-XXX-bin.jar</code>
  
 
3. Copy this file into $TOMCAT/libs
 
3. Copy this file into $TOMCAT/libs
Automatic install: /usr/share/tomcat7/lib
+
* Automatic install: <code>/usr/share/tomcat7/lib</code>
 +
* Manual install, Tomcat '''instance''':    <code>/opt/tomcat-base/lib/</code>
  
  
Line 101: Line 102:
  
  
Add - replace ''myDataSource'' by your setting, as earlier:
+
Add ~line 33 (replace ''myDataSource'' by your setting, as earlier):
  
 
<syntaxhighlight lang="xml">
 
<syntaxhighlight lang="xml">
Line 115: Line 116:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
 +
All applications will then have access to <code>jdbc/myDataSource</code>
  
 
===Take changes into account===
 
===Take changes into account===

Latest revision as of 12:30, 1 April 2015


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>