• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Reuse PreparedStatement

 
Ranch Hand
Posts: 130
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 425
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 130
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 425
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 130
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Purushothaman Thambu

Thank You
 
Mo-om! You're embarassing me! Can you just read a tiny ad like a normal person?
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic