Hi All,
Following is the Demo for connecting remote java client to DataSource(Oracle) on JBoss.....
Step-1:Create a oracle-ds.xml and place it in server\default\deploy directory
oracle-ds.xml (server\default\deploy\oracle-ds.xml)
<datasources>
<local-tx-datasource>
<jndi-name>MyOracleDS</jndi-name>
<connection-url>jdbc:oracle:thin:@localhost:1521/XE</connection-url>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>SYSTEM</user-name>
<password>tiger</password>
<exception-sorter-class-name>
org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter
</exception-sorter-class-name>
<use-java-context>false</use-java-context>
<!-- should only be used on drivers after 3.22.1 with "ping" support
<valid-connection-checker-class-name>
org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker
</valid-connection-checker-class-name>
-->
<!-- sql to call when connection is created
<new-connection-sql>some arbitrary sql</new-connection-sql>
-->
<!-- sql to call on an existing pooled connection when it is obtained from pool -
MySQLValidConnectionChecker is preferred for newer drivers
-->
<check-valid-connection-sql>select * from dual</check-valid-connection-sql>
<!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
<metadata>
<type-mapping>Oracle9i</type-mapping>
</metadata>
</local-tx-datasource>
</datasources>
Step-2 :Create a Java Program DataSourceClient.java
DataSourceClient.java
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import java.util.Properties;
public class DataSourceClient {
public static void main(String[] args) throws Exception {
testDataSource();
}
private static void testDataSource()
throws NamingException, SQLException {
final String sql = "select systimestamp from dual";
Properties properties = new Properties();
properties.put("java.naming.factory.initial","org.jnp.interfaces.NamingContextFactory");
properties.put("java.naming.factory.url.pkgs","org.jboss.namingrg.jnp.interfaces ");
properties.put("java.naming.provider.url","jnp://localhost:1099");
InitialContext ic = new InitialContext(properties);
DataSource ds = null;
try {
ds = (DataSource) ic.lookup("java:/MyOracleDS");
} catch (Exception e) {
System.out.println(e);
}
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
con = ds.getConnection();
stmt = con.createStatement();
rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString(1));
}
} finally {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
}
}
}
echo %classpath%
C:\JBoss\jboss-5.1.0.GA\jboss-5.1.0.GA\client\*;C:\Oracle\Ora81\jdbc\lib\classes111.zip;C:\Users\xyz\Downloads\jars\ojdbc6.jar;.;
Output:
2011-04-28 01:53:02.694 -4:00....
Thanks
Sathya
Sathya
