How Do I: Use a "WHERE... LIKE" Clause in a Database Control?

Curly braces ({}) within literals (strings within quotes) are ignored. This means that statements like the following will not work as you might expect:

/**
 * @jc:sql statement::
 *     SELECT name
 *     FROM employees
 *     WHERE content LIKE '%{partialName}%'
 * ::
 */
public String[] partialNameSearch(String partialName);

Since the curly braces are ignored inside the literal string, the expected substitution of the partialName Java String into the SELECT statement does not occur. To avoid this problem, pre-format the match string before invoking the Database control method, as shown below. Note that single quotes are not included in the pre-formatted string because single quotes are implicitly added to the substitution value when it is passed to the SQL query.

String partialNameToMatch = "%" + matchString + "%"
String [] names = myDBControl.partialNameSeach(partialNameToMatch);

Then pass the pre-formatted string to the Database control:

/**
 * @jc:sql statement::
 *     SELECT name
 *     FROM employees
 *     WHERE name LIKE {partialNameToMatch}
 * ::
 */
public String[] partialNameSearch(String partialNameToMatch);

For more information about parameter substitution in Database control methods, see Parameter Substitution in @jc:sql Statements.

Related Topics

Database Control