problem in web application with connection pooling

[Cathy Valdez]

Web Application with Connection Pooling problem
Below are software installed in my PC:
Windows98
Tomcat v. 4.1.27
J2SE/JDK v. 1.3.1
MySQL Server v. 4.0.17
MySQL Connector/J v. 3.0.10

Below is the content of AUTOEXEC.BAT:

rem - By Windows Setup - C:\WINDOWS\COMMAND\MSCDEX /V /D:ATAPI_CD /M:10
set PATH=c:\jdk1.3.1\bin;%PATH%
set CLASSPATH=%CLASSPATH%;.;c:\jdk1.3.1\jre\lib\rt.jar;c:\mysql-connector-java\mysql-connector-java\mysql-connector-java-3.0.10-stable-bin.jar;c:\ServletDevel;c:\ServletDevel\database\lib\conn_pool.jar;c:\ServletDevel\jsp;c:\ServletDevel\database
set JAVA_HOME=c:\jdk1.3.1

From MySQL Server, I've created the database wordgames with tables (word, wordcategory & wordhint) under it.
Below is the deployment descriptor (web.xml)and is located in the deployment path, c:\Tomcat\webapps\database\WEB-INF:

<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN" "http://java.sun.com/j2ee/dtds/web-app_2_3.dtd">
<web-app>
<display-name>SL-314 Database Exercise #1 Solution</display-name>
<description>
This Web Application is a solution for Database Exercise #1.
This WebApp demonstrates a typical servlet design using Connection
Pooling and Data Access Objects to interface with a database.
</description>
<!--
Step: Enter the parameters for constructing the Connection Pool.
-->
<context-param>
<param-name>jdbcDriver</param-name>
<param-value>com.mysql.jdbc.Driver</param-value>
</context-param>
<context-param>
<param-name>jdbcURL</param-name>
<param-value>jdbc:mysql://host:3306/wordgames</param-value>
</context-param>
<context-param>
<param-name>jdbcUserName</param-name>
<param-value>cathy</param-value>
</context-param>
<context-param>
<param-name>jdbcPassword</param-name>
<param-value>aprfeb</param-value>
</context-param>
<context-param>
<param-name>minimumConnections</param-name>
<param-value>1</param-value>
</context-param>
<context-param>
<param-name>maximumConnections</param-name>
<param-value>5</param-value>
</context-param>
<!--
Step: Enter the servlet context listener class name
-->
<listener>
<listener-class>sl314.web.InitializeConnectionPool</listener-class>
</listener>
<servlet>
<servlet-name>WordScrambleServlet</servlet-name>
<servlet-class>sl314.web.ScrambleGameServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>WordScrambleServlet</servlet-name>
<url-pattern>/wordScramble</url-pattern>
</servlet-mapping>
<error-page>
<exception-type>sl314.util.sql.ConnNotAvailException</exception-type>
<location>/error/server_busy.html</location>
</error-page>
<error-page>
<exception-type>sl314.util.sql.ShuttingDownException</exception-type>
<location>/error/server_shutdown.html</location>
</error-page>
</web-app>

Java files below are located at the development path, c:\ServletDevel\database\sl314\domain:
ScrambleGameService.java
Word.java
WordCategory.java
WordCategoryDAO.java
WordCategoryService.java
WordDAO.java
Files above were compiled successful and class files of each is copied in the deployment path, c:\Tomcat\webapps\database\WEB-INF\classes\sl314\domain
NamingService.java is located at the development path, c:\ServletDevel\database\sl314\util:
NamingService.java is compiled successful and its class file is copied in the deployment path, c:\Tomcat\webapps\database\WEB-INF\classes\sl314\util
Java files below are located at the development path, c:\ServletDevel\database\sl314\web:
InitializeConnectionPool.java
ScrambleGameServlet.java
Files above were compiled successful and class files of each is copied in the deployment path, c:\Tomcat\webapps\database\WEB-INF\classes\sl314\web
When I entered the URL,
http://localhost:8080/database/
for the message form in the Address field, the page for the URL,
http://localhost:8080/database/index.html
is displayed, looks like below:
Duke's Arcade
Select from one of the following games:

Word Scramble
Word Guess

Then I selected the Word Scramble link, but errors below is displayed:

HTTP Status 500 -
--------------------------------------------------------------------------------
type Exception report
message
description The server encountered an internal error () that prevented it from fulfilling this request.
exception
javax.servlet.ServletException
at sl314.web.ScrambleGameServlet.showCategories(ScrambleGameServlet.java:54)
at sl314.web.ScrambleGameServlet.doGet(ScrambleGameServlet.java:29)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2416)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:601)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:392)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:565)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:619)
at java.lang.Thread.run(Thread.java:484)
root cause
java.lang.NullPointerException
at sl314.domain.WordCategoryDAO.selectAllCategories(WordCategoryDAO.java:38)
at sl314.domain.WordCategoryService.getCategories(WordCategoryService.java:16)
at sl314.web.ScrambleGameServlet.showCategories(ScrambleGameServlet.java:43)
at sl314.web.ScrambleGameServlet.doGet(ScrambleGameServlet.java:29)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2416)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:601)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:392)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:565)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:619)
at java.lang.Thread.run(Thread.java:484)
--------------------------------------------------------------------------------
Apache Tomcat/4.1.27 

Below are the corresponding lines of the root cause of
errors above:
1.)
sl314.domain.WordCategoryDAO.selectAllCategories(WordCategoryDAO.java:38)
Below is the code of line 38:
connection = connectionPool.getConnectionNoWait();
2.)
sl314.domain.WordCategoryService.getCategories(WordCategoryService.java:16)
Below is the code of line 16:
return wordCatDataAccess.selectAllCategories();
3.)
sl314.web.ScrambleGameServlet.showCategories(ScrambleGameServlet.java:43)
Below is the code of line 43:
List categoryList = wordCatSvc.getCategories();
4.)
sl314.web.ScrambleGameServlet.doGet(ScrambleGameServlet.java:29)
Below is the code of line 29:

showCategories(request, response);
I'm new to connection pooling, are these errors have
something to do with connection pooling?
JAR file, conn_pool.jar is located at the deployment
path: C:\Tomcat\webapps\database\WEB-INF\lib
Can anybody there please help? I really need to make run this web application. Do you think the contents of autoexec.bat and web.xml above are okay?
Cathy Valdez
[ February 25, 2004: Message edited by: Cathy Valdez ]

[David O'Meara]

Sorry it took me so lomg to get back to you. If anyone else wants to join in, this thread is related to the one here, which contains much more source code.

connection = connectionPool.getConnectionNoWait();

This means the connectionPool variable is null. It gets instantiated like this:

 NamingService nameSvc = NamingService.getInstance();
ConnectionPool connectionPool = (ConnectionPool) nameSvc.getAttribute("connectionPool");

so it looks like the NamingService is returning null.
The ConnectionPool gets initailised in InitializeConnectionPool like this:

try {
//*** Step: Get a reference to the NamingService singleton
NamingService nameSvc = NamingService.getInstance();
//*** Step: Create ConnectionPool object
connectionPool = new ConnectionPool(jdbcDriver, jdbcURL,
jdbcUserName, jdbcPassword,
minimumConnections,
maximumConnections);
//*** Step: Store the connection pool in the "application" scope.
nameSvc.setAttribute("connectionPool", connectionPool);
context.log("Connection pool created for URL=" + jdbcURL);
} catch (ClassNotFoundException cnfe) {
context.log("The JDBC Driver class " + jdbcDriver
+ " was no tfound.", cnfe);
} catch (InstantiationException ie) {
context.log("The JDBC Driver class " + jdbcDriver + " could "
+ "no tmake an object.", ie);
} catch (IllegalAccessException iae) {
context.log("The JDBC Driver (" + jdbcDriver + ") class "
+ "or initializer is no taccessible.", iae);
} catch (SQLException sex) {
context.log("There was an error in creating the "
+ "connection pool for " + jdbcURL, sex);
}

Are you sure the ConnectionPool is being initialised? ie is the above code getting called, and is it working OK or does it return an error? If it returns an error, nothing will be written to the NamingService and life will go on. If this is a critical error, it may be better to allow an Exception to propogate so that your application stops. You can hardly continue, can you?
Maybe (for now) cange it like this and see how it behaves:

} catch (ClassNotFoundException cnfe) {
context.log("The JDBC Driver class " + jdbcDriver
+ " was no tfound.", cnfe);
throw new RuntimeException("ClassNotFoundException: JDBC Driver not found");
} catch (InstantiationException ie) {
context.log("The JDBC Driver class " + jdbcDriver + " could "
+ "no tmake an object.", ie);
throw new RuntimeException("InstantiationException: JDBC Driver couldn't be instantiated");
} catch (IllegalAccessException iae) {
context.log("The JDBC Driver (" + jdbcDriver + ") class "
+ "or initializer is no taccessible.", iae);
throw new RuntimeException("IllegalAccessException: Driver not accessible");
} catch (SQLException sex) {
context.log("There was an error in creating the "
+ "connection pool for " + jdbcURL, sex);
throw new RuntimeException("SQLException: Error creating the connection pool");
}

...and see what happens. You may want to create your own checked Exception and throw that instead.

[Cathy Valdez]

David O' Meara,
I did your suggestions, but when I tried to compile InitializeConnectionPool.java , errors below were displayed:

InitializeConnectionPool.java: 22:cannot resolve symbol
symbol : class ServletContextEvent
location: class sl314.web.InitializeConnectionPool
public void contextInitialized(ServletContextEvent sce){
InitializeConnectionPool.java: 71:cannot resolve symbol
symbol : class ServletContextEvent
location: class sl314.web.InitializeConnectionPool
public void contextDestroyed(ServletContextEvent sce){
InitializeConnectionPool.java: 23:cannot resolve symbol
symbol : class ServletContextEvent
location: class sl314.web.InitializeConnectionPool
ServletContext context = sce.getServletContext();
InitializeConnectionPool.java: 72:cannot resolve symbol
symbol : class ServletContextEvent
location: class sl314.web.InitializeConnectionPool
ServletContext context = sce.getServletContext();

Even ScrambleGameServlet.java when compiled, errors below occured:

 ScrambleGameServlet.java: 128:cannot resolve symbol
symbol : class HttpSession
location: class sl314.web. ScrambleGameServlet
HttpSession session = request.getSession();
ScrambleGameServlet.java: 178:cannot resolve symbol
symbol : class ServletException
location: class sl314.web. ScrambleGameServlet
throw new ServletException(e);
ScrambleGameServlet.java: 187:cannot resolve symbol
symbol : class HttpSession
location: class sl314.web. ScrambleGameServlet
HttpSession session = request.getSession();
ScrambleGameServlet.java: 228:cannot resolve symbol
symbol : class HttpSession
location: class sl314.web. ScrambleGameServlet
HttpSession session = request.getSession();

Thank you for your time reading and making suggestions in my Post Ads.
Cathy Valdez
[ February 26, 2004: Message edited by: Cathy Valdez ]

[David O'Meara]

Are you still compiling from the c:\ServletDevel\database\sl314\web directory and then copying them to the c:\Tomcat\webapps\database\WEB-INF\classes\sl314\web directory?
Have you changed your classpath since last time? This is just a little bit wierd - InitializeConnectionPool can apparently see ServletContextListener but not ServletContextEvent. They are both from Servlets2.3, so you should be able to see both or neither.
From the command line, try running these:
javap javax.servlet.ServletContextListener
javap javax.servlet.ServletContextEvent
They should both return data, or your classpath is broken.

[Cathy Valdez]

David O' Meara,
Yes, I am still compiling from the c:\ServletDevel\database\sl314\web directory and then copying them to the c:\Tomcat\webapps\database\WEB-INF\classes\sl314\web directory.
Below is the content of AUTOEXEC.BAT

rem - By Windows Setup - C:\WINDOWS\COMMAND\MSCDEX /V /D:ATAPI_CD /M:10
set PATH=c:\jdk1.3.1\bin;%PATH%
set CLASSPATH=%CLASSPATH%;.;c:\jdk1.3.1\jre\lib\rt.jar;c:\mysql-connector-java\mysql-connector-java\mysql-connector-java-3.0.10-stable-bin.jar;c:\ServletDevel;c:\ServletDevel\database\lib\conn_pool.jar;c:\ServletDevel\jsp;c:\ServletDevel\database
set JAVA_HOME=c:\jdk1.3.1

c:\>javap javax.servlet.ServletContextListener

Compiled from ServletContextListener.java
public interface javax.servlet.ServletContextListener extends java.util.EventListener
/* ACC_SUPER bit NOT set */
{
public abstract void contextInitialized(javax.servlet.ServletContextEvent);
public abstract void contextDestroyed(javax.servlet.ServletContextEvent);
}

c:\>javap javax.servlet.ServletContextEvent

Compiled from ServletContextEvent.java
public class javax.servlet.ServletContextEvent extends java.util.EventObject {
public javax.servlet.ServletContextEvent(javax.servlet.ServletContext);
public javax.servlet.ServletContext getServletContext();
}

Thanks.
Cathy Valdez
[ February 27, 2004: Message edited by: Cathy Valdez ]