Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Statement vs PreparedStatement

 
anish jain
Ranch Hand
Posts: 129
  • Mark post as helpful
  • send pies
  • 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...
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Probably because its already in use in millions of lines of code.
 
Vinoth Thirunavukarasu
Ranch Hand
Posts: 164
Android Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Also PreparedStatement extends Statement
They just updated Statement interface in order to have some new functionality
 
Tim Holloway
Saloon Keeper
Pie
Posts: 18166
53
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • 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...
 
Scott Selikoff
author
Saloon Keeper
Posts: 4014
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Also, not all queries take input parameters, so it "may" use less memory to not use a PreparedStatement.
 
Deepak Pant
Ranch Hand
Posts: 446
  • Mark post as helpful
  • send pies
  • 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.
 
philippe-daniel brin
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • 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
Pie
Posts: 18166
53
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • 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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic