Table of Contents
Purpose of this tutorial
- Create and test a RAC datasoure with WildFly jboss-cli.sh command utility
- Deploy and integrate the Oracle JDBC driver as a Wildfly Module
- Run a short Web application to display JDBC driver and Oracle Database version
Deploying the Oracle JDBC Driver as a Wildfly module
Prepare module.xml and copy ojdbc7.jar to its destination [oracle@wls1 ~]$ ls /usr/local/wildfly-8.2.0.Final/modules/com/oracle/ojdbc/main/ module.xml ojdbc7.jar [oracle@wls1 ~]$ cat /usr/local/wildfly-8.2.0.Final/modules/com/oracle/ojdbc/main/module.xml ?xml version="1.0" ?> <module xmlns="urn:jboss:module:1.1" name="com.oracle.ojdbc"> <resources> <resource-root path="ojdbc7.jar"/> </resources> <dependencies> <module name="javax.api"/> <module name="javax.transaction.api"/> </dependencies> </module> --> Restart Wildfly and your Datasource and verify the Oracle Driver : [oracle@wls1 WILDFLY]$ $WILDFLY_HOME/bin/jboss-cli.sh --connect /subsystem=datasources:installed-drivers-list { "outcome" => "success", "result" => [ { "driver-name" => "oracle", "deployment-name" => undefined, "driver-module-name" => "com.oracle.ojdbc", "module-slot" => "main", "driver-datasource-class-name" => "", "driver-xa-datasource-class-name" => "oracle.jdbc.xa.client.OracleXADataSource", "driver-class-name" => "oracle.jdbc.OracleDriver", "driver-major-version" => 12, "driver-minor-version" => 1, "jdbc-compliant" => true } Setup RAC datasource by using the RAC SCAN address [oracle@wls1 WILDFLY]$ cat add_ds.cli if (outcome != success) of /subsystem=datasources/data-source=myRacDS:read-resource data-source add \ --name=myRacDS \ --driver-name=oracle \ --jndi-name=java:jboss/datasources/myRacDS \ --user-name="scott"\ --password="tiger"\ --use-java-context=true \ --use-ccm=true \ --min-pool-size=5 \ --max-pool-size=10 \ --pool-prefill=true \ --allocation-retry=1 \ --prepared-statements-cache-size=32 \ --share-prepared-statements=true \ --connection-url="jdbc:oracle:thin:@ract2-scan.grid12c.example.com:1521/banka" end-if [oracle@wls1 WILDFLY]$ $WILDFLY_HOME/bin/jboss-cli.sh --connect --file=add_ds.cli Test the connection [oracle@wls1 WILDFLY]$ $WILDFLY_HOME/bin/jboss-cli.sh --connect /subsystem=datasources/data-source=myRacDS:test-connection-in-pool { "outcome" => "success", "result" => [true] } Check DS properties [oracle@wls1 WILDFLY]$ $WILDFLY_HOME/bin/jboss-cli.sh --connect /subsystem=datasources/data-source=myRacDS:read-resource { "outcome" => "success", "result" => { "allocation-retry" => 1, "allocation-retry-wait-millis" => undefined, "allow-multiple-users" => false, "background-validation" => undefined, "background-validation-millis" => undefined, "blocking-timeout-wait-millis" => undefined, "capacity-decrementer-class" => undefined, "capacity-decrementer-properties" => undefined, "capacity-incrementer-class" => undefined, "capacity-incrementer-properties" => undefined, "check-valid-connection-sql" => undefined, "connection-listener-class" => undefined, "connection-listener-property" => undefined, "connection-properties" => undefined, "connection-url" => "jdbc:oracle:thin:@ract2-scan.grid12c.example.com:1521/banka", "datasource-class" => undefined, "driver-class" => undefined, "driver-name" => "oracle", "enabled" => true, "exception-sorter-class-name" => undefined, "exception-sorter-properties" => undefined, "flush-strategy" => undefined, "idle-timeout-minutes" => undefined, "initial-pool-size" => undefined, "jndi-name" => "java:jboss/datasources/myRacDS", "jta" => true, "max-pool-size" => 10, "min-pool-size" => 5, "new-connection-sql" => undefined, "password" => "tiger", "pool-prefill" => true, "pool-use-strict-min" => undefined, "prepared-statements-cache-size" => 32, "query-timeout" => undefined, "reauth-plugin-class-name" => undefined, "reauth-plugin-properties" => undefined, "security-domain" => undefined, "set-tx-query-timeout" => false, "share-prepared-statements" => true, "spy" => false, "stale-connection-checker-class-name" => undefined, "stale-connection-checker-properties" => undefined, "track-statements" => "NOWARN", "transaction-isolation" => undefined, "url-delimiter" => undefined, "url-selector-strategy-class-name" => undefined, "use-ccm" => true, "use-fast-fail" => false, "use-java-context" => true, "use-try-lock" => undefined, "user-name" => "scott", "valid-connection-checker-class-name" => undefined, "valid-connection-checker-properties" => undefined, "validate-on-match" => false, "statistics" => { "jdbc" => undefined, "pool" => undefined } } } If you need to remove the DS run: $WILDFLY_HOME/bin/jboss-cli.sh --connect /subsystem=datasources/data-source=myRacDS:remove
Test Oracle JDBC driver from a JBOSS WEB application
Run the following JAVA code: try { String clname= "oracle.jdbc.OracleDriver"; Class.forName (clname); setJdbcInfo( StackTraceUtil.add_hmtl_pre_tag( "JDBC Driver Check - Loading Driver class ok : " + clname)); java.util.Properties props = System.getProperties(); java.util.Enumeration propNames = props.propertyNames(); setJdbcInfo(getJdbcInfo() + StackTraceUtil.add_hmtl_pre_tag("JDK Version: " + props.getProperty("java.version"))); setJdbcInfo(getJdbcInfo() + StackTraceUtil.add_hmtl_pre_tag( "ClassPath : " + props.getProperty("java.class.path"))); Connection c = ds1.getConnection(); java.sql.DatabaseMetaData md = c.getMetaData(); setJdbcInfo(getJdbcInfo() + StackTraceUtil.add_hmtl_pre_tag( "Driver Name : "+md.getDriverName())); setJdbcInfo(getJdbcInfo() + StackTraceUtil.add_hmtl_pre_tag( "Driver Version : "+md.getDriverVersion())); setJdbcInfo(getJdbcInfo() + StackTraceUtil.add_hmtl_pre_tag( "Database Product Version: " +md.getDatabaseProductVersion())); c.close(); } catch ( Exception e1) .. Very likely Class.forName (clname); throws the following Exceptions in checkDriver() oracle.jdbc.OracleDriver from [Module "deployment.WF_JdbcDriverTest.war:main" from Service Module Loader] java.lang.ClassNotFoundException: oracle.jdbc.OracleDriver from [Module "deployment.WF_JdbcDriverTest.war:main" from Service Module Loader] at org.jboss.modules.ModuleClassLoader.findClass(ModuleClassLoader.java:213) at org.jboss.modules.ConcurrentClassLoader.performLoadClassUnchecked(ConcurrentClassLoader.java:459) at org.jboss.modules.ConcurrentClassLoader.performLoadClassChecked(ConcurrentClassLoader.java:408) at org.jboss.modules.ConcurrentClassLoader.performLoadClass(ConcurrentClassLoader.java:389) at org.jboss.modules.ConcurrentClassLoader.loadClass(ConcurrentClassLoader.java:134) at java.lang.Class.forName0(Native Method) at java.lang.Class.forName(Class.java:191) at DriverTest.DriverTestBean.checkDriver(DriverTestBean.java:54) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at com.sun.el.parser.AstValue.invoke(AstValue.java:292) at com.sun.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:304) at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105 Note if working with Datasources you may see following stack: Caused by: oracle.ucp.UniversalConnectionPoolException: Error during pool creation in Universal Connection Pool Manager: java.sql.SQLException: Invalid Universal Connection Pool configuration: java.sql.SQLException: Unable to create factory class instance with provided factory class name: java.lang.ClassNotFoundException: oracle.jdbc.pool.OracleDataSource from [Module "deployment.WF_UCPPool.war:main" from Service Module Loader] at oracle.ucp.util.UCPErrorHandler.newUniversalConnectionPoolException(UCPErrorHandler.java:359) at oracle.ucp.util.UCPErrorHandler.throwUniversalConnectionPoolException(UCPErrorHandler.java:58) at oracle.ucp.admin.UniversalConnectionPoolManagerBase.createConnectionPool(UniversalConnectionPoolManagerBase.java:528) at UcpPool.UcpPoolBean.rebindPool(UcpPoolBean.java:289) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
Detailed Problem description:
- Why is Module Loader looking for the JDBC driver ?
- We expect that Wildfly picks up the Oracle JDBC Driver as we have installed the JDBC Driver as a Module
Note if have configured the Oracle JDBC driver as a library in your WAR file the above code works fine .
Solution to integrate the Oracle JDBC Driver with Wildfly
Add a dependency to the Oralce JDBC driver module by adding jboss-deployment-structure.xml under your WEB_INF folder: <?xml version="1.0" encoding="UTF-8"?> <!-- To change this license header, choose License Headers in Project Properties. To change this template file, choose Tools | Templates and open the template in the editor. --> <jboss-deployment-structure> <deployment> <dependencies> <module name="com.oracle.ojdbc" /> </dependencies> </deployment> </jboss-deployment-structure> Rerun the test program - ths JSF output should display the following : JDBC Driver Check - Loading Driver class ok : oracle.jdbc.OracleDriver JDK Version: 1.7.0_71 ClassPath : /usr/local/wildfly-8.2.0.Final/jboss-modules.jar Driver Name : Oracle JDBC driver Driver Version : 12.1.0.2.0 Database Product Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options
JSF code : index.xhtml
<?xml version='1.0' encoding='UTF-8' ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"
xmlns:h="http://xmlns.jcp.org/jsf/html">
<h:head>
<title>JDBC Driver Test</title>
</h:head>
<h:body>
<h:form>
<h:commandButton id="cBnt1" value="JDBC Driver Check" action="#{driverTestBean.checkDriver}"></h:commandButton>
<hr color="blue" size="3" width="90%" align="center"/>
<h:panelGrid columns="6" border="3">
<h:outputText style="color: red" value="JDBC Driver Status :" />
<h:outputText id="optxt1" style="color: blue" value="#{driverTestBean.jdbcInfo}" escape="false" />
</h:panelGrid>
<hr color="blue" size="3" width="90%" align="center"/>
<h:outputText style="color: red" value="Exceptions : " escape="false" />
<br/>
<h:outputText id="optxt2" style="color: red" value="#{driverTestBean.exceptInfo}" escape="false" />
</h:form>
</h:body>
</html>
JAVA Code: DriverTestBean.java
package DriverTest;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.io.Writer;
import java.sql.Connection;
import javax.annotation.Resource;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.sql.DataSource;
/**
*
* @author Helmut Hutzler
*/
@ManagedBean
@SessionScoped
public class DriverTestBean
{
private String exceptInfo="";
private String jdbcInfo=" --> please Press JDBC Driver Check Button";
@Resource(lookup="java:jboss/datasources/myRacDS")
private DataSource ds1;
public String getJdbcInfo()
{
return jdbcInfo;
}
public void setJdbcInfo(String jdbcInfo)
{
this.jdbcInfo = jdbcInfo;
}
public String getExceptInfo()
{
return exceptInfo;
}
public void setExceptInfo(String exceptInfo)
{
this.exceptInfo = exceptInfo;
}
public String checkDriver() throws Exception
{
try
{
String clname= "oracle.jdbc.OracleDriver";
Class.forName (clname);
setJdbcInfo( StackTraceUtil.add_hmtl_pre_tag( "JDBC Driver Check - Loading Driver class ok : " + clname));
java.util.Properties props = System.getProperties();
java.util.Enumeration propNames = props.propertyNames();
setJdbcInfo(getJdbcInfo() + StackTraceUtil.add_hmtl_pre_tag("JDK Version: " + props.getProperty("java.version")));
setJdbcInfo(getJdbcInfo() + StackTraceUtil.add_hmtl_pre_tag( "ClassPath : " + props.getProperty("java.class.path")));
Connection c = ds1.getConnection();
java.sql.DatabaseMetaData md = c.getMetaData();
setJdbcInfo(getJdbcInfo() + StackTraceUtil.add_hmtl_pre_tag( "Driver Name : "+md.getDriverName()));
setJdbcInfo(getJdbcInfo() + StackTraceUtil.add_hmtl_pre_tag( "Driver Version : "+md.getDriverVersion()));
setJdbcInfo(getJdbcInfo() + StackTraceUtil.add_hmtl_pre_tag( "Database Product Version: " +md.getDatabaseProductVersion()));
c.close();
} catch ( Exception e1)
{
generic_exp("Error in checkDriver()", e1);
}
return "index";
}
public void generic_exp(String ip, Exception e1)
{
if ( ip != null)
setExceptInfo( StackTraceUtil.add_hmtl_pre_tag(ip));
if ( e1 != null)
{
setExceptInfo(getExceptInfo() + StackTraceUtil.add_hmtl_pre_tag(e1.getMessage()));
setExceptInfo(getExceptInfo() + StackTraceUtil.add_hmtl_pre_tag( StackTraceUtil.getStackTrace(e1)));
}
}
}
class StackTraceUtil
{
public static String getStackTrace(Throwable aThrowable)
{
final Writer result = new StringWriter();
final PrintWriter printWriter = new PrintWriter(result);
aThrowable.printStackTrace(printWriter);
return result.toString();
}
public static String add_hmtl_pre_tag (String ip)
{
String op = "<pre>" + ip + "</pre>" ;
return op;
}
}
Hi Helmut – thanks for the explanation on how to integrate. Very useful. Had a lot of problems getting this to work until I found this page..