CallableStatement

Callable statements are implemented by the CallableStatement object. A CallableStatement is a way to execute stored procedures in a JDBC-compatible database. The best reference for this is Sun's Javasoft web site ( https://java.sun.com/products/jdbc/  ), because callable statements represent a changing and evolving standard, and their application will depend greatly on your version of Java, and JDBC.

PreparedStatement

A PreparedStatement, in contrast to a CallableStatement, is used for SQL statements that are executed multiple times with different values. For instance, you might want to insert several values into a table, one after another. The advantage of the PreparedStatement is that it is pre-compiled, reducing the overhead of parsing SQL statements on every execution. Example 12-6 is an example of how a PreparedStatement might be used.

PreparedStatement ps = null;

try {
  ps = c.prepareStatement("INSERT INTO authors VALUES (?, ?, ?)");
  ps.setInt(1, 495);
  ps.setString(2, "Light-Williams");
  ps.setString(3, "Corwin");
} catch (SQLException se) {
  System.out.println("We got an exception while preparing a statement:" +
                     "Probably bad SQL.");
  se.printStackTrace();
  System.exit(1);
}

try {
  ps.executeUpdate();
} catch (SQLException se) {
  System.out.println("We got an exception while executing an update:" +
                     "possibly bad SQL, or check the connection.");
  se.printStackTrace();
  System.exit(1);
}

You can see that Example 12-6 prepares a statement in a similar fashion as before, except it uses a question mark (?) character in place of each value that you want to supply. Use the appropriate PreparedStatement set method (e.g., setInt, setString) to set each value. The specific set method that you use for a column depends on the data type of the column.

The PreparedStatement approach is useful because it avoids manual conversion of Java types to SQL types. For instance, the you do not have to worry about quoting or escaping when going to a text type.

Notice that the first parameter passed to a set method indicates the specific placeholder parameter (the question marks) that you are setting. A value of 1 corresponds to the first question mark, a value of 2 corresponds to the second, and so on.

The other strength of the PreparedStatement is that you can use it over and over again with new parameter values, rather than having to create a new Statement object for each new set of parameters. This approach is obviously more efficient, as only one object is created.

Use the set methods each time to specify new parameter values.

ResultSetMetaData

You can interrogate JDBC for detailed information about a query's result set using a ResultSetMetaData object. ResultSetMetaData is a class that is used to find information about the ResultSet returned from a executeQuery call. It contains information about the number of columns, the types of data they contain, the names of the columns, and so on.

Two of the most common methods in the ResultSetMetaData are getColumnName and getColumnTypeName. These retrieve the name of a column, and the name of its associated data type, respectively, each in the form of a String.

Example 12-7 is an example of using the ResultSetMetaData to get the name and data type of the first column in a ResultSet called rs. This code could logically follow the acquisition of the ResultSet named rs in Example 12-4.

Overall, the PreparedStatement mechanism is considerably more robust than the Statement class.

ResultSetMetaData rsmd = null;
try {
  rsmd = rs.getMetaData();
} catch (SQLException se) {
  System.out.println("We got an exception while getting the metadata:" +
                     "check the connection.");
  se.printStackTrace();
  System.exit(1);
}

String columnName = null,
       columnType = null;
try {
  columnName = rsmd.getColumnName(1);
  columnType = rsmd.getColumnTypeName(1);
} catch (SQLException se) {
  System.out.println("We got an exception while getting the column name:" +
                     "check the connection.");
  se.printStackTrace();
  System.exit(1);
}

System.out.print("The name of the first column is: '");
System.out.print(columnName);
System.out.println("'");
System.out.print("The data type of the first column is: ");
System.out.println(columnType);

There are many other useful methods in the ResultSetMetaData class, all of which are well documented in the JDK API documentation.

DatabaseMetaData

Finally, DatabaseMetaData is a class that can be used to fetch information about the database you are using. Use it to answer questions such as:

• What kind of catalogs are in the database?

• What brand of database am I working with?

• What username am I?

Example 12-8 uses DatabaseMetaData to query the JDBC driver for the username used to establish the connection, and the database URL.

DatabaseMetaData dbmd = null;

try {
    dbmd = c.getMetaData();
} catch (SQLException se) {
    System.out.println("We got an exception while getting the metadata:" +
                       " check the connection.");
    se.printStackTrace();
    System.exit(1);
}

String username = null;
try {
    username = dbmd.getUserName();
} catch (SQLException se) {
  System.out.println("We got an exception while getting the username:" +
                     "check the connection.");
  se.printStackTrace();
  System.exit(1);
}

String url = null;
try {
  url = dbmd.getURL();
} catch (SQLException se) {
  System.out.println("We got an exception while getting the URL:" +
                     "check the connection.");
  se.printStackTrace();
  System.exit(1);
}

System.out.println("You are connected to '" + url +
                   "' with user name '" + username + "'");

Once again, the best source for the most current information about DatabaseMetaData's many other methods is in the JDK API documentation.