http://www.daxiongmao.eu/wiki/index.php?title=Tomcat_MySQL_datasource&feed=atom&action=history
Tomcat MySQL datasource - Revision history
2024-03-28T18:43:21Z
Revision history for this page on the wiki
MediaWiki 1.31.1
http://www.daxiongmao.eu/wiki/index.php?title=Tomcat_MySQL_datasource&diff=2236&oldid=prev
WikiFreak: /* web.xml */
2015-04-01T11:30:03Z
<p><span dir="auto"><span class="autocomment">web.xml</span></span></p>
<table class="diff diff-contentalign-left" data-mw="interface">
<col class="diff-marker" />
<col class="diff-content" />
<col class="diff-marker" />
<col class="diff-content" />
<tr class="diff-title" lang="en">
<td colspan="2" style="background-color: #fff; color: #222; text-align: center;">← Older revision</td>
<td colspan="2" style="background-color: #fff; color: #222; text-align: center;">Revision as of 11:30, 1 April 2015</td>
</tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l102" >Line 102:</td>
<td colspan="2" class="diff-lineno">Line 102:</td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td></tr>
<tr><td class='diff-marker'>−</td><td style="color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>Add <del class="diffchange diffchange-inline">- </del>replace ''myDataSource'' by your setting, as earlier:</div></td><td class='diff-marker'>+</td><td style="color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>Add <ins class="diffchange diffchange-inline">~line 33 (</ins>replace ''myDataSource'' by your setting, as earlier<ins class="diffchange diffchange-inline">)</ins>:</div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><syntaxhighlight lang="xml"></div></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><syntaxhighlight lang="xml"></div></td></tr>
<tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l116" >Line 116:</td>
<td colspan="2" class="diff-lineno">Line 116:</td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div></syntaxhighlight></div></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div></syntaxhighlight></div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td></tr>
<tr><td colspan="2"> </td><td class='diff-marker'>+</td><td style="color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">All applications will then have access to <code>jdbc/myDataSource</code></ins></div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>===Take changes into account===</div></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>===Take changes into account===</div></td></tr>
</table>
WikiFreak
http://www.daxiongmao.eu/wiki/index.php?title=Tomcat_MySQL_datasource&diff=2235&oldid=prev
WikiFreak at 11:25, 1 April 2015
2015-04-01T11:25:40Z
<p></p>
<table class="diff diff-contentalign-left" data-mw="interface">
<col class="diff-marker" />
<col class="diff-content" />
<col class="diff-marker" />
<col class="diff-content" />
<tr class="diff-title" lang="en">
<td colspan="2" style="background-color: #fff; color: #222; text-align: center;">← Older revision</td>
<td colspan="2" style="background-color: #fff; color: #222; text-align: center;">Revision as of 11:25, 1 April 2015</td>
</tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l12" >Line 12:</td>
<td colspan="2" class="diff-lineno">Line 12:</td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>http://dev.mysql.com/downloads/connector/j/  </div></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>http://dev.mysql.com/downloads/connector/j/  </div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td></tr>
<tr><td class='diff-marker'>−</td><td style="color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>2. Decompress content and extract mysql-connector-java-XXX-bin.jar  </div></td><td class='diff-marker'>+</td><td style="color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>2. Decompress content and extract <ins class="diffchange diffchange-inline"><code></ins>mysql-connector-java-XXX-bin.jar<ins class="diffchange diffchange-inline"></code></ins></div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>3. Copy this file into $TOMCAT/libs</div></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>3. Copy this file into $TOMCAT/libs</div></td></tr>
<tr><td class='diff-marker'>−</td><td style="color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>Automatic install: /usr/share/tomcat7/lib</div></td><td class='diff-marker'>+</td><td style="color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins class="diffchange diffchange-inline">* </ins>Automatic install: <ins class="diffchange diffchange-inline"><code></ins>/usr/share/tomcat7/lib<ins class="diffchange diffchange-inline"></code></ins></div></td></tr>
<tr><td colspan="2"> </td><td class='diff-marker'>+</td><td style="color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins class="diffchange diffchange-inline">* Manual install, Tomcat '''instance''':    <code>/opt/tomcat-base/lib/</code></ins></div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #222; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td></tr>
</table>
WikiFreak
http://www.daxiongmao.eu/wiki/index.php?title=Tomcat_MySQL_datasource&diff=2225&oldid=prev
WikiFreak: 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..."
2015-04-01T08:08:07Z
<p>Created page with "<a href="/wiki/index.php?title=Category:Linux" title="Category:Linux">Category:Linux</a> <a href="/wiki/index.php?title=Category:Development" title="Category:Development">Category:Development</a> This page describes the MySQL datasource. However, you can use it to install any JDBC datasource, as long as you adjust the ''driv..."</p>
<p><b>New page</b></p><div>[[Category:Linux]]<br />
[[Category:Development]]<br />
<br />
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''.<br />
<br />
<br />
=Add MySQL datasource=<br />
<br />
==Setup MySQL JDBC connector==<br />
<br />
1. Download MySQL JDBC driver<br />
http://dev.mysql.com/downloads/connector/j/ <br />
<br />
2. Decompress content and extract mysql-connector-java-XXX-bin.jar <br />
<br />
3. Copy this file into $TOMCAT/libs<br />
Automatic install: /usr/share/tomcat7/lib<br />
<br />
<br />
==Declare MySQL datasource==<br />
<br />
===Server.xml===<br />
<br />
Edit the configuration file<br />
<br />
* Automatic install: /etc/tomcat7/server.xml<br />
* Manual install: /opt/tomcat-base/server.xml<br />
<br />
<syntaxhighlight lang="bash"><br />
vim $TOMCAT/server.xml<br />
</syntaxhighlight><br />
<br />
<br />
Add - replace ''myDataSource'' & ''mySchema'' by your settings:<br />
<br />
<syntaxhighlight lang="xml"><br />
<GlobalNamingResources><br />
<br />
<!-- ####################################################################### --><br />
<!-- MySQL datasource --><br />
<!-- ####################################################################### --><br />
<!-- maxActive: Maximum number of database connections in pool. Set to -1 for no limit. --><br />
<!-- maxIdle: Maximum number of idle database connections to retain in pool. Set to -1 for no limit. --><br />
<!-- maxWait: Maximum time to wait for a database connection to become available in ms. Set to -1 to wait indefinitely. --><br />
<!-- driverClassName: Class name for the official MySQL Connector/J driver is com.mysql.jdbc.Driver. --><br />
<br />
<Resource name="jdbc/myDataSource" <br />
auth="Container" type="javax.sql.DataSource"<br />
username="user" <br />
password="password" <br />
url="jdbc:mysql://localhost:3306/mySchema" <br />
maxActive="50" maxIdle="30" maxWait="10000"<br />
driverClassName="com.mysql.jdbc.Driver"<br />
factory="org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory"<br />
removeAbandoned="true"<br />
validationQuery="select 1" validationInterval="30000"<br />
testOnBorrow="true" testWhileIdle="true" <br />
timeBetweenEvictionRunsMillis="60000"<br />
numTestsPerEvictionRun="5"<br />
poolPreparedStatements="true"<br />
/><br />
<br />
</GlobalNamingResources><br />
</syntaxhighlight><br />
<br />
Technical note:<br />
* Tomcat 8+: factory="org.apache.''tomcat.dbcp.'''dbcp2'''''.BasicDataSourceFactory"<br />
* Tomcat 7 >= 7.0.52 : factory="org.apache.''tomcat.dbcp.dbcp''.BasicDataSourceFactory"<br />
* Tomcat 6,7 < 7.0.52 : factory="org.apache.''commons''.dbcp.BasicDataSourceFactory" <br />
<br />
<br />
===Context.xml===<br />
<br />
Edit:<br />
<br />
<syntaxhighlight lang="bash"><br />
$TOMCAT/context.xml<br />
</syntaxhighlight><br />
<br />
<br />
Add - replace ''myDataSource'' by your setting, as earlier:<br />
<br />
<syntaxhighlight lang="xml"><br />
<!-- ####################################################################### --><br />
<!-- MySQL datasource --><br />
<!-- ####################################################################### --><br />
<ResourceLink name="jdbc/myDataSource" <br />
global="jdbc/myDataSource"<br />
type="javax.sql.datasource" /><br />
</syntaxhighlight><br />
<br />
<br />
<br />
===web.xml===<br />
<br />
Edit:<br />
<br />
<syntaxhighlight lang="bash"><br />
$TOMCAT/web.xml<br />
</syntaxhighlight><br />
<br />
<br />
Add - replace ''myDataSource'' by your setting, as earlier:<br />
<br />
<syntaxhighlight lang="xml"><br />
<!-- ####################################################################### --><br />
<!-- MySQL datasource --><br />
<!-- ####################################################################### --><br />
<resource-ref><br />
<description>My super application datasource</description><br />
<res-ref-name>jdbc/myDataSource</res-ref-name><br />
<res-type>javax.sql.DataSource</res-type><br />
<res-auth>Container</res-auth><br />
</resource-ref><br />
</syntaxhighlight><br />
<br />
<br />
===Take changes into account===<br />
<br />
Restart tomcat:<br />
<br />
<syntaxhighlight lang="bash"><br />
service tomcat7 restart<br />
</syntaxhighlight><br />
<br />
Check result:<br />
http://localhost:8080/manager/text/resources <br />
<br />
<br />
<br />
=Use datasource=<br />
<br />
To use the datasource with a JNDI name you must prefix it with: <br />
* java:comp/env/<br />
<br />
<syntaxhighlight lang="java"><br />
java:comp/env/jdbc/myDataSource<br />
</syntaxhighlight><br />
<br />
<br />
<br />
=Datasource tweak=<br />
<br />
You can tweak the datasource using some specific config parameters.<br />
<br />
<br />
Edit:<br />
<br />
<syntaxhighlight lang="bash"><br />
$TOMCAT/server.xml<br />
</syntaxhighlight><br />
<br />
<br />
Edit your JDBC resource:<br />
<br />
<syntaxhighlight lang="xml"><br />
<Resource auth="Container"<br />
name="jdbc/myDataSource"<br />
username="user"<br />
password="password"<br />
type="javax.sql.DataSource"<br />
<br />
<!-- JDBC Driver --><br />
url="jdbc:mysql://localhost:3306/rtd" <br />
driverClassName="com.mysql.jdbc.Driver"<br />
<br />
<!-- To manage connection pool and close inactive connections --><br />
maxActive="50" maxIdle="30" maxWait="10000"<br />
maxIdle="10"<br />
maxWait="5000"<br />
maxActive="30" <br />
<br />
<!-- Log settings --><br />
logAbandoned="true" To report the stacktrace of the faulty code<br />
removeAbandoned="true" To remedy connection starvation while leaky code is not fixed<br />
removeAbandonedTimeout="60" Interval for fixing connection starvation<br />
<br />
<!-- custom query to perform regular checks. Interval in ms --><br />
validationQuery="select 1 from dual" <br />
validationInterval="30000" <br />
testOnBorrow="true"<br />
testOnReturn="false"<br />
testWhileIdle="true"<br />
timeBetweenEvictionRunsMillis="5000"<br />
numTestsPerEvictionRun="3"<br />
minEvictableIdleTimeMillis="30000"<br />
/><br />
</syntaxhighlight><br />
<br />
More tweaks: http://commons.apache.org/proper/commons-dbcp/configuration.html <br />
<br />
<br />
=Drivers=<br />
<br />
You can use the following JDBC drivers: <br />
<br />
<br />
Edit:<br />
<br />
<syntaxhighlight lang="bash"><br />
$TOMCAT/server.xml<br />
</syntaxhighlight><br />
<br />
<br />
Adjust your datasource:<br />
<br />
<syntaxhighlight lang="xml"><br />
<Resource auth="Container"<br />
name="jdbc/myDataSource"<br />
username="user"<br />
password="password"<br />
type="javax.sql.DataSource"<br />
<br />
<!-- JDBC Driver --><br />
<!-- MySQL --><br />
url="jdbc:mysql://localhost:3306/rtd" <br />
driverClassName="com.mysql.jdbc.Driver"<br />
<br />
<!-- Oracle --><br />
url="jdbc:oracle:thin:@server.domain:1521:development"<br />
driverClassName="oracle.jdbc.driver.OracleDriver"<br />
<br />
...<br />
</Resource><br />
</syntaxhighlight></div>
WikiFreak