Configure Connection Pooling

The configuration of connection pooling varies from one J2EE/Web/Database server to another. Here we illustrated some of them. You have to consult the document of the server you are using.

Tomcat 5.5 + MySQL

To configure connection pooling for Tomcat 5.5, you have to edit $TOMCAT_DIR/conf/context.xml[65], and add the following content under the <Context> element. The information that depends on your installation and usually need to be changed is marked in the blue color.

<!-- The name you used above, must match _exactly_ here!
    The connection pool will be bound into JNDI with the name    
    "java:/comp/env/jdbc/MyDB"    
-->
<Resource name="jdbc/MyDB" username="someuser" password="somepass" 
    url="jdbc:mysql://localhost:3306/test"     
    auth="Container" defaultAutoCommit="false"     
    driverClassName="com.mysql.jdbc.Driver" maxActive="20"     
    timeBetweenEvictionRunsMillis="60000"     
    type="javax.sql.DataSource" />    
</ResourceParams>

Then, in web.xml, you have to add the following content under the <web-app> element as follows.

<resource-ref>
<res-ref-name>jdbc/MyDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

JBoss + MySQL

The following instructions is based on section 23.3.4.3 of the reference manual of MySQL 5.0.

To configure connection pooling for JBoss, you have to add a new file to the directory called deploy ($JBOSS_DIR/server/default/deploy). The file name must end with "-ds.xml", which tells JBoss to deploy this file as JDBC Datasource. The file must have the following contents. The information that depends on your installation and usually need to be changed is marked in the blue color.

<datasources>
<local-tx-datasource>
<!-- This connection pool will be bound into JNDI with the name
"java:/MyDB" -->
<jndi-name>MyDB</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/test</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>someser</user-name>
<password>somepass</password>

<min-pool-size>5</min-pool-size>

<!-- Don't set this any higher than max_connections on your
MySQL server, usually this should be a 10 or a few 10's
of connections, not hundreds or thousands -->

<max-pool-size>20</max-pool-size>

<!-- Don't allow connections to hang out idle too long,
never longer than what wait_timeout is set to on the
server...A few minutes is usually okay here,
it depends on your application
and how much spikey load it will see -->

<idle-timeout-minutes>5</idle-timeout-minutes>

<!-- If you're using Connector/J 3.1.8 or newer, you can use
our implementation of these to increase the robustness
of the connection pool. -->

<exception-sorter-class-name>com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter</exception-sorter-class-name>
<valid-connection-checker-class-name>com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker</valid-connection-checker-class-name>

</local-tx-datasource>
</datasources>

JBoss + PostgreSQL

<datasources>
    <local-tx-datasource>    
    <!-- This connection pool will be bound into JNDI with the name    
     "java:/MyDB" -->    
    <jndi-name>MyDB</jndi-name>    
        
    <!-- jdbc:postgresql://[servername]:[port]/[database name] -->    
    <connection-url>jdbc:postgresql://localhost/test</connection-url>    

    <driver-class>org.postgresql.Driver</driver-class>    
    <user-name>someuser</user-name>    
    <password>somepass</password>    
    <min-pool-size>5</min-pool-size>    
    <max-pool-size>20</max-pool-size>    
    <track-statements>false</track-statements>    
    </local-tx-datasource>    
</datasources>