Using prepared statements for more efficient access

If you use the Statement interface, you parse each statement that you send to the database, generate an access plan, and execute the statement. The steps before 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.

For more information about prepared statements, see Preparing statements.

Example

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 InsertDynamic method of the JDBCExample class carries out a prepared statement:

public static void InsertDynamic( Connection con,
              String ID, String name )
{
  try 
  {
    // Build the INSERT statement
    // ? is a placeholder character
    String sqlStr = "INSERT INTO Departments " +
            "( DepartmentID, DepartmentName ) " +
            "VALUES ( ? , ? )";

    // Prepare the statement
    PreparedStatement stmt =
      con.prepareStatement( sqlStr );

    // Set some values
    int idValue = Integer.valueOf( ID );
    stmt.setInt( 1, idValue );
    stmt.setString( 2, name );

    // Execute the statement
    int iRows = stmt.executeUpdate();

    // Print the number of rows inserted
    System.out.println(iRows + " rows inserted");
  }
  catch (SQLException sqe)
  {
    System.out.println("Unexpected exception : " +
              sqe.toString() + ", sqlstate = " +
              sqe.getSQLState());
  }
  catch (Exception e)
  {
    e.printStackTrace();
  }
}

Source code available

This code fragment is part of the JDBCExample Java file included in the samples-dir\SQLAnywhere\JDBC directory.

Notes

The executeUpdate method returns an integer that reflects the number of rows affected by the operation. In this case, a successful INSERT would return a value of one (1).
When run as a server-side class, the output from System.out.println goes to the database server messages window.

Run the JDBC Insert example

To run the JDBC Insert example

Using Interactive SQL, connect to the sample database as the DBA.
Ensure the JDBCExample class has been installed.

For more information about installing the Java examples classes, see Preparing for the examples.

Define a stored procedure named JDBCInsert that acts as a wrapper for the JDBCExample.Insert method in the class:

CREATE PROCEDURE JDBCInsert( IN arg1 INTEGER, IN arg2 CHAR(50) )
  EXTERNAL NAME 'JDBCExample.Insert(ILjava/lang/String;)V'
  LANGUAGE JAVA;

Call the JDBCExample.Insert method as follows:
CALL JDBCInsert( 202, 'Southeastern Sales' );
The Insert method causes the InsertDynamic method to be invoked.
Confirm that a row has been added to the Departments table.
SELECT * FROM Departments;
The example program displays the updated contents of the Departments table in the database server messages window.

There is a similar method in the example class called DeleteDynamic that shows how to delete the row that has just been added.

Define a stored procedure named JDBCDelete that acts as a wrapper for the JDBCExample.Delete method in the class:

CREATE PROCEDURE JDBCDelete( in arg1 integer )
  EXTERNAL NAME 'JDBCExample.Delete(I)V'
  LANGUAGE JAVA;

Call the JDBCExample.Delete method as follows:
CALL JDBCDelete( 202 );
The Delete method causes the DeleteDynamic method to be invoked.
Confirm that the row has been deleted from the Departments table.
SELECT * FROM Departments;
The example program displays the updated contents of the Departments table in the database server messages window.

Discuss this page in DocCommentXchange.