Creating a Connection Pool

[Jehan Jaleel]

Once a Connection has been created to a JDBC data store, is there any
way to store this Connection object in memory so it does not have to
be created for each request.

I know this is what Data Sources a for, but suppose we could not
create a data source through the app server. Can it still be done
through code?

Thanks for any help,
Jehan

[Anton Golovin]

Hi, Jehan! There is a way to do so by opening a connection and then using it and returning it to the pool without closing it. Then you could have many connections sitting in a pool waiting to be used - and less overhead. I would suggest just writing a JavaBean class to accomplish that.

[Jeanne Boyarsky]

"I know this is what Data Sources a for, but suppose we could not create a data source through the app server"
Why can't use create a data source through the app server? Is this a standalone application?

[Jehan Jaleel]

" by opening a connection and then using it and returning it to the pool without closing it."

Anton,
How exactly would I return the Connection to the pool? What exactly is the pool? Is it an object by itself? Usually when I get a connection it is like this...

Class.forName("com.hxtt.sql.text.TextDriver");
Connection conn = DriverManager.getConnection("jdbc:Text...");Statement stmt = conn.createStatement();

Can you please shed some light on this?

Thanks,
Jehan

[Jehan Jaleel]

Jeanne,
The data source I am using is tab-delimeted text file and my app server (WAS) does not allow me to create a data source for it?

Alternatively do you know of any good JDBC drivers that can read text files and work with WAS?

Thanks,
Jehan

[Jeanne Boyarsky]

Jehan,
I don't know of any driver for delimitted files. Unless htxx provides one ...

A connection pool is something you need to write. The idea is that you instantiate a fixed number of connections. Then you provide methods to claim them and return them to the pool. I suggest searching for "connection pool" on the web. You may find something prewritten as this is a common problem.

[Jehan Jaleel]

The problem is even if I create an object that gives and takes back Connections, that object must still be accessed from my EJBs, so I need a way to persist it for the life of the application.

Thanks again for any help.

[Jayesh Lalwani]

I thought than one of the major reasons of using EJB's is that you dont manage database connections yourself, and you let the container manage the connections for you

Why are you opening connections from EJB in the first place?

[Jeanne Boyarsky]

Jayesh,
I asked that earlier and Jehan said it was because they couldn't find a driver for delimited files.

Jehan,
Why not make the class static or a singleton and get a copy in the bean?

[Joseph Hatton]

Which version of WAS are you on? WAS 3.5,4.03.5.01? I have never heard of DataSource coming from a tab-delimited file? If you are WAS 4.0+, you have to create the DataSource through the Admin Console.

I think you need to clarify if you want to use straight JDBC connection or use the connection pooling. Straight JDBC connection does not require datasource. ONLY connection pooling requires DataSource object. Again that reside on the server because how is the server going to manage the pool of connections if it does not reside on the server.

[Jehan Jaleel]

Jeanne,
That sounds like a good idea. I only wonder if EJBs support having a single static class that can be accessed by all beans?

[Jehan Jaleel]

Joseph,
I am currently on WAS 5.1 and to the best of my knowledge there is no DataSource for text files now. The only way I can access it now is through straight JDBC connection, I would like to create a data source for it in WAS so I can have a connection pool and not have to create a connection each time I use it.

[Anandh Ramesh]

hi,


what i suggest is read chapter 10 of the book
"Core Servlets And JSP" available in the site
www.coreservlets.com .

this deals with JDBC and Connection Pooling


Cheers
--Anandh

[Anandh Ramesh]

hi

sorry for the mistake... read chapter "18" of the book.

not chapter 10

cheers
-Anandh

[Jeanne Boyarsky]

Originally posted by Jehan Jaleel:
Jeanne,
That sounds like a good idea. I only wonder if EJBs support having a single static class that can be accessed by all beans?

Jehan,
EJBs are just classes so they can access a static class just as any other can. If you put the static class in the EJB project you should be good. Note that if you use a clustered server, you will really have one static class per clone. But this is ok as it still provides some caching/pooling capability.

Anandh,
Thanks for the reference. Note that we have an edit button (paper and pencil icon) if you want to change a previous post.

[Anandh Ramesh]

hi

i didn't notice that... thanks...

cheers
Anandh