Using Prepared Statements
Sometimes it is convenient or efficient to use a
PreparedStatement object to send SQL statements to the database. This special type of statement includes the more general service com.sun.star.sdbc.Statement already discussed.
When to Use a PreparedStatement Object
Using a PreparedStatement object reduces execution time, if executing a
Statement object many times as in the example above.
The main feature of a
PreparedStatement object is that it is given an SQL statement when it is created, unlike a
Statement object. This SQL statement is sent to the DBMS right away where it is compiled. As a result, the
PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when the
PreparedStatement is executed, the DBMS can run the
PreparedStatement's SQL statement without having to analyze and optimize it again.
PreparedStatement objects can be used for SQL statements without or without parameters. The advantage of using SQL statements with parameters is that the same statement can be used with different values supplied each time it is executed. This is shown in an example in the following sections.
Creating a PreparedStatement Object
PreparedStatement objects are created using
prepareStatement() on a
Connection object. Using our open connection con from the previous examples, code could be written like the following to create a
PreparedStatement object that takes two input parameters:
XPreparedStatement updateStreet = con.prepareStatement( "UPDATE SALESMAN SET STREET = ? WHERE SNR = ?");
updateStreet now contains the SQL update statement that has also been sent to the DBMS and precompiled.
Supplying Values for PreparedStatement Parameters
Before executing a
PreparedStatement object, values to replace the question mark placeholders or named parameters, such as
param2 have to be supplied. This is accomplished by calling one of the
setXXX() methods defined in the interface com.sun.star.sdbc.XParameters of the prepared statement. For instance, to substitute a question mark with a value that is a Java int, call
setInt(). If the value is a Java String, call the method
setString(). There is a
setXXX() method for each type in the Java programming language.
updateStreet() from the previous example, the following line of code sets the first question mark placeholder to a Java String with a value of '34 Main Road':
XParameters setPara = (XParameters)UnoRuntime.queryInterface(XParameters.class, updateStreet); setPara.setString(1, "34 Main Road");
The example shows that the first argument given to a
setXXX() method indicates which question mark placeholder should be set, and the second argument contains the value for the placeholder. The next example sets the second placeholder parameter to the Java int 1:
After these values have been set for its two input parameters, the SQL statement in
updateStreet is equivalent to the SQL statement in the String object
updateString() used in the previous update example. Therefore, the following two code fragments accomplish the same thing:
Code Fragment 1:
String updateString = "UPDATE SALESMAN SET STREET = '34 Main Road' WHERE SNR = 1"; stmt.executeUpdate(updateString);
Code Fragment 2:
XPreparedStatement updateStreet = con.prepareStatement( "UPDATE SALESMAN SET STREET = ? WHERE SNR = ? "); XParameters setPara = (XParameters)UnoRuntime.queryInterface(XParameters.class,updateStreet); setPara.setString(1, "34 Main Road"); setPara.setInt(2, 1); updateStreet.executeUpdate();
executeUpdate() was used to execute the
Statement stmt and the
PreparedStatement updateStreet. Notice that no argument is supplied to
executeUpdate() when it is used to execute
updateStreet. This is true because
updateStreet already contains the SQL statement to be executed.
Looking at the above examples, a
PreparedStatement object with parameters was used instead of a statement that involves fewer steps. If a table is going to be updated once or twice, a statement is sufficient, but if the table is going to be updated often, it is efficient to use a
PreparedStatement object. This is especially true in a situation where a for loop or while loop can be used to set a parameter to a succession of values. This is shown later in this section.
Once a parameter has been set with a value, it retains that value until it is reset to another value or the method
clearParameters() is called. Using the
updateStreet, the following code fragment illustrates reusing a prepared statement after resetting the value of one of its parameters and leaving the other one as is:
// set the 1st parameter (the STREET column) to Maryland setPara.setString(1, "Maryland"); // use the 2nd parameter to select George Flint, his unique identifier SNR is 4 setPara.setInt(2, 4); // write changes to database updateStreet.executeUpdate(); // changes STREET column back to Michigan road // the 2nd parameter for SNR still is 4, only the first parameter is adjusted updateStreet.executeUpdate(); setPara.setString(1, "Michigan road"); // write changes to database updateStreet.executeUpdate();
|Content on this page is licensed under the Public Documentation License (PDL).|