# Connection pooling with JayBird 1.5

Connection pooling provides effective way to handle physical database connections. It is believed that establishing new connection to the database takes some noticeable amount or time and in order to speed things up one has to reuse connections as much as possible. While this is true for some software and for old versions of Firebird database engine, establishing connection is hardly noticeable with Firebird 1.0.3 and Firebird 1.5. So why is connection pooling needed?

There are few reasons for this. Each good connection pool provides a possibility to limit number of physical connections established with the database server. This is an effective measure to localize connection leaks. Any application cannot open more physical connections to the database than allowed by connection pool. Good pools also provide some hints where connection leak occurred. Another big advantage of connection pool is that it becomes a central place where connections are obtained, thus simplifying system configuration. However, main advantage of good connection pool comes from the fact that in addition to connection pooling, it can pool also prepared statement. Tests executed using AS3AP benchmark suite show that prepared statement pooling might increase speed of the application by 100% keeping source code clean and understandable.

## Usage scenario

When some statement is used more than one time, it makes sense to use prepared statement. It will be compiled by the server only once, but reused many times. It provides significant speedup when some statement is executed in a loop. But what if some prepared statement will be used during lifetime of some object? Should we prepare it in object's constructor and link object lifetime to JDBC connection lifetime or should we prepare statement each time it is needed? All such cases make handling of the prepared statements hard, they pollute application's code with irrelevant details.

Connection and statement pooling remove such details from application's code. How would the code in this case look like? Here's the example

Example 1. Typical JDBC code with statement pooling
    001
    002
    003
    004
    005
    006
    007
    008
    009
    010
    011
    012
    013
    014
    015
    016
    ...
    Connection connection = dataSource.getConnection();
    try {
        PreparedStatement ps = connection.prepareStatement(
            “SELECT * FROM test_table WHERE id = ?”);
        try {
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                // do something here
            }
        } finally {
            ps.close();
        }
    } finally {
        connection.close();
    }
    ...

Lines 001-016 show typical code when prepared statement pooling is used. Application obtains JDBC connection from the data source (instance of `javax.sql.DataSource` interface), prepares some SQL statement as if it is used for the first time, sets parameters, and executes the query. Lines 012 and 015 ensure that statement and connection will be released under any circumstances. Where do we benefit from the statement pooling? Call to prepare a statement in lines 003-004 is intercepted by the pool, which checks if there's a free prepared statement for the specified SQL query. If no such statement is found it prepares a new one. In line 012 prepared statement is not closed, but returned to the pool, where it waits for the next call.

## Connection pool

JayBird 1.5 connection pooling classes belong to `org.firebirdsql.pool.*` package.

Table 1. Description of some connection pool classes.
    AbstractConnectionPool.java
| Base class for all connection pools. Can be used for implementing custom pools, not necessarily for JDBC connections.
    BasicAbstractConnectionPool.java
| Subclass of `AbstractConnectionPool`, implements `javax.sql.ConnectionPoolDataSource` interface. Also provides some basic properties (minimum and maximum number of connections, blocking and idle timeout, etc) and code to handle JNDI-related issues.
    DriverConnectionPoolDataSource.java
| Implementation of `javax.sql.ConnectionPoolDataSource` for arbitrary JDBC drivers, uses `java.sql.DriverManager` to obtain connections, can be used as JNDI object factory.
    FBConnectionPoolDataSource.java
| JayBird specific implementation of `javax.sql.ConnectionPoolDataSource` and `javax.sql.XADataSource` interfaces, can be used as JNDI object factory.
    FBSimpleDataSource.java
| Implementation of `javax.sql.DataSource` interface, no connection and statement pooling is available, connections are physically opened in `getConnection()` method and physically closed in their `close()` method.
    FBWrappingDataSource.java
| Implementation of `javax.sql.DataSource` interface that uses `FBConnectionPoolDataSource` to allocate connections. This class defines some additional properties that affect allocated connections. Can be used as JNDI object factory.
    SimpleDataSource.java
| Implementation of `javax.sql.DataSource` interface that uses `javax.sql.ConnectionPoolDataSource` to allocate physical connections.

### org.firebirdsql.pool.FBConnectionPoolDataSource

This class is a corner stone of connection and statement pooling in JayBird. It can be instantiated within the application as well as it can be made accessible to other applications via JNDI. Class implements both `java.io.Serializable` and `javax.naming.Referenceable` interfaces, which allows using it in a wide range of web and application servers.

Class implements both `javax.sql.ConnectionPoolDataSource` and j`avax.sql.XADataSource` interfaces. Pooled connections returned by this class implement `javax.sql.PooledConnection` and `javax.sql.XAConnection` interfaces and can participate in distributed JTA transactions.

Class provides following configuration properties:

Property | Getter | Setter | Description
--- | --- | --- | ---
Standard properties
`maxIdleTime` | + | + | Maximum time in milliseconds after which idle connection in the pool is closed.
`maxPoolSize` | + | + | Maximum number of open physical connections.
`minPoolSize` | + | + | Minimum number of open physical connections. If value is greater than 0, corresponding number of connections will be opened when first connection is obtained.
`maxStatements` | + | + | Maximum size of prepared statement pool. If 0, statement pooling is switched off. When application requests more statements than can be kept in pool, JayBird will allow creating that statements, however closing them would not return them back to the pool, but rather immediately release the resources.
Pool properties
`blockingTimeout` | + | + | Maximum time in milliseconds during which application can be blocked waiting for a connection from the pool. If no free connection can be obtained, exception is thrown.
`idleTimeout` | + | + | Deprecated. Same as `maxIdleTime`.
`retryInterval` | + | + | Period in which pool will try to obtain new connection while blocking the application.
`maxConnections` | + | + | Deprecated. Same as `maxPoolSize`.
`minConnections` | + | + | Deprecated. Same as `minPoolSize`.
`pooling` | + | + | Allows to switch connection pooling off.
`statementPooling` | + | + | Allows to switch statement pooling off.
`pingStatement` | + | + | Statement that will be used to “ping” JDBC connection, in other words, to check if it is still alive. This statement must always succeed.
`pingInterval` | + | + | Time during which connection is believed to be valid in any case. Pool “pings” connection before giving it to the application only if more than specified amount of time passed since last “ping”.
`isolation` | + | + | Default transaction isolation level. All connections returned from the pool will have this isolation level. One of: `TRANSACTION_READ_COMMITTED` `TRANSACTION_REPEATABLE_READ` `TRANSACTION_SERIALIZABLE`
`transactionIsolationLevel` | + | + | Integer value from `java.sql.Connection` interface corresponding to the transaction isolation level specified in `isolation` property.
Firebird specific properties
`database` | + | + | Path to the database in the format `[host/port:]/path/to/database.fdb`
`type` | + | + | Type of the driver to use. Possible values are: `PURE_JAVA` or `TYPE4` for type 4 JDBC driver `NATIVE` or `TYPE2` for type 2 JDBC driver `EMBEDDED` for using embedded version of the Firebird.
`blobBufferSize` | + | + | Size of the buffer used to transfer BLOB content. Maximum value is 64k-1.
`socketBufferSize` | + | + | Size of the socket buffer. Needed on some Linux machines to fix performance degradation.
`charSet` | + | + | Character set for the connection. Similar to `encoding` property, but accepts Java names instead of Firebird ones.
`encoding` | + | + | Character encoding for the connection. See Firebird documentation for more information.
`userName` | + | + | Name of the user that will be used by default.
`password` | + | + | Corresponding password.
`roleName` | + | + | SQL role to use.
`tpbMapping` | + | + | TPB mapping for different transaction isolation modes.
`nonStandardProperty` | - | + `setNonStandardProperty(String)` `setNonStandardProperty(String, String)` | Allows to set any valid connection property that does not have corresponding setter method. Two setters are available: `setNonStandardProperty(String)` method takes only one parameter in form `“propertyName[=propertyValue]”`, this allows setting non-standard parameters using configuration files. `setNonStandardProperty(String, String)` takes property name as first parameter, and its value as the second parameter.
Pool runtime properties
`freeSize` | + | - | Tells how many free connections are in the pool. Value is between 0 and `totalSize`.
`workingSize` | + | - | Tells how many connections were taken from the pool and are currently used in the application.
`totalSize` | + | - | Total size of open connection. At the pool creation – 0, after obtaining first connection – between `minPoolSize` and `maxPoolSize`.
`connectionCount` | + | - | Deprecated. Same as `freeSize`.

### org.firebirdsql.pool.DriverConnectionPoolDataSource

This class provides connection and statement pooling capabilities for arbitrary any JDBC driver. It is very similar to the previous class, however instead of allocating physical connections via JayBird JCA framework, it uses `java.sql.DriverManager`. It also does not implement `javax.sql.XADataSource` interface and pooled connections cannot participate in distributed JTA transactions.

Property | Getter | Setter | Description
--- | --- | --- | ---
Class-specific properties
`driverClassName` | + | + | Name of the JDBC driver class.
`jdbcUrl` | + | + | JDBC URL that will be used to allocate connections.
`property` | `getProperty(String)` | `setProperty(String, String)` | Allows setting additional connection properties.

Note that this class does not provide getters and setters for standard propertied like user name, password, etc. This might be viewed as a limitation/inconvenience, however we believe that if there is situation where `DriverConnectionPoolDataSource.setProperty(String, String)` cannot be used, one can easily create a subclass providing all necessary setters.

### org.firebirdsql.pool.FBWrappingDataSource

This class is a wrapper for `FBConnectionPoolDataSource` converting interface from `javax.sql.ConnectionPoolDataSource` to `javax.sql.DataSource`. It defines same properties as `FBConnectionPoolDataSource` does.