Statement vs PreparedStatement

[anish jain]

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]

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

[Vinoth Thirunavukarasu]

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

[Tim Holloway]

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]

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

[Deepak Pant]

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]

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]

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.