|
Tip of the Week
Tip for Week of September 21, 2003
JDBC Connection Caching using OC4J
This tip comes from Jason Bennett, Consultant for Oracle Corporation, in Charlotte, NC.
This tip details the steps required to setup an Oracle JDBC Connection Cache using an OC4J DataSource and provides sample Java classes.
Step 1. Create a Data Source under a specific OC4J Instance using 9iAS
Enterprise Manager Web Site or Edit an existing Data Source using the
following values:
GENERAL SECTION
Name: CachedDS
Description: Datasource used for connection cache.
Data Source Class: oracle.jdbc.pool.OracleConnectionCacheImpl
Schema: <not required>
Username: <db user or schema name>
Password: <password for db user/schema>
JDBC_URL: jdbc:oracle:thin@<host>:<TNS Listener Port>:<sid>
JDBC Driver: oracle.jdbc.driver.OracleDriver
JNDI LOCATIONS SECTION
Location: jdbc/CachedDS
Transactional (XA) Location: <optional>
EJB Location: <optional>
CONNECTION ATTRIBUTES SECTION
Connection Retry Interval (secs): 30
Max Connection Attempts: 5
Cached Connection Inactivity Timeout(secs): 15
Maximum Open Connections: 30 <depends on load and application>
Minimum Open Connections: 5 <This will actually open 5 connections>
Wait for Free Connection Timeout (secs): 30
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.
Step 2. Create Java classes to retrieve and use a cached connection.
JDBC Factory Class. This class is used as factory to return
pooled/cached connections to the requestor.
import java.sql.*;
import javax.sql.*;
import oracle.sql.*;
import javax.naming.*;
public class JDBCConnectionFactory
{
private static InitialContext context = null;
private static DataSource jdbcURL = null;
private static void initJDBCConnectionFactory()
{
try {
context = new InitialContext();
jdbcURL = (DataSource) context.lookup(jdbc/CachedDS);
System.out.println("Obtained Cached Data Source ");
}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;
}
}
}
JDBC Connection Base Class: Base class for all other classes that need
a JDBC connection. The class handles obtaining the cached connection
through its constructor. The class also provides methods for error
tracking and closing connections.
import java.sql.*;
import javax.sql.*;
import oracle.sql.*;
import javax.naming.*;
public class JDBCConnectionBase
{
protected Connection conn = null;
protected CallableStatement cs = null;
protected ResultSet rset = null;
protected StringBuffer v_error = new StringBuffer();
public JDBCConnectionBase(){}
public void setPooledConnection(String username)
{
try
{
//Obtain a Cached connection from the JDBCConnectionFactory class
conn = JDBCConnectionFactory.getPooledConnection();
//This allows the DB Session to identify the application user who
accesses
// a schema object with the cached db connection.
PreparedStatement ps = conn.prepareCall("begin
dbms_session.set_identifier(?); end;");
ps.setString(1,username);
ps.execute();
ps.close();
}catch(SQLException e)
{
System.out.println("Error getting pooled
connection:"+e.getMessage());
}
}
//Method to check for errors in the class level error buffer
public boolean existErrors()
{
boolean v_return = false;
if (v_error.length() > 0)
{
v_return = true;
}
return v_return;
}
//Returns all errors contained in the class level error buffer
public String getErrors()
{
return this.v_error.toString();
}
//Clears all errors from the class level error buffer
public void clearErrors()
{
v_error = null;
v_error = new StringBuffer();
}
//Closes open connections and performs cleanup on the cached
connection. The
//best place to call this method is in the 'finally' block of a
try-catch-finally
protected void closeConnections()
{
try
{
if (rset != null)
{
rset.close();
rset = null;
}
if(cs != null)
{
cs.close();
cs = null;
}
if (!conn.isClosed())
{
PreparedStatement ps = conn.prepareCall("begin
dbms_session.set_identifier(?); end;");
ps.setString(1,null);
ps.execute();
ps.close();
/* Since we are using a cached connection, we want to clear any
transactions before
return the connection to the pool/cache. */
conn.rollback();
conn.close();
conn=null;
}
}catch(SQLException e)
{
System.err.println("Error closing connections: "+e.getMessage());
this.v_error.append("Error closing connections:
").append(e.getMessage());
}catch(Exception e)
{
System.err.println("Error closing connections: "+e.getMessage());
this.v_error.append("Error closing connections:
").append(e.getMessage());
}
}
}
Example Class that extends the JDBCConnectionBase:
import java.sql.*;
import javax.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
import java.util.HashMap;
public class JDBCDataAccessObject extends JDBCConnectionBase
{
private String username;
public JDBCDataAccessObject(String username)
{
super();
this.username = username;
}
//The connection Object is instantiated by the Constructor
public void insertData(String p_user_id){
try{
setPooledConnection(username);
CallableStatement cs = conn.prepareCall("begin
mypackage.insertData(?); end;");
cs.setString(1,p_user_id);
cs.execute();
cs.close();
conn.commit();
}catch(SQLException e)
{
System.err.println("Error inserting record: "+e.getMessage());
v_error.append("Error inserting record:
").append(e.getMessage());
}catch(Exception e)
{
System.err.println("Error inserting record: "+e.getMessage());
v_error.append("Error inserting record.");
e.printStackTrace();
}finally
{
closeConnections();
}
}
public HashMap getData(int p_id)
{
HashMap hm_Data = new HashMap();
try
{
setPooledConnection(username);
CallableStatement cs = conn.prepareCall("begin ? :=
myPackage.get_data(?); end;");
cs.registerOutParameter(1,OracleTypes.CURSOR);
cs.setInt(2,p_id);
cs.execute();
rset = (ResultSet)cs.getObject(1);
//Note: This is an example. If a HASH key is repeated, an
error will actually be thrown.
//For a multi-row result set, a second hashmap with a numeric
key would be placed in each row
// of the first hashmap. This example assumes only one row will
be returned.
while(rset.next()){
hm_Data.put("DATA1",rset.getString("DATA1"));
hm_Data.put("DATA2",rset.getString("DATA2"));
}
cs.close();
}catch(SQLException e)
{
System.err.println("Error obtaining record: "+e.getMessage());
v_error.append("Error obtaining record: "+e.getMessage());
}finally
{
closeConnections();
}
return hm_Data;
}
}//end class
| 
Bookmark
