Databases Reference
In-Depth Information
Often, database applications use connection pooling, statement pooling, or a
combination of both to obtain better performance. How do these features affect
whether you should use a
Statement
or
PreparedStatement
object?
If you're using JDBC 3.0 and earlier, use the following guidelines:
•
If you're using statement pooling and a SQL statement will be executed only
once, use a
Statement
object, which is not placed in the statement pool. This
avoids the overhead associated with finding that statement in the pool.
•
If a SQL statement will be executed infrequently but may be executed multi-
ple times during the life of a statement pool inside a connection pool, use a
PreparedStatement
object. Under similar circumstances without statement
pooling, use a
Statement
object.
JDBC 4.0 provides a more granular level of statement pooling. Statement
pooling implementations give no weight to a
PreparedStatement
object that's
executed 100 times versus one that's executed only twice. JDBC 4.0 allows appli-
cations to hint to the pool manager about whether a prepared statement should
be pooled or nonpooled. Prepared statements that are executed multiple times
can be pooled to provide optimal performance. Those that are used infrequently
can be nonpooled and, consequently, do not affect the pool.
See “SQL Statements,“ page 27, for more information about using state-
ments versus prepared statements. See “Using Statement Pooling with
Connection Pooling,” page 238, for information about performance and using
statement pooling with connection pooling.
Updating large amounts of data typically is done by preparing an
Insert
state-
ment and executing that statement multiple times, resulting in many network
round trips to the database server.
Performance Tip
To reduce the number of network round trips when updating large
amounts of data, you can send multiple
Insert
statements to the
database at a time using the
addBatch()
method of the
PreparedStatement
interface.