Using prepared statements for more efficient access

If you use the Statement interface, you parse each statement you send to the database, generate an access plan, and execute the statement. The steps prior to actual execution are called preparing the statement.

You can achieve performance benefits if you use the PreparedStatement interface. This allows you to prepare a statement using placeholders, and then assign values to the placeholders when executing the statement.

Using prepared statements is particularly useful when carrying out many similar actions, such as inserting many rows.

The following example illustrates how to use the PreparedStatement interface, although inserting a single row is not a good use of prepared statements.

The following method of the JDBCExamples class carries out a prepared statement:

public static void JInsertPrepared(int id, String name) try {
      conn = DriverManager.getConnection( 
                    "jdbc:default:connection");

      // Build the INSERT statement
      // ? is a placeholder character
      String sqlStr = "INSERT INTO Department "
	+ "( dept_id, dept_name ) "
	+ "VALUES ( ? , ? )" ;

      // Prepare the statement
      PreparedStatement stmt = conn.prepareStatement( sqlStr );
      
      stmt.setInt(1, id);
      stmt.setString(2, name );
      Integer IRows = new Integer( 
                          stmt.executeUpdate() );

      // Print the number of rows updated
      System.out.println(IRows.toString() + " row inserted" );
    }
    catch ( Exception e ) {
      System.out.println("Error: " + e.getMessage());
      e.printStackTrace();
    }
  }

Once you have installed the JDBCExamples class into the sample database, you can execute this example by entering the following statement:

call JDBCExamples>>InsertPrepared( 
                           202, 'Eastern Sales' )

The string argument is enclosed in single quotes, which is appropriate for SQL. If you invoked this method from a Java application, use double quotes to delimit the string.