Java Reference
In-Depth Information
Using JDBC Prepared Statements
Problem
You want to save the overhead of parsing, compiling, and otherwise setting up a statement
that will be called multiple times.
Solution
Use a
PreparedStatement
.
Discussion
An SQL query consists of textual characters. The database must first parse a query and then
compile it into something that can be run in the database. This can add up to a lot of over-
head if you are sending a lot of queries. In some types of applications, you'll use a number of
queries that are the same syntactically but have different values:
select * from payroll where personnelNo = 12345;
select * from payroll where personnelNo = 23740;
select * from payroll where personnelNo = 97120;
In this case, the statement needs to be parsed and compiled only once. But if you keep mak-
ing up select statements and sending them, the database mindlessly keeps parsing and com-
piling them. Better to use a prepared statement, in which the variable part is replaced by a
parameter marker (a question mark). Then the statement need only be parsed (or organized,
optimized, compiled, or whatever) once:
PreparedStatement ps = conn.prepareStatement(
"select * from payroll where personnelNo = ?;")
Before you can use this prepared statement, you must fill in the blanks with the appropriate
set methods. These take a parameter number (starting at one, not zero like most things in
Java) and the value to be plugged in. Then use
executeQuery()
with no arguments because
the query is already stored in the statement:
