13 October 2009

How to use JDBC with Oracle Database in JBoss

This article is a quick and dirty yet complete article about how to setup Oracle as a DataSource for using in JDBC-based application with session beans in JBoss 4.2 .

First you need to add this file to : $JBOSS_HOME/server/default/deploy
File: oracle-ds.xml
<?xml version="1.0" encoding="UTF-8"?>
<!-- The jndi name of the DataSource, it is prefixed with java:/ -->
<!-- Datasources are not available outside the virtual machine -->
<!-- I am using oracle 10g, change the url to suite you, try and tell me as the only version of oracle I tired was 10g -->

<!-- The minimum connections in a pool/sub-pool. Pools are lazily constructed on first use -->

<!-- The maximum connections in a pool/sub-pool -->


The rest of work is in your session bean code, suppose we have a bean that is called BidManagerBean, here's the code:
import org.jboss.resource.adapter.jdbc.WrappedConnection;
import oracle.jdbc.OracleConnection;

// import other staff ...

public class ItemManagerBean {
// mapped name is the name in jndi-name tag in oracle-ds.xml
@Resource(name="ds/AnyNameSuchAsYourAppName", mappedName="java:/OracleDS")
private DataSource datasource;
private OracleConnection oraConnection;

private Logger logger = Logger.getLogger(ItemManagerBean.class);

// this method is the post construct method (i.e. called before any other method is being callded, after the bean inited and DI injected)
public void init() {
try {
logger.info("start post consturct method");

Connection inconnection = datasource.getConnection();
WrappedConnection wConn = (WrappedConnection) inconnection;
Connection underlyingConn = wConn.getUnderlyingConnection();
oraConnection = (OracleConnection) underlyingConn;

}catch(SQLException ex) {

public void destroy() {
try {
logger.info("start pre destroy method");
oraConnection = null;
}catch (SQLException ex) {
// testing the connection by submiting a simple query
public List<Item> getAllItems() throws ItemsNotFoundException{
List<Item> itemList = null;
Statement stmt = null;
ResultSet rs = null;
try {
stmt = oraConnection.createStatement();
rs = stmt.executeQuery(sql);
itemList = constructItemList(rs);

}catch (SQLException ex) {
throw new ItemsNotFoundException(ex);
}finally {
try {
}catch (Throwable ignore) {}
return itemList;

You are done.
Resources : http://www.jboss.org/community/wiki/setUpAOracleDataSource
Post a Comment