Database Reference
In-Depth Information
Instead, invoke
setNull()
with a second argument that indicates the type of the column:
java.sql.Types.CHAR
for a string,
java.sql.Types.INTEGER
for an integer, and so
forth.
The
set
XXX
()
calls add quotes around data values if necessary, so don't put quotes
around the
?
placeholder characters in the statement string.
To handle a statement that returns a result set, the process is similar, but execute the
prepared statement with
executeQuery()
rather than
executeUpdate()
:
PreparedStatement
s
;
s
=
conn
.
prepareStatement
(
"SELECT * FROM profile WHERE cats > ?"
);
s
.
setInt
(
1
,
2
);
// bind 2 to first placeholder
s
.
executeQuery
();
// ... process result set here ...
s
.
close
();
// close statement
2.6. Handling Special Characters in Identifiers
Problem
You need to construct SQL statements that refer to identifiers containing special char‐
acters.
Solution
Quote each identifier so it can be inserted safely into statement strings.
Discussion
Recipe 2.5
discusses how to handle special characters in data values by using place‐
holders or quoting methods. Special characters also can be present in identifiers such
as database, table, and column names. For example, the table name
some table
contains
a space, which is not permitted by default:
mysql>
CREATE TABLE some table (i INT);
ERROR 1064 (42000): You have an error in your SQL syntax near 'table (i INT)'
Special characters are handled differently in identifiers than in data values. To make an
identifier safe for insertion into an SQL statement, quote it by enclosing it within back‐
ticks:
mysql>
CREATE TABLE `some table` (i INT);
Query OK, 0 rows affected (0.04 sec)
In MySQL, backticks are always permitted for identifier quoting. The double-quote
character is permitted as well, if the
ANSI_QUOTES
SQL mode is enabled. Thus, with
ANSI_QUOTES
enabled, both of these statements are equivalent: