What is the difference between a Statement and a PreparedStatement

First analyze how a database handle these statements:
a) Parse the incoming SQL query
b) Compile the SQL query
c) Plan/optimize the data acquisition path
d) Execute the optimized query and return the data

Now following these 4 steps

A Statement will always proceed through the four steps above for
each SQL query sent to the database.

A PreparedStatement pre-executes steps (a) to (c) in the execution process above. Thus, when creating a PreparedStatement some pre-optimization is performed immediately. The resultant effect is less load on the database engine at execution time.


Statement Example


// Assume a database connection,
Connenction conn;
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 myTable");
} catch(Exception ex)
{
System.err.println("Database exception: " + ex);

}



PreparedStatement example


// Assume a database connection, conn.
PreparedStatement stmnt = null;
ResultSet rs = null;
try
{
// Create the prepared stmnt
PreparedStatement stmnt =
conn.prepareStatement("select * from myTable");
// Execute the query to obtain the
ResultSet rs = stmnt.executeQuery();

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

Another advantage of the PreparedStatement class is the ability to create an incomplete query and supply parameter values at execution time. This type of query is well suited for filtering queries which may differ in parameter value only also
known as parameterized query.

SELECT fisrtName FROM employees WHERE salary > 100

To create a parametrized prepared statement
// 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
stmnt.setInt(1, 200);
// Execute the query to obtain the
ResultSet rs = stmnt.executeQuery();
}
catch(Exception ex)
{
System.err.println("Database exception: " + ex);
}

Summarizing the differences:

1. Prepared Statement is a slightly more powerful version of a Statement and is always quick and easy to handle as a Statement.
2. Prepared Statement may be parametrized.