client-java.sources.src.etc.connection_pool.html Maven / Gradle / Ivy
Connection pooling with JayBird 1.5
Roman Rokytskyy, Firebird Foundation
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 javax.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.