Thursday, 21 February 2013

Difference Between Prepared and PreparedStatement

Four Necessary steps to complete execution of a query:
                                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


A Statement will need to perform four steps above for each SQL query. 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.

Another advantage of the PreparedStatement class is the ability to create an incomplete query and supply parameter values at execution time.
Query like this:-
"SELECT firstName FROM employees WHERE salary > ?");

after prepare a statement object we can set the values for incomplete places.
like this:-  stmnt.setInt(1, 200);

There are three different kinds of statements:
  • Statement: Used to implement simple SQL statements with no parameters.
  • PreparedStatement: Used for precompiling SQL statements that might contain input parameters.
  • CallableStatement: Used to execute stored procedures that may contain both input and output parameters.
 Statement Example:

Connection conn=null;
Statement stmnt = null;
ResultSet rs = null;
try
{
   // Create the Statement
   stmnt = conn.createStatement();

   // Execute the query to obtain the ResultSet 
   rs = stmnt.executeQuery("select * from aTable");
}
catch(Exception ex)
{

   System.err.println("Database exception: " + ex);
} 

PreparedStatement Example:

Connection conn=null;
PreparedStatement stmnt = null;
ResultSet rs = null;
try
{
   // Create the PreparedStatement 
   // Step 1-3 performed at here 
   stmnt = conn.prepareStatement("select * from aTable");

   // Execute the query to obtain the ResultSet 
   rs = stmnt.executeQuery();
}
catch(Exception ex)
{
   System.err.println("Database exception: " + ex);
} 


Parametrized PreparedStatement Example:

// Assume a database connection, conn.
PreparedStatement stmnt = null;
ResultSet rs = null;
try
{
  // Create the PreparedStatement, leaving a '?'
  // to indicate placement of a parameter.
  stmnt = conn.prepareStatement(
    "SELECT firstName FROM employees WHERE salary > ?");

  // Complete the statement 
  // Set 200 at the place of '?'
  stmnt.setInt(1, 200);

  // Execute the query to obtain the ResultSet 
  rs = stmnt.executeQuery();
}
  catch(Exception ex)
{
  System.err.println("Database exception: " + ex);
}