Not able to connect to Oracle through tomcat
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