Writing MySQL-Based Programs - MySQL - page 89

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: