In JDBC, strings containing SQL commands are just normal strings - the SQL is not parsed or interpreted by the Java compiler. So there is no special mechanism for dealing with special characters;
if you need to use a quote (") within a Java string, you must escape it.
The Java programming language supports all the standard C escapes, such as \n for newline, \t for tab, etc. In this case, you would use \" to represent a quote within a string literal:
String stringWithQuote =
"\"No,\" he replied, \"I did not like that salted licorice.\"";
This only takes care of one part of the problem: letting us control the exact string that is passed on to the database. If you want tell the database to interpret characters like a single quote (') literally (and not as string delimiters, for instance), you need to use a different method. JDBC allows you to specify a separate, SQL escape character that causes the character following to be interpreted literally, rather than as a special character.
An example of this is if you want to issue the following SQL command:
SELECT * FROM BIRDS
WHERE SPECIES='Williamson's Sapsucker'
In this case, the apostrophe in "Williamson's" is going to cause a problem for the database because SQL will interpret it as a string delimiter. It is not good enough to use the C-style escape \', because that substitution would be made by the Java compiler before the string is sent to the database.
Different flavors of SQL provide different methods to deal with this situation. JDBC abstracts these methods and provides a solution that works for all databases. With JDBC you could write the SQL as follows:
Statement statement = // obtain reference to a Statement
statement.executeQuery(
"SELECT * FROM BIRDS WHERE SPECIES='Williamson/'s Sapsucker' {escape '/'}");
The clause in curly braces, namely {escape '/'}, is special syntax used to inform JDBC drivers what character the programmer has chosen as an escape character. The forward slash used as the SQL escape has no special meaning to the Java compiler; this escape sequence is interpreted by the JDBC driver
and translated into database-specific SQL before the SQL command is issued to the database.
Escape characters are also important when using the SQL LIKE clause. This usage is explicitly addressed in section 11.5 of the JDBC specification:
The characters "%" and "_" have special meaning in SQL LIKE clauses (to match zero or more characters, or exactly one character, respectively). In order to interpret them literally, they can be preceded with a special escape character in strings, e.g. "\". In order to specify the escape character used to quote these characters, include the following syntax on the end of the query:
{escape 'escape-character'}
For example, the query
SELECT NAME FROM IDENTIFIERS WHERE ID LIKE '\_%' {escape '\'} finds identifier names that begin with an underbar.
