Re: PostgreSQL/Tomcat JNDI Datasource Questions

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.
>>
>

--
Thank you,
Warren Killian
warrenk19@comcast.net

Q: How many Zen masters does it take to screw in a light bulb?
A: None. The universe spins the bulb and the Zen master stays out
   of the way.