aspose file tools*
The moose likes Servlets and the fly likes Using Connection Pool question again Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Java » Servlets
Bookmark "Using Connection Pool question again" Watch "Using Connection Pool question again" New topic
Author

Using Connection Pool question again

Ken Shamrock
Ranch Hand

Joined: Jan 23, 2002
Posts: 139
I tried using the Connection Pool provided by http://www.coreservlets.com/
Since its testing servlet only query the DB once, now I changed to query two times per one Servlet Request:
String query ="SELECT * FROM user_table";
String query2 ="SELECT * FROM school_list";
Connection connection = connectionPool.getConnection();

displayTable(connection, query, out);
//connectionPool.free(connection);

//connection = connectionPool.getConnection();

displayTable(connection, query2, out);
connectionPool.free(connection);

I haven't closed my connection on my own, just use free(connection) at the end, no problem.
if I free a Connection before the SECOND query
Connection connection = connectionPool.getConnection();

displayTable(connection, query, out);
connectionPool.free(connection);

//connection = connectionPool.getConnection();

displayTable(connection, query2, out);
connectionPool.free(connection);

It CAN STILL SHOW two tables EVEN I FREE the Connection BEFORE SECOND query.
It confused me, I have free up the Connection, how come I still have Connection for the second query?
It's so strange. I had to be careful because if I've any logic mistake, the application will be totally failed..
Secondly, is it not important for I don't close a connection? (ie con.close())
but it's important for me to close resultset and connection statement? (rs.close(),stmt.close())
thanks!!!


Thanks everyone who helped me
Varun Khanna
Ranch Hand

Joined: May 30, 2002
Posts: 1400
Originally posted by Ken Shamrock:

String query ="SELECT * FROM user_table";
String query2 ="SELECT * FROM school_list";
Connection connection = connectionPool.getConnection();

displayTable(connection, query, out);
//connectionPool.free(connection);

//connection = connectionPool.getConnection();

displayTable(connection, query2, out);
connectionPool.free(connection);

I haven't closed my connection on my own, just use free(connection) at the end, no problem.

Create a pool of 3 connections. If 3 user access it simultaneously each user will be using one.Supppose 4th user comes, you will need to track somewhere how many connections are busy and how many are free,say have 2 variable named, busyConnections and freeConnections, so you will have busyConnections =3 and freeConnections=0,so using this you can request 4th user to wait, now if first user logs out he will say connectionPool.free(connection) which will "only" help in knowing that now busyConnections =2 and freeConnections=1, hence you can now let 4th user come in.
Hope this helps
-Varun


- Varun
Blake Minghelli
Ranch Hand

Joined: Sep 13, 2002
Posts: 331
It CAN STILL SHOW two tables EVEN I FREE the Connection BEFORE SECOND query.
It confused me, I have free up the Connection, how come I still have Connection for the second query?

Without looking at the connectionPool code I believe I can still safely assume that its free() method does nothing more than marking the connection as "available", i.e. it does not close the connection or do anything that would prevent you from using it if you still had a reference to it.
In your case, even though you called the free() method, you still have a reference to it via the "connection" variable. Therefore, you are still able to use the connection like normal. That's probably not a good thing since the connectionPool thinks it is no longer being used and may "give" it to someone else who calls getConnection().
I guess you as the developer must be careful and discontinue using a connection object once it's been returned (free()) to the connectionPool. Perhaps put the call to free() in a finally block so it is always the last thing that occurs.
You may also consider adding a mechanism into your connectionPool (or connection object) that throws an exception if you use a connection after it has been returned?
Good luck.


Blake Minghelli<br />SCWCD<br /> <br />"I'd put a quote here but I'm a non-conformist"
Ken Shamrock
Ranch Hand

Joined: Jan 23, 2002
Posts: 139
Thanks varun Khanna and Blake Minghelli, I get what you mean.
So,
Connection connection = connectionPool.getConnection();
displayTable(connection, query, out);
connectionPool.free(connection);
displayTable(connection, query2, out);
connectionPool.free(connection);
should be prevented.
How about the code below, is this legal and safe? (use the same connection object to query 2 different things, I think it's no problem)
Connection connection = connectionPool.getConnection();
displayTable(connection, query, out);
//connectionPool.free(connection);
//connection = connectionPool.getConnection();
displayTable(connection, query2, out);
connectionPool.free(connection);
Also, Should I close the connection object on my own or JVM will take care of that?

The code below is the safest but I think as far as the above code is legal, I should not open another object for second query, am i right? Thanks very much:
Connection connection = connectionPool.getConnection();
displayTable(connection, query, out);
connection = connectionPool.getConnection(); //or Connection connection2=...getConnection()
displayTable(connection, query2, out);
connectionPool.free(connection);
Asher Tarnopolski
Ranch Hand

Joined: Jul 28, 2001
Posts: 260
did you consider to use prepareStament, to add batches to it and to execute the batch?


Asher Tarnopolski
SCJP,SCWCD
Ken Shamrock
Ranch Hand

Joined: Jan 23, 2002
Posts: 139
preparestmt..sometimes,
my database class will handle it, in the past.
now maybe need to rewrite some code
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Using Connection Pool question again