Re: PostgreSQL/Tomcat JNDI Datasource Questions

Warren,

A JSP ends up being a servlet at some point. So I'd say that there
should still be a way to implement destroy inside a jsp page. ( I don't
believe in JSP, and this is just another reason )

Just thinking out loud you should be able to put a servlet into your
webapp that will just close the pool.  All it would do is start on load,
and run destroy

Dave

Warren Killian wrote:

> Hi Dave,
>
> Good advice.  But I'm afraid not using Servlets.  My application is
> written entirely with JSP's in which I'm using the JSTL actions to
> perform all of the database operations.  I've scanned all the
> information I can find on the "sql" JSTL and haven't found any action
> which would let me specifically close database connections.  Have I
> missed something in my research?  Thanks for the feedback.
>
> Warren Killian
> warrenk19@comcast.net
>
> Dave Cramer wrote:
>
>> Warren,
>>
>> You need to implement destroy in your servlet and close the
>> connections when the servlet is destroyed.
>>
>> Dave
>>
>> Warren Killian wrote:
>>
>>> Hello group,
>>>
>>> I have several questions concerning configuring and using a JNDI
>>> DataSource
>>> with PostgreSQL and Apache Tomcat.  Currently I have
>>> successfully configured a "basic" DataSource.  Currently, my
>>> configuration
>>> which resides in a context.xml file is as follows:
>>>
>>>        <Context className="org.apache.catalina.core.StandardContext"
>>>                 backgroundProcessorDelay="-1"
>>>                 cachingAllowed="true"
>>>
>>> charsetMapperClass="org.apache.catalina.util.CharsetMapper"
>>>
>>> configFile="/usr/local/jwsdp-1.3/conf/Catalina/localhost/test.xml"
>>>                 cookies="true"
>>>                 crossContext="false"
>>>                 debug="0"
>>>                 displayName="test"
>>>                 docBase="/usr/local/jwsdp-1.3/webapps/test"
>>>                 domain="Catalina"
>>>                 engineName="Catalina"
>>>                 j2EEApplication="none"
>>>                 j2EEServer="none"
>>>                 lazy="true"
>>>                 managerChecksFrequency="6"
>>>                 path="/test"
>>>                 privileged="false"
>>>                 reloadable="true"
>>>                 startupTime="8"
>>>                 swallowOutput="false"
>>>                 tldScanTime="0"
>>>                 useNaming="true"
>>>
>>> wrapperClass="org.apache.catalina.core.StandardWrapper">
>>>
>>>          <Resource name="jdbc/test" scope="Shareable"
>>>                           type="javax.sql.DataSource"/>
>>>          <ResourceParams name="jdbc/test">
>>>            <parameter>
>>>               <name>factory</name>
>>>
>>> <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
>>>            </parameter>
>>>            <parameter>
>>>              <name>driverClassName</name>
>>>              <value>org.postgresql.Driver</value>
>>>            </parameter>
>>>            <parameter>
>>>              <name>url</name>
>>>              <value>jdbc:postgresql://myIP/test</value>
>>>            </parameter>
>>>            <parameter>
>>>              <name>username</name>
>>>              <value>Whoopie</value>
>>>            </parameter>
>>>            <parameter>
>>>              <name>password</name>
>>>              <value>Doopie</value>
>>>            </parameter>
>>>            <parameter>
>>>               <name>removeAbandoned</name>
>>>               <value>true</value>
>>>            </parameter>
>>>            <parameter>
>>>               <name>removeAbandonedTimeout</name>
>>>               <value>20</value>
>>>            </parameter>
>>>            <parameter>
>>>              <name>maxWait</name>
>>>              <value>5000</value>
>>>            </parameter>
>>>            <parameter>
>>>              <name>maxActive</name>
>>>              <value>5</value>
>>>            </parameter>
>>>            <parameter>
>>>              <name>maxIdle</name>
>>>              <value>2</value>
>>>            </parameter>
>>>            <parameter>
>>>              <name>validationQuery</name>
>>>              <value>SELECT * FROM test</value>
>>>            </parameter>
>>>          </ResourceParams>
>>>        </Context>
>>>
>>> Using the above configuration, my JSP pages can connect to
>>> PostgreSQL and
>>> perform all required database operations.
>>>
>>> But all is not right!  I have the following issues:
>>>
>>> 1.) Connections are NEVER closed
>>> Say I hit my JSP page which perform a query and display the results.
>>> Using netstat I can see that one connection to PostgreSQL has been
>>> established.  But then, if I uninstall and reinstall my web app and
>>> hit my
>>> JSP page again, then the output of "netstat -p -t" shows that a second
>>> connection to PostgreSQL is established. The first connection never
>>> goes away!  If I continue to
>>> uninstall/reinstall/hit JSP page, the connections continue to acrue and
>>> never go away.  The number of connections can even exceed the value of
>>> "maxActive" defined in my context.xml file.  What am I doing wrong?
>>>
>>> 2.) Idle connections aren't closed
>>> As mentioned above, the connections which are opened by my web app are
>>> kept around forever and never closed.  In my context.xml file above,
>>> I've
>>> defined the "removeAbandoned" parameter to true and have set the
>>> "removeAbandonedTimeout" parameter to 20 seconds.  But the
>>> connections are
>>> never closed.  Every time I run "netstat -p -t", the connections are
>>> still there. :(
>>>
>>> 3.) Is my configuration correct for performing Pooling?
>>> I "can" get "a" database connection using this configuration.  But
>>> is this
>>> adequate to perform connection pooling?  How can I tell if/when
>>> connection
>>> pooling is actually being performed correctly?
>>> Any help would be greatly appreciated.
>>>
>>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561