Commons DBCP error !!

sherbir · September 27th, 2004, 07:07 AM

Hi friends,
I am implementing the Commons DBCP package in one of my JSP pages.

This is the scenario :

In my CATALINA_HOME/common/lib I have the files :

commons-dbcp-1.1.jar
commons-pool-1.1.jar
commons-collections.jar

I have set the CLASSPATHS in my Environment variables to the appropriate JARs.

I have added a Context to my server.xml file as :

<Context path="/CPool" docBase="CPool" debug="5" reloadable="true" crossContext="true">
<Logger className="org.apache.catalina.logger.FileLogger" prefix="localhost_CPool_Log." suffix=".txt" timestamp="true"/>
<Resource name="jdbc/test_dsn" auth="Container" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/test_dsn">
                     <parameter>
        <name>factory</name>
        <value>org.apache.commons.dbcp.BasicDataSourceFact ory</value>
    </parameter>

    <parameter>
        <name>maxActive</name>
        <value>0</value>
    </parameter>

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

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

    <parameter>
        <name>driverClassName</name>
        <value>org.gjt.mm.mysql.Driver</value>
    </parameter>

    <parameter>
        <name>url</name>
        <value>jdbc:mysql://localhost:8080/omamconsultants_com?autoreconnect=true</value>
    </parameter>

    </ResourceParams>
</Context>

and I have made a new web.xml file in my CPool/WEB-INF directory as :

<web-app>

<description> MySQL Test Application for Connection Pooling.</description>

<resource-ref>
    <description>Connection Pooling</description>
    <res-ref-name>jdbc/test_dsn</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref>
</web-app>

Finally, I have made a JSP to access the database table as :

<%
int i=0;

try
{
                     Context ctx = new InitialContext();

    if (ctx == null)
    {
        throw new Exception("No Context available");
    }

    DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/test_dsn");

    if (ds != null)
    {
        Connection con = ds.getConnection();

        if (con != null)
        {
            System.out.println("Connection " + con.toString() + " established.");
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM t_sherbir");

            while (rs.next())
            {
                System.out.println("Fetching record " + (i+1) + " from table...");
            }

            con.close();
        }
    }
}
catch(Exception e) {}
%>

But when I run the program, I am getting this error :

org.apache.jasper.JasperException: Unable to compile class for JSP

An error occurred at line: 18 in the jsp file: /CPool/pool2.jsp

Generated servlet error:
    [javac] Compiling 1 source file

C:\jakarta-tomcat-4.1.29\work\Standalone\localhost\examples\CPool\po ol2_jsp.java:76: cannot resolve symbol
symbol : class DataSource
location: class org.apache.jsp.pool2_jsp
    DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/test_dsn");
        ^

An error occurred at line: 18 in the jsp file: /CPool/pool2.jsp

Generated servlet error:
C:\jakarta-tomcat-4.1.29\work\Standalone\localhost\examples\CPool\po ol2_jsp.java:76: cannot resolve symbol
symbol : class DataSource
location: class org.apache.jsp.pool2_jsp
    DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/test_dsn");
                         ^
2 errors

    at org.apache.jasper.compiler.DefaultErrorHandler.jav acError(DefaultErrorHandler.java:130)
    at org.apache.jasper.compiler.ErrorDispatcher.javacEr ror(ErrorDispatcher.java:293)
    at org.apache.jasper.compiler.Compiler.generateClass( Compiler.java:353)
    at org.apache.jasper.compiler.Compiler.compile(Compil er.java:370)
    at org.apache.jasper.JspCompilationContext.compile(Js pCompilationContext.java:473)
    at org.apache.jasper.servlet.JspServletWrapper.servic e(JspServletWrapper.java:190)
    at org.apache.jasper.servlet.JspServlet.serviceJspFil e(JspServlet.java:295)
    at org.apache.jasper.servlet.JspServlet.service(JspSe rvlet.java:241)
    at javax.servlet.http.HttpServlet.service(HttpServlet .java:853)
    at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:247)
    at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.StandardWrapperValve.invo ke(StandardWrapperValve.java:256)
    at org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invokeNext(StandardPipeline.j ava:643)
    at org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:480)
    at org.apache.catalina.core.ContainerBase.invoke(Cont ainerBase.java:995)
    at org.apache.catalina.core.StandardContextValve.invo ke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invokeNext(StandardPipeline.j ava:643)
    at org.apache.catalina.authenticator.AuthenticatorBas e.invoke(AuthenticatorBase.java:494)
    at org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invokeNext(StandardPipeline.j ava:641)
    at org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:480)
    at org.apache.catalina.core.ContainerBase.invoke(Cont ainerBase.java:995)
    at org.apache.catalina.core.StandardContext.invoke(St andardContext.java:2417)
    at org.apache.catalina.core.StandardHostValve.invoke( StandardHostValve.java:180)
    at org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invokeNext(StandardPipeline.j ava:643)
    at org.apache.catalina.valves.ErrorDispatcherValve.in voke(ErrorDispatcherValve.java:171)
    at org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invokeNext(StandardPipeline.j ava:641)
    at org.apache.catalina.valves.ErrorReportValve.invoke (ErrorReportValve.java:172)
    at org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invokeNext(StandardPipeline.j ava:641)
    at org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:480)
    at org.apache.catalina.core.ContainerBase.invoke(Cont ainerBase.java:995)
    at org.apache.catalina.core.StandardEngineValve.invok e(StandardEngineValve.java:174)
    at org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invokeNext(StandardPipeline.j ava:643)
    at org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:480)
    at org.apache.catalina.core.ContainerBase.invoke(Cont ainerBase.java:995)
    at org.apache.coyote.tomcat4.CoyoteAdapter.service(Co yoteAdapter.java:193)
    at org.apache.coyote.http11.Http11Processor.process(H ttp11Processor.java:781)
    at org.apache.coyote.http11.Http11Protocol$Http11Conn ectionHandler.processConnection(Http11Protocol.jav a:549)
    at org.apache.tomcat.util.net.TcpWorkerThread.runIt(P oolTcpEndpoint.java:589)
    at org.apache.tomcat.util.threads.ThreadPool$ControlR unnable.run(ThreadPool.java:666)
    at java.lang.Thread.run(Thread.java:536)

So any1 has an idea where I'm going wrong ?

Please help immediately.

Regards,
Sherbir

angrycat · September 30th, 2004, 04:08 AM

Hello,

I dont think you should be using the JDBC commands that you are.
To make use of DBCP you need to use JNDI, hence the need to set up web.xml and server.xml.

I think you need to replace your connection code with a JNDI equivalent.

Please let me know how you get on as I am soon to use DBCP myself.
I also replied to your post in servlets where I mentioned that the Tomcat documentation shows how to use JNDI to access a database.
Here is the sample JSP code from the example, as you can see connections are no longer managed from the JSP.
Note ="jdbc/TestDB"> is defined in web.xml also included below:

<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<sql:query var="rs" dataSource="jdbc/TestDB">
select id, foo, bar from testdata
</sql:query>

<html>
  <head>
    <title>DB Test</title>
  </head>
  <body>

  <h2>Results</h2>

<c:forEach var="row" items="${rs.rows}">
    Foo ${row.foo}<br/>
    Bar ${row.bar}<br/>
</c:forEach>

  </body>
</html>

*********************web.xml********************

<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
    version="2.4">
<web-app>
  <description>MySQL Test App</description>
  <resource-ref>
      <description>DB Connection</description>
      <res-ref-name>jdbc/TestDB</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>
</web-app>