Java Reference
In-Depth Information
I
NSERTING
, U
PDATING
,
AND
D
ELETING
Performing update operations on a database is quite similar to performing queries.
A statement is passed as a string to the
Connection
object, and a
Statement
object is
returned. The statement can then be executed. The only difference is that the
method that executes an update is
executeUpdate()
, whereas the statement to exe-
cute the query was
executeQuery()
. The
executeUpdate()
method is used for in-
serting, updating, or deleting, that is, for all operations that modify the database.
Since updating is so similar to querying from a Java perspective (even though
the SQL is quite different), I'll use this opportunity to introduce two new concepts:
the prepared statement and parameter handling.
For the database manager to process SQL, two steps must occur. First, the data-
base manager must parse the SQL and decide on an optimization strategy for exe-
cution. Second, it must carry out the execution.
If you intend to use the same SQL statement repeatedly, you would like to
avoid the overhead of the first step since in every case the parsing and the strategy
determination will arrive at the same result. The prepared statement allows you to
do this. A
prepared statement
is a precompiled SQL statement with the statement
parsed and the optimization strategy already determined. By supplying placehold-
ers in the SQL statement for the values that need to change from execution to exe-
cution, you can execute the same statement repeatedly with different values and
avoid the overhead of compiling the statement more than once.
Look at the following update statement:
String updateSQL = "UPDATE POSTING_ACCOUNTS SET ACCOUNT_BAL = ?
WHERE COMPANY_ID = ?";
The question marks represent placeholders for parameters that you will supply
prior to execution. The parameters are numbered left to right in the statement, be-
ginning with 1. Parameter 1 represents the value that will be used to set the value of
ACCOUNT_BAL. Parameter 2 is the value for completing the equal condition in the
WHERE clause. You create a
PreparedStatement
by calling the
prepareStatement()
method of
Connection
.
PreparedStatement sqlStmt = dbConnection .prepareStatement(updateSQL);
Once you have prepared the statement, you then set the values of your parameters.
sqlStmt.setDouble(1, 1000.00);
sqlStmt.setString(2, "C001");