• 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

Whats the difference in Statement and PreperedStatement ?

 
Ranch Hand
Posts: 241
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
is there any difference in performace using preparedStatement instead statement or it�s used just for let the code more elegant ?
 
Ranch Hand
Posts: 4982
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In DB aspect, each Statement is different. Thus, even the DB cache the Statement, the hit ratio will be low unless the EXACT statement is being executed again.
In addition, the following statetments are different:
SELECT * FROM TABLE WHERE ID='10';
and
SELECT * FROM TABLE WHERE ID = '10';

For PreparedStatement, if the same SQL is used, but only parameter is different, like:
SELECT * FROM TABLE WHERE ID='10';
AND
SELECT * FROM TABLE WHERE ID='20';
If you use Statement, the cached SQL will not be reused. However, if you used:
SELECT * FROM TABLE WHERE ID=?;
This SQL can be reused by passing different values of IDs in it.
In addition, for security reason, PreparedStatement is better, as you cannot directly pass a SQL to the server for execution.
For example, if you pass the SQL directly:

The hacker may delete all records inside the DB.
But if you use PrepareStatement, you can avoid direct execution of SQL.
Of course, it all depends on how your data interaction design.
Nick
 
author
Posts: 11962
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Vinicius Boson:
is there any difference in performace using preparedStatement instead statement or it�s used just for let the code more elegant ?


For me, the biggest advantage of using a PreparedStatement is that the code is more robust -- you don't have to worry about encoding variables correctly and you don't need to worry about SQL attacks.
Regarding the performance aspect, the PreparedStatement is faster only when the same SQL skeleton is used, say, 1000 times in a row -- before that it's likely that Statement yields better performance. However, it all depends on the JDBC driver you're using so it's better to simply try it out and see it for yourself which is faster.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic