Lennart Jorelid Short answer:
- 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.
|
Longer answer: 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.
Code samples |
Statement example |
// Assume a database connection, 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 aTable");
}
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 PreparedStatement
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);
}
|
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:
SELECT firstName FROM employees WHERE salary > 50
SELECT firstName FROM employees WHERE salary > 200
To create a parametrized prepared statement,
use the following syntax:
// 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);
}
|