Using Prepared Statements
- ResultSetMetaData
- Using Prepared Statements
- PreparedStatement From DataSource Queries
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.
The 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
Similar to Statement
objects, 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 = ?");
The variable 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 param1
or 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.
Using the PreparedStatement
object 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:
setPara.setInt(2, 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();
The method 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 PreparedStatement
object 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). |