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.
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.
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...
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.
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
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.