OC4J connection caching problem-Help pls
Hi Friends
My problem is explained below:
--
JDBC Connection Caching using OC4J
I have Created a Data Source under a specific OC4J(oc4j_alc1) Instance using 9iAS Enterprise Manager Web Site using the following values:
GENERAL SECTION
Name: OracleAlcDS
Description: Datasource For ALC Logistics
Data Source Class: oracle.jdbc.pool.OracleConnectionCacheImpl
Schema:
Username: alcw
Password: alcw
JDBC_URL: jdbc:oracle:thin:@206.101.32.33:1521:store
JDBC Driver: oracle.jdbc.driver.OracleDriver
JNDI LOCATIONS SECTION
Location: jdbc/OracleAlcDS
Transactional (XA) Location:
EJB Location:
CONNECTION ATTRIBUTES SECTION
Connection Retry Interval (secs): 1
Max Connection Attempts: 5
Cached Connection Inactivity Timeout(secs): 60
Maximum Open Connections: 20
Minimum Open Connections: 5
Wait for Free Connection Timeout (secs): 60
The connection cache will be instantiated as soon as the OC4J instance
is started. In the case of the example above, five connections will be
instantiated immediately up to a maximum of 30.
PROPERTY SECTION
cache_scheme :FIXED_RETURN_NULL_SCHEME
(So that the maximum limit should not be exceeded)
Step 2.
Created Java classes to retrieve and use a cached connection.
This class is used as factory to return pooled/cached connections to the requestor.
package com.allenLund.dbConnection;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;
public class MyDBConnectionPool
{
private static InitialContext context = null;
private static DataSource jdbcURL = null;
private static InitialContext contextAdmin = null;
private static DataSource jdbcURLAdmin = null;
private static void initJDBCConnectionFactory()
{
try {
context = new InitialContext();
jdbcURL = (DataSource) context.lookup("jdbc/OracleAlcDS");
}catch(NamingException e)
{
System.err.println("Error looking up Data Source from Factory: "+e.getMessage());
}
}
public static Connection getPooledConnection()
{
try
{
// Lazy initialization
if (jdbcURL == null)
{
initJDBCConnectionFactory();
System.out.println("Initialization Successfull");
}
//Returns an available connection from the connection cache.
return jdbcURL.getConnection();
}catch(SQLException e)
{
System.out.println("Error getting pooled connection from Factory:"+e.getMessage());
return null;
}
}
//for Admin(for current application just ignore this method
private static void initJDBCConnectionFactoryAdmin()
{
try {
contextAdmin = new InitialContext();
jdbcURLAdmin = (DataSource) context.lookup("jdbc/OracleAlcAdminDS");
System.out.println("Obtained Cached Data Source ");
}catch(NamingException e)
{
System.err.println("Error looking up Data Source from Factory: "+e.getMessage());
}
}
//for Admin(for current application just ignore this method
public static Connection getPooledConnectionAdmin()
{
try
{
// Lazy initialization
if (jdbcURLAdmin == null)
{
initJDBCConnectionFactoryAdmin();
System.out.println("the second connection************************");
}
//Returns an available connection from the connection cache.
return jdbcURLAdmin.getConnection();
}catch(SQLException e)
{
System.out.println("Error getting pooled connection from Factory:"+e.getMessage());
return null;
}
}
}
Next step is how I am using this Base class in my application
We are creating an instance of MyDBConnectionPool class in our LoginServlet
(LoginServlet.java)and setting in our application context.
Code snippet:
ServletContext context = config.getServletContext();
connectionPool = new MyDBConnectionPool();
context.setAttribute("connectionPool", connectionPool);
In the jsp pages where database connection is required we are doing
the following steps :
<%!
private Connection conn = null;
private MyDBConnectionPool connectionPool = null;
%>
<%
// DB connection pool
if (connectionPool == null)
connectionPool = (MyDBConnectionPool)application.getAttribute("connectionPool");
conn = connectionPool.getPooledConnection();
.
.
.
conn.close();
%>
I have used various combination of these attributes
Cached Connection Inactivity Timeout(secs):
Maximum Open Connections:
Minimum Open Connections:
But it did not work.
But I think caching of connection is not happening properly.
As a result, I feel instead of using connection from the pool everytime a new connection is being created.
growing number of connections() are pointing towards this.
Open JDBC Connections are exceeding the value Maximum Open Connections
even if we are using cache_scheme :FIXED_RETURN_NULL_SCHEME
Please guide me to find the problem.
