X

Learn Tips and Best Practices from the Oracle JDBC Development

  • March 24, 2017

Using Universal Connection Pool (UCP) as a Pool Datasource in JBoss 7.0 EAP

In a previous article we detailed the steps to configure Universal Connection Pooling (UCP) running in JBoss AS 6.1 as a singleton service. This service allows consumers to acquire database connections with all the intrinsic benefits of UCP (also detailed in the previous article):

. Runtime Connection Load Balancing (RCLB)
. Fast Connection Failover (FCF)
. Transaction Affinity
. Built-in support for Database Resident Connection Pooling (DRCP) and Application Continuity (AC)

In this article we are going to take a more generic approach -avoiding the usage of a wrapping service- to directly use UCP as a Pool Data Source defined in JBoss' configuration. We are also going to build a basic Servlet to retrieve the pool through JNDI and test it through an http request. We will deploy the web application in an updated version of JBoss (JBoss 7.0 EAP).

1. First step is the same as in the previous article which is downloading JBoss 7.0 EAP https://developers.redhat.com/products/eap/download

2. Also as in the previous article, optionally download JBoss Developer Studio https://developers.redhat.com/products/devstudio/download

3. Configure JBoss 7.0 EAP as a server in JBoss Developer Studio (Servers / New Server / Red Hat JBoss Enterprise Application Platform 7.0 and select installation directory)

4. Install UCP. JDBC driver and ONS as a module. First you need to download ucp.jar, ons.jar and ojdbc8.jar from OTN: https://www.oracle.com/technetwork/database/application-development/jdbc/downloads/jdbc-ucp-19c-5460552.html. Then you can either create the module using JBoss Command Line Interface (jboss-cli) with the following command:

	JBOSS_HOME/bin/jboss-cli.sh -c
	module add --name=com.oracle.ucp \
	  --resources=~/Downloads/ucp.jar:~/Downloads/ojdbc8.jar:~/Downloads/ons.jar \
	  --dependencies=javax.api,javax.transaction.api

Or you can manually create the file JBOSS_HOME/modules/com/oracle/ucp/main/module.xml with the following content. If you executed this step with jboss-cli, you can use the following xml excerpt as a validation.

<?xml version="1.0" ?>
<
module xmlns="urn:jboss:module:1.1" name="com.oracle.ucp" slot="main">
    <
resources>
        <
resource-root path="ucp.jar"/>
        <
resource-root path="ons.jar"/>
        <
resource-root path="ojdbc8.jar"/>
    </
resources>
    <dependencies>
        <module name="javax.api"/>
        <
module name="javax.transaction.api"/>
    </
dependencies>
</
module>
In the same folder you should drop ucp.jar, ons.jar and ojdbc8.jar.

5. Create the driver reference. In this step you can also use jboss-cli executing the following command:

	JBOSS_HOME/bin/jboss-cli.sh -c
	/subsystem=datasources/jdbc-driver=oracle-ucp:\
	  add(driver-name=oracle-ucp, driver-module-name=com.oracle.ucp, \
		  driver-xa-datasource-class-name=oracle.ucp.jdbc.PoolXADataSourceImpl, \
		  driver-datasource-class-name=oracle.ucp.jdbc.PoolDataSourceImpl)

Or you can manually edit JBOSS_HOME/standalone/configuration/standalone.xml adding the following driver under subsystem/datasources/drivers (notice module name should be the same as defined in previous step). If you executed this step using jboss-cli you can validate the result by looking at this file.

<driver name="oracle-ucp" module="com.oracle.ucp">
    <
xa-datasource-class>oracle.ucp.jdbc.PoolXADataSourceImpl</xa-datasource-class>
    <
datasource-class>oracle.ucp.jdbc.PoolDataSourceImpl</datasource-class>
</
driver>

6. Create the datasource. Just like in the previous steps, you can create it using jboss-cli issuing the following command:

	JBOSS_HOME/bin/jboss-cli.sh -c
	data-source add --name=mypool --jndi-name="java:/datasources/mypool" \
	  --connection-properties=InactiveConnectionTimeout=0,PropertyCycle=900,\
		ValidateConnectionOnBorrow=true,MaxPoolSize=40,MinPoolSize=0,\
		FastConnectionFailoverEnabled=true,AbandonedConnectionTimeout=0,\
		URL=jdbc:oracle:thin:@myhost:5521:mysid,ConnectionPoolName=mypool,\
		ConnectionWaitTimeout=0,TimeToLiveConnectionTimeout=0,\
		Password=tiger,User=scott,\
		ConnectionFactoryClassName=oracle.jdbc.pool.OracleDataSource \
	  --driver-name=oracle-ucp \ 
	  --min-pool-size=0 \
	  --max-pool-size=20 \
	  --allow-multiple-users=false \
	  --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker \
	  --validate-on-match=true \
	  --use-fast-fail=true \
	  --stale-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.oracle.OracleStaleConnectionChecker \
	  --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter

Or you can manually edit JBOSS_HOME/standalone/configuration/standalone.xml adding the following datasource under subsystem/datasources (the most important attribute to notice is the datasource-class, in which we inject UCP Pool Data Source). Just like in the previous steps, you can validate jboss-cli execution by looking at this file.

<datasource jndi-name="java:/datasources/mypool" pool-name="mypool" enabled="true">
    <
connection-url>jdbc:oracle:thin:@myhost:5521:mysid</connection-url>
    <
datasource-class>oracle.ucp.jdbc.PoolDataSourceImpl</datasource- class>
    <
connection-property name="InactiveConnectionTimeout">0</connection- property>
    <
connection-property name="PropertyCycle">900</connection-property>
    <
connection-property name="ValidateConnectionOnBorrow">true</connection-property>
    <
connection-property name="MaxPoolSize">40</connection-property>
    <
connection-property name="MinPoolSize">0</connection-property>
    <
connection-property name="FastConnectionFailoverEnabled">true</connection-property>
    <
connection-property name="AbandonedConnectionTimeout">0</connection-property>
    <
connection-property name="URL">jdbc:oracle:thin:@myhost:5521:mysid</connection-property>
    <
connection-property name="ConnectionPoolName">mypool</connection-property>
    <
connection-property name="ConnectionWaitTimeout">0</connection-property>
    <
connection-property name="TimeToLiveConnectionTimeout">0</connection-property>
    <
connection-property name="Password">tiger</connection-property>
    <
connection-property name="User">scott</connection-property>
    <
connection-property name="ConnectionFactoryClassName">
        
oracle.jdbc.pool.OracleDataSource</connection-property>
    <
driver>oracle-ucp</driver>
    <
pool>
        <
min-pool-size>0</min-pool-size>
        <
max-pool-size>20</max-pool-size>
        <
allow-multiple-users>false</allow-multiple-users>
    </
pool>
    <
validation>
        <
valid-connection-checker class-   name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/>
        <
validate-on-match>true</validate-on-match>
        <
use-fast-fail>true</use-fast-fail>
        <
stale-connection-checker class- name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleStaleConnectionChecker"/>  
        <
exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"/>
    </
validation>
</
datasource>

7. At this step you can already test the datasource in the web console. Start the application server by running JBOSS_HOME/bin/standalone.sh, or Right click on the server and “Start”.

8. Open the console in http://localhost:9990 and execute “Configuration / Subsystems / Datasources / Non – XA / MyPool / View / Connection / Test Connection” (you can also correlate here all the values you manually entered in the configuration file).

9. Create the Servlet. From JBoss Developer Studio execute “New / Project / Dynamic Web Project “ and end with default options. Be aware that the name of the project -that would be by default the name of the deployed war- is going to be the same as the relative path to your web app. You can do this step without JBoss Developer Studio, by just compiling below servlet and deploying it in JBoss EAP 7.0 manually wrapped in a war file with a web.xml descriptor.

package sample;
import
java.io.IOException;
import
java.io.PrintWriter;
import
java.sql.Connection;
import
java.sql.ResultSet;
import
java.sql.SQLException;
import
java.sql.Statement;
import
javax.naming.Context;
import
javax.naming.InitialContext;
import
javax.naming.NamingException;
import
javax.servlet.ServletException;
import
javax.servlet.annotation.WebServlet;
import
javax.servlet.http.HttpServlet;
import
javax.servlet.http.HttpServletRequest;
import
javax.servlet.http.HttpServletResponse;
import
javax.sql.DataSource;
// URL to reach the Servlet

@WebServlet("/OracleUCPJBoss")
public class OracleUCPJBoss extends HttpServlet {
  private
static final long serialVersionUID = 1L;
 // Pool Datasource reference, to be instantiated at init
  private DataSource ds = null;
  // Retrieve Datasource reference using JNDI
  @Override
  public
void init() throws ServletException {
    Context
initContext;
    try
{
      initContext
= new InitialContext();ds = (DataSource) initContext.lookup("java:/datasources/mypool");
    } catch (NamingException e) {
      e.printStackTrace();
    }
  }

  // GET request handling
  protected void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
    // Retrieve connection from the pool
    try (Connection conn = ds.getConnection(); Statement st = conn.createStatement()) {
      // Initialize output and retrieve parameters
      PrintWriter pw = response.getWriter();
      String job = request.getParameter("job");
      ResultSet rs = null;
      // List employees. If job parameter is sent, filter this list
      if (job == null) {
        rs = st.executeQuery("select empno, ename, job from emp");
      } else {
        rs = st.executeQuery("select empno, ename, job from emp where job = '" + job + "'");
      }
      // Show list on browser
      while (rs.next()) {
        pw.println(rs.getString("empno") + " - " + rs.getString("ename") + " - " + rs.getString("job"));
      }
      // Debug info
      pw.println("Served at: " + request.getContextPath());
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
  // Re-route any Post request
  protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
    doGet(request, response);
  }
}

10. Deploy the web application: right click on the project and execute “Run as / Run on Server” and select the JBoss EAP 7.0 server (or just copy the compiled war to JBOSS_HOME/standalone/deployments). Test the servlet by opening an browser on: http://localhost:8080/OracleUCPDS/OracleUCPJBoss

And to test the filter:

http://localhost:8080/OracleUCPDS/OracleUCPJBoss?job=CLERK


 

 

 

 

 

 

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.