Writing JDBC Applications with MySQL - Using Placeholders

Sometimes it's necessary to construct queries from values containing characters that require special treatment. For example, in queries, string values are written enclosed within quotes, but any quote characters in the string itself should be doubled or escaped with a backslash to avoid creating malformed SQL. In this case, it's much easier to let JDBC handle the escaping for you, rather than fooling around trying to do so yourself. To use this approach, create a different kind of statement (a PreparedStatement), and refer to the data values in the query string by means of placeholder characters. Then tell JDBC to bind the data values to the placeholders and it will handle any special characters automatically.

Suppose you have two variables nameVal and catVal from which you want to create a new record in the animal table. To do so without regard to whether or not the values contain special characters, issue the query like this:

PreparedStatement s;
   s = conn.prepareStatement (
               "INSERT INTO animal (name, category) VALUES(?,?)");
   s.setString (1, nameVal);
   s.setString (2, catVal);
   int count = s.executeUpdate ();
   s.close ();
   System.out.println (count + " rows were inserted");

The '?' characters in the query string act as placeholders--special markers indicating where data values should be placed. The setString() method takes a placeholder position and a string value and binds the value to the appropriate placeholder, performing any special-character escaping that may be necessary. The method you use to bind a value depends on the data type. For example, setString() binds string values and setInt() binds integer values.

Error Handling