Not able to connect to Oracle through tomcat

From:

K Gaur <gaurkuber@gmail.com>

Newsgroups:

comp.lang.java.help

Date:

Tue, 6 Jul 2010 12:59:05 -0700 (PDT)

Message-ID:

<57fd60ce-5a44-474b-aaf9-d860d14f9f9a@y21g2000pro.googlegroups.com>

Hello All,

i am facing a peculiar problem. I am trying to connect to Oracle by
using the OracleDataSource Object as:

__________________________

server.xml config:

<Resource name="jdbc/TeaApp" auth="Container"
     type="oracle.jdbc.pool.OracleDataSource"
driverClassName="oracle.jdbc.OracleDriver"

factory="oracle.jdbc.pool.OracleDataSourceFactory"
     url="jdbc:oracle:thin:@localhost:1521:orcl"
     username="scott" password="harekrishna"
maxActive="20" maxIdle="10"
              maxWait="-1"/>
__________________________

web.xml config:

<resource-ref>
<description>Oracle Datasource example</description>
<res-ref-name>jdbc/TeaApp</res-ref-name>
<res-type>oracle.jdbc.pool.OracleDataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
__________________________

Java class for establishing the connection:

package com.example.web;

import oracle.jdbc.pool.OracleDataSource;

import javax.naming.Context;
import javax.naming.InitialContext;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class ConnectionPool implements Serializable {
   String message = "Not Connected";

public void init() {
   Connection conn = null;
   ResultSet rst = null;
   Statement stmt = null;
   try {
      Context initContext = new InitialContext();
      Context envContext = (Context) initContext.lookup("java:/comp/
env");
      OracleDataSource ds = (OracleDataSource) envContext.lookup("jdbc/
db1");

     if (envContext == null) throw new Exception("Error: No Context");
     if (ds == null) throw new Exception("Error: No DataSource");
     if (ds != null) conn = ds.getConnection();

     if (conn != null) {
        message = "Got Connection " + conn.toString() + ", ";
        stmt = conn.createStatement();
        rst = stmt.executeQuery("SELECT count(*) FROM emp");
     }
     if (rst.next()) message = rst.getString(1);

   rst.close();
   rst = null;
   stmt.close();
   stmt = null;
   conn.close(); // Return to connection pool
   conn = null; // Make sure we don't close it twice

} catch (Exception e) {
   e.printStackTrace();
} finally {
   // Always make sure result sets and statements are closed,
   // and the connection is returned to the pool
   if (rst != null) {
      try {
         rst.close();
      } catch (SQLException e) {;}
      rst = null;
   }

   if (stmt != null) {
      try {
         stmt.close();
      } catch (SQLException e) {;}
      stmt = null;
   }

   if (conn != null) {
      try {
         conn.close();
      } catch (SQLException e) {;}
      conn = null;
   }
}
}

public String getMessage() {return message;}

}
_______________________________

The JSP for running the java class:

<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>
<html>
   <head><title>JSP Page</title></head>
<body>
<% com.example.web.ConnectionPool ocp = new
com.example.web.ConnectionPool();
   ocp.init(); %>

<h2>Results</h2>
    Message: <%= ocp.getMessage() %>
</body>
</html>
______________________________

when i run the JSP http://localhost:8080/TeaApp/DBTest.jsp the
following is output:

Results
Message: Not Connected

The output should change someplace or other in the program for example
if the connection is not obtained then an exception is thrown, or, if
everything goes fine, String should be set as the count of records
present and printed.

Moreover to check if i was even able to connect to database i tested
it using this class and it worked fine :
_____________________________________________

import java.sql.*;
import oracle.jdbc.pool.OracleDataSource;

public class TestDBOracle {

  static final String connect_string = "jdbc:oracle:thin:scott/
harekrishna@//localhost:1521/orcl.om";

  public static void main(String[] args)
      throws ClassNotFoundException, SQLException
  {

    OracleDataSource ods = new OracleDataSource();
    ods.setURL(connect_string);

    Connection conn = ods.getConnection ();

    Statement stmt = conn.createStatement();
    ResultSet rset =
         stmt.executeQuery("select count(*) from EMP");
    while (rset.next()) {
         System.out.println (rset.getString(1));
    }
    stmt.close();
    System.out.println ("Ok.");
  }
}
__________________________________

Please help me out.

Thanks in advance.

Regards
Kuber