• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Using Connection Pool question again

 
Ken Shamrock
Ranch Hand
Posts: 139
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!!!
 
Varun Khanna
Ranch Hand
Posts: 1400
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Blake Minghelli
Ranch Hand
Posts: 331
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Ken Shamrock
Ranch Hand
Posts: 139
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 260
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
did you consider to use prepareStament, to add batches to it and to execute the batch?
 
Ken Shamrock
Ranch Hand
Posts: 139
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
preparestmt..sometimes,
my database class will handle it, in the past.
now maybe need to rewrite some code
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic