Difference between Statement And PreparedStatement acc to given condition

Question

Most relational databases handles a JDBC / SQL query in four steps:

1. Parse the incoming SQL query
2. Compile the SQL query
3. Plan/optimize the data acquisition path
4. Execute the optimized query / acquire and return data

could someone explain me these steps in these following code

Statement st=con.createStatement();
ResultSet rset=st.executeQuerry("Select * from tab");

PrepareStatement stm=con.preparedStatement("select * from tab");
ResulSet rset=stm.executeQuerry();

what difference acc to above will be in these?

Answer 1

PrepareStatementare pre compiled and thus it will be faster. Notable difference comes when you have where conditions in select statement, with Statement sql queries are subject to SQL Injection whereas using PrepareStatement one is safe from SQL Injection.

The same applies when you have DML statements like INSERT, UPDATE, DELETE etc.

You can find more details https://stackoverflow.com/a/13245124/599528

Answer 2

Prepared Statement queries are pre-compiled on database and there access plan will be reused to execute further queries which allows them to execute much quicker than normal queries generated by Statement object.

Prepared Statement also allows you to write dynamic and parametric query.Using parametric queries and PreparedStatement you prevent many forms of SQL injection because all the parameters passed as part of place-holder will be escaped automatically by JDBC Driver.

Answer 3

Difference will be visible in terms of multiple invocation, as PreparedStatements are stored in a pre-compiled format, if the driver supports precompilation, then the con.preparedStatement("select * from tab"); will send the the statement to the database for precompilation. You can see the difference here :

http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#createStatement()

http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#prepareStatement(java.lang.String)