JDBC connection pooling 1

[siberian]

Hello all, long time answer giver in the perl forums, first time question asker in the Java forum

I am designing my application and I am trying to get a solid handle on how JDBC handles database connection pooling. With Apache:BI I know that each database has its own handle and these handles are cached. This means that a mod_perl process can open and cache however many different databases I tell it to open but EACH database is individually cached.

I have not dug into this too much with JDBC yet but is this essentially how it is handled as well? Or does JDBC connect to the service and pool at that level?

If it is pooling at the database level like Apache:BI does has anyone seen what sorts of limitations in terms of how many persistant handles can stick around?

Generally speaking I am trying to decide whether to create a database instance per customer or if I should have one large database with a customer identifier. I know with Apache:BI its much more efficient to have the one large DB but it adds a layer of complexity to the code since every object has to have a customer context available to every method.

Not sure if it matters but I am using Firebird for my backend.

Thanks!

 

[sedj]

Generally speaking a connection pool does this :

1) When the application server starts, the db is contacted via TCP sockets, and a "Connection" object is obtained.
2) These connections are stored in a vector (*or similar object holder)
3) When a client requires a connection, a Connection object is leased from the pool (but no extra phsical network connection to the db is made)
4) When the client closes the connection, the Connection object is returned to the pool, rather than the physical network connection dropped.

 

[siberian]

This sounds like a high levle explaination. The deeper issue is :
Is the connection on the actual DATABASE level or is it on the DAEMON level.

So lets say I have 1 instance of a DB running with 20 tables.

1) If I make a pool of connections to the daemon do I have the ability to maintain my pool and connect to all databases running on the instance?

Or

2) Do I need to make a pool for each individual database running on the instance.

So its an instance vs db pooling thing. I think when you say '(but no extra phsical network connection to the db is made)' you are telling me that 1 is the way it works. Is that correct?

Thanks for the help!

 

[sedj]

I'm not sure what you mean by "DAEMON level".

Generally, an instance of a connection pool is assocaiated with one set of dbhost/dbname/user/password combinations. You can achive multiple db/dbname pools by instantiating serveral pools ...

 

[siberian]

'Daemon level' - Sorry, unixy, thinking in terms of a demon or daemon, should be calling it an 'instance'.

This is the same as my experience has been, pooling is done at the db level, not the instance level.

Thanks everyone for clearing that minor point up. Catching my stride now.

 

[sedj]

Yeah, I'm familiar with a daemon concept, but unsure of your meaning in terms of a conn pool - which is why I asked ... btw, daemons in java are often referred to as 'Threads' - ie background processes which are *somewhat* disconnected from the calling object - like "./myscript &" in *ix world ...

Interstingly enough, the Thread.setDaemon() call is meant to garbage collect the daemon thread when the calling object is destroyed (ie garbage collected) - but in practice I have found this is unreliable, causing a JVM to hang on exit sometimes. I think its because Thread.stop() has been deprecated in 1.4 SDK, and the GC retains a weak reference. Still, thats off the topic ...

Any more queries, we're always here !