Re: Problem connecting to Oracle with Tomcat
K Gaur wrote:
I am facing a peculiar problem. i [sic] am trying to connect to Oracle us=
ing
the OracleDataSource object using the following configuration :
Why do you prefer 'OracleDataSource' to 'javax.sql.DataSource'?
I've used Oracle DB with Tomcat much as you describe, except that I
didn't use 'OracleDataSource' and I didn't specify the factory and I
used the application context.xml instead of the server.xml. Still,
the 'DataSource' setup that you show seems OK.
___________________________
server.xml file 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 file 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>
__________________________
The java class for connecting to the database:
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:/co=
mp/
env");
OracleDataSource ds = (OracleDataSource) envContext.lookup(=
"jdbc/
TeaApp");
if (envContext == null) throw new Exception("Error: No Con=
text");
if (ds == null) throw new Exception("Error: No DataSource"=
);
if (ds != null) conn = ds.getConnection();
Your unconventional layout made it hard to distinguish where 'conn'
was set.
if (conn != null) {
What should be the path 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;
Setting these variable to 'null' is superfluous.
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();
You've already closed the statement and the connection, and not in the
'finally', so closing the result set is superfluous.
} 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 to run this 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(); %>
Scriptlet in a JSP is a big no-no.
<h2>Results</h2>
Message: <%= ocp.getMessage() %>
</body>
</html>
__________________________________
On running the JSP http://localhost:8080/TeaApp/DBTest.jsp
following is output:
Results
Message: Not Connected
If there is anything that goes wrong or right with the program then
output should change, either if the connection is not obtained then
exception should occur or if everything goes well then the number of
rows must be output as "message" String should be reset.
Moreover, to test if i was even getting the connection, i tried making
the following 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.");
}}
_____________________________________
In what directory do you have the ojdbc*.jar file?
--
Lew