Create a connection pool in glassfish

Original article: http://purebit.net/archiver/showtopic-50.aspx

 

SmartWater-9:10:00

Creating JDBC connection pool & Resource in 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
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 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 clijdbc-connection-pool can be created

[Copy to clipboard]

Code:

As_install_root/bin/asadmin
Create-jdbc-connection-pool -- export ceclassname
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

[Copy to clipboard]

Code:

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 the connection pool

[Copy to clipboard]

Code:

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.

[Copy to clipboard]

Code:

As_install_root/bin/asadmin list * Oracle-pool *
Domain. Resources. jdbc-connection-pool.oracle-pool

List the properties of Connection Pool

[Copy to clipboard]

Code:

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

[Copy to clipboard]

Code:

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

[Copy to clipboard]

Code:

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 templatessun-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_performance.xml" as oracle-resource.xml and modify the URL, user, password values.

[Copy to clipboard]

Code:

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 programmaticallyusing 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]

[Copy to clipboard]

Code:

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 = 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/jaradish ");
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> using ing connection pool

Administration Guide> deployments for specific drivers

AMX