Copy Page

You are not logged in. Any changes you make will be marked as anonymous. You may want to Log In if you already have an account. You can also Sign Up for a new account.

<h1>JDBC pools configuration</h1>

<p>BTM XA datasources can be created via some java code or via a BTM-specific tool called the Resource Loader. You are free to choose the method you prefer, there is absolutely no difference between them.</p>

<h4>Contents</h4>
<img class="editor-inline-macro" src="/plugins/servlet/confluence/placeholder/macro?definition=e3RvYzptYXhMZXZlbD0zfG1pbkxldmVsPTJ9&amp;locale=en_GB&amp;version=2" data-macro-name="toc" data-macro-parameters="maxLevel=3|minLevel=2">

<h2>Using the BTM API</h2>

<p>BTM comes bundled with a JDBC XA connection pool which is very easy to configure. You basically have to create an instance of <a href="http://btm.codehaus.org/api/1.2/bitronix/tm/resource/jdbc/PoolingDataSource.html">bitronix.tm.resource.jdbc.PoolingDataSource</a> set some properties and you're done.</p>

<p>Here is an example of datasource creation that connects to an Oracle database:</p>
<table class="wysiwyg-macro" data-macro-name="code" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e2NvZGV9&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="PLAIN_TEXT"><tr><td class="wysiwyg-macro-body"><pre>
PoolingDataSource myDataSource = new PoolingDataSource();                                         (1)
myDataSource.setClassName("oracle.jdbc.xa.client.OracleXADataSource");                            (2)
myDataSource.setUniqueName("oracle");                                                             (3)
myDataSource.setMinPoolSize(0);                                                                   (4)
myDataSource.setMaxPoolSize(5);                                                                   (5)
myDataSource.setAcquireIncrement(1);                                                              (6)
myDataSource.setAllowLocalTransactions(true);                                                     (7)
myDataSource.setTestQuery("SELECT 1 FROM DUAL");                                                  (8)
myDataSource.setUseTmJoin(true);                                                                  (9)
myDataSource.setDeferConnectionRelease(true);                                                     (10)
myDataSource.setAutomaticEnlistingEnabled(true);                                                  (11)
myDataSource.setAcquisitionTimeout(30);                                                           (12)
myDataSource.setAcquisitionInterval(1);                                                           (13)
myDataSource.setPreparedStatementCacheSize(5);                                                    (14)
myDataSource.getDriverProperties().setProperty("user", "users1");                                 (15)
myDataSource.getDriverProperties().setProperty("password", "users1");                             (16)
myDataSource.getDriverProperties().setProperty("URL", "jdbc:oracle:thin:@localhost:1521:XE");     (17)

Connection c = myDataSource.getConnection();                                                      (18)
 // do some SQL
c.close();                                                                                        (19)

myDataSource.close();                                                                             (20)
</pre></td></tr></table>

<p> <strong>1.</strong> The Bitronix <code>PoolingDataSource</code> is a javabean that implements <code>java.sql.DataSource</code>.<br />
 <strong>2.</strong> You have to specify the driver's <code>XADataSource</code> implementation here.<br />
 <strong>3.</strong> Each datasource must be assigned a unique name. This is required for distributed crash recovery.<br />
 <strong>4.</strong> This datasource can contain at least 0 connection. 0 is the default value when unspecified.<br />
 <strong>5.</strong> This datasource can contain at most 5 connections.<br />
 <strong>6.</strong> If there aren't enough connections in the pool to fulfill a request, new connections will be created, by increments of 1 at a time.<br />
 <strong>7.</strong> You have to set <code>allowLocalTransactions</code> to true if you want to be able to run SQL statements outside of XA transactions scope. Defaults to false.<br />
 <strong>8.</strong> When specified, this query will be executed to check that the connection is still valid before handing it to the application code.<br />
 <strong>9.</strong> Set <code>useTmJoin</code> to false if the vendor's <code>XADataSource</code> implementation does not implement <code>XAResource.isSameRM()</code> properly. Refer to the <a class="confluence-link" href="/display/BTM/JdbcXaSupportEvaluation" data-linked-resource-id="10748312" data-linked-resource-type="page" data-linked-resource-default-alias="JdbcXaSupportEvaluation" data-base-url="http://docs.codehaus.org">JdbcXaSupportEvaluation</a> page to see if your database needs it. Defaults to true.<br />
 <strong>10.</strong> Set <code>deferConnectionRelease</code> to false if the vendor's <code>XADataSource</code> implementation supports transactions interleaving. Refer to the <a class="confluence-link" href="/display/BTM/JdbcXaSupportEvaluation" data-linked-resource-id="10748312" data-linked-resource-type="page" data-linked-resource-default-alias="JdbcXaSupportEvaluation" data-base-url="http://docs.codehaus.org">JdbcXaSupportEvaluation</a> page to see if your database supports it. Defaults to true.<br />
 <strong>11.</strong> Set <code>automaticEnlistingEnabled</code> to false if you do not want the <code>PoolingDataSource</code> to automatically enlist/delist the connections into the XA transactions. You then have to enlist <code>XAResource</code> objects manually into the <code>Transaction</code> objects for them to participate in XA transactions. Defaults to true.<br />
 <strong>12.</strong> The amount of seconds the pool will block when a connection is requested but the pool is empty and cannot grow anymore. Defaults to 30.<br />
 <strong>13.</strong> The amount of seconds the pool will wait when a connection has been tested invalid before trying to acquire a new one. Defaults to 1.<br />
 <strong>14.</strong> The amount of prepared statements cached per pooled connection. Defaults to 0, meaning statement caching is disabled.<br />
 <strong>15,16,17.</strong> The driverProperties is a <code>java.util.Properties</code> object. You have to add into it a set of property name / property value of the <code>OracleXADataSource</code> class. You have to refer to the driver's documentation to know what can / has to be set. The <a href="http://www.oracle.com/technology/docs/tech/java/sqlj_jdbc/doc_library/javadoc/oracle.jdbc.xa.OracleXADataSource.html">OracleXADataSource javadoc</a> contains this list for the Oracle case. BTM will perform conversion from <code>String</code> to <code>boolean</code> or to <code>int</code> when necessary.<br />
 <strong>18,19.</strong> You can now use the <code>PoolingDataSource</code> like any other <code>java.sql.DataSource</code>.<br />
 <strong>20.</strong> Remember to close the <code>PoolingDataSource</code> after you're done with it to release the connections.</p>

<table class="wysiwyg-macro" data-macro-name="tip" data-macro-parameters="title=No XADataSource implementation ?" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e3RpcDp0aXRsZT1ObyBYQURhdGFTb3VyY2UgaW1wbGVtZW50YXRpb24gP30&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="RICH_TEXT"><tr><td class="wysiwyg-macro-body"><p>If your database vendor does not provide a <code>XADataSource</code> implementation, you should have a look at the <a class="confluence-link" href="/display/BTM/LastResourceCommit" data-linked-resource-id="183075032" data-linked-resource-type="page" data-linked-resource-default-alias="LastResourceCommit" data-base-url="http://docs.codehaus.org">Last Resource Commit optimization</a>.</p></td></tr></table>

<h3>Minimal settings</h3>
<p>You do not have to set properties that have a default value. Here is a simplified version of the previous code creating a <code>PoolingDataSource</code> with minimal settings:</p>

<table class="wysiwyg-macro" data-macro-name="code" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e2NvZGV9&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="PLAIN_TEXT"><tr><td class="wysiwyg-macro-body"><pre>
PoolingDataSource myDataSource = new PoolingDataSource();                                         (1)
myDataSource.setClassName("oracle.jdbc.xa.client.OracleXADataSource");                            (2)
myDataSource.setUniqueName("oracle");                                                             (3)
myDataSource.setMaxPoolSize(5);                                                                   (4)
myDataSource.setAllowLocalTransactions(true);                                                     (5)
myDataSource.setTestQuery("SELECT 1 FROM DUAL");                                                  (6)
myDataSource.getDriverProperties().setProperty("user", "users1");                                 (7)
myDataSource.getDriverProperties().setProperty("password", "users1");                             (8)
myDataSource.getDriverProperties().setProperty("URL", "jdbc:oracle:thin:@localhost:1521:XE");     (9)

Connection c = myDataSource.getConnection();                                                      (10)
 // do some SQL
c.close();                                                                                        (11)

myDataSource.close();                                                                             (12)
</pre></td></tr></table>

<p>This will create a <code>PoolingDataSource</code> that will work exactly the same as the previous one. The only difference is that unspecified properties have been left untouched with their default value.</p>

<h3>Eager initialization</h3>
<p>The connection pool will be initialized during the first call to <code>getConnection()</code>. It might be desirable to initialize the pool eagerly, like during application startup rather than having to wait for the first requests. This can be done by calling <code>init()</code>:</p>

Connection c = myDataSource.getConnection();                                                      (11)
 // do some SQL
c.close();                                                                                        (12)

myDataSource.close();                                                                             (13)
</pre></td></tr></table>

<p>Now line 10 will initialize the pool instead of line 11.</p>

<table class="wysiwyg-macro" data-macro-name="note" data-macro-parameters="title=Initialization must happen before TM startup" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e25vdGU6dGl0bGU9SW5pdGlhbGl6YXRpb24gbXVzdCBoYXBwZW4gYmVmb3JlIFRNIHN0YXJ0dXB9&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="RICH_TEXT"><tr><td class="wysiwyg-macro-body">
<p>Please remember that connection pools should always be initialized before BTM starts up, ie: before calling <code>TransactionManagerServices.getTransactionManager()</code> as during initialization, the connection pool will register itself to the transaction manager as recoverable resource.</p>

<p>If you do not follow this rule, BTM might not be able to fully recover XA transactions pending finalization because not all resources are available for startup recovery.</p>

<p>To circumvent this problem, BTM will run a full recovery each time a new connection pool is registered after it started up. You should not rely on this as pending XA transactions might still hold locks on registered connection pools that could block new transactions.</p>

<p>If this is problematic for you, please let us know by voting for <a href="http://jira.codehaus.org/browse/BTM-4">BTM-4</a>.</p></td></tr></table>

<h2>Using the Resource Loader</h2>

<p>A datasource configuration utility is also bundled with BTM. It is convenient to use it rather than create your datasources in code. Refer to the <a class="confluence-link" href="/display/BTM/ResourceLoader12" data-linked-resource-id="72056885" data-linked-resource-type="page" data-linked-resource-default-alias="ResourceLoader12" data-base-url="http://docs.codehaus.org">Resource Loader</a> page for more details.</p>

<p>Here is the equivalent Resource Loader configuration of the previous code example:</p>

<table class="wysiwyg-macro" data-macro-name="tip" data-macro-parameters="title=Datasource initialization / shutdown" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e3RpcDp0aXRsZT1EYXRhc291cmNlIGluaXRpYWxpemF0aW9uIC8gc2h1dGRvd259&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="RICH_TEXT"><tr><td class="wysiwyg-macro-body"><p>The Resource Loader will always eager initialize the created datasources and close them when the transaction manager shuts down.</p></td></tr></table>

<p>You just have to write those properties in a simple text file and tell BTM where to load it by setting the <code>resourceConfigurationFilename</code> property of the <a class="confluence-link" href="/display/BTM/Configuration12" data-linked-resource-id="5636097" data-linked-resource-type="page" data-linked-resource-default-alias="Configuration12" data-base-url="http://docs.codehaus.org">Configuration12</a> object.</p>

<p>Now you also have to know how to get the datasource created by the Resource Loader. There are multiple ways:</p>

<ul>
	<li>Add <code>bitronix.tm.resource.bind=true</code> to your resource loader properties file. The datasources will then be bound to the default JNDI server using their <code>uniqueName</code> as their JNDI name.</li>
</ul>

<ul>
	<li>Another way (in case the JNDI context is read only, like in Tomcat) is to bind a <a href="http://btm.codehaus.org/api/1.2/bitronix/tm/resource/ResourceObjectFactory.html">bitronix.tm.resource.ResourceObjectFactory</a> object, passing it a <a href="http://java.sun.com/j2se/1.4.2/docs/api/javax/naming/Reference.html">javax.naming.Reference</a> containing a <a href="http://java.sun.com/j2se/1.4.2/docs/api/javax/naming/StringRefAddr.html">javax.naming.StringRefAddr</a> containing the datasource's <code>uniqueName</code> as <code>addrType</code> somewhere in your JNDI tree. The <code>bitronix.tm.resource.ResourceObjectFactory</code> class will just return the datasource with the specified <code>uniqueName</code>. This is explained more in-depth in the <a class="confluence-link" href="/display/BTM/Tomcat12" data-linked-resource-id="9240733" data-linked-resource-type="page" data-linked-resource-default-alias="Tomcat12" data-base-url="http://docs.codehaus.org">Tomcat12</a> integration page.</li>
</ul>

<ul>
	<li>The last way is to call <a href="http://btm.codehaus.org/api/1.2/bitronix/tm/resource/ResourceRegistrar.html#get(java.lang.String)">bitronix.tm.resource.ResourceRegistrar.get(String uniqueName)</a>. This is the least preferred method as this ties your code to BTM which you probably want to avoid.<br />
<img class="confluence-embedded-image confluence-external-resource" src="http://www.bitronix.be/images/shim.gif" data-image-src="http://www.bitronix.be/images/shim.gif"></li>
</ul>