creating jdbc connection pool & resource in GlassFish

 

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

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

common-tasks

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

new jdbc connection pool

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


pool-properties

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

pool 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.

ping jdbc connection pool

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

new jdbc resource
i) Press New

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

new jdbc resource


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

resource-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

 

Comments:

Hi guys. Could i use this code for GlassFish v2? Regards F.

Posted by Foli on June 01, 2007 at 07:46 AM IST #

Hi
I am trying to create a connection pool on Glassfish V2.
Where do I mention the JAR file while configuring the connection pool ?
Where should I amend the CLASSPATH where server can find the JAR file containing Oracle JDBC drivers?
Please let me know.
Thanks in advance,
amit

Posted by amit on October 09, 2007 at 07:27 PM IST #

Thanks for tutorial

Posted by nuboat on February 26, 2008 at 01:01 AM IST #

my problem is the utf-8 with spanish language into SELECT \* FROM comun WERE nombre = X.
in snipped code

NC=(Comun) em.createNamedQuery("Comun.findByNombre")
.setParameter("nombre",tk)
.getSingleResult();
Existe = true;
em.clear();

if X = 'Ano' OR X = 'Coche' It's works ok.
so if X = 'Año' OR X = 'Cóche' NO works. ('año' & 'Cóche' are into table, of course)

All parametes in mysql.cnf is 'utf-8' ON.

i'm desesperated.

Posted by anarsoft on August 04, 2008 at 09:50 AM IST #

Hi!!

I have done the configuration for MySQL programmattically, but my implementation done for Oracle doesn't work. I think I have to introduce a new type of resource, especifying what my Database Vendor is. I think it should be very similar to the example above, something like:

map.put("ResType", "javax.sql.ConnectionPoolDataSource");

But intead of ResType I should put something like "VendorType" or "databaseVendor" and I don't know the exact keyword for this.

If somebody knows the answer or could tell me how can I find it it would be very useful!!

Thank you all!!

Posted by Pakito on April 23, 2009 at 09:53 AM IST #

Hi, there is any way to reset the connection pool? i mean, kill all conections.

Posted by pablo bullian on November 16, 2009 at 05:51 PM IST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

jagadish

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today