JDBC persistence connection

HaVoC

I am doing a simply java servlet that runs a query and spits back html.
Problem is that when i first run the servlet i have to initial my connection pool
which takes ages.

What would be the best way to setup a persistence JDBC connection pool do reduce the delay of initialising the connection pool ?

Static

HaVoC wrote:

Problem is that when i first run the servlet i have to initial my connection pool
which takes ages.

What would be the best way to setup a persistence JDBC connection pool do reduce the delay of initialising the connection pool ?

If it's a case of losing the connection because you restart the app server, then you'll just have to live with that. If you're just redeploying the app, perhaps look at using Data Sources (any half decent appserver should support them).

lynchie

Why does your connection pool take ages to initialise? How many connections are you putting in the pool? You are only initialising the pool once, correct?

madramor

A connection pool manages a number of connections to a persistant storage device (usually a DB)

The reason a connection pool is used; the biggest overhead when performing DB requests is in the creation of the connection to the database.

So the Connection Pool has a number of connection to the databse that have already been created
these can be used when required without having to perform the whole creat connection process
they are returned to the pool after they are used, but not closed so that they can be used again.

Look how apache uses connection pooling,
you can specify the number of connections to create on startup,
this means the expensive work of creating the connections that are placed in the pool is performed at startup and not when they are first required, which is what is causing your problem.

So you want to create a number of connections when the pool is started.
apache has the "initialSize" parameter that you can set, default is 0
http://jakarta.apache.org/commons/dbcp/configuration.html

What type of connection pool are you using?

shamrock2004

What about JNDI?Pretty useful for connection pooling. Check this out, tomcat + JNDI. http://tomcat.apache.org/tomcat-4.1-doc/jndi-datasource-examples-howto.html

HaVoC

I have been using this connection pool from coreservlets :http://archive.coreservlets.com/coreservlets/ConnectionPool.java

I only initialise the the pool once at start up of the servlet in init() method.
I'm going to have a look at JNDI Datasource looks promising

madramor

HaVoC wrote:

I'm going to have a look at JNDI Datasource looks promising

1:
that link is for version 4, which has a different syntax to the latest version 5.5 which vastly superior.

2:
JNDI has nothing to do with Connection Pooling, it is an interface to components

3:
If you setup the JNDI data source you would still have the exact same problem.

4:
JNDI datasource
http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html

provides an interface to
http://jakarta.apache.org/commons/dbcp/configuration.html

which is already included with tomcat, that is what I linked to in my previous post and told you how to solve your problem

5:

HaVoC wrote:

I only initialise the the pool once at start up of the servlet in init() method.

you never put any database code in a servlet

HaVoC

Cool thanks for the reply madramor.

It started of as a slap together one report now there is talk of more and more so I'm trying to get it coded properly now.