Win a copy of Machine Learning with TensorFlow this week in the Artificial Intelligence and Machine Learning forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Bear Bibeault
  • Henry Wong
  • Devaka Cooray
Saloon Keepers:
  • salvin francis
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Frits Walraven
Bartenders:
  • Jj Roberts
  • Carey Brown
  • Scott Selikoff

Statement vs PreparedStatement

 
Ranch Hand
Posts: 129
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In one of the interview faced by me recently, an interviewer asked me if we can do anything and in much faster way using PreparedStatemt than Statement then why the Statement does not get removed from JDBC API's??
Please tell me the answer of the above question...
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Probably because its already in use in millions of lines of code.
 
Ranch Hand
Posts: 164
Android Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Also PreparedStatement extends Statement
They just updated Statement interface in order to have some new functionality
 
Saloon Keeper
Posts: 23295
158
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
There are a number of reasons why a plain Statement still has uses. One of them is so we can separate the professional programmers from the amateurs by giving them a way to set up for SQL injection attacks.

Seriously, the more you can reuse a PreparedStatement, the more efficient it can be (relatively speaking). However, they can also be more expensive to set up than simple Statements, so for one-shot use, a non-prepared Statement may have a performance advantage. It also does let you execute statements using criteria that might have been passed in non-parameterized from external sources.

Such as SQL injection attackers...
 
author
Posts: 4275
34
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Also, not all queries take input parameters, so it "may" use less memory to not use a PreparedStatement.
 
Ranch Hand
Posts: 446
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Statement extends Wrapper (JDBC 4.x+) and is further extended by PreparedStatement and CallableStatement. If you carefully look at the APIs, the common stuff is at higher abstract level. I think it is very much needed and will never be taken out from the APIs.
 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Basically, there is a resource/time overhead cost to "prepare" a statement in the DB Server; but that cost will be saved every time you run the statement once it's prepared while an unprepared statement will have less overhead but it will cost every time you run the statement.
 
Tim Holloway
Saloon Keeper
Posts: 23295
158
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

philippe-daniel brin wrote:Basically, there is a resource/time overhead cost to "prepare" a statement in the DB Server; but that cost will be saved every time you run the statement once it's prepared while an unprepared statement will have less overhead but it will cost every time you run the statement.



Actually, academically speaking, that's not entirely true. A common practice is for database servers to cache the source text of SQL statements they receive and use the statement text as a hashtable key to the compiled statement, so that the bulk of the work: compiling the SQL and setting up context - is only done once. At least until the cache entry expires and gets purged. However, this only applies if the SQL statement is character-for-character identical to its previous version. So the overhead for the first "SELECT * FROM xyz WHERE post_date = '2010-07-03'" is going to be more than subsequent requests. However, a "SELECT * FROM xyz WHERE post_date = '2010-07-05'" would not benefit. It would have to set up its own cached context.

This might seem to be a fairly useless thing to do, except that paged SQL fetches may use it (or a variant of it), as would periodic polling and other repeated requests. The cost of caching is quite low compared to cranking the whole thing up from scratch.
 
Good heavens! What have you done! Here, try to fix it with this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic