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

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

Blog NameDev2Dev

CATEGORIES
Blogs Home
Blogs Directory
Author Directory
Contact Us
Oracle Cloud Trial:
$300 in Free Credits

Try Oracle Cloud for Free
FRIDAY, MARCH 24, 2017
Share
Facebook
LinkedIn
Twitter
Google Plus
Email
Comment
Using Universal Connection Pool (UCP) as a Pool Datasource in JBoss 7.0 EAP
By: Pablo Silberkasten
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 file (standalone.xml). 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 (link) 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. Let’s assume your JBoss installation directory is JBOSS_HOME (If that is the case you should be able to start the application server by running JBOSS_HOME/bin/standalone.sh, you could also do it by Right click on the server and “Start”). Then you need to download ucp.jar, ons.jar and ojdbc8.jar from OTN: http://www.oracle.com/technetwork/database/features/jdbc/jdbc-ucp-122-3110062.html
In JBOSS_HOME/modules/com/oracle/ucp/main create a module.xml file with the following content:

In the same folder you should drop ucp.jar, ons.jar and ojdbc8.jar.
5. Create the driver reference. In JBOSS_HOME/standalone/configuration/standalone.xml add the following driver under subsystem/datasources/drivers (notice module name should be the same as defined in previous step).

oracle.ucp.jdbc.PoolXADataSourceImpl
oracle.ucp.jdbc.PoolDataSourceImpl

6. Create the datasource. In JBOSS_HOME/standalone/configuration/standalone.xml add the following datasource under subsystem/datasources (the most important attribute to notice is the datasource-class, in which we inject UCP Pool Data Source).

jdbc:oracle:thin:@myhost:5521:mysid
oracle.ucp.jdbc.PoolDataSourceImpl
0
900
true
40
0
true
0
jdbc:oracle:thin:@myhost:5521:mysid
mypool
0
0
tiger
scott

oracle.jdbc.pool.OracleDataSource
oracle-ucp

0
20
false

true
true

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

Join the discussion
Facebook

Twitter

LinkedIn

Google+

YouTube

Instagram

Oracle RSS Feed
Oracle
Integrated Cloud Applications & Platform Services
Site Map Legal Notices Terms of Use Privacy
Cookie Preferences

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.