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"?>
<datasources>
<local-tx-datasource>
<!-- The jndi name of the DataSource, it is prefixed with java:/ -->
<!-- Datasources are not available outside the virtual machine -->
<jndi-name>OracleDS</jndi-name>
<!-- 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 -->
<connection-url>jdbc:oracle:thin:@localhost:1521:XE</connection-url>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>ejb</user-name>
<password>system</password>

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

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

<prepared-statement-cache-size>32</prepared-statement-cache-size>
<valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
<metadata><type-mapping>Oracle9i</type-mapping></metadata>
</local-tx-datasource>
</datasources>

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 ...

@Local(ItemManager.class)
@Stateless
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)
@PostConstruct
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) {
ex.printStackTrace();
}
}

@PreDestroy
public void destroy() {
try {
logger.info("start pre destroy method");
oraConnection.close();
oraConnection = null;
}catch (SQLException ex) {
ex.printStackTrace();
}
}
// 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 {
String sql = "SELECT ID, NAME, PRICE FROM ITEMS";
stmt = oraConnection.createStatement();
rs = stmt.executeQuery(sql);
itemList = constructItemList(rs);
rs.close();

}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