JDBC

Configuring a Connection Pool

A connection pool is a store of database connections that can be used and (more importantly) re-used to connect to a RDBMS database. They help to improve both the performance and scalability of the system.

Rather than creating a new connection each time one is needed a pool of logical connections is created when Payara Server is started. When a new connection is required the pool is searched for an available connection. If one is available it is returned to the requester. If none is available then the request is either queued or a new connection is established depending on how many connections are already in the pool and how the pool is configured.

Once the connection is finished with, rather than closing it the connection is returned to the connection pool for use by the next requester.

Creating a connection pool

It is possible to create a JDBC connection pool on the web administration console, or using administration commands.

Using Admin Console

In the left hand panel of the Admin console go to Resources → JDBC → JDBC Connection Pools

Click the New button and enter the following values:

Setting Name Value

Pool Name

test-pool

Resource Type

javax.sql.DataSource

Driver Vendor

(value for the driver, e.g. Derby30 for a Derby DB, MySQL for a MySQL DB)

Click Next and then click Finish on the next screen, accepting the default values.

New JDBC Connection Pool

Add the following properties to the connection pool by going to the Additional Properties tab of the connection pool:

Setting name Value Description

PortNumber

3306

Database port

Password

mypassword

Database password

User

root

Database user

serverName

localhost

Database address

DatabaseName

test

Name of the database

connectionAttributes

Other connection attributes (supported by the JDBC driver)

Connection pool properties

To test the connection attributes, go back to the General tab and click the Ping button at the top of the screen. You should see a message stating that the ping succeeded.

Using administration commands

You can also create a JDBC connection pool using the asadmin command line tool with the following options:

asadmin> create-jdbc-connection-pool --datasourceclassname com.mysql.jdbc.jdbc2.optional.MysqlDataSource --restype javax.sql.DataSource --property user=root:password=test:DatabaseName=test:ServerName=localhost:port=3306 test-pool

With this command, you need to specify the full name of the driver’s class instead of just selecting a value from a drop-down menu.

You can test the connection to the database from the command line run the following command:

asadmin> ping-connection-pool test-pool

Connection pool sizing

Connection pools should be sized to cater for the maximum number of concurrent connections.

The maximum size should be set in accordance with the maximum number of client requests your system or application can process. If your application receives 100 requests and each of those requires a database connection then if your connection pool is anything less than 100 some of those requests will have to wait for a connection to either be created or become available.

The minimum size of the connection pool ensures that a number of connections to the database are always established - this means that if you have a pool with a minimum size of 10 and you receive 10 requests then all can retrieve a database connection without waiting for the pool to create a new connection.

Setting the sizes using the admin console

Click on the connection pool name and under the Pool Settings section you will find Initial and Minimum Pool Size and Maximum Pool Size. Set these to your required sizes:

Connection Pool sizes in the admin console

Setting the sizes using administration commands

To set the initial and minimum pool size (to the value of 10 for example), use the following administration commands:

asadmin> set resources.jdbc-connection-pool.test-pool.steady-pool-size=10

To set the maximum pool size (to the value of 200 for example), use the following administration commands:

asadmin> set resources.jdbc-connection-pool.test-pool.max-pool-size=200

Connection validation

Connection validation ensures that connections aren’t assigned to your application after the connection has already gone stale.

Connection validation is always a trade-off between how sure you want to be that a connection is valid and the performance impact that the validation mechanism can exert into application that use the pool. There will be a negative performance impact by having to return an invalid connection by your application and borrow a new one, so finding the right balance is key.

Before using a connection from the pool a simple query is sent to test the connection. If there is an issue with the connection, it is removed from the pool and another one is used. If you have an issue such as the database being down and you have a large number of connections then each of those connections will be tested and removed.

In order to avoid this, you can set a connection validation so that if a connection fails, all remaining open connections are closed.

Set up the validation using the admin console

  1. Click on the name of the connection pool

  2. Select the Advanced tab

  3. Scroll down to the Connection Validation section and select the following settings:

    1. Connection Validation required

    2. Validation method - custom-validation

    3. Validation class name – a proper class name for the chosen JDBC driver, such as org.glassfish.api.jdbc.validation.DerbyConnectionValidation for a Derby DB

From the same screen, you can also set whether to close all connections on failure.

Connection validation configuration in Admin console

Set up the validation using administration commands

To turn on connection validation using asadmin commands:

asadmin> set resources.jdbc-connection-pool.test-pool.connection-validation-method=custom-validation
asadmin> set resources.jdbc-connection-pool.test-pool.validation-classname= org.glassfish.api.jdbc.validation.DerbyConnectionValidation
asadmin> set resources.jdbc-connection-pool.test-pool.is-connection-validation-required=true

You can also set whether to close all connections on failure with the following command:

asadmin> set resources.jdbc-connection-pool.test-pool.fail-all-connections=true

Statement and Connection Leak Detection

This feature allows you to set specific time-outs so that if SQL statements or JDBC connections haven’t been closed by an application (potentially leading to a memory leak) they can be logged and/or closed.

By default these values are set to 0 meaning this detection feature is turned off.

When working on a test or staging environment, it is recommended that leaks are logged after a short timeout but not closed. On production environments, it is recommended that leaks are closed and all logged leaks are monitored instead.

Configuring Leak Detection using the admin console

  1. Click on the name of the JDBC connections pool

  2. Select the Advanced tab

  3. Scroll down to Connection Settings

  4. Set the Connection Leak Timeout and Statement Leak Timeout value in seconds

Leak Detection setting in Admin console

Configuring Leak Detection using administration commands

You also can set the time-out values using the following asadmin commands:

asadmin> set resources.jdbc-connection-pool.test-pool.statement-leak-timeout-in-seconds=5
asadmin> set resources.jdbc-connection-pool.test-pool.connection-leak-timeout-in-seconds=5

You can turn on reclaiming of the leaking resources with the following commands:

asadmin> set resources.jdbc-connection-pool.DerbyPool.connection-leak-reclaim=true
asadmin> setresources.jdbc-connection-pool.DerbyPool.statement-leak-reclaim=true

Once these values are set, if connection or statement leaks are detected, you will see messages similar to the example below in the application log:

WARNING: A potential connection leak detected for connection pool test-pool. The stack trace of the thread is provided below:
 ...

JDBC Call Logging (SQL Tracing)

:page-aliases:Technical Documentation/Payara Server Documentation/advanced-jdbc/log-jdbc-calls.adoc

This is a feature best used for development purposes due to the volume of trace data that is produced.

SQL calls made through a JDBC connection pool can be traced, with the time taken to execute the call also recorded. SQL Tracing is ideal for debugging those hard to pin down performance issues during the development phase and as all SQL is visible SQL tracing is also a great way to see the SQL generated out of your JPA code.

Enabling JDBC Call Logging

With Log JDBC Calls configured on the connection call each call into the connection pool is timed and logged to the server log at the FINE level. A typical log message with Log JDBC Enabled is shown below;

[#|2016-02-04T18:51:01.467+0000|FINE|Payara 5.2021.5|javax.enterprise.resource.sqltrace.com.sun.gjc.util|_ThreadID=35;_ThreadName=http-listener-1(5);_TimeMillis=1454611861467;_LevelValue=500;ClassName=com.sun.gjc.util.SQLTraceLogger;MethodName=sqlTrace;|
  PoolName=H2Pool | ExecutionTime=1ms | ClassName=org.apache.derby.client.net.NetConnection40 | MethodName=prepareStatement | arg[0]=SELECT ID, AGE, BIO, BIRTHDATE, BIRTHDAY, DATEFORMAT, DATEOFBIRTH, DATEOFHIRE, EMAIL, HIREDATE, HIREDAY, MEMBERAGE, NAME, TODAYSDATE FROM MEMBERENTITY WHERE (NAME = ?) | arg[1]=1003 | arg[2]=1007 | |#]

Using the Administration Console

SQL Tracing can be enabled through the Payara Server Web administration console. First, navigate to the advanced table of your JDBC connection pool, then select the JDBC > JDBC Connection pools > ${Your Connection pool}.

Then select the Advanced Tab in the main window of the administration console. Then select the checkbox next to Log JDBC Calls to enable logging of all SQL calls:

Enabling SQL Tracing in the administration console

Using Asadmin Commands

The Log JDBC Calls setting for a JDBC connection pool can also be configured via asadmin using the set command to set the fish.payara.log-jdbc-calls property of your connection pool to true, like in the following example:

asadmin set domain.resources.jdbc-connection-pool.__TimerPool.log-jdbc-calls=true

Application Deployment

In Java EE 7+ applications and above, a JDBC data source can be deployed by adding the @DataSourceDefinition annotation to a managed component (like and @Stateless EJB bean for example). The Log JDBC Calls setting can be configured using this annotation as well:

@DataSourceDefinition(
    name = "java:app/MyApp/MyDS",
    className = "org.h2.jdbcx.JdbcDataSource",
    url = "jdbc:h2:mem:test",
    properties = {"fish.payara.log-jdbc-calls=true"})

The Data source definition can also be added to a deployment descriptor of an application, for example in the web.xml standard deployment descriptor:

<data-source>
   <name>java:global/ExampleDataSource</name>
   <class-name>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</class-name>
   <server-name>localhost</server-name>
   <port-number>3306</port-number>
   <database-name>mysql</database-name>
   <user>test</user>
   <password>test</password>
   <!-- Example of how to use a Payara specific custom connection pool setting -->
   <property>
       <name>fish.payara.log-jdbc-calls</name>
       <value>true</value>
   </property>
</data-source>

or in the definition of a jdbc-connection-pool in a payara-resources.xml file:

<jdbc-connection-pool name="examplePool"
        res-type="javax.sql.DataSource"
        datasource-classname="org.h2.jdbcx.JdbcDataSource" log-jdbc-calls="true">
    <property name="user" value="test"/>
    <property name="password" value="test"/>
    <property name="url" value="java:global/ExampleDataSource"/>
</jdbc-connection-pool>

This can also be done in the creation of a JDBC Connection Pool with the --logjdbccalls option.

asadmin create jdbc-connection-pool --datasourceclassname org.h2.jdbcx.JdbcDataSource --restype javax.sql.XADataSource --logjdbccalls=true examplePool

Advanced Connection Pool Properties in Deployment Descriptors

:page-aliases:Technical Documentation/Payara Server Documentation/advanced-jdbc/advanced-connection-pool-properties.adoc

Payara Server supports setting advanced JDBC connection pool properties directly on data source definitions made with the @DataSourceDefinition annotation or directly on deployment descriptors.

Setting Properties in the Deployment Descriptor

In a Java EE 7+ application, a data source definition can be added to a deployment descriptor of an application. For a web application, you would this in the standard web.xml deployment descriptor. To set advanced properties just add a property element as shown in the following example:

<data-source>
    <name>java:global/ExampleDataSource</name>
    <class-name>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</class-name>
    <server-name>localhost</server-name>
    <port-number>3306</port-number>
    <database-name>mysql</database-name>
    <user>test</user>
    <password>test</password>
    <!-- Example of how to use a Payara specific custom connection pool setting -->
    <property>
       <name>fish.payara.is-connection-validation-required</name>
       <value>true</value>
    </property>
</data-source>

Setting Properties on the @DataSourceDefinition Annotation.

In Java EE 7+ applications, a JDBC data source can be deployed by adding the @DataSourceDefinition annotation to a managed component (like and @Stateless EJB bean for example). To configure advanced JDBC properties, use the properties attribute of the annotation to define an array of the properties to configure:

@DataSourceDefinition(
    name = "java:app/MyApp/MyDS",
    className = "org.h2.jdbcx.JdbcDataSource",
    url = "jdbc:h2:mem:test",
    properties = {"fish.payara.is-connection-validation-required=true"})

Full List of Properties

The complete list of configurable properties is the following:

Property Value Type Default Notes

fish.payara.is-connection-validation-required

Boolean

false

true - Validate connections, allow server to reconnect in case of failure

fish.payara.connection-validation-method

String

The method of connection validation table, auto-commit, meta-data, custom-validation

fish.payara.validation-table-name

String

The name of the table used for validation if the validation method is set to table

fish.payara.validation-classname

String

The name of the custom class used for validation if the validation-method is set to custom-validation

fish.payara.fail-all-connections

Boolean

false

Close all connections and reconnect on failure, otherwise reconnect only when used

fish.payara.allow-non-component-callers

Boolean

false

Enable the pool to be used by non-component callers such as Servlet Filters

fish.payara.validate-atmost-once-period-in-seconds

Number

0

Specifies the time interval in seconds between successive requests to validate a connection at most once. Default value is 0, which means the attribute is not enabled.

fish.payara.connection-leak-timeout-in-seconds

Number

0

0 implies no connection leak detection

fish.payara.connection-leak-reclaim

Boolean

false

If enabled, leaked connection will be reclaimed by the pool after connection leak timeout occurs

fish.payara.connection-creation-retry-attempts

Number

0

Number of attempts to create a new connection. 0 implies no retries

fish.payara.connection-creation-retry-interval-in-seconds

Number

10

Time interval between retries while attempting to create a connection. Effective when Creation Retry Attempts is greater than 0.

fish.payara.statement-timeout-in-seconds

Number

-1

Timeout property of a connection to enable termination of abnormally long running queries. -1 implies that it is not enabled.

fish.payara.lazy-connection-enlistment

Boolean

false

Enlist a resource to the transaction only when it is actually used in a method

fish.payara.lazy-connection-association

Boolean

false

Connections are lazily associated when an operation is performed on them

fish.payara.associate-with-thread

Boolean

false

When the same thread is in need of a connection, it can reuse the connection already associated with that thread

fish.payara.pooling

Boolean

true

When set to false, disables connection pooling for the pool

fish.payara.statement-cache-size

Number

0

Caching is enabled when set to a positive non-zero value (for example, 10)

fish.payara.match-connections

Boolean

true

Turns connection matching for the pool on or off

fish.payara.max-connection-usage-count

Number

0

Connections will be reused by the pool for the specified number of times, after which they will be closed. 0 implies the feature is not enabled.

fish.payara.wrap-jdbc-objects

Boolean

true

When set to true, application will get wrapped jdbc objects for Statement, PreparedStatement, CallableStatement, ResultSet, DatabaseMetaData

fish.payara.sql-trace-listeners

String

Comma-separated list of classes that implement the org.glassfish.api.jdbc.SQLTraceListener interface

fish.payara.ping

Boolean

false

When enabled, the pool is pinged during creation or reconfiguration to identify and warn of any erroneous values for its attributes

fish.payara.init-sql

String

Specify a SQL string to be executed whenever a connection is created from the pool

fish.payara.statement-leak-timeout-in-seconds

Number

0

0 implies no statement leak detection

fish.payara.statement-leak-reclaim

Boolean

false

If enabled, leaked statement will be reclaimed by the pool after statement leak timeout occurs

fish.payara.statement-cache-type

String

fish.payara.slow-query-threshold-in-seconds

Number

-1

SQL queries that exceed this time in seconds will be logged. Any value ⇐ 0 disables Slow Query Logging

fish.payara.log-jdbc-calls

Boolean

false

When set to true, all JDBC calls will be logged allowing tracing of all JDBC interactions including SQL

Example Data Source Configuration

An example data source configured on the web.xml and deployed with a custom SQL Trace Listener is shown below. This data source is configured to also validate all connections returned from the connection pool before giving them to the application using the built-in MySQL Connection Validation class. The data source is also configured to log any queries that exceed 5 seconds and also logs ALL JDBC statements:

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
     version="3.1">
    <session-config>
        <session-timeout>30</session-timeout>
    </session-config>
    <data-source>
      <name>java:global/ExampleDataSource</name>
      <class-name>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</class-name>
      <server-name>localhost</server-name>
      <port-number>3306</port-number>
      <database-name>mysql</database-name>
      <user>test</user>
      <password>test</password>
      <!-- Example of how to use a Payara specific custom connection pool setting -->
      <property>
          <name>fish.payara.slow-query-threshold-in-seconds</name>
          <value>5</value>
      </property>
      <property>
          <name>fish.payara.log-jdbc-calls</name>
          <value>true</value>
      </property>
      <property>
          <name>fish.payara.sql-trace-listeners </name>
          <value>fish.payara.examples.payaramicro.datasource.example.CustomSQLTracer</value>
      </property>
      <property>
          <name>fish.payara.is-connection-validation-required</name>
          <value>true</value>
      </property>
      <property>
          <name>fish.payara.connection-validation-method</name>
          <value>custom-validation</value>
      </property>
      <property>
          <name>fish.payara.validation-classname</name>
          <value>org.glassfish.api.jdbc.validation.MySQLConnectionValidation</value>
      </property>
    </data-source>
</web-app>