JBoss and Oracle XE

JBoss and Oracle XE (Express Edition) make for an interesting, and cost effective, J2EE stack for non-mission critical, small scale, software applications. Getting JBoss and Oracle XE to work together is really straight forward.

Resolve port conflicts
By default, JBoss and Oracle XE both use port 8080. So, when using JBoss and Oracle XE on the same machine, something has got to give. As one is most likely to be using and interacting with the container more, it might as well be the database that has to adapt. To change the default Oracle XE HTTP Listening port (8080) to 9090 do the following:

1. Start sqlplus (Run SQL Command Line icon in windows)
2. Enter ‘connect / sysdba’
3. Enter ‘exec dbms_xdb.sethttpport(9090);’
4. Stop and then Start the Oracle XE database

Note that this does not change the default TNS Listening port 1521, although the short cut (/app/oracle/product/10.2.0/server/Database_homepage.url) to launch the Database home page will need to be updated.

Setup DataSource
The Oracle XE installation comes with a sample HR database. A DataSource in JBoss can be defined for it, but first the Oracle JDBC library needs to be in the JBoss server classpath. Copy ojdbc14.jar from /app/oracle/product/10.2.0/server/jdbc/lib to /server/default/lib. Now the DataSource can be defined. DataSources are ‘deployed’ in JBoss. Their configuration information is in an XML file with a name that ends in ‘-ds’.

In /server/default/deploy create a file called oraclexe-ds.xml with the following content:

   
      
      
      OracleXEDS

      jdbc:oracle:thin:@127.0.0.1:1521:XE

      oracle.jdbc.driver.OracleDriver
      hr
      hr

      5
      100

      
      org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter
   

Remember that in Unlocking the Sample User Account step in Oracle XE Getting Started Guide, the HR account password is set to ‘hr’. The console for JBoss should display something like:

[ConnectionFactoryBindingService] Bound ConnectionManager 'jboss.jca:service=DataSourceBinding,name=OracleXEDS' to JNDI name 'java:OracleXEDS'

Testing DataSource witha JDBC client
A simple JSP, in an expanded WebApp, can be used to test the DataSource. In /server/default/deploy create a directory called ‘jdbcclient.war’. In that directory create a file called ‘client.jsp’ and add the following content:

<%@page contentType="text/html"
import="java.util.*,javax.naming.*,javax.sql.DataSource,java.sql.*"
%>
<%

DataSource ds = null;
Connection con = null;
PreparedStatement pr = null;
InitialContext ic;
try {
ic = new InitialContext();
ds = (DataSource)ic.lookup( "java:/OracleXEDS" );
con = ds.getConnection();
pr = con.prepareStatement("SELECT EMPLOYEE_ID, LAST_NAME FROM EMPLOYEES");
ResultSet rs = pr.executeQuery();
while (rs.next()) {
out.println("
" +rs.getString("EMPLOYEE_ID") + " | " +rs.getString("LAST_NAME"));
}
rs.close();
pr.close();
}catch(Exception e){
out.println("Exception thrown " +e);
}finally{
if(con != null){
con.close();
}
}
%>

Open your browser and point it to http://localhost:8080/jdbcclient/client.jsp. A list of employee numbers and last names get displayed. This ‘jdbcclient’ approach is used in the JBoss DataSource tutorials.