IT Certificaions

Google Cloud Platform

Sunday, January 30, 2011

Statement and Prepared Statement:::::Nucleous Software Java Interview Questions: Qu1

Difference between Statement and Prepared Statement in JDBC


1. The PreparedStatement is a slightly more powerful version of a Statement, and should always be at least as quick and easy to handle as a Statement

The PreparedStatement may be parametrized.

2. In general a PreparedStatement will outperform a Statement, but there are cases in which a Statement is faster, and you may have created this situation. The main advantage to a PreparedStatement is that it does not need to be compiled on the database every time, the database compiles it once and prepares itself as well as it can given that it doesn't know everything about the query. It may be possible to add more performance tweaks, but the database just can't be sure.

When it comes to a Statement with no unbound variables, the dtaabae is free to optimise to its full extent. The individual query will be faster, but the down side is that you need to do the database compilation all the time, and this is worse than the benefit of the faster query.

Unless the same Statement gets run over and over, in which case it will be cached too and behave like a better optimised PreparedStatement. In practice I've found the difference is not actually worth worrying about. Even if a PreparedStatement is 'no better than' a Statement, it's flexibility is much nicer. 


3.
InterfacesRecommended Use
StatementUse for general-purpose access to your database. Useful when you are using static SQL statements at runtime. The Statement interface cannot accept parameters.
PreparedStatementUse when you plan to use the SQL statements many times. The PreparedStatement interface accepts input parameters at runtime.
CallableStatementUse when you want to access database stored procedures. The CallableStatement interface can also accept runtime input parameters.