<td>false</td>
</tr>
<tr>
	<td>logfile</td>
	<td>Overrides generic logfile for this pool</td>
	<td>string</td>
	<td>&nbsp;</td>
</tr>
<tr>
	<td>dateformat</td>
	<td>Formatting string used for log entries (SimpleDateFormat object used)</td>
	<td>string</td>
	<td>EEE&nbsp;MMM&nbsp;dd&nbsp;hh:mm:ss.SSS&nbsp;ZZZ&nbsp;yyyy</td>
</tr>
</table>
</blockquote>

<p>You can define more than one pool provided each one has a different pool name. To specify multiple database driver classes simply comma or whitespace separate them. Each driver entry needs to be the fully-qualified class name of a valid JDBC Driver which implements the java.sql.Driver interface.</p>

<p>You can optionally supply additional properties to the JDBC driver by adding <code>&lt;poolname&gt;.prop.<i>property</i>=<i>value</i></code> within the properties file. This allows you to pass values to the driver which otherwise might be passed by adding them to the JDBC URL.</p>

<p>Internal validation of the properties is performed, so if you specify impossible values the pool will default to: no pooled items, unlimited total connections, no expiry. This will behave similarly to not having a pool, so it is worth thinking through the numbers you use. <i>Each of the defaults shown are used to avoid making any assumptions about the needs of any individual circumstance. Each time a pool is setup you should take care to analyse and determine the entries that are likely to perform well in that specific situation.</i></p>

<p>So, for example, the properties for a locally-hosted MySQL database with a pool name of <i>local</i> could be:</p>
<pre>
	drivers=com.mysql.jdbc.Driver
	logfile=dbpool.log

	local.url=jdbc:mysql://localhost:3306/homeDB
	local.user=Nemo
	local.password=LuckyFin
	local.maxpool=10
	local.maxconn=20
	local.expiry=0
</pre>

<p><code>maxpool</code> determines the maximum number of connections that can be held in the pool.<br>
<code>maxconn</code> determines the absolute maximum number of connections that can be created for use.<br>
<code>expiry</code> is the expiry time for individual connections (seconds).</p>

<p>These properties determine the behaviour of the pool when in use. The number of connections available for use is dictated by <code>maxpool</code> and <code>maxconn</code>. A maximum of <code>maxpool</code> items are ever held for re-use in the pool, although up to <code>maxconn</code> can be created and handed out, but the surplus connections will be destroyed when handed back. If <code>maxconn</code> is set to zero then an unlimited number of additional connections can be checked out, but the surplus will be destroyed when checked back in.</p>

<p>Depending on the connection demands of your application you will see differing behaviour in the pool. With a low demand only a few connections are likely to ever be created (sometimes fewer than <code>maxpool</code>), whereas with a very high demand you may well stretch the pooling system to it's limits. The likely usage should be carefully taken into consideration when configuring the properties file.</p>

<h4>What this means...</h4>
<p>You have great flexibility to create connection pools dealing with most situations you are likely to require. For instance, some database drivers only give you a limited number of connections due to either limited resources or licence limitations. In this case you set <code>maxconn</code> at or just below this limit, and establish a convenient <code>maxpool</code> size which deals well with the average load. If you have a high-demand application with a limited number of connections you may simply want to remove the <code>expiry</code> and make the <code>maxpool</code> equal to <code>maxconn</code> which will simply pool all available connections and keep them open; this gives you maximum database connection performance, but may tie up resources on the machine which could be used elsewhere.</p>

<h4>Example...</h4>
<p>The pool shown above will create new connections as required, up to a maximum of 20 simultaneous connections. When each connection is returned it will only be kept in the pool if there are fewer than 10 already in the pool. Otherwise it will close the returned connection. This occurs because there is no expiry for the pool.</p>

<p>The example below shows the properties file for an Oracle database using the Oracle Thin JDBC driver to access the database called &quot;test&quot;.</p>
<pre>
	drivers=oracle.jdbc.driver.OracleDriver
	logfile=dbpool.log

	local.url=jdbc:oracle:thin:@myDB.myISP.com:1521:test
	local.user=b_lightyear
	local.password=BeyondInfinity
	local.maxpool=10
	local.maxconn=30
	local.expiry=180
	local.validator=snaq.db.AutoCommitValidator
</pre>
<p>In this case there is a maximum limit on the number of connections as might be the case when using an ISP-hosted database server. To save resources and to avoid compromising performance the pool is only 10 connections in size. When demand is high the pool size is able to expand up to 30, and when demand is low, connections will expire after three minutes of idleness (180 seconds). This example also makes use of more rigorous connection validation (for more information see the <a href="#Advanced">Advanced section</a>).</p>


<hr size="1">


<a name="Choosing"></a><h3>Choosing Pooling Parameters</h3>

<p>One difficult aspect of using a connection pooling system is figuring out what parameters to use to get the best out of it. This is not surprising, as it dependent on many factors: the application (and it's code efficiency), the database drivers, the database application, the database platform, etc. All these variables (and more besides) lead to the inevitability that choosing good parameters is a matter of trial and error; there is no absolute way of knowing what is best without trying out a few ideas. Try to step back from the details of the code that has been written and view the application as a whole.</p>

<p>By answering some of the questions below they may assist you in coming to more useful values for the parameters in question. Bear in mind that you can turn on the debug option for each pool to see more detailed usage information in the log. This helps determine whether the behaviour is appropriate to the circumstances, and is highly recommended during development. Be aware that enabling debug can be detrimental to performance, so remember to disable it again for production releases.</p>

<p>General questions to think about:</p>
<ul>
<li>Are database accesses generally quick, or do they take a long time?
<li>Are database accesses generally frequent or infrequent?
<li>Are there any database accesses which obviously take a long time to complete?
</ul>

<p>If your application only occasionally requires access to a database then it's possible you don't even need connection pooling. However, it might still be able to provide a convenient mechanism for providing access to the database. Additionally, if the system is likely to be up-scaled you will need to think of the future implications in terms of database access.</p>

<p>Issues that may affect the likely value for <code>maxpool</code>:</p>
<ul>
<li>How much connection demand is there under normal use? ...and under heavy use?
<li>Are connections usually used for a long time, or only quickly?
</ul>

<p>Issues that affect the likely value for <code>maxconn</code>:</p>
<ul>
<li>Is there a limit on the number of connections imposed by the JDBC driver (either code or licence)?
<li>Does the database have resource limitations so only a certain number of connections can be used simultaneously?
<li>Are there other system limitations (memory, processor, etc.) that restrict connection creation (or creation efficiency)?
</ul>

<p>Issues that affect the likely value for <code>expiry</code>:</p>
<ul>
<li>Does the database automatically close connections after a certain period of inactivity?
<li>Is this behaviour an option which can be switched off?
<li>Are the connections reliable over long periods of inactivity (hidden resource use, memory leaks, etc.)?
<li>How much connection demand is there under normal use? ...and under heavy use?
</ul>

<p>Issues that may affect whether to use statement caching:</p>
<ul>
<li>Does the application frequently issue the same queries to the database (Prepared/CallableStatements)?
<li>Does the application make use of user-specified Statements (ie. ResultSet type, concurrency settings, etc.)?
</ul>

<p>Issues that may affect whether/how to use custom connection validation:</p>
<ul>
<li>Do you frequently find connections in an invalid state after a while?
<li>Do you need to validate connections, but the database doesn't support the auto-commit feature?
</ul>

<p>By looking at the system as a whole and trying to determine it's database access behaviour you can try to understand the most likely useful values for these parameters. Try them out to see if they give a performance boost. Try adjusting them a bit and seeing what effect this has on the performance. You may find adding timing and/or pool analysis code to the application helps achieve better performance. To achieve this the pooling system has methods to discover connection &quot;hit rate&quot;, the pool parameters, and information about the number of connections held, both available and in-use.</p>


<hr size="1">


<a name="Advanced"></a><h3>Advanced Features</h3>

<h4>Connection Validation</h4>
<p>Each time a connection is requested from a pool it is checked for validity prior to being handed out. By default this check is done using the <code>isClosed()</code> method of the connection object. Although this method is relatively quick to return, it is not 100% reliable as it only returns whether the <code>close()</code> method has explicitly been called (the connection may have become invalid another way). You can override this default connection validation by using a custom validation class. The simplest way of making the validation more rigorous is to use the supplied <code>snaq.db.AutoCommitValidator class</code>.</p>

<p>The <code>AutoCommitValidator</code> class has been provided as a convenience to perform connection validation using the standard SQL call <code>setAutoCommit(true)</code> which should determine whether the connection can be used without problems, provided your JDBC database driver supports this feature. However, you can write your own validation classes as required by implementing the <code>ConnectionValidator</code> interface and specifying the class name in the properties file.</p>

<p>If using a standalone ConnectionPool call the setValidator(ConnectionValidator) method. If using the pool manager include this line (key/value pair) in your properties:</p>
<pre>
	&lt;poolname&gt;.validator=snaq.db.AutoCommitValidator
</pre>

<p>Bear in mind that you will need to find a practical balance between the rigorousness of the validation and the performance of the application. To obtain an (almost) error-proof validation you may wish to provide a class which performs full queries on the database to determine if it's operational. Sometimes this is necessary to provide the best quality service, but it might also impact performance if the connection needs to be tested in this way each time it is requested from the pool. However, it is also worth noting that some database/driver combinations can operate in a strange way if the validation is not rigorous enough, due to connections being closed unexpectedly and the validation routine not identifying the closure. The likelihood of this is liable to increase when using non-Type-4 JDBC drivers which use native code instead of being pure Java. If in doubt write connection validators that are more rigorous than required, then reduce the strength later to enhance speed performance and assess the situation. If necessary enable debugging information to obtain more information about the functioning of the pool.</p>

<h4>Disabling statement caching</h4>
<p>Caching of Statement objects is provided for by the pooling system by using a connection wrapper class (Statements created with a user-specified ResultSet type and concurrency are not cached; only the default statements are cached). In the event of using DBPool in a system where caching is not required you can disable the caching by adding the following to the properties file:</p>
<pre>
	&lt;poolname&gt;.cache=false
</pre>
<p>or by directly disabling caching on a standalone pool by calling one of the <code>setCaching(...)</code> methods.</p>

<h4>Password Encryption</h4>
<p>Connection pools are usually setup using the text-based properties file, which contains a plaintext password. Should the security of the machine hosting this properties file be compromised this allows the plaintext password to become available, which in turn compromises the security of the database server. To deal with this users can create a class which implements the PasswordDecoder interface, which has a one method signature:</p>
<pre>
	char[] decode(String encoded);
</pre>

<p>A very simple example PasswordDecoder (snaq.db.RotDecoder) has been provided which performs simple Rot13-encoding. This algorithm is very insecure and is only provided as an example implementation for demonstration purposes.</p>

<p>Once you have implemented your PasswordDecoder class, if using a standalone ConnectionPool call the setPasswordDecoder(PasswordDecoder) method, or if using the pool manager include this line (key/value pair) in your properties:</p>
<pre>
	&lt;poolname&gt;.decoder=&lt;full-qualified classname&gt;
</pre>

<p>Note: this mechanism only allows for implementation of password-based key systems, and while it is not as potentially secure as key-pair encryption, it does dramatically enhance security.</p>

<p>Bear in mind that you will need to find a practical balance between the rigorousness of the validation and the performance of the application. To obtain an (almost) error-proof validation you may wish to provide a class which performs full queries on the database to determine if it's operational. Sometimes this is necessary to provide the best quality service, but it might also impact performance if the connection needs to be tested in this way each time it is requested from the pool. However, it is also worth noting that some database/driver combinations can operate in a strange way if the validation is not rigorous enough, due to connections being closed unexpectedly and the validation routine not identifying the closure. The likelihood of this is liable to increase when using non-Type-4 JDBC drivers which use native code instead of being pure Java. If in doubt write connection validators that are more rigorous than required, then reduce the strength later to enhance speed performance and assess the situation. If necessary enable debugging information to obtain more information about the functioning of the pool.</p>

<h4>Asynchronous connection destruction</h4>
<p>Sometimes you will come across situations where the destruction of a database connection takes a disproportionate length of time. This could occur because the connections regularly get into an unstable state for some reason, or simply that the nature of the system being used means that many other resources need to be cleaned up as well. This situation can be indicative of a more serious instability (worth considering more rigorous connection validation), but there are some occasions when this is simply unavoidable. To ensure that pooling performance is not affected when this occurs you can turn on this option to perform the connection destruction asynchronously, thereby returning control immediately and not tying up the pool unnecessarily. It is recommended (and the default setting) that you leave this option disabled unless you are sure you need it. To enable this option add this line to the properties file:</p>
<pre>
	&lt;poolname&gt;.async=true
</pre>
<p>or call <code>setAsyncDestroy(true)</code> on a ConnectionPool instance.</p>

<h4>Asynchronous/forced pool release</h4>
<p>You can make a connection pool release it's resources asynchronously by calling the <code>releaseAsync()</code> method instead of the <code>release()</code> method when finished with a pool. This will return immediately and perform the pool cleanup in a background thread. This can be useful if the application continues after it has finished using the pool, but you don't want the application to have to wait for all the resources of the pool to be released, which in some circumstances can take a while.</p>

<p>You can force a connection pool to be released by using the <code>releaseForcibly()</code> method. Any open connections which have not been returned to the pool will be forcibly closed using this method. This method of release can sometimes be useful when the pool has been used to hand out connections to third party code where it is unreliable whether or not all connections will be returned, and an application termination is required. Bear in mind that forcibly closing connections can sometimes lead to unpredictable results in terms of database contents.</p>

<p><i>Note: When using the ConnectionPoolManager calling release() performs the release synchronously and forcibly, so it is wise to ensure all connections from the underlying pools have been finished with and closed.</i></p>

<h4>Pool Listeners</h4>
<p>Sometimes it's useful to have code be able react to the current state of a connection pool. For this reason ConnectionPool objects can issue events about their activity to objects which have registered interest. Listener objects must implement the ConnectionPoolEventListener interface, and can register/deregister their interest by using the provided methods:</p>
<pre>