JPA SQLServer Connection Pool using Instance Name

Creating a connection pool in Glassfish is fairly simple, as I have described in my previous post. All you need to do is to specify the URL and the Port number where the database engine is listening for the incoming connections in the JPA configuration file, i.e. sun-resources.xml.

In my previous post I used the port number of the database server instance to connect to the database. Sometimes, specially in the clustered enviournment it is not possible for you to specify the port number and you need to use the instance name to communicate with the server.

Doing that is fairly simple. All you need to do is to replace the portNumber property with the instanceName property in the configuration file. You also need to change the Database Connection URL and replae the portNumber with the instanceName. The resulting configuration will look something like this:

<jdbc-connection-pool name="MSSQL_POOL_TEST" 
                       datasource-classname="com.microsoft.sqlserver.jdbc.SQLServerDataSource" 
                       lazy-connection-enlistment="false" match-connections="false" 
                       max-pool-size="32" pool-resize-quantity="2" res-type="javax.sql.XADataSource" 
                       statement-timeout-in-seconds="-1" steady-pool-size="8"> 
         <property name="serverName" value="localhost" /> 
         <property name="instanceName" value="DBInstance" /> 
         <property name="databaseName" value="TestDB" /> 
         <property name="User" value="sa" /> 
         <property name="Password" value="5QLPWD" /> 
         <property name="URL" 
                      value="jdbc:sqlserver://localhost;instanceName=DBInstance;databaseName=TestDB" /> 
         <property name="driverClass" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" /> 
</jdbc-connection-pool>

Your connection pool is now using the instance name and as a result if the cluster management engine ever need to make any changes to the port numbers, your connection pool will not be effected by that.

Using Eclipselink with Glassfish

Using Eclipselink as your JPA provider is fairly simple in Glassfish V2. All you need to do to be able to create a database access object is to place the eclipse link jar files in the Glassfish library folder and create/update just two xml files, i.e. persistance.xml and sun-resources.xml. Here it how it goes.

Sample persistance.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0" 
  xmlns="http://java.sun.com/xml/ns/persistence"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
            http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">
  <persistence-unit name="MSSQL_POOL_TEST" transaction-type="JTA">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <jta-data-source>MSSQL_POOL_TEST</jta-data-source>
    <properties>
      <property name="eclipselink.logging.level" value="FINE" />
      <property name="eclipselink.target-server" value="SunAS9" />
    </properties>
  </persistence-unit>
</persistence>

This is a very simple file containing only one persistance unit. The persistance unit name MSSQL_POOL_TEST is the JNDI name of the connection pool that the persistance provider will use. The actual connection pool is defined in the sun-resources.xml file. The transaction type I am using here is default JPA transaction.

Sample sun-resources.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE resources PUBLIC 
"-//Sun Microsystems, Inc.//DTD Application Server 9.0 Resource Definitions //EN"
      "http://www.sun.com/software/appserver/dtds/sun-resources_1_3.dtd">
<resources>
    <jdbc-resource enabled="true" jndi-name="MSSQL_POOL_TEST"
               object-type="user" pool-name="MSSQL_POOL_TEST" />
    <jdbc-connection-pool name="MSSQL_POOL_TEST"
        allow-non-component-callers="false" associate-with-thread="false"
        connection-creation-retry-attempts="0"
        connection-creation-retry-interval-in-seconds="10"
        connection-leak-reclaim="false" connection-leak-timeout-in-seconds="0"
        connection-validation-method="auto-commit"
        datasource-classname="com.microsoft.sqlserver.jdbc.SQLServerDataSource"
        fail-all-connections="false" idle-timeout-in-seconds="300"
        is-connection-validation-required="true"
        is-isolation-level-guaranteed="true" lazy-connection-association="false"
        lazy-connection-enlistment="false" match-connections="false"
        max-connection-usage-count="0" max-pool-size="32"
        max-wait-time-in-millis="60000" non-transactional-connections="true"
        pool-resize-quantity="2" res-type="javax.sql.XADataSource"
        statement-timeout-in-seconds="-1" steady-pool-size="8"
        validate-atmost-once-period-in-seconds="0" wrap-jdbc-objects="false">
        <property name="serverName" value="localhost" />
        <property name="portNumber" value="28150" />
        <property name="databaseName" value="TestDB" />
        <property name="User" value="sa" />
        <property name="Password" value="5QLPWD" />
        <property name="URL"
                value="jdbc:sqlserver://localhost:28150;databaseName=TestDB" />
        <property name="driverClass" 
                value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
    </jdbc-connection-pool>
</resources>

That's it....

Your SQLServer Connection Pool is now created with a persistance context. All you need now is the jar files for the database drivers and eclipse link in the Glassfish lib folder or any other location in your classpath. I am referring to the Glassfish lib folder only because of ease of use when you try it yourself for the first time.

Most of the properties of the connection pool are selfexplanatory, however, I will try to provide further explanation of all of these properties in a saparate post sometime.