WebSVN - Integrator Subversion - Blame - Rev 771 - /projeto-trade/apache-tomcat-9.0.112/webapps/docs/jndi-datasource-examples-howto.html

Rev	Author	Line No.	Line
771	blopes	1	<!DOCTYPE html SYSTEM "about:legacy-compat">
		2	<html lang="en"><head><META http-equiv="Content-Type" content="text/html; charset=UTF-8"><link href="./images/docs-stylesheet.css" rel="stylesheet" type="text/css"><title>Apache Tomcat 9 (9.0.112) - JNDI Datasource How-To</title></head><body><div id="wrapper"><header><div id="header"><div><div><div class="logo noPrint"><a href="https://tomcat.apache.org/"><img alt="Tomcat Home" src="./images/tomcat.png"></a></div><div style="height: 1px;"></div><div class="asfLogo noPrint"><a href="https://www.apache.org/" target="_blank"><img src="./images/asf-logo.svg" alt="The Apache Software Foundation" style="width: 266px; height: 83px;"></a></div><h1>Apache Tomcat 9</h1><div class="versionInfo">
		3	Version 9.0.112,
		4	<time datetime="2025-11-06">Nov 6 2025</time></div><div style="height: 1px;"></div><div style="clear: left;"></div></div></div></div></header><div id="middle"><div><div id="mainLeft" class="noprint"><div><nav><div><h2>Links</h2><ul><li><a href="index.html">Docs Home</a></li><li><a href="https://cwiki.apache.org/confluence/display/TOMCAT/FAQ">FAQ</a></li></ul></div><div><h2>User Guide</h2><ul><li><a href="introduction.html">1) Introduction</a></li><li><a href="setup.html">2) Setup</a></li><li><a href="appdev/index.html">3) First webapp</a></li><li><a href="deployer-howto.html">4) Deployer</a></li><li><a href="manager-howto.html">5) Manager</a></li><li><a href="host-manager-howto.html">6) Host Manager</a></li><li><a href="realm-howto.html">7) Realms and AAA</a></li><li><a href="security-manager-howto.html">8) Security Manager</a></li><li><a href="jndi-resources-howto.html">9) JNDI Resources</a></li><li><a href="jndi-datasource-examples-howto.html">10) JDBC DataSources</a></li><li><a href="class-loader-howto.html">11) Classloading</a></li><li><a href="jasper-howto.html">12) JSPs</a></li><li><a href="ssl-howto.html">13) SSL/TLS</a></li><li><a href="ssi-howto.html">14) SSI</a></li><li><a href="cgi-howto.html">15) CGI</a></li><li><a href="proxy-howto.html">16) Proxy Support</a></li><li><a href="mbeans-descriptors-howto.html">17) MBeans Descriptors</a></li><li><a href="default-servlet.html">18) Default Servlet</a></li><li><a href="cluster-howto.html">19) Clustering</a></li><li><a href="balancer-howto.html">20) Load Balancer</a></li><li><a href="connectors.html">21) Connectors</a></li><li><a href="monitoring.html">22) Monitoring and Management</a></li><li><a href="logging.html">23) Logging</a></li><li><a href="apr.html">24) APR/Native</a></li><li><a href="virtual-hosting-howto.html">25) Virtual Hosting</a></li><li><a href="aio.html">26) Advanced IO</a></li><li><a href="maven-jars.html">27) Mavenized</a></li><li><a href="security-howto.html">28) Security Considerations</a></li><li><a href="windows-service-howto.html">29) Windows Service</a></li><li><a href="windows-auth-howto.html">30) Windows Authentication</a></li><li><a href="jdbc-pool.html">31) Tomcat's JDBC Pool</a></li><li><a href="web-socket-howto.html">32) WebSocket</a></li><li><a href="rewrite.html">33) Rewrite</a></li><li><a href="cdi.html">34) CDI 2 and JAX-RS</a></li><li><a href="graal.html">35) AOT/GraalVM Support</a></li></ul></div><div><h2>Reference</h2><ul><li><a href="RELEASE-NOTES.txt">Release Notes</a></li><li><a href="config/index.html">Configuration</a></li><li><a href="api/index.html">Tomcat Javadocs</a></li><li><a href="servletapi/index.html">Servlet 4.0 Javadocs</a></li><li><a href="jspapi/index.html">JSP 2.3 Javadocs</a></li><li><a href="elapi/index.html">EL 3.0 Javadocs</a></li><li><a href="websocketapi/index.html">WebSocket 1.1 Javadocs</a></li><li><a href="jaspicapi/index.html">JASPIC 1.1 Javadocs</a></li><li><a href="annotationapi/index.html">Common Annotations 1.3 Javadocs</a></li><li><a href="https://tomcat.apache.org/connectors-doc/">JK 1.2 Documentation</a></li></ul></div><div><h2>Apache Tomcat Development</h2><ul><li><a href="building.html">Building</a></li><li><a href="changelog.html">Changelog</a></li><li><a href="https://cwiki.apache.org/confluence/display/TOMCAT/Tomcat+Versions">Status</a></li><li><a href="developers.html">Developers</a></li><li><a href="architecture/index.html">Architecture</a></li><li><a href="tribes/introduction.html">Tribes</a></li></ul></div></nav></div></div><div id="mainRight"><div id="content"><h2>JNDI Datasource How-To</h2><h3 id="Table_of_Contents">Table of Contents</h3><div class="text">
		5	<ul><li><a href="#Introduction">Introduction</a></li><li><a href="#DriverManager,_the_service_provider_mechanism_and_memory_leaks">DriverManager, the service provider mechanism and memory leaks</a></li><li><a href="#Database_Connection_Pool_(DBCP_2)_Configurations">Database Connection Pool (DBCP 2) Configurations</a><ol><li><a href="#Installation">Installation</a></li><li><a href="#Preventing_database_connection_pool_leaks">Preventing database connection pool leaks</a></li><li><a href="#MySQL_DBCP_2_Example">MySQL DBCP 2 Example</a></li><li><a href="#Oracle_8i,_9i_&_10g">Oracle 8i, 9i & 10g</a></li><li><a href="#PostgreSQL">PostgreSQL</a></li></ol></li><li><a href="#Non-DBCP_Solutions">Non-DBCP Solutions</a></li><li><a href="#Oracle_8i_with_OCI_client">Oracle 8i with OCI client</a><ol><li><a href="#Oracle_8i_with_OCI_client/Introduction">Introduction</a></li><li><a href="#Putting_it_all_together">Putting it all together</a></li></ol></li><li><a href="#Common_Problems">Common Problems</a><ol><li><a href="#Intermittent_Database_Connection_Failures">Intermittent Database Connection Failures</a></li><li><a href="#Random_Connection_Closed_Exceptions">Random Connection Closed Exceptions</a></li><li><a href="#Context_versus_GlobalNamingResources">Context versus GlobalNamingResources</a></li><li><a href="#JNDI_Resource_Naming_and_Realm_Interaction">JNDI Resource Naming and Realm Interaction</a></li></ol></li></ul>
		6	</div><h3 id="Introduction">Introduction</h3><div class="text">
		7
		8	<p>JNDI Datasource configuration is covered extensively in the
		9	JNDI-Resources-HOWTO. However, feedback from <code>tomcat-user</code> has
		10	shown that specifics for individual configurations can be rather tricky.</p>
		11
		12	<p>Here then are some example configurations that have been posted to
		13	tomcat-user for popular databases and some general tips for db usage.</p>
		14
		15	<p>You should be aware that since these notes are derived from configuration
		16	and/or feedback posted to <code>tomcat-user</code> YMMV :-). Please let us
		17	know if you have any other tested configurations that you feel may be of use
		18	to the wider audience, or if you feel we can improve this section in anyway.</p>
		19
		20	<p>
		21	<b>Please note that JNDI resource configuration changed somewhat between
		22	Tomcat 7.x and Tomcat 8.x as they are using different versions of
		23	Apache Commons DBCP library.</b> You will most likely need to modify older
		24	JNDI resource configurations to match the syntax in the example below in order
		25	to make them work in Tomcat 9.
		26	See <a href="https://tomcat.apache.org/migration.html">Tomcat Migration Guide</a>
		27	for details.
		28	</p>
		29
		30	<p>
		31	Also, please note that JNDI DataSource configuration in general, and this
		32	tutorial in particular, assumes that you have read and understood the
		33	<a href="config/context.html">Context</a> and
		34	<a href="config/host.html">Host</a> configuration references, including
		35	the section about Automatic Application Deployment in the latter reference.
		36	</p>
		37	</div><h3 id="DriverManager,_the_service_provider_mechanism_and_memory_leaks">DriverManager, the service provider mechanism and memory leaks</h3><div class="text">
		38
		39	<p><code>java.sql.DriverManager</code> supports the
		40	<a href="https://docs.oracle.com/javase/8/docs/api/index.html?java/sql/DriverManager.html">service
		41	provider</a> mechanism. This feature is that all the available JDBC drivers
		42	that announce themselves by providing a <code>META-INF/services/java.sql.Driver</code>
		43	file are automatically discovered, loaded and registered,
		44	relieving you from the need to load the database driver explicitly before
		45	you create a JDBC connection.
		46	However, the implementation is fundamentally broken in all Java versions for
		47	a servlet container environment. The problem is that
		48	<code>java.sql.DriverManager</code> will scan for the drivers only once.</p>
		49
		50	<p>The <a href="config/listeners.html">JRE Memory Leak Prevention Listener</a>
		51	that is included with Apache Tomcat solves this by triggering the driver scan
		52	during Tomcat startup. This is enabled by default. It means that only
		53	libraries visible to the common class loader and its parents will be scanned for
		54	database drivers. This include drivers in <code>$CATALINA_HOME/lib</code>,
		55	<code>$CATALINA_BASE/lib</code>, the class path and (where the JRE supports it)
		56	the endorsed directory. Drivers packaged in web applications (in
		57	<code>WEB-INF/lib</code>) and in the shared class loader (where configured) will
		58	not be visible and will not be loaded automatically. If you are considering
		59	disabling this feature, note that the scan would be triggered by the first web
		60	application that is using JDBC, leading to failures when this web application is
		61	reloaded and for other web applications that rely on this feature.
		62	</p>
		63
		64	<p>Thus, the web applications that have database drivers in their
		65	<code>WEB-INF/lib</code> directory cannot rely on the service provider
		66	mechanism and should register the drivers explicitly.</p>
		67
		68	<p>The list of drivers in <code>java.sql.DriverManager</code> is also
		69	a known source of memory leaks. Any Drivers registered
		70	by a web application must be deregistered when the web application stops.
		71	Tomcat will attempt to automatically discover and deregister any
		72	JDBC drivers loaded by the web application class loader when the web
		73	application stops.
		74	However, it is expected that applications do this for themselves via
		75	a <code>ServletContextListener</code>.
		76	</p>
		77
		78	</div><h3 id="Database_Connection_Pool_(DBCP_2)_Configurations">Database Connection Pool (DBCP 2) Configurations</h3><div class="text">
		79
		80	<p>The default database connection pool implementation in Apache Tomcat
		81	relies on the libraries from the
		82	<a href="https://commons.apache.org/">Apache Commons</a> project.
		83	The following libraries are used:
		84	</p>
		85
		86	<ul>
		87	<li>Commons DBCP 2</li>
		88	<li>Commons Pool 2</li>
		89	</ul>
		90
		91	<p>
		92	These libraries are located in a single JAR at
		93	<code>$CATALINA_HOME/lib/tomcat-dbcp.jar</code>. However,
		94	only the classes needed for connection pooling have been included, and the
		95	packages have been renamed to avoid interfering with applications.
		96	</p>
		97
		98	<p>DBCP 2 provides support for JDBC 4.1.</p>
		99
		100	<div class="subsection"><h4 id="Installation">Installation</h4><div class="text">
		101
		102	<p>See the <a href="https://commons.apache.org/dbcp/configuration.html">
		103	DBCP 2 documentation</a> for a complete list of configuration parameters.
		104	</p>
		105
		106	</div></div>
		107
		108	<div class="subsection"><h4 id="Preventing_database_connection_pool_leaks">Preventing database connection pool leaks</h4><div class="text">
		109
		110	<p>
		111	A database connection pool creates and manages a pool of connections
		112	to a database. Recycling and reusing already existing connections
		113	to a database is more efficient than opening a new connection.
		114	</p>
		115
		116	<p>
		117	There is one problem with connection pooling. A web application has
		118	to explicitly close ResultSet's, Statement's, and Connection's.
		119	Failure of a web application to close these resources can result in
		120	them never being available again for reuse, a database connection pool "leak".
		121	This can eventually result in your web application database connections failing
		122	if there are no more available connections.</p>
		123
		124	<p>
		125	There is a solution to this problem. The Apache Commons DBCP 2 can be
		126	configured to track and recover these abandoned database connections. Not
		127	only can it recover them, but also generate a stack trace for the code
		128	which opened these resources and never closed them.</p>
		129
		130	<p>
		131	To configure a DBCP 2 DataSource so that abandoned database connections are
		132	removed and recycled, add one or both of the following attributes to the
		133	<code>Resource</code> configuration for your DBCP 2 DataSource:
		134	</p>
		135	<div class="codeBox"><pre><code>removeAbandonedOnBorrow=true</code></pre></div>
		136	<div class="codeBox"><pre><code>removeAbandonedOnMaintenance=true</code></pre></div>
		137	<p> The default for both of these attributes is <code>false</code>. Note that
		138	<code>removeAbandonedOnMaintenance</code> has no effect unless pool
		139	maintenance is enabled by setting <code>timeBetweenEvictionRunsMillis</code>
		140	to a positive value. See the
		141	<a href="https://commons.apache.org/dbcp/configuration.html">
		142	DBCP 2 documentation</a> for full documentation on these attributes.
		143	</p>
		144
		145	<p>
		146	Use the <code>removeAbandonedTimeout</code> attribute to set the number
		147	of seconds a database connection has been idle before it is considered abandoned.
		148	</p>
		149
		150	<div class="codeBox"><pre><code>removeAbandonedTimeout="60"</code></pre></div>
		151
		152	<p>
		153	The default timeout for removing abandoned connections is 300 seconds.
		154	</p>
		155
		156	<p>
		157	The <code>logAbandoned</code> attribute can be set to <code>true</code>
		158	if you want DBCP 2 to log a stack trace of the code which abandoned the
		159	database connection resources.
		160	</p>
		161	<div class="codeBox"><pre><code>logAbandoned="true"</code></pre></div>
		162	<p>
		163	The default is <code>false</code>.
		164	</p>
		165
		166	</div></div>
		167
		168	<div class="subsection"><h4 id="MySQL_DBCP_2_Example">MySQL DBCP 2 Example</h4><div class="text">
		169
		170	<h5>0. Introduction</h5>
		171	<p>Versions of <a href="https://www.mysql.com/products/mysql/index.html">MySQL</a> and JDBC
		172	drivers that have been reported to work:
		173	</p>
		174	<ul>
		175	<li>MySQL 3.23.47, MySQL 3.23.47 using InnoDB,, MySQL 3.23.58, MySQL 4.0.1alpha</li>
		176	<li><a href="https://www.mysql.com/products/connector-j">Connector/J</a> 3.0.11-stable (the official JDBC Driver)</li>
		177	<li><a href="http://mmmysql.sourceforge.net">mm.mysql</a> 2.0.14 (an old 3rd party JDBC Driver)</li>
		178	</ul>
		179
		180	<p>Before you proceed, don't forget to copy the JDBC Driver's jar into <code>$CATALINA_HOME/lib</code>.</p>
		181
		182	<h5>1. MySQL configuration</h5>
		183	<p>
		184	Ensure that you follow these instructions as variations can cause problems.
		185	</p>
		186
		187	<p>Create a new test user, a new database and a single test table.
		188	Your MySQL user <strong>must</strong> have a password assigned. The driver
		189	will fail if you try to connect with an empty password.
		190	</p>
		191	<div class="codeBox"><pre><code>mysql> GRANT ALL PRIVILEGES ON . TO javauser@localhost
		192	-> IDENTIFIED BY 'javadude' WITH GRANT OPTION;
		193	mysql> create database javatest;
		194	mysql> use javatest;
		195	mysql> create table testdata (
		196	-> id int not null auto_increment primary key,
		197	-> foo varchar(25),
		198	-> bar int);</code></pre></div>
		199	<blockquote>
		200	<strong>Note:</strong> the above user should be removed once testing is
		201	complete!
		202	</blockquote>
		203
		204	<p>Next insert some test data into the testdata table.
		205	</p>
		206	<div class="codeBox"><pre><code>mysql> insert into testdata values(null, 'hello', 12345);
		207	Query OK, 1 row affected (0.00 sec)
		208
		209	mysql> select * from testdata;
		210	+----+-------+-------+
		211	\| ID \| FOO \| BAR \|
		212	+----+-------+-------+
		213	\| 1 \| hello \| 12345 \|
		214	+----+-------+-------+
		215	1 row in set (0.00 sec)
		216
		217	mysql></code></pre></div>
		218
		219	<h5>2. Context configuration</h5>
		220	<p>Configure the JNDI DataSource in Tomcat by adding a declaration for your
		221	resource to your <a href="config/context.html">Context</a>.</p>
		222	<p>For example:</p>
		223	<div class="codeBox"><pre><code><Context>
		224
		225	<!-- maxTotal: Maximum number of database connections in pool. Make sure you
		226	configure your mysqld max_connections large enough to handle
		227	all of your db connections. Set to -1 for no limit.
		228	-->
		229
		230	<!-- maxIdle: Maximum number of idle database connections to retain in pool.
		231	Set to -1 for no limit. See also the DBCP 2 documentation on this
		232	and the minEvictableIdleTimeMillis configuration parameter.
		233	-->
		234
		235	<!-- maxWaitMillis: Maximum time to wait for a database connection to become available
		236	in ms, in this example 10 seconds. An Exception is thrown if
		237	this timeout is exceeded. Set to -1 to wait indefinitely.
		238	-->
		239
		240	<!-- username and password: MySQL username and password for database connections -->
		241
		242	<!-- driverClassName: Class name for the old mm.mysql JDBC driver is
		243	org.gjt.mm.mysql.Driver - we recommend using Connector/J though.
		244	Class name for the official MySQL Connector/J driver is com.mysql.jdbc.Driver.
		245	-->
		246
		247	<!-- url: The JDBC connection url for connecting to your MySQL database.
		248	-->
		249
		250	<Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
		251	maxTotal="100" maxIdle="30" maxWaitMillis="10000"
		252	username="javauser" password="javadude" driverClassName="com.mysql.jdbc.Driver"
		253	url="jdbc:mysql://localhost:3306/javatest"/>
		254
		255	</Context></code></pre></div>
		256
		257	<h5>3. web.xml configuration</h5>
		258
		259	<p>Now create a <code>WEB-INF/web.xml</code> for this test application.</p>
		260	<div class="codeBox"><pre><code><web-app xmlns="http://java.sun.com/xml/ns/j2ee"
		261	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
		262	xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
		263	http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
		264	version="2.4">
		265	<description>MySQL Test App</description>
		266	<resource-ref>
		267	<description>DB Connection</description>
		268	<res-ref-name>jdbc/TestDB</res-ref-name>
		269	<res-type>javax.sql.DataSource</res-type>
		270	<res-auth>Container</res-auth>
		271	</resource-ref>
		272	</web-app></code></pre></div>
		273
		274	<h5>4. Test code</h5>
		275	<p>Now create a simple <code>test.jsp</code> page for use later.</p>
		276	<div class="codeBox"><pre><code><%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
		277	<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
		278
		279	<sql:query var="rs" dataSource="jdbc/TestDB">
		280	select id, foo, bar from testdata
		281	</sql:query>
		282
		283	<html>
		284	<head>
		285	<title>DB Test</title>
		286	</head>
		287	<body>
		288
		289	<h2>Results</h2>
		290
		291	<c:forEach var="row" items="${rs.rows}">
		292	Foo ${row.foo}<br/>
		293	Bar ${row.bar}<br/>
		294	</c:forEach>
		295
		296	</body>
		297	</html></code></pre></div>
		298
		299	<p>That JSP page makes use of
		300	<a href="http://www.oracle.com/technetwork/java/index-jsp-135995.html">JSTL</a>'s
		301	SQL and Core taglibs. You can get it from
		302	<a href="https://tomcat.apache.org/taglibs/standard/">Apache Tomcat Taglibs - Standard Tag Library</a>
		303	project — just make sure you get a 1.1.x or later release. Once you have
		304	JSTL, copy <code>jstl.jar</code> and <code>standard.jar</code> to your web app's
		305	<code>WEB-INF/lib</code> directory.
		306
		307	</p>
		308
		309	<p>Finally deploy your web app into <code>$CATALINA_BASE/webapps</code> either
		310	as a warfile called <code>DBTest.war</code> or into a sub-directory called
		311	<code>DBTest</code></p>
		312	<p>Once deployed, point a browser at
		313	<code>http://localhost:8080/DBTest/test.jsp</code> to view the fruits of
		314	your hard work.</p>
		315
		316	</div></div>
		317
		318	<div class="subsection"><h4 id="Oracle_8i,_9i_&_10g">Oracle 8i, 9i & 10g</h4><div class="text">
		319	<h5>0. Introduction</h5>
		320
		321	<p>Oracle requires minimal changes from the MySQL configuration except for the
		322	usual gotchas :-)</p>
		323	<p>Drivers for older Oracle versions may be distributed as *.zip files rather
		324	than .jar files. Tomcat will only use <code>.jar</code> files installed in
		325	<code>$CATALINA_HOME/lib</code>. Therefore <code>classes111.zip</code>
		326	or <code>classes12.zip</code> will need to be renamed with a <code>.jar</code>
		327	extension. Since jarfiles are zipfiles, there is no need to unzip and jar these
		328	files - a simple rename will suffice.</p>
		329
		330	<p>For Oracle 9i onwards you should use <code>oracle.jdbc.OracleDriver</code>
		331	rather than <code>oracle.jdbc.driver.OracleDriver</code> as Oracle have stated
		332	that <code>oracle.jdbc.driver.OracleDriver</code> is deprecated and support
		333	for this driver class will be discontinued in the next major release.
		334	</p>
		335
		336	<h5>1. Context configuration</h5>
		337	<p>In a similar manner to the mysql config above, you will need to define your
		338	Datasource in your <a href="config/context.html">Context</a>. Here we define a
		339	Datasource called myoracle using the thin driver to connect as user scott,
		340	password tiger to the sid called mysid. (Note: with the thin driver this sid is
		341	not the same as the tnsname). The schema used will be the default schema for the
		342	user scott.</p>
		343
		344	<p>Use of the OCI driver should simply involve a changing thin to oci in the URL string.
		345	</p>
		346	<div class="codeBox"><pre><code><Resource name="jdbc/myoracle" auth="Container"
		347	type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"
		348	url="jdbc:oracle:thin:@127.0.0.1:1521:mysid"
		349	username="scott" password="tiger" maxTotal="20" maxIdle="10"
		350	maxWaitMillis="-1"/></code></pre></div>
		351
		352	<h5>2. web.xml configuration</h5>
		353	<p>You should ensure that you respect the element ordering defined by the DTD when you
		354	create you applications web.xml file.</p>
		355	<div class="codeBox"><pre><code><resource-ref>
		356	<description>Oracle Datasource example</description>
		357	<res-ref-name>jdbc/myoracle</res-ref-name>
		358	<res-type>javax.sql.DataSource</res-type>
		359	<res-auth>Container</res-auth>
		360	</resource-ref></code></pre></div>
		361	<h5>3. Code example</h5>
		362	<p>You can use the same example application as above (assuming you create the required DB
		363	instance, tables etc.) replacing the Datasource code with something like</p>
		364	<div class="codeBox"><pre><code>Context initContext = new InitialContext();
		365	Context envContext = (Context)initContext.lookup("java:/comp/env");
		366	DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle");
		367	Connection conn = ds.getConnection();
		368	//etc.</code></pre></div>
		369	</div></div>
		370
		371
		372	<div class="subsection"><h4 id="PostgreSQL">PostgreSQL</h4><div class="text">
		373	<h5>0. Introduction</h5>
		374	<p>PostgreSQL is configured in a similar manner to Oracle.</p>
		375
		376	<h5>1. Required files </h5>
		377	<p>
		378	Copy the Postgres JDBC jar to $CATALINA_HOME/lib. As with Oracle, the
		379	jars need to be in this directory in order for DBCP 2's Classloader to find
		380	them. This has to be done regardless of which configuration step you take next.
		381	</p>
		382
		383	<h5>2. Resource configuration</h5>
		384
		385	<p>
		386	You have two choices here: define a datasource that is shared across all Tomcat
		387	applications, or define a datasource specifically for one application.
		388	</p>
		389
		390	<h6>2a. Shared resource configuration</h6>
		391	<p>
		392	Use this option if you wish to define a datasource that is shared across
		393	multiple Tomcat applications, or if you just prefer defining your datasource
		394	in this file.
		395	</p>
		396	<p><i>This author has not had success here, although others have reported so.
		397	Clarification would be appreciated here.</i></p>
		398
		399	<div class="codeBox"><pre><code><Resource name="jdbc/postgres" auth="Container"
		400	type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
		401	url="jdbc:postgresql://127.0.0.1:5432/mydb"
		402	username="myuser" password="mypasswd" maxTotal="20" maxIdle="10" maxWaitMillis="-1"/></code></pre></div>
		403	<h6>2b. Application-specific resource configuration</h6>
		404
		405	<p>
		406	Use this option if you wish to define a datasource specific to your application,
		407	not visible to other Tomcat applications. This method is less invasive to your
		408	Tomcat installation.
		409	</p>
		410
		411	<p>
		412	Create a resource definition for your <a href="config/context.html">Context</a>.
		413	The Context element should look something like the following.
		414	</p>
		415
		416	<div class="codeBox"><pre><code><Context>
		417
		418	<Resource name="jdbc/postgres" auth="Container"
		419	type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
		420	url="jdbc:postgresql://127.0.0.1:5432/mydb"
		421	username="myuser" password="mypasswd" maxTotal="20" maxIdle="10"
		422	maxWaitMillis="-1"/>
		423	</Context></code></pre></div>
		424
		425	<h5>3. web.xml configuration</h5>
		426	<div class="codeBox"><pre><code><resource-ref>
		427	<description>postgreSQL Datasource example</description>
		428	<res-ref-name>jdbc/postgres</res-ref-name>
		429	<res-type>javax.sql.DataSource</res-type>
		430	<res-auth>Container</res-auth>
		431	</resource-ref></code></pre></div>
		432
		433	<h5>4. Accessing the datasource</h5>
		434	<p>
		435	When accessing the datasource programmatically, remember to prepend
		436	<code>java:/comp/env</code> to your JNDI lookup, as in the following snippet of
		437	code. Note also that "jdbc/postgres" can be replaced with any value you prefer, provided
		438	you change it in the above resource definition file as well.
		439	</p>
		440
		441	<div class="codeBox"><pre><code>InitialContext cxt = new InitialContext();
		442	if ( cxt == null ) {
		443	throw new Exception("Uh oh -- no context!");
		444	}
		445
		446	DataSource ds = (DataSource) cxt.lookup( "java:/comp/env/jdbc/postgres" );
		447
		448	if ( ds == null ) {
		449	throw new Exception("Data source not found!");
		450	}</code></pre></div>
		451
		452	</div></div>
		453	</div><h3 id="Non-DBCP_Solutions">Non-DBCP Solutions</h3><div class="text">
		454	<p>
		455	These solutions either utilise a single connection to the database (not recommended for anything other
		456	than testing!) or some other pooling technology.
		457	</p>
		458	</div><h3 id="Oracle_8i_with_OCI_client">Oracle 8i with OCI client</h3><div class="text">
		459	<div class="subsection"><h4 id="Oracle_8i_with_OCI_client/Introduction">Introduction</h4><div class="text">
		460	<p>Whilst not strictly addressing the creation of a JNDI DataSource using the OCI client, these notes can be combined with the
		461	Oracle and DBCP 2 solution above.</p>
		462	<p>
		463	In order to use OCI driver, you should have an Oracle client installed. You should have installed
		464	Oracle8i(8.1.7) client from cd, and download the suitable JDBC/OCI
		465	driver(Oracle8i 8.1.7.1 JDBC/OCI Driver) from <a href="http://otn.oracle.com/">otn.oracle.com</a>.
		466	</p>
		467	<p>
		468	After renaming <code>classes12.zip</code> file to <code>classes12.jar</code>
		469	for Tomcat, copy it into <code>$CATALINA_HOME/lib</code>.
		470	You may also have to remove the <code>javax.sql.*</code> classes
		471	from this file depending upon the version of Tomcat and JDK you are using.
		472	</p>
		473	</div></div>
		474
		475	<div class="subsection"><h4 id="Putting_it_all_together">Putting it all together</h4><div class="text">
		476	<p>
		477	Ensure that you have the <code>ocijdbc8.dll</code> or <code>.so</code> in your <code>$PATH</code> or <code>LD_LIBRARY_PATH</code>
		478	(possibly in <code>$ORAHOME\bin</code>) and also confirm that the native library can be loaded by a simple test program
		479	using <code>System.loadLibrary("ocijdbc8");</code>
		480	</p>
		481	<p>
		482	You should next create a simple test servlet or JSP that has these
		483	<strong>critical lines</strong>:
		484	</p>
		485	<div class="codeBox"><pre><code>DriverManager.registerDriver(new
		486	oracle.jdbc.driver.OracleDriver());
		487	conn =
		488	DriverManager.getConnection("jdbc:oracle:oci8:@database","username","password");</code></pre></div>
		489	<p>
		490	where database is of the form <code>host:port:SID</code> Now if you try to access the URL of your
		491	test servlet/JSP and what you get is a
		492	<code>ServletException</code> with a root cause of <code>java.lang.UnsatisfiedLinkError:get_env_handle</code>.
		493	</p>
		494	<p>
		495	First, the <code>UnsatisfiedLinkError</code> indicates that you have
		496	</p>
		497	<ul>
		498	<li>a mismatch between your JDBC classes file and
		499	your Oracle client version. The giveaway here is the message stating that a needed library file cannot be
		500	found. For example, you may be using a classes12.zip file from Oracle Version 8.1.6 with a Version 8.1.5
		501	Oracle client. The classesXXX.zip file and Oracle client software versions must match.
		502	</li>
		503	<li>A <code>$PATH</code>, <code>LD_LIBRARY_PATH</code> problem.</li>
		504	<li>It has been reported that ignoring the driver you have downloaded from otn and using
		505	the classes12.zip file from the directory <code>$ORAHOME\jdbc\lib</code> will also work.
		506	</li>
		507	</ul>
		508	<p>
		509	Next you may experience the error <code>ORA-06401 NETCMN: invalid driver designator</code>
		510	</p>
		511	<p>
		512	The Oracle documentation says : "Cause: The login (connect) string contains an invalid
		513	driver designator. Action: Correct the string and re-submit."
		514
		515	Change the database connect string (of the form <code>host:port:SID</code>) with this one:
		516	<code>(description=(address=(host=myhost)(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))</code>
		517	</p>
		518	<p>
		519	<i>Ed. Hmm, I don't think this is really needed if you sort out your TNSNames - but I'm not an Oracle DBA :-)</i>
		520	</p>
		521	</div></div>
		522	</div><h3 id="Common_Problems">Common Problems</h3><div class="text">
		523	<p>Here are some common problems encountered with a web application which
		524	uses a database and tips for how to solve them.</p>
		525
		526	<div class="subsection"><h4 id="Intermittent_Database_Connection_Failures">Intermittent Database Connection Failures</h4><div class="text">
		527	<p>
		528	Tomcat runs within a JVM. The JVM periodically performs garbage collection
		529	(GC) to remove java objects which are no longer being used. When the JVM
		530	performs GC execution of code within Tomcat freezes. If the maximum time
		531	configured for establishment of a database connection is less than the amount
		532	of time garbage collection took you can get a database connection failure.
		533	</p>
		534
		535	<p>To collect data on how long garbage collection is taking add the
		536	<code>-verbose:gc</code> argument to your <code>CATALINA_OPTS</code>
		537	environment variable when starting Tomcat. When verbose gc is enabled
		538	your <code>$CATALINA_BASE/logs/catalina.out</code> log file will include
		539	data for every garbage collection including how long it took.</p>
		540
		541	<p>When your JVM is tuned correctly 99% of the time a GC will take less
		542	than one second. The remainder will only take a few seconds. Rarely,
		543	if ever should a GC take more than 10 seconds.</p>
		544
		545	<p>Make sure that the db connection timeout is set to 10-15 seconds.
		546	For DBCP 2 you set this using the parameter <code>maxWaitMillis</code>.</p>
		547
		548	</div></div>
		549
		550	<div class="subsection"><h4 id="Random_Connection_Closed_Exceptions">Random Connection Closed Exceptions</h4><div class="text">
		551	<p>
		552	These can occur when one request gets a db connection from the connection
		553	pool and closes it twice. When using a connection pool, closing the
		554	connection just returns it to the pool for reuse by another request,
		555	it doesn't close the connection. And Tomcat uses multiple threads to
		556	handle concurrent requests. Here is an example of the sequence
		557	of events which could cause this error in Tomcat:
		558	</p>
		559	<pre>
		560	Request 1 running in Thread 1 gets a db connection.
		561
		562	Request 1 closes the db connection.
		563
		564	The JVM switches the running thread to Thread 2
		565
		566	Request 2 running in Thread 2 gets a db connection
		567	(the same db connection just closed by Request 1).
		568
		569	The JVM switches the running thread back to Thread 1
		570
		571	Request 1 closes the db connection a second time in a finally block.
		572
		573	The JVM switches the running thread back to Thread 2
		574
		575	Request 2 Thread 2 tries to use the db connection but fails
		576	because Request 1 closed it.
		577	</pre>
		578	<p>
		579	Here is an example of properly written code to use a database connection
		580	obtained from a connection pool:
		581	</p>
		582	<div class="codeBox"><pre><code> Connection conn = null;
		583	Statement stmt = null; // Or PreparedStatement if needed
		584	ResultSet rs = null;
		585	try {
		586	conn = ... get connection from connection pool ...
		587	stmt = conn.createStatement("select ...");
		588	rs = stmt.executeQuery();
		589	... iterate through the result set ...
		590	rs.close();
		591	rs = null;
		592	stmt.close();
		593	stmt = null;
		594	conn.close(); // Return to connection pool
		595	conn = null; // Make sure we don't close it twice
		596	} catch (SQLException e) {
		597	... deal with errors ...
		598	} finally {
		599	// Always make sure result sets and statements are closed,
		600	// and the connection is returned to the pool
		601	if (rs != null) {
		602	try { rs.close(); } catch (SQLException e) { ; }
		603	rs = null;
		604	}
		605	if (stmt != null) {
		606	try { stmt.close(); } catch (SQLException e) { ; }
		607	stmt = null;
		608	}
		609	if (conn != null) {
		610	try { conn.close(); } catch (SQLException e) { ; }
		611	conn = null;
		612	}
		613	}</code></pre></div>
		614
		615	</div></div>
		616
		617	<div class="subsection"><h4 id="Context_versus_GlobalNamingResources">Context versus GlobalNamingResources</h4><div class="text">
		618	<p>
		619	Please note that although the above instructions place the JNDI declarations in a Context
		620	element, it is possible and sometimes desirable to place these declarations in the
		621	<a href="config/globalresources.html">GlobalNamingResources</a> section of the server
		622	configuration file. A resource placed in the GlobalNamingResources section will be shared
		623	among the Contexts of the server.
		624	</p>
		625	</div></div>
		626
		627	<div class="subsection"><h4 id="JNDI_Resource_Naming_and_Realm_Interaction">JNDI Resource Naming and Realm Interaction</h4><div class="text">
		628	<p>
		629	In order to get Realms to work, the realm must refer to the datasource as
		630	defined in the <GlobalNamingResources> or <Context> section, not a datasource as renamed
		631	using <ResourceLink>.
		632	</p>
		633	</div></div>
		634
		635	</div></div></div></div></div><footer><div id="footer">
		636	Copyright © 1999-2025, The Apache Software Foundation
		637	<br>
		638	Apache Tomcat, Tomcat, Apache, the Apache Tomcat logo and the Apache logo
		639	are either registered trademarks or trademarks of the Apache Software
		640	Foundation.
		641	</div></footer></div></body></html>

Subversion Repositories Integrator Subversion

(root)/projeto-trade/apache-tomcat-9.0.112/webapps/docs/jndi-datasource-examples-howto.html - Rev 771