Java Cookbook

Using JDBC Parameterized 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 overhead 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 only needs to be parsed and compiled once. But if you keep making up select statements and sending them, the database will mindlessly keep parsing and compiling them. Better to use a prepared statement in which the variable part is replaced by a special marker (a question mark in JDBC). 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, since the query is already stored ...