aspose file tools*
The moose likes JDBC and the fly likes Connection Pooling Help Needed Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Connection Pooling Help Needed" Watch "Connection Pooling Help Needed" New topic
Author

Connection Pooling Help Needed

Jack Bernstein
Greenhorn

Joined: Jan 13, 2010
Posts: 12
I have spent a long time over the past few days trying to figure out connection pooling, but all the help available seems to apply to java servlet stuff. I'm not doing anything with Tomcat or anything like that, I am just trying to create a simple connection pool for querying data from my database. What is the simplest tool for me to use to insert connection pooling code into my already existing JDBC code.

If it's relevant, I'm using Java, Eclipse, PostgreSQL, and Windows.

Any help would be greatly appreciated!
Peter Johnson
author
Bartender

Joined: May 14, 2008
Posts: 5842
    
    7

I'm not sure why you would want to use connection pooling. Connection pooling is necessary in Java EE apps because of the nature of how web apps work (discreet requests made from possibly thousands of users). Having a database connection per user is not possible and having a connection pool thus allows the users, and requests, to share a finite resource and also reduces one of the time consuming processes of responding to a web requested - establishing a database connection.

With a Java SE app, however, you can easily create a connection at the start of the app and maintain the connection during the entire life of the app. After all, only the app will use that connection, and the app is used by one user.

Unless there is something about your app that I don't know.

P.S. Not sure if your new display name is that much of an improvement, unless your email address is really off also.
By the way, welcome to Java Ranch!


JBoss In Action
Jack Bernstein
Greenhorn

Joined: Jan 13, 2010
Posts: 12
I'm not sure how to properly explain it. I have an application that queries a database about every 5 seconds. I've been asked to make it use connection pooling. Most stuff I find online doesn't seem to apply to what I'm doing though.

Also, is there a way to hide my email address? I thought that it was hidden already.
Peter Johnson
author
Bartender

Joined: May 14, 2008
Posts: 5842
    
    7

You don't need a connection pool for that - just have the app establish a connection at the start and then use that same connection every time the app needs to communicate with the database. Then close the connection before the app exits. In effect, this gives you a connection pool of 1 connection.

I have a hunch that the person telling you to use a connection pool is thinking "the app only needs to talk to the database every 5 seconds, so rather than keeping the connection open (during the time the app is not talking to the database), why not use a connection pool?" The only problem with that is that by definition a connection pool establishes N connections to the database and keeps those connections open for the duration of the pool. Yes, you can have the pooling mechanism establish more connection if needed, and close connections no longer needed, keeping the number of connections between an arbitrary min and max connection count, but let's ignore that for this discussion. The key here is that the connections in the pool remain open even when not being used.

So like I said in the prior paragraph, you can easily code a pool of 1 connection. A connection pool that ranges from 0 to 1 connections is also easy: open the connection when you need it and close it afterwards. Come to think of it, that is what your app might be doing already and thus the suggestion to "use a connection pool" might be to prevent this continuous bouncing of the connection. In which case go with the connection pool of 1 connection.
Jack Bernstein
Greenhorn

Joined: Jan 13, 2010
Posts: 12
I guess that's what I'll do then. Thanks for your help!
Rodrigo Bossini
Ranch Hand

Joined: Jul 03, 2009
Posts: 113
Peter Johnson wrote:You don't need a connection pool for that - just have the app establish a connection at the start and then use that same connection every time the app needs to communicate with the database. Then close the connection before the app exits. In effect, this gives you a connection pool of 1 connection.

I have a hunch that the person telling you to use a connection pool is thinking "the app only needs to talk to the database every 5 seconds, so rather than keeping the connection open (during the time the app is not talking to the database), why not use a connection pool?" The only problem with that is that by definition a connection pool establishes N connections to the database and keeps those connections open for the duration of the pool. Yes, you can have the pooling mechanism establish more connection if needed, and close connections no longer needed, keeping the number of connections between an arbitrary min and max connection count, but let's ignore that for this discussion. The key here is that the connections in the pool remain open even when not being used.

So like I said in the prior paragraph, you can easily code a pool of 1 connection. A connection pool that ranges from 0 to 1 connections is also easy: open the connection when you need it and close it afterwards. Come to think of it, that is what your app might be doing already and thus the suggestion to "use a connection pool" might be to prevent this continuous bouncing of the connection. In which case go with the connection pool of 1 connection.


Very interesting. Would I have any problems if I do implement the 1 connection pool and use it in a multithreaded application? Say, I have 50 threads that will probably access the database simultaneously. Can I still keep one single connection open for the entire life of the app and use this single connection in all of my threads?


I see wind mills
Peter Johnson
author
Bartender

Joined: May 14, 2008
Posts: 5842
    
    7

Rodrigo Bossini wrote:Would I have any problems if I do implement the 1 connection pool and use it in a multithreaded application?

Yes you might have a problem. You would be better off with one connection per thread. If you know up front how many threads you will have, and if each thread will be fairly long-lived, you could do one connection per thread.

For example, assume an app that generates reports base on info in the database. It does multiple reports simultaneously. For each it fires off a thread. That thread opens a connection and maintains that connection for its entire duration and closes the connection after the report is done just before the thread goes away.

If on the other hand you have short-lived threads, then you will want to use a connection pool mechanism. For example, a thread that makes only a single SQL call and then exists. This thread would ask the pool for a connection (possibly waiting until one becomes available), makes the SQL call using that connection, and then "closes" the connection, which really puts the connection back into the available pool.

If your application is using autocommit, and if your JDBC driver allows for multiple results sets to be processed simultaneously, then you could get away with a single connection After all, the database doesn't really care, nor would your application. This would also work in a pure read-only situation.

If you need to to do your own transactions then you will need a connection per thread because that is how the database manages transactions - one per connection. Same situation if the JDBC driver cannot handle multiple result sets - you will either have to sync the threads on database access or use a connection per thread.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Connection Pooling Help Needed