aspose file tools*
The moose likes JDBC and the fly likes Reuse PreparedStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Reuse PreparedStatement" Watch "Reuse PreparedStatement" New topic
Author

Reuse PreparedStatement

Syed Saifuddin
Ranch Hand

Joined: Sep 01, 2003
Posts: 130
Hi

I am reading JDBC 3 Specs and found the enhancement "Reuse of prepared statements by connection pools" it is not clear to me and when I search it on google it create more confusion to me.

First thing what is in my mind is when we use preparedStatement as
/*************************************************/
1 - PreparedStatement ps = conn.prepareStatement(SQL);
2 - ps.setInt(1 , Intarg);
3 - ps.executeQuery();
4 - ps.close();
5 - conn.close();
/**************************************************/

The SQL in line 1 is parse and cashe. But after reading new material it seems to be that the SQL is cashed until the ps.close() is not called. And once the ps.close() and conn.close() is called the code is start execution from the start.

I dont know that every thing is clear or not. but for simplicity think as the above five line is in procedure.

Then the ps is built every time from the start or reuse when it call repetedly. Keep in mind that line 4 and line 5 is executed before the function return.

Any effort is really appreciated
Thank You


Thank You & Best Regards,

Syed Saifuddin,
Senior Software Engineer

SAP Oracle AIX & Java Training
http://www.socialinet.com
Purushoth Thambu
Ranch Hand

Joined: May 24, 2003
Posts: 425
Pooled prepared statements are just like prepared statement. You should perform all the 5 steps you listed. When you prep.close() the logical prepared statement is closed not the physical prepared statement. The prepared statement is returned to the pool.

When you create a same prepared statement the java will check to see if similar prepared statement is already available and if available then the statement is returned to user. The advantage is the circumventing overhead of creating prepared statement:
- Parse
- Creating execution plan (same as in prepared statement - here there is a possibility that when close() is called the prepared statement might have been removed from the DB library cache. With pooled prepared statement, the CURSOR is never allowed to be closed by the DB by keeping it open)
Syed Saifuddin
Ranch Hand

Joined: Sep 01, 2003
Posts: 130
Hi Purushothaman Thambu
ThANK YOU for ur reply
but one thing you says that
"Pooled prepared statements are just like prepared statement"

this mean Pooled prepared statements and prepared statement are similar but different thing.

please make more clearification

Thank you again
Purushoth Thambu
Ranch Hand

Joined: May 24, 2003
Posts: 425
From a programmer point of view both pooled and non-pooled prepared statement means same things. Pooled statements are managed by the server. The physical connection doesn't get closed when you invoke close() on pooled prepared statement- that's the difference.
Syed Saifuddin
Ranch Hand

Joined: Sep 01, 2003
Posts: 130
Hi Purushothaman Thambu

Thank You
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Reuse PreparedStatement