Friday, 25 January 2013

Difference between CreateStatement and PreparedStatement

CreateStatement(i) Use for general-purpose access to your database. (ii) Useful when you are using static SQL statements at runtime because The Statement interface cannot accept parameters.

(iii) Usually suitable for DDL commands such as Create,alter,drop,etc(iv) In terms of efficiency, it is suitable to use CreateStatement only when we know that we will not need to execute the SQL query multiple times.ex- Statementstmt= con.createStatement();

stmt.executeUpdate("DROP TABLE PRODUCTS IF EXISTS");

where as PreparedStatement Use (i) when you plan to use the SQL statements many times i.ein the context of multiple executions. foreg- suppose a table to require 1000 times a insert same statement,we go for a prepare statment,(ii) you must use a PreparedStatement object if you want to use large objects like BLOBs or CLOBs.(iii) to run dynamic queries beacuseThe PreparedStatement interface accepts input parameters at runtimeex- String sql="select * from emp where emp_id = ?";PreparedStatement pStmt = conn.prepareStatement(sql); pStmt.setLong(1, profile.getUserId());

(v) Prepared is faster because it is precompiled.Most relational databases handles a JDBC / SQL query in four steps:

Parse the incoming SQL query

Compile the SQL query

Plan/optimize the data acquisition path

Execute the optimized query / acquire and return data

A Statement will always proceed through the four steps above for each SQL query sent to the database. A PreparedStatement pre-executes steps (1) - (3) in the execution process above. Thus, when creating a PreparedStatement some pre-optimization is performed immediately. The effect is to lessen the load on the database engine at execution time.

(vi)Prepared is an important protection from SQL injection attacks.

Conclusion :

We can perform all operations using both statements but these above described certain context in which one statement is efficient over another like

For DDL statement use CreateStatement() because these operations used rarely.
For DMl statements use PreparedStatent(). However if any operation that needs to be performed one or two time during life cycle you can use any one of them.