Using SQL parameters

When you create an SQL statement, you can create SQL parameters in order to reuse the statement with different values. This practice can provide performance benefits. Prepare generic statements that use named variables, and then execute the statements when they are required by iterating through the variable values, binding the values to the named variables in each iteration.

You can choose from the following ways to number the parameters:

  • A question mark (?) in the statement causes each parameter to be numbered sequentially, starting from 1.

  • A question mark followed by an integer (?NNN) in the statement provides each parameter with the number NNN.

You can use the Statement.bind() method to provide names for SQL parameters. The bind() method takes the number of the parameter and the value to be bound to it. If you use a number outside of the allowed range, a DatabaseException is thrown. All bindings can be reset using Statement.reset().

Here's an example of a statement that uses parameters to create an upper bound and lower bound that can be defined each time the statement is run. This example numbers the parameters sequentially.

Statement s = Database.createStatement("SELECT * FROM T WHERE a < ? AND a > ?");
    s.bind(1, upperBound);
    s.bind(2, lowerBound);
    Cursor c = s.getCursor();

Here's an example of the same statement, except that explicit numbers are specified for the parameters:

Statement s = Database.createStatement("SELECT * FROM T WHERE a < ?5 AND a > ?12");
    s.bind(5, upperBound); 
    s.bind(12, lowerBound);

The getFormalName() method converts a parameter index to an SQL parameter name. For getFormalName() to be able to return the parameter name, you must provide a name in the query. For example, when you call getFormalName(1), the statement "SELECT * FROM T WHERE a = :a" returns :a. When parameters such as a question mark (?) are used as placeholders, getFormalName() cannot return a parameter name. For example, getFormalName(1) will not return the name for the parameter in this statement: "SELECT * FROM T WHERE a = ?"

Was this information helpful? Send us your comments.