Hello,
I seem to be having some difficulty with the Apache DBCP API. I have followed what few and
seemingly outdated examples I could find on the web.
I have a simple servlet based application I wish to create a pool of connections for. This is the
way I have it set up:
I have a Servlet Context Listener class to initialize the base pool. this is the code:
<DBCPoolingListener code here>
package com.toprx.netsuite.estore.listeners;
import java.sql.SQLException;
import java.sql.*;
import javax.servlet.*;
import javax.naming.*;
import javax.sql.*;
import org.apache.tomcat.dbcp.dbcp.*;
import org.apache.tomcat.dbcp.pool.*;
import org.apache.tomcat.dbcp.pool.impl.*;
import com.microsoft.sqlserver.jdbc.SQLServerConnection;
public class DBCPoolingListener implements ServletContextListener{
public void contextInitialized(ServletContextEvent sce)
{
// DataSource testds = null;
SQLServerConnection testConn = null;
try
{
// OBTAIN OUR ENVIRONMENT NAMING CONTEXT
System.out.println("DBCPoolingListener::contextIntialized Msg: Obtaining environment naming context");
Context envCtx = (Context) new InitialContext().lookup("java:comp/env");
// LOOK UP OUR DATA SOURCE (AS DEFINED IN WEB.XML? OR SERVER.XML)
System.out.println("DBCPoolingListener::contextIntialized Msg: Looking up DataSources for TopRx, Medispan, and ASW");
BasicDataSource toprxDS = (BasicDataSource) envCtx.lookup("jdbc/estoreTopRxDB");
// SET UP A CONNECTION FACTORY BASED ON THE DATASOURCE
ConnectionFactory toprxConnectionFactory = new DataSourceConnectionFactory(toprxDS);
PoolableConnectionFactory toprxPCF = new PoolableConnectionFactory(toprxConnectionFactory, null, null, null, false, true);
// SET UP THE ACTUAL TOPRX POOL
GenericObjectPool toprxConnectionPool = new GenericObjectPool(toprxPCF);
// THIS OBJECT IS WHAT WE WILL ACTUALLY USE TO GET CONNECTIONS FROM THE POOL
PoolingDataSource toprxDataSource = new PoolingDataSource(toprxConnectionPool);
// MAKE THE TOPRX, MEDISPAN, AND ASW POOL AVAILABLE AS AN ATTRIBUTE IN THE APP ENVIRONMENT
sce.getServletContext().setAttribute("toprxDBCPool", toprxDataSource);
}
catch(NamingException e)
{
e.printStackTrace();
}
}
The above code causes an excetion to be thrown and the app does not start up correctly.
Before, I had the code as such:
// SET UP THE ACTUAL TOPRX POOL
GenericObjectPool toprxConnectionPool = new GenericObjectPool(null);
PoolableConnectionFactory toprxPCF = new PoolableConnectionFactory(toprxConnectionFactory, toprxConnectionPool, null, null, false, true);
passing null to the GenericObjectPool, then adding the
pool to the PoolableConnectionFactory. that started fine, but when I retrieve a connection
it looks like this:
System.out.println("connection is " + trxconn);
jdbc:sqlserver://192.168.5.20:1433;disableStatementPooling=true;responseBuffering=full;encrypt=false;databaseName=TopRx;selectMethod=direct;trustServerCertificate=false;lastUpdateCount=true;sendStringParametersAsUnicode=True;, UserName=handheld, Microsoft SQL Server 2005 JDBC Driver
Instead of this:
com.microsoft.sqlserver.jdbc.SQLServerConnection@1071e12
this is the code in the main servlet :
public class Start extends HttpServlet implements Servlet {
static final long serialVersionUID = 1L;
.
.
.
public PoolingDataSource TopRxDatasource = null;
try
{
Connection trxconn = TopRxDatasource.getConnection();
if (trxconn == null)
{
System.out.println("Start::doPost MSG: TopRxDataConnetion returns NULL");
}
else
{
System.out.println("Start::doPost MSG: TopRxDataConnection is NOT NULL");
System.out.println("Start::doPost MSG: Connection is: " + trxconn);
}
testTopRxConn = ((DelegatingConnection)trxconn).getInnermostDelegate();
}
catch(SQLException sqle)
{
do{
System.err.println("Start::doPost: MSG: On attempted Login");
System.err.println("Start::doPost: Exception MSG: " + sqle.getMessage());
System.err.println("SQL State: " + sqle.getSQLState());
System.err.println("Vendor code: " + sqle.getErrorCode());
}while((sqle = sqle.getNextException()) != null);
}
This is the Context.xml, which sits in META-INF:
<Context>
<Resource name="jdbc/estoreTopRxDB"
type="javax.sql.DataSource"
auth="Container"
username="blahblah"
password="********"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://192.168.5.20:1433;DatabaseName=TopRx;disableStatementPooling=true;sendStringParametersAsUnicode=true"
maxActive="4"
maxIdle="4"
initialSize="4"
/>
</Context>
When I issue the getConnection and examine what it returns this is what I get:
System.out.println("connection is " + trxconn);
jdbc:sqlserver://192.168.5.20:1433;disableStatementPooling=true;responseBuffering=full;encrypt=false;databaseName=TopRx;selectMethod=direct;trustServerCertificate=false;lastUpdateCount=true;sendStringParametersAsUnicode=True;, UserName=handheld, Microsoft SQL Server 2005 JDBC Driver
I am confused as to how to set up the pool and itialize it with some connections. I am using a JNDI data source and want instantiate an initial pool with say, 4 connections, why is this so difficult? what am I missing or not doing?
Do I have to manually create and add connections to the pool or should the PoolableConnectionFactory tell
the DataSourceConnection factory to do it based on the Resource Config options?
Any help would be greatly appreciated!
Thanks!
Chris