aspose file tools*
The moose likes JDBC and the fly likes Statement vs PreparedStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Statement vs PreparedStatement" Watch "Statement vs PreparedStatement" New topic
Author

Statement vs PreparedStatement

anish jain
Ranch Hand

Joined: Feb 03, 2010
Posts: 129
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

Joined: Apr 14, 2004
Posts: 10336

Probably because its already in use in millions of lines of code.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Vinoth Thirunavukarasu
Ranch Hand

Joined: Dec 18, 2008
Posts: 164

Also PreparedStatement extends Statement
They just updated Statement interface in order to have some new functionality

Java Best Practices
Linux Best Practices
Amortization Calculator

Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 16070
    
  21

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...


Customer surveys are for companies who didn't pay proper attention to begin with.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3707
    
    5

Also, not all queries take input parameters, so it "may" use less memory to not use a PreparedStatement.


My Blog: Down Home Country Coding with Scott Selikoff
Deepak Pant
Ranch Hand

Joined: Feb 13, 2004
Posts: 443
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

Joined: Jun 29, 2010
Posts: 3
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

Joined: Jun 25, 2001
Posts: 16070
    
  21

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.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Statement vs PreparedStatement