JDBC Statement设置逃逸字符

http://my.oschina.net/forrest420/blog/63539

 

JDBC Statement的逃逸字符开关设置

void setEscapeProcessing(boolean enable)

                         throws SQLException

Sets escape processing on or off. If escape scanning is on (the default), the driver will do escape substitution before sending the SQL statement to the database. Note: Since prepared statements have usually been parsed prior to making this call, disabling escape processing for PreparedStatements objects will have no effect. 

Parameters:

enable - true to enable escape processing; false to disable it 

Throws: 

SQLException - if a database access error occurs or this method is called on a closed Statement

1.

The “\%” and “\_” sequences are used to search for literal instances of “%” and “_” in pattern-matching contexts where they would otherwise be interpreted as wildcard characters.  If you use “\%” or “\_” outside of pattern-matching contexts, they evaluate to the strings “\%” and “\_”, not to “%” and “_”.

在模式匹配中使用：

当逃逸字符设置开启时， “\%” and “\_”字符就是代表 “%” and “_” 字符本身，

当逃逸字符设置关闭时， “%” and “_”作为通配符使用。

当不在模式匹配中使用：

如果  “\%” or “\_”  在模式匹配之外使用的时候，它们就是 “\%” and “\_”字符串本身，而不是 “%” and “_”字符

2.

Escape for LIKE escape characters The percent sign (%) wildcard>(%) and underbar (_) wildcard>(_) characters work like wild cards in SQL LIKE clauses (% matches zero or more characters, and _ matches exactly one character). In order to interpret them literally, they can be preceded by a backslash (\), which is a special escape character in strings. One can specify which character to use as the escape character by including the following syntax at the end of a query:

{escape 'escape-character'}

For example, the following query, using the backslash character as an escape character, finds identifier names that begin with an underbar.

stmt.executeQuery("SELECT name FROM Identifiers 

      WHERE Id LIKE '\_%' {escape '\'}");

3.

JDBC escape syntax for LIKE clauses

The percent sign % and underscore _ are metacharacters within SQL LIKE clauses. JDBC provides syntax to force these characters to be interpreted literally. The JDBC clause immediately following a LIKE expression allows you to specify an escape character:

Syntax

WHERE CharacterExpression [ NOT ] LIKE

    CharacterExpressionWithWildCard

    { ESCAPE 'escapeCharacter' }

-- find all rows in which a begins with the character "%"

SELECT a FROM tabA WHERE a LIKE '$%%' {escape '$'}

-- find all rows in which a ends with the character "_"

SELECT a FROM tabA WHERE a LIKE '%=_' {escape '='}