Inserts, updates, and deletes using JDBC

Static SQL statements such as INSERT, UPDATE, and DELETE, which do not return result sets, are executed using the executeUpdate method of the Statement class. Statements, such as CREATE TABLE and other data definition statements, can also be executed using executeUpdate.

The addBatch, clearBatch, and executeBatch methods of the Statement class may also be used. Due to the fact that the JDBC specification is unclear on the behavior of the executeBatch method of the Statement class, the following notes should be considered when using this method with the SQL Anywhere JDBC drivers:

Processing of the batch stops immediately upon encountering a SQL exception or result set. If processing of the batch stops, then a BatchUpdateException will be thrown by the executeBatch method. Calling the getUpdateCounts method on the BatchUpdateException will return an integer array of row counts where the set of counts prior to the batch failure will contain a valid non-negative update count; while all counts at the point of the batch failure and beyond will contain a -1 value. Casting the BatchUpdateException to a SQLException will provide additional details as to why batch processing was stopped.
The batch is only cleared when the clearBatch method is explicitly called. As a result, calling the executeBatch method repeatedly will re-execute the batch over and over again. In addition, calling execute(sql_query) or executeQuery(sql_query) will correctly execute the specified SQL query, but will not clear the underlying batch. Hence, calling the executeBatch method followed by execute(sql_query) followed by the executeBatch method again will execute the set of batched statements, then execute the specified SQL query, and then execute the set of batched statements again.

The following code fragment illustrates how to execute an INSERT statement. It uses a Statement object that has been passed to the InsertStatic method as an argument.

public static void InsertStatic( Statement stmt )
{
  try
  {
    int iRows = stmt.executeUpdate(
      "INSERT INTO Departments (DepartmentID, DepartmentName)"
      + " VALUES (201, 'Eastern Sales')" );
    // 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 JDBCExample that acts as a wrapper for the JDBCExample.main method in the class:

CREATE PROCEDURE JDBCExample( IN arg CHAR(50) )
  EXTERNAL NAME 'JDBCExample.main([Ljava/lang/String;)V'
  LANGUAGE JAVA;

Call the JDBCExample.main method as follows:
CALL JDBCExample( 'insert' );
The argument string 'insert' causes the InsertStatic 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 DeleteStatic that shows how to delete the row that has just been added. Call the JDBCExample.main method as follows:
CALL JDBCExample( 'delete' );
The argument string 'delete' causes the DeleteStatic 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.