Jagadish's Blog

 

Connection Pool manages a pool of connections and serves the user requests quickly as creating a new
connection for each user request will take longer time. Since the connections are pooled, it is possible
that a connection may become invalid or stale, i.e., database server may set the connection state to
closed because of network failure or database might have been restarted. In such scenario, if the application
retrieves a connection from connection pool, failure will occur indicating that the connection is no more valid.


To circumvent stale connections issue, connection pool property "is-connection-validation-required" can be set
to true. This flag indicates the connection pool to validate the connection for every request by the application.
Validating a connection ensures that the connection is in healthy state. If the connection validation fails,
connection will be removed from the pool and the application will get valid connection. [Pool will either return the
next valid connection or create a new connection]. Thus validation ensures that every getConnection() request
by the application  will be served by a valid connection.

To set validation :
eg: DerbyPool

Following command will list resources by name "DerbyPool"

AS_INSTALL_ROOT/bin/asadmin list "*DerbyPool*" domain.resources.jdbc-connection-pool.DerbyPool

To get the list of connection pool attributes and properties,

AS_INSTALL_ROOT/bin/asadmin get domain.resources.jdbc-connection-pool.DerbyPool.* ... ... domain.resources.jdbc-connection-pool.DerbyPool.connection-validation-method = auto-commit ... ... domain.resources.jdbc-connection-pool.DerbyPool.is-connection-validation-required = false domain.resources.jdbc-connection-pool.DerbyPool.validation-table-name =

 To enable connection validation :

AS_INSTALL_ROOT/bin/asadmin set domain.resources.jdbc-connection-pool.DerbyPool.is-connection-validation-required=true domain.resources.jdbc-connection-pool.DerbyPool.is-connection-validation-required = true

By default, the validation mode is "auto-commit". Auto-commit property of the connection to be validated
will be set & reset by the validation logic. Most of the jdbc-drivers cache the "auto-commit" value of the
connection and hence actual validation (connecting to database) does not happen.

Instead, setting table-based validation will ensure that validation happens properly. A valid table name need to
be provided, validation logic will issue a query on this table. If the connection is stale, this query will fail and
hence will be removed from the pool.

To set Table-based validation :

AS_INSTALL_ROOT/bin/asadmin set domain.resources.jdbc-connection-pool.DerbyPool.connection-validation-method=table domain.resources.jdbc-connection-pool.DerbyPool.connection-validation-method = table bin/asadmin set domain.resources.jdbc-connection-pool.DerbyPool.validation-table-name=sys.systables domain.resources.jdbc-connection-pool.DerbyPool.validation-table-name = sys.systables

Sometimes, database restart may result in all the connections in the pool to be invalid.
In such a case, first get connection after restart will result in validating a connection, removing it from the pool,
validating the next connection and so on. Eventually all connections will be validated, removed from the pool
and pool will be reinitialized.

Following property "fail-all-connections" will ensure that when one of the connections become valid, pool will be
reinitialized, ie., all connections will be destroyed and initialized again.

AS_INSTALL_ROOT/bin/asadmin set domain.resources.jdbc-connection-pool.DerbyPool.fail-all-connections=true domain.resources.jdbc-connection-pool.DerbyPool.fail-all-connections = true

Above property need to be set only in cases where there are more possibilities of database restarts/failures, as
all connections are dropped and recreated for a single connection validation failure.

References

Application Server - Reference Manual > create-jdbc-connection-pool


glassfish

 

 JDBC Connection Pool & Resource can be created in multiple ways.

• Admin Console
• CLI
• Using sun-resources.xml templates
• Creating Connection Pool & Resource programmatically

1) Admin Console

Admin console provides user intuitive interface to create a connection pool & resource for database access.
a) Copy the jdbc driver that will be used for database connectivity, to GLASSFISH_INSTALL/domains/<domainname>/lib/ext or
add the jdbc-driver path to "classpath-prefix" and restart application server.

b) Using Admin console, Common Tasks > create jdbc connection pool



c) Provide a name for the connection pool, select the resource type, select the database vendor and press Next



d) Console will list the the default pool settings and also the properties of datasource class provided by the vendor.




e) Fill the values for datasource properties, eg: servername, user, password, URL, databasename etc., and press Finish
   
f) Pool will be created



g) To check whether the values provided for datasource for database connectivity are valid and works fine, press Ping.
Ping will use these properties and try to establish a connection.



This connection pool need to be associated with a jdbc-resource.

h) To create a jdbc-resource that will be used by the application :
Resources > Jdbc Resources


i) Press New

j) provide the jndi name for the resource and the pool with which the resource need to be associated.




k) Press OK, jdbc-resource will be created.

2) Using CLI

jdbc-connection-pool can be created as

AS_INSTALL_ROOT/bin/asadmin create-jdbc-connection-pool --datasourceclassname oracle.jdbc.pool.OracleDataSource --restype javax.sql.DataSource --property user=dbuser:password=dbpassword:url="jdbc\:oracle\:thin\:@localhost\:1521\:ORCL" oracle-pool Command create-jdbc-connection-pool executed successfully.

jdbc-resource can be created as

AS_INSTALL_ROOT/bin/asadmin create-jdbc-resource --connectionpoolid oracle-pool jdbc/oracle-resource Command create-jdbc-resource executed successfully.

To test whether the connection settings are correct, ping connection pool

AS_INSTALL_ROOT/bin/asadmin ping-connection-pool oracle-pool Command ping-connection-pool executed successfully.

Properties of connection pool can be read/changed as follows.

AS_INSTALL_ROOT/bin/asadmin list *oracle-pool* domain.resources.jdbc-connection-pool.oracle-pool

List the properties of connection pool

AS_INSTALL_ROOT/bin/asadmin get domain.resources.jdbc-connection-pool.oracle-pool.* domain.resources.jdbc-connection-pool.oracle-pool.allow-non-component-callers = false domain.resources.jdbc-connection-pool.oracle-pool.associate-with-thread = false domain.resources.jdbc-connection-pool.oracle-pool.connection-creation-retry-attempts = 0 domain.resources.jdbc-connection-pool.oracle-pool.connection-creation-retry-interval-in-seconds = 10 domain.resources.jdbc-connection-pool.oracle-pool.connection-leak-reclaim = false domain.resources.jdbc-connection-pool.oracle-pool.connection-leak-timeout-in-seconds = 0 domain.resources.jdbc-connection-pool.oracle-pool.connection-validation-method = auto-commit domain.resources.jdbc-connection-pool.oracle-pool.datasource-classname = oracle.jdbc.pool.OracleDataSource domain.resources.jdbc-connection-pool.oracle-pool.description = domain.resources.jdbc-connection-pool.oracle-pool.fail-all-connections = false domain.resources.jdbc-connection-pool.oracle-pool.idle-timeout-in-seconds = 300 domain.resources.jdbc-connection-pool.oracle-pool.is-connection-validation-required = false domain.resources.jdbc-connection-pool.oracle-pool.is-isolation-level-guaranteed = true domain.resources.jdbc-connection-pool.oracle-pool.lazy-connection-association = false domain.resources.jdbc-connection-pool.oracle-pool.lazy-connection-enlistment = false domain.resources.jdbc-connection-pool.oracle-pool.match-connections = false domain.resources.jdbc-connection-pool.oracle-pool.max-connection-usage-count = 0 domain.resources.jdbc-connection-pool.oracle-pool.max-pool-size = 32 domain.resources.jdbc-connection-pool.oracle-pool.max-wait-time-in-millis = 60000 domain.resources.jdbc-connection-pool.oracle-pool.name = oracle-pool domain.resources.jdbc-connection-pool.oracle-pool.non-transactional-connections = false domain.resources.jdbc-connection-pool.oracle-pool.pool-resize-quantity = 2 domain.resources.jdbc-connection-pool.oracle-pool.property.password = dbpassword domain.resources.jdbc-connection-pool.oracle-pool.property.url = jdbc:oracle:thin:@localhost:1521:ORCL domain.resources.jdbc-connection-pool.oracle-pool.property.user = dbuser domain.resources.jdbc-connection-pool.oracle-pool.res-type = javax.sql.DataSource domain.resources.jdbc-connection-pool.oracle-pool.statement-timeout-in-seconds = -1 domain.resources.jdbc-connection-pool.oracle-pool.steady-pool-size = 8 domain.resources.jdbc-connection-pool.oracle-pool.transaction-isolation-level = domain.resources.jdbc-connection-pool.oracle-pool.validate-atmost-once-period-in-seconds = 0 domain.resources.jdbc-connection-pool.oracle-pool.validation-table-name = domain.resources.jdbc-connection-pool.oracle-pool.wrap-jdbc-objects = false

Changing a property

AS_INSTALL_ROOT/bin/asadmin set domain.resources.jdbc-connection-pool.oracle-pool.steady-pool-size=20 domain.resources.jdbc-connection-pool.oracle-pool.steady-pool-size = 20

AS_INSTALL_ROOT/bin/asadmin get domain.resources.jdbc-connection-pool.oracle-pool.steady-pool-size    domain.resources.jdbc-connection-pool.oracle-pool.steady-pool-size = 20

3) Using sun-resources.xml templates

sun-resources.xml can be used to create jdbc-connection-pool & resources.
Templates for various databases will be available in GLASSFISH_INSTALL/lib/install/templates/resources/jdbc
Copy and modify the templates with appropriate values.

eg:
copy "oracle_type4_datasource.xml"  as oracle-resource.xml and modify the URL, user, password values.

GLASSFISH_INSTALL/bin/asadmin add-resources GLASSFISH_INSTALL/lib/install/templates/resources/jdbc/oracle-resource.xml  ========================= Added Resource Type: jdbc-connection-pool  ========================= Added Resource Type: jdbc-resource Command add-resources executed successfully.

4) Creating Connection Pool & Resource programmatically

Using AMX (Application Server Management Extenstions) resource/pool can be created. Following snippet
will create a connection pool, resource and resource-ref for the "server" instance.
[needs appserv-ext.jar in classpath]

import java.util.Map; import java.util.HashMap; import com.sun.appserv.management.client.AppserverConnectionSource; import com.sun.appserv.management.DomainRoot; import com.sun.appserv.management.config.*; public class ResourceCreator {     public static final String hostName = "localhost";     public static final int JMX_PORT = 8686;     public static final String user = "admin";     public static final String password = "adminadmin";     public static void main(String[] args) throws Exception {         AppserverConnectionSource appserver =                 new AppserverConnectionSource(AppserverConnectionSource.PROTOCOL_RMI, hostName, JMX_PORT, user, password, null);         String connPoolName = "mysql-con-pool";         String resourceName = "jdbc/mysql-resource";         DomainRoot domainRoot = appserver.getDomainRoot();         //check whether the con.pool already exists, else, create one         Map<String, JDBCConnectionPoolConfig> conPoolMap = domainRoot.getDomainConfig().getJDBCConnectionPoolConfigMap();         if (conPoolMap.containsKey(connPoolName)) {             System.out.println("JDBC Connection pool : " + connPoolName + " already exists");         } else {             Map<String, String> map = new HashMap<String, String>();             map.put("property.user", "jagadish");             map.put("ResType", "javax.sql.ConnectionPoolDataSource");             map.put("property.password", "jagadish");             map.put("property.databaseName", "jagadish");             map.put("property.url", "jdbc:mysql://localhost:3306/jagadish");             domainRoot.getDomainConfig().createJDBCConnectionPoolConfig(connPoolName, "com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource", map);             System.out.println("JDBC Connection pool : " + connPoolName + " created ! ");         }         //check whether the jdbc resource already exists, else, create one         Map<String, JDBCResourceConfig> resMap = domainRoot.getDomainConfig().getJDBCResourceConfigMap();         if (resMap.containsKey(resourceName)) {             System.out.println("JDBC Resource : " + resourceName + " already exists");         } else {             domainRoot.getDomainConfig().createJDBCResourceConfig(resourceName, connPoolName, null);             System.out.println("JDBC Resource : " + resourceName + " created !");         }         //create a resource-ref for the instance "server"         Map<String, StandaloneServerConfig> map1 = domainRoot.getDomainConfig().getStandaloneServerConfigMap();         {             StandaloneServerConfig sc = map1.get("server");             Map<String, ResourceRefConfig> rm = sc.getResourceRefConfigMap();             if (rm.containsKey(resourceName)) {                 System.out.println(resourceName + " already referenced for the server instance");             } else {                 sc.createResourceRefConfig(resourceName, true);                 System.out.println(resourceName + " reference created for the server instance");             }         }     } }

References :

GlassFish
Administration Guide > configuring connection pool
Administration Guide > configurations for specific drivers
AMX

glassfish