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

Connection pool runs out of connections

Tristan Van Poucke
Ranch Hand

Joined: Jun 30, 2008
Posts: 47
Hello,

I started on writing my own connection pool, and I something is wrong.
If i test my site with testing software it runs ok. But if I combine that with me clicking on random links in the website, I start loosing connections
And eventually I run out of connection wich leads to null pointer exceptions.

this is my code:


Am I doing something wrong ?
Bauke Scholtz
Ranch Hand

Joined: Oct 08, 2006
Posts: 2458
I have not looked at your code closely, but do you close the connections after use in your JDBC code / DAO class? Do you close connections after a period of use? Do you actually lease connections from pool? Do you reap dead connections?

After all, maybe this is just for learning purposes, but in real I would forget about it at all and go for a long-existing and thoroughly tested open source connection pooling framework which is undoubtely more robust and reliable than you ever would code. For example Apache Commons DBCP, C3P0 and Proxool. Also keep in mind that most application servers ships with built in connection pooling facilities, like Apache Tomcat. I would prefer that above a homegrown pool.

If it is actually for learning purposes, here´s an article which I found very useful when I wanted to learn something about connection pooling some ages ago: http://java.sun.com/developer/onlineTraining/Programming/JDCBook/conpool.html Note: once again, don´t use it for production. It´s just a bare example, there is much more to be done in the given code example.
Tristan Van Poucke
Ranch Hand

Joined: Jun 30, 2008
Posts: 47
Okay, thank you very much.

Now I started with apache dbcp, but I don't understand completely.

I have a singleton class wich gets properties from a property file and uses them to open connections and store them in the connectionpool.

Like this (snipped code)


Then I have this method "checkout" wich should give a connection to the user, this is being called whenever a user loads a page.



But with my previous way of connecting, I closed the connection everytime the requested page was done loading.
I used a method "checkIn()", I don't understand how i could return the method in the connection pool...
Any suggestions?
Bauke Scholtz
Ranch Hand

Joined: Oct 08, 2006
Posts: 2458
You shouldn't give a connection to an user.

You should write JDBC code the usual way. Acquire and close the connection (and statement and resultset) in the shortest possible scope. Thus already inside the same method block as you invoke queries on the database. The connection pooling implementation will then behind the scenes worry about actually closing the connection or releasing it to the pool. You shouldn't worry about it in your code. Just do connection.close() and the connection pooling implementation will take the work over.
Tristan Van Poucke
Ranch Hand

Joined: Jun 30, 2008
Posts: 47
Okay, I have done this.
I opened the connection and closed it once per page because when working without a connection pool this method seemed faster.
But now with the connectionpool I open it just before the query and close it afterward, because the connection does not really get opened and closed, this works
at about the same speed.

Only problem now is that I get a "connection is closed" SQLException on every method I try to close the connection
Tristan Van Poucke
Ranch Hand

Joined: Jun 30, 2008
Posts: 47
I found my mistake.

Apparently in some persistence method's I called other persistence methods.
That tried to get a connection from the connectionpool, and then close it.
The first persistence method will try to close that connection again.

Thanks for the help
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Connection pool runs out of connections