Escape Sequences (Java Enterprise in a Nutshell)

2.12. Escape Sequences

Escape sequences allow JDBC programs to package certain database commands in a database-independent manner. Since different databases implement different features (especially scalar SQL functions) in different ways, in order to be truly portable, JDBC needs to provide a way to access at least a subset of that functionality in a standard way. We've already seen escape sequences twice: with the various SQL date and time functions, and with the CallableStatement object.

A JDBC escape sequences consists of a pair of curly braces, a keyword, and a set of parameters. Thus, call is the keyword for stored procedures, while d, t, and ts are keywords for dates and times. One keyword we haven't seen yet is escape. This keyword specifies the character that is used to escape wildcard characters in a LIKE statement:

stmt.executeQuery(
   "SELECT * FROM ApiDocs WHERE Field_Name like 'TRANS\_%' {escape '\'}");

Normally, the underscore ( _ ) character is treated as a single-character wildcard, while the percent sign (%) is the multiple-character wildcard. By specifying the backslash (\) as the escape character, we can match on the underscore character itself. Note that the escape keyword can also be used outside wildcard searches. For example, SQL string termination characters (such as the single quote) need to be escaped when appearing within strings.

The fn keyword allows the use of internal scalar database functions. Scalar functions are a fairly standard component of most database architectures, even though the actual implementations vary. For instance, many databases support the SOUNDEX(string) function, which translates a character string into a numerical representation of its sound. Another function, DIFFERENCE(string1,string2), computes the difference between the soundex values for two strings. If the values are close enough, you can assume the two words sound the same ("Beacon" and "Bacon"). If your database supports DIFFERENCE, you can use it by executing a SQL statement that looks like this:

{fn DIFFERENCE("Beacon", "Bacon")}

Available scalar functions differ depending on the database being used. In addition, some drivers, such as Oracle's, do not support the {fn} escape mechanism at all.

The last escape keyword is oj, which is used for outer joins. The syntax is simply:

{oj outer-join}

Outer joins are not supported by some databases and are sufficiently complex (and unrelated to the JDBC API per se) as to be beyond the scope of this chapter. For more information, consult the SQL documentation for your database.

Note that when performance is an issue, you can use the setEscapeProcessing() method of Statement to turn off escape-sequence processing.