|
| |
Prepared Statements
The work that a database must do to execute a SQL
statement is not inconsiderable. In general, it must:
- Interpret/Parse the statement for valid syntax; throw
an exception if invalid.
- Perform semantic analysis on the statement to match
referenced tables and columns to tables and columns
in the database; throw an exception if a referenced
table or column does not exist, or there is a type
mismatch of some kind, invalid operation, etc.
- Optimize the statement. In other words,
figure out the most efficient way of executing the
statement, given the existence (or otherwise) of
indexes on columns referenced in the statement, etc.
- Create a plan. That is, create a series of
instructions for how to execute the statement, based
on the results of the optimization.
- Execute the statement, by following the plan
instructions..
- Return any results to the user.
That's a lot of work!
There are many applications where the same statement, or
almost the same statement, is executed many times during the
course of a typical day. This is especially true in OLTP
(On-Line Transaction Processing) applications, where there
typically is a small number of transactions types being
executed many, many times during a day.
What does 'almost the same statement' mean? Typically, it
means that the application is repeatedly executing the same
statement over and over again, with only certain parameters
changing. For example, consider the following statements:
SELECT name, salary
FROM employees
WHERE salary > 70000;
and:
SELECT name, salary
FROM employees
WHERE salary > 200000;
You'll notice that they are essentially the same
statement, except for the value supplied in the WHERE clause.
Normally, you'd execute this by using the following kind
of code:
stmt = conn.createStatement();
rs = stmt.executeQuery(
"SELECT name, salary " +
"FROM employees " +
"WHERE salary > 70000"// ... rs = stmt.executeQuery(
"SELECT name, salary " +
"FROM employees " +
"WHERE salary > 200000");// ... |
In other words, you'd execute almost the same query twice
(or in a typically application, many hundreds or thousands of
times a day). This can be quite inefficient, because it
repeats many of the steps involved in that execution
unnecessarily.
The PreparedStatement
Interface
There's a better way. Notice that the small change in the
WHERE clause value doesn't really have any significant effect
on many of the initial steps for execution of the SQL
statement? Let's extract the essence of the above statement:
SELECT name, salary
FROM employees
WHERE salary > ?;
By replacing the literal value with a '?', we have parameterized
the statement.
Now, let's see if we can separate out those steps which
don't have to be performed every time, in order to execute
the statement:
- Interpret/Parse the statement for valid syntax; throw
an exception if invalid.
- Perform semantic analysis on the statement to match
referenced tables and columns to tables and columns
in the database; throw an exception if a referenced
table or column does not exist, or there is a type
mismatch of some kind, invalid operation, etc.
- Optimize the statement. In other words,
figure out the most efficient way of executing the
statement, given the existence (or otherwise) of
indexes on columns referenced in the statement, etc.
- Create a plan. That is, create a series of
instructions for how to execute the statement, based
on the results of the optimization.
In other words, we need to perform most of the work only
once, and then when we actually wish to execute the
statement, all we have to do is:
- Supply the actual value for the parameter
- Execute the query, using the plan.
The execution of the first 4 steps, which need only to be
performed once, is known as preparing the
statement.
To represent this concept, JDBC provides the PreparedStatement
class. A PreparedStatement
extends from Statement,
so it is a kind of statement that provides additional
capabilities: the ability to be prepared.
PreparedStatement
provides the ability to bind parameters
to the various ? present in the statement source. In order to
accomplish this, it provides a number of setXXX(...) methods
which can specify the data and type to be bound to each ? in
the statement.
Here's an example:
package jdbc;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
public class PreparedStatementUse
{
public static void main(String[] args)
{
Connection conn = null;
PreparedStatement stmt = null;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection("jdbc:odbc:Employees");
stmt = conn.prepareStatement(
"SELECT name, salary " +
"FROM employees " +
"WHERE salary > ? " +
"ORDER BY salary");
listEmployees(stmt, 80000.00);
listEmployees(stmt, 200000.00);
}
catch(ClassNotFoundException ex)
{
ex.printStackTrace();
}
catch(SQLException ex)
{
ex.printStackTrace();
}
finally
{
if (conn != null)
{
try
{
if (stmt != null)
{
stmt.close();
}
conn.close();
}
catch(SQLException ex)
{ /* Do nothing */ }
}
}
}
private static void listEmployees(PreparedStatement stmt,
double minSalary)
throws SQLException
{
System.out.println(
"==List of employees with salary > " + minSalary + "==");
ResultSet rs = null;
try
{
stmt.setDouble(1, minSalary);
rs = stmt.executeQuery();
int count = 0;
while (rs.next())
{
String name = rs.getString(1);
double salary = rs.getDouble(2);
System.out.print(" Name: " + name);
System.out.println(", Salary: $" + salary);
}
}
finally
{
if (rs != null)
{
try
{
rs.close();
}
catch(SQLException ex)
{ /* Do nothing */ }
}
}
}
} |
which outputs something like:
==List of employees with salary > 80000.0==
Name: Michael Solistes , Salary: $90000.0
Name: Alfred J Prufrock , Salary: $120000.0
Name: Barbara Smith , Salary: $134000.0
Name: Charles Schultz , Salary: $230000.0
==List of employees with salary > 200000.0==
Name: Charles Schultz , Salary: $230000.0
Callable Statements
Most real relational databases now provide the ability to
store executable procedures, which may be called by clients.
These are called persistent stored modules (PSM)
by the SQL standard. To use these procedures, you must do the
following:
- Create (and debug) the stored procedure.
- Store it in the database
- Call the procedure from SQL
In the case of JDBC, the call to the procedure is done
with a CallableStatement,
which extends PreparedStatement,
and provides additional capabilities above and beyond what
that class provides.
While PreparedStatement
supplied a number of setXXX(...) methods which are used to
bind input parameters for the statement, CallableStatement
can also bind output parameters, that is parameters that
return data back to the client. So CallableStatement
provides a set of getXXX() methods for this purpose.
- To call a stored procedure, you first construct a
CallableStatement. For example:
CallableStatement cs = con.prepareCall("{ call DOWORK(?, ?) }");
Notice the syntax of the "SQL statement":
{ call DOWORK(?, ?) }
This is an example of the syntax used by JDBC when it
needs to be able to adapt to different native database
syntax for a feature. In this case, many databases have
their own special syntax for how they make a call to a
stored procedure. Rather than forcing clients to write
explicit syntax for a call, different for each database
driver, JDBC, like ODBC, provides a way of specifying
that the appropriate syntax needs to be invoked in a
database-specific way. The JDBC driver is responsible for
interpreting the syntax, and automatically converting it
into the native database call syntax.
- Once you have constructed the CallableStatement, you
must register each parameter that can return a value
(i.e. each OUT or INOUT parameter):
cs.registerOutParameter(2, java.sql.VARCHAR);
- You must set the values of the input parameters
exactly as you would for a PreparedStatement.
cs.setDouble(1, minSalary);
- You can execute the CallableStatement the same way
you execute a PreparedStatement:
boolean resultSetReturned = cs.execute();
- To retrieve the values of returned parameters, you
use the getXXX() methods:
String s = cs.getString(2);
- It is possible for the CallableStatement
to return more than one ResultSet,
in which case you can call getMoreResults() to check
if there is another result set available.
Stored procedures can also return values.
- If the stored procedure returns a value, it can be
constructed using the following syntax:
CallableStatement cs = con.prepareCall("{ ? = call GETVALUE(?) }");
In this case, the result is identified as an OUT
parameter by the first ?. A return value is treated as
teh first OUT parameter, and it must be registered in the
same way as other OUT parameters.
|
