Using PreparedStatements to Send SQL Commands

Many applications execute the same SQL statement over and over again, changing one or more of the data values in the statement between each iteration. If you use a Statement object to repeatedly execute a SQL statement, the server must parse, plan, and optimize the statement every time. JDBC offers another Statement derivative, the PreparedStatement to reduce the amount of work required in such a scenario.

Listing 1.6 demonstrates invoking a PreparedStatement that accepts an employee ID and employee name and inserts that employee information in the emp table:

Listing 1.6

public void AddEmployee(Connection con)
{
  try
  {
    Console c = System.console();
    String command = "INSERT INTO emp(empno,ename) VALUES(?,?)";
    PreparedStatement stmt = con.prepareStatement(command);
    stmt.setObject(1,new Integer(c.readLine("ID:")));
    stmt.setObject(2,c.readLine("Name:"));
    stmt.execute();

    System.out.println("The procedure successfully executed.");
  }
  catch(Exception err)
  {
     System.out.println("An error has occurred.");
     System.out.println("See full details below.");
     err.printStackTrace();
  }
}

Instead of hard-coding data values in the SQL statement, you insert placeholders to represent the values that will change with each iteration. Listing 1.6 shows an INSERT statement that includes two placeholders (each represented by a question mark):

String command = "INSERT INTO emp(empno,ename) VALUES(?,?)";

With the parameterized SQL statement in hand, the AddEmployee() method can ask the Connection object to prepare that statement and return a PreparedStatement object:

PreparedStatement stmt = con.prepareStatement(command);

At this point, the PreparedStatement has parsed and planned the INSERT statement, but it does not know what values to add to the table. Before executing the PreparedStatement, you must supply a value for each placeholder by calling a setter method. setObject() expects two arguments:

• A parameter number; parameter number one corresponds to the first question mark, parameter number two corresponds to the second question mark, etc.

• The value to substitute for the placeholder.

The AddEmployee() method prompts the user for an employee ID and name and calls setObject() with the values supplied by the user:

stmt.setObject(1,new Integer(c.readLine("ID:")));
stmt.setObject(2, c.readLine("Name:"));

And then asks the PreparedStatement object to execute the statement:

stmt.execute();

If the SQL statement executes as expected, AddEmployee() displays a message that confirms the execution. If the server encounters an exception, the error handling code displays an error message.