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

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>