Connection to oracle 8

[amit_danwar]

Dear All,
I am new user of JBoss,i am able to call a java class file from .jsp page. Now i want to make database connection with oracle. Can anyone pl guide me,how to make database connection with oracle through a java class.
Please help me.
Regards
Amit.

[larry054]

Amit,

Your question is not a Jboss question. It is a standard Java (J2SE) question. Since you don't know that , here is an example taken from a servlet that illustrates the technique. Please refer to a standard Java reference such as "Beginning Java 2" by Ivor Horton.


import java.sql.*;
...
Object[][] values=new Object[][];
...
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:ACCOUNT/PASSWORD@localhost:1521:ORCL");
PreparedStatement prepStmt = con.prepareStatement(selectString);
ResultSet rs = prepStmt.executeQuery();
int i=0;
while(rs.next()) {
for (int j=0;j<4;j++) values[j]=rs.getString(j+1);
i++;
}
} catch (Exception ex) {
System.out.println("Unable to execute query " +
ex.getMessage());
}

Hope this helps.
Larry

[amit_danwar]

Larry thanks a lot for your reply,
Do i need to write any descriptor and if yes then can you pl help me out in writing a descriptor.

Best Regards
Amit.

[amit_danwar]

Hi All,

I am using the following files

1. JSP file
In this .jsp file i am callling method of a java file
2. Java Class file
I am using the following code
try{
InitialContext ctx = new InitialContext();
javax.sql.DataSource ds= (javax.sql.DataSource) ctx.lookup("java:/OracleDS");
conn = ds.getConnection();
}

3. oracel-ds.xml

<?xml version="1.0" encoding="UTF-8"?>

<!-- ===================================================================== -->
<!-- -->
<!-- JBoss Server Configuration -->
<!-- -->
<!-- ===================================================================== -->

<!-- $Id: oracle-ds.xml,v 1.1.2.2 2003/04/01 04:51:12 d_jencks Exp $ -->
<!-- ==================================================================== -->
<!-- Datasource config for Oracle originally from Steven Coy -->
<!-- ==================================================================== -->



<local-tx-datasource>
<jndi-name>OracleDS</jndi-name>
<connection-url>jdbc:oracle:thin:@youroraclehost:1521:yoursid</connection-url>

<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>x</user-name>
y
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
</local-tx-datasource>



This is givin me the error OracleDS not found...
Pl suggest if i am missing something.
Please Help
Regards
Amit

[larry054]

The example I gave earlier works without a descriptor since all the connection info is in the code. Indeed, it doesn't even need JBoss.

The method you proposed will also work. I think your lookup has the wrong context however. It should be "java:comp/env/jdbc/OracleDS".

Also, I use an application descriptor to connect the jndi name to the oracle-ds. (Since you are mapping a jndi to a resource of the same name, I think JBoss can make the connection automatically. Maybe someone else knows the answer to that.) For example:

---Source code---

private String dbName = "java:comp/env/jdbc/OracleDS";
...
InitialContext ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup(dbName);
con = ds.getConnection();
...

---descriptor---

<resource-ref>
<res-ref-name>jdbc/OracleDS</res-ref-name>
<resource-name>jdbc/OracleDS</resource-name>
</resource-ref>
...

---oracle-ds.xml

What you have looks good.

[kar2000]

Well, I have a doubt in this regard - is there any means of finding in the jboss 3.0.7 server side and not in the Oracle 8 database server side,

how many connections are there in the pool,

how many connections are in use and

how many connections are being returned at a given point of time?