Difference between revisions of "Documentation/DevGuide/Database/Using Prepared Statements"

Latest revision as of 14:57, 21 December 2020

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();