asked on

Connect pooling problem + java + tomcat + oracle

Hi experts,

I'm developing an application using Tomcat with Oracle database. I have a problem with connection pooling that I can't solve.
What must I do to have connnection pooling successful?

The error I get is

javax.naming.NameNotFoundException: Name jdbc is not bound in this context
at org.apache.naming.NamingContext.lookup(NamingContext.java:811)
.
.
.
.

My server.xml file is





<Server port="8005" shutdown="SHUTDOWN" debug="0">


<Listener className="org.apache.catalina.mbeans.ServerLifecycleListener"
debug="0"/>
<Listener className="org.apache.catalina.mbeans.GlobalResourcesLifecycleListener"
debug="0"/>


<GlobalNamingResources>


<Environment name="simpleValue" type="java.lang.Integer" value="30"/>


<Resource name="UserDatabase" auth="Container"
type="org.apache.catalina.UserDatabase"
description="User database that can be updated and saved">
</Resource>
<ResourceParams name="UserDatabase">
<parameter>
<name>factory</name>
<value>org.apache.catalina.users.MemoryUserDatabaseFactory</value>
</parameter>
<parameter>
<name>pathname</name>
<value>conf/tomcat-users.xml</value>
</parameter>
</ResourceParams>

</GlobalNamingResources>




<Service name="Tomcat-Standalone">




<Connector className="org.apache.coyote.tomcat4.CoyoteConnector"
port="888" minProcessors="5" maxProcessors="75"
enableLookups="true" redirectPort="8443"
acceptCount="100" debug="0" connectionTimeout="20000"
useURIValidationHack="false" disableUploadTimeout="true" />






<Connector className="org.apache.coyote.tomcat4.CoyoteConnector"
port="8009" minProcessors="5" maxProcessors="75"
enableLookups="true" redirectPort="8443"
acceptCount="10" debug="0" connectionTimeout="20000"
useURIValidationHack="false"
protocolHandlerClassName="org.apache.jk.server.JkCoyoteHandler"/>

















<Engine name="Standalone" defaultHost="localhost" debug="0">





<Logger className="org.apache.catalina.logger.FileLogger"
prefix="catalina_log." suffix=".txt"
timestamp="true"/>




<Realm className="org.apache.catalina.realm.UserDatabaseRealm"
debug="0" resourceName="UserDatabase"/>













<Host name="localhost" debug="0" appBase="webapps"
unpackWARs="true" autoDeploy="true">








<Logger className="org.apache.catalina.logger.FileLogger"
directory="logs" prefix="localhost_log." suffix=".txt"
       timestamp="true"/>







<Context path="/examples" docBase="examples" debug="0"
reloadable="true" crossContext="true">
<Logger className="org.apache.catalina.logger.FileLogger"
prefix="localhost_examples_log." suffix=".txt"
       timestamp="true"/>
<Ejb name="ejb/EmplRecord" type="Entity"
home="com.wombat.empl.EmployeeRecordHome"
remote="com.wombat.empl.EmployeeRecord"/>





              
<Environment name="maxExemptions" type="java.lang.Integer"
value="15"/>
<Parameter name="context.param.name" value="context.param.value"
override="false"/>
<Resource name="jdbc/EmployeeAppDb" auth="SERVLET"
type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/EmployeeAppDb">
<parameter><name>user</name><value>sa</value></parameter>
<parameter><name>password</name><value></value></parameter>
<parameter><name>driverClassName</name>
<value>org.hsql.jdbcDriver</value></parameter>
<parameter><name>driverName</name>
<value>jdbc:HypersonicSQL:database</value></parameter>
</ResourceParams>
<Resource name="mail/Session" auth="Container"
type="javax.mail.Session"/>
<ResourceParams name="mail/Session">
<parameter>
<name>mail.smtp.host</name>
<value>localhost</value>
</parameter>
</ResourceParams>
<ResourceLink name="linkToGlobalResource"
global="simpleValue"
type="java.lang.Integer"/>
</Context>


<Context path="/cmgcrm" reloadable="true" docBase="cmgcrm" crossContext="true" debug="0"/>
<Resource name="jdbc/OracleDS"
auth="Container"
type="javax.sql.DataSource"/>

       <ResourceParams name="jdbc/OracleDS">
       <parameter>
       <name>factory</name>
       <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
       </parameter>

       
       <parameter>
       <name>maxActive</name>
       <value>100</value>
       </parameter>

       
       <parameter>
       <name>maxIdle</name>
       <value>30</value>
       </parameter>

       
       <parameter>
       <name>maxWait</name>
       <value>10000</value>
       </parameter>

       
       <parameter>
       <name>username</name>
       <value>cmg_crm</value>
       </parameter>
       <parameter>
       <name>password</name>
       <value>cmg_crm</value>
       </parameter>

       
       <parameter>
       <name>driverClassName</name>
       <value>oracle.jdbc.driver.OracleDriver</value>
       </parameter>

       
       <parameter>
       <name>url</name>
       <value>jdbc:oracle:thin:@10.9.8.54:1521:cmgdev</value>
       </parameter>
       </ResourceParams>

</Host>

</Engine>

</Service>






</Server>

In case if you need to read my programs that accesses the database.

package my.com.cmg.crm.base;

import java.sql.*;
import java.util.*;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.*;
import java.sql.Connection;
import javax.naming.InitialContext;
import javax.naming.Context;
import javax.sql.DataSource;
import my.com.cmg.crm.util.PropertyLoader;

public class BaseDAO {

protected Connection con;
static String iContext = "", callFrm = "";
static Properties p = null;
Context initialContext = null;

public static void setInitContext(String iconx){
            //System.out.println("Hi..the context is " + iconx);
if(iconx.equals("C")){
// oracle datasource
iContext = "jdbc/OfficerDS";
}else{
// tomcat datasource
iContext = "java:/comp/env/jdbc/OracleDS";
}

}

public static void setJdbcCall(){
callFrm = "deamon";
}

public static void resetJdbcCall(){
callFrm = "";
}

protected Connection getConnection() throws Exception{
if(callFrm.equals("deamon")){
return getConnDriver();
}else{
initialContext = new InitialContext();
DataSource ds = (DataSource)initialContext.lookup(iContext);
this.con = ds.getConnection();
return this.con;
}

}

protected void testConnection(){

try{
System.out.println("in Base DAO , connection is : "+con);
System.out.println("Creating statement");
Statement stmt = con.createStatement();
System.out.println("After Creating statement");
ResultSet rs = stmt.executeQuery("Select * from tab");
System.out.println("After getting resultset");
while (rs.next()){
System.out.println(rs.getString(1));
}
}catch(Exception e){
e.printStackTrace();
}
}

protected void returnConnection(){
try{
this.con.close();
}catch(Exception e){
this.con = null;
}
}

      //change to public by ricky from protected
protected void returnConnection(Connection oCon){
try{
oCon.close();
}catch(Exception e){
oCon = null;
}
}

protected void returnConnection(Statement stmt,ResultSet rs){
try{
this.con.close();
if (stmt != null){
stmt.close();
}

if (rs != null){
rs.close();
}

}catch(Exception e){
this.con = null;
}
}

public void returnNewConnection(Connection oCon,Statement stmt,ResultSet rs){

try{

if (stmt != null){
stmt.close();
}

if (rs != null){
rs.close();
}

oCon.close();
}catch(Exception e){
this.con = null;
}
}

public Connection getNewConnection() throws Exception{
Context initialContext = new InitialContext();
DataSource ds = (DataSource)initialContext.lookup(iContext);
return ds.getConnection();
}

//Used by standalone daemon program to get connection
//change to public from private by ricky
private Connection getConnDriver() throws ClassNotFoundException, SQLException, Exception {
PropertyLoader.load();
String driver = PropertyLoader.getValue("drivername");
            System.out.println("Hi..the driver is " + driver);
String connectString = PropertyLoader.getValue("dburl");
String user = PropertyLoader.getValue("username");
String password = PropertyLoader.getValue("password");


/*String driver = "oracle.jdbc.driver.OracleDriver";
String connectString = "jdbc:oracle:thin:@10.9.8.54:1521:cmgdev";
String user = "cmgcrm";
String password = "cmgcrm";*/

Class.forName(driver);
this.con = DriverManager.getConnection(connectString, user, password);
return this.con;
}

public final static void main(String[] args){
try{
BaseDAO oDAO = new BaseDAO();
Connection con =oDAO.getConnDriver();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select itm_item_code from t_item_mstr ");
while (rs.next()){
System.out.println(rs.getString("ITM_ITEM_CODE"));
}
stmt.close();
rs.close();
oDAO.returnConnection(con);
}catch(Exception e){
e.printStackTrace();
}

}

}

package my.com.cmg.crm.common.model;

import java.sql.*;
import java.util.ArrayList;
import java.util.Hashtable;

import my.com.cmg.crm.base.BaseDAO;
import my.com.cmg.crm.common.vo.UserVO;

public class LoginDAO extends BaseDAO {

      public LoginDAO() {
      }

      public UserVO authenLogin(String userID, String password) throws Exception {
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            UserVO userVO = null;


            BaseDAO.setInitContext("C");
            String sqlGetUser
            = "SELECT * FROM USERS "
            + "where upper(LOGIN_NAME) = '" + userID.toUpperCase()
            + "' and PASSWORD = '" + password + "'";

try{

con = getConnection();
//con = getConnDriver();
pstmt = con.prepareStatement(sqlGetUser);
rs = pstmt.executeQuery();
if(rs.next()){
userVO = new UserVO();
userVO.setUserID(rs.getString("USER_ID"));
userVO.setLoginName(rs.getString("LOGIN_NAME"));
userVO.setPassword(rs.getString("PASSWORD"));
userVO.setPasswordHint(rs.getString("PASSWORD_HINT"));
userVO.setUserName(rs.getString("USER_NAME"));
userVO.setGender(rs.getInt("GENDER"));
userVO.setUserNRICPassport(rs.getString("NRIC_PASSPORT"));
userVO.setEmployeeID(rs.getString("EMPLOYEE_ID"));
userVO.setRaceID(rs.getInt("RACE_ID"));
userVO.setBranchID(rs.getInt("BRANCH_ID"));
userVO.setDepartmentID(rs.getInt("DEPARTMENT_ID"));
userVO.setRemarks(rs.getString("REMARKS"));
userVO.setUpdateBy(rs.getString("UPDATE_BY"));
userVO.setUpdateDate(rs.getString("UPDATE_DATE"));

}

}catch(Exception e){
e.printStackTrace();
throw e;
}

finally{
if(pstmt != null)
pstmt.close();

if(rs != null)
rs.close();

returnConnection();
//returnConnection(con);
return userVO;
}
      }

}

Please help me..