Connection Pool

[Tolls]

No.
It's a package (a jar in this case) that you pass a configuration to.
If this were a webapp then the webapp would do it for you (assuming you'd given the correct info to, eg, Tomcat).
Most of the examples you are getting are probably for webapps, since that's the place it makes the most sense to use them. Many desktop apps wouldn't bother.

[mine0926]

Oh! ok. I am developing desktop application in a server/client (just small LAN), so it does not matter if I do not use connection pool? And database/application performance will not be that bad as what shows in tutorials?

Many thanks for the info.

[Tolls]

Is the server going to hold the db access code?
If so (and there are mutliple clients/threads) then a pool makes sense.

[mine0926]

Pardon, but what is db access code?
There are multiple clients that will connect to database (on server) at the same time.

[Tolls]

Code that accesses the db.
But it sounds like your server is the db, so that doesn't apply.

I would start by not using a pool and grab a connection as and when.
Should the grabbing of a connection be a problem (ie it takes a while to get a connection due to network factors) then you can look into a pool. You should be able to easily swap out your getConnection code if you've written your system in a modular way.

[JosAH]

Pardon, but what is db access code
There are multiple clients that will connect to database (on server) at the same time.

That's an excellent scenario for a Connection pool; i.e. a client gets a Connection from the pool (actually the server does it on behalf of the client request) and returns it to the pool when it has obtained all the date for the client request.

kind regards,

Jos

[mine0926]

The first time of connection take time i think 5-10 seconds. Then after that connection responses fast.
Only when I test about 200000 records it respond almost a minute so I 'page' the record by 30 and it works fine.

I would like to discuss it but I have to go. I will be back tommorrow and update what I learn on tutorials.

Many many thanks... :)

[Tolls]

That's an excellent scenario for a Connection pool; i.e. a client gets a Connection from the pool (actually the server does it on behalf of the client request) and returns it to the pool when it has obtained all the date for the client request.

kind regards,

Jos

See, I read that as the client is accessing the db directly.
If there's code on the server then yes, that's a connection pool situation.

[JosAH]

See, I read that as the client is accessing the db directly.
If there's code on the server then yes, that's a connection pool situation.

I tried to clarify myself in the phrase between parentheses but obviously I failed or my reading audience is far too stupid to understand a clear discourse ;-)

kind regards,

Jos

[Tolls]

Excuuuuse me...

And I really don't like these smileys...

[mine0926]

So the client will request from the server if there is an available connection from the pool, if there is available then server will return an open connection. Will I code it myself or the tool for connection pool (.jar file like Apache DBCP) will do this requesting to the server?

Thanks

[JosAH]

Excuuuuse me...

And I really don't like these smileys...

No need to apologize, I don't like these smilies either, that's why I don't use them; they are for the stupid people around us :-P

kind regards,

Jos

[Tolls]

So the client will request from the server if there is an available connection from the pool, if there is available then server will return an open connection. Will I code it myself or the tool for connection pool (.jar file like Apache DBCP) will do this requesting to the server?

Thanks

You haven't given us much information on your architecture.

By "server" do you mean that there's some java there or do you mean "the database"?

If the latter then you won't have a connection pool on the server since it's the database.
If the former then you should have a pool.

[mine0926]

The "server" is the database, so I will not have to use connection pool, right?

Connection pool is advisable to use when I develop Web Applications? What are the other situations that connection pool is applicable?

Thank you.

[JosAH]

The "server" is the database, so I will not have to use connection pool, right?

Connection pool is advisable to use when I develop Web Applications? What are the other situations that connection pool is applicable?

Thank you.

Web applications have nothing to do with it; you use a Connection pool if you have to handle more than one database requests that have (almost) no relation with each other; if more than one client want your server to access a database a Connection pool comes in handy because the server can't open as many connections as there are clients and the server doesn't know when those clients make their requests. So your server keeps a couple Connections in a pool and uses them when it needs one.

kind regards,

Jos

[Tolls]

Connection pools are most often used on servers, like webapps.
They are also used on desktop apps (ie on the client) where a connection might take a while to create (depending on network) and you want to make use of a connection pool's reconnecting code, without having to write your own. But then the overhead (not sure, haven't really looked into that side of things) might not be worth it. The connection pool keeps its connections alive by essentially making a call on them every so often on a separate thread.

[Tolls]

Web applications have nothing to do with it; you use a Connection pool if you have to handle more than one database requests that have (almost) no relation with each other; if more than one client want your server to access a database a Connection pool comes in handy because the server can't open as many connections as there are clients and the server doesn't know when those clients make their requests. So your server keeps a couple Connections in a pool and uses them when it needs one.

kind regards,

Jos

That does not apply in this situation, since that would imply a connection pool on each client.
The server is the database.
The client is connecting directly with the database.
There is no multi-threaded server code in between.

[JosAH]

The connection pool keeps its connections alive by essentially making a call on them every so often on a separate thread.

I hate it when databases do that, i.e. close the Connections when they've been idle for some time. Those silly "select * from dual" queries over and over again ... When I want a Connection it is mine for as long as I want ;-)

kind regards,

Jos

[JosAH]

That does not apply in this situation, since that would imply a connection pool on each client.
The server is the database.
The client is connecting directly with the database.
There is no multi-threaded server code in between.

Nonono, the pool should exist at the server and clients know nothing about a database; all they know is that a server coughs up the wanted data. (at least that's what I meant to write ;-)

kind regards,

Jos

[Tolls]

I hate it when databases do that, i.e. close the Connections when they've been idle for some time. Those silly "select * from dual" queries over and over again ... When I want a Connection it is mine for as long as I want ;-)

kind regards,

Jos

Blame eejit users who don't bother logging off.
A database has limited resources, and clogging them up with twits who aren't doing anything is a waste...:)

Nonono, the pool should exist at the server and clients know nothing about a database; all they know is that a server coughs up the wanted data. (at least that's what I meant to write ;-)

kind regards,

Jos

Oh yes, quite agree.
Exposing the database like that is poor architecture...and can lead to the problem above of a database running out of available connections. And eejit users.

It also means if you move the db or change it in anyway you then have to update all the clients.