Executing SQL Commands with executeUpdate()

In the previous example ListEmployees executed a SELECT statement using the Statement.executeQuery() method. executeQuery() was designed to execute query statements so it returns a ResultSet that contains the data returned by the query. The Statement class offers a second method that you should use to execute other types of commands (UPDATE, INSERT, DELETE, and so forth). Instead of returning a collection of rows, the executeUpdate() method returns the number of rows affected by the SQL command it executes.

The signature of the executeUpdate() method is:

int executeUpdate(String sqlStatement)

Provide this method a single parameter of type String, containing the SQL command that you wish to execute.

Using executeUpdate() to INSERT Data

The example that follows demonstrates using the executeUpdate() method to add a row to the emp table.

NOTE: the following example is not a complete application, only a method - the samples in the remainder of this document do not include the code required to set up and tear down a Connection. To experiment with the example, you must provide a class that invokes the sample code.

Listing 1.3

public void updateEmployee(Connection con)
{
  try
  {
    Console console = System.console();
    Statement stmt  = con.createStatement();

    String empno = console.readLine("Employee Number :");
    String ename = console.readLine("Employee Name   :");
    int rowcount = stmt.executeUpdate("INSERT INTO emp(empno, ename)
      VALUES("+empno+",'"+ename+"')");
    System.out.println("");
    System.out.println("Success - "+rowcount+" rows affected.");
  }
  catch(Exception err)
  {
    System.out.println("An error has occurred.");
    System.out.println("See full details below.");
    err.printStackTrace();
  }
}

The updateEmployee() method expects a single argument from the caller, a Connection object that must be connected to an Advanced Server database:

public void updateEmployee(Connection con)

Next, updateEmployee() prompts the user for an employee name and number:

String empno = console.readLine("Employee Number :");
String ename = console.readLine("Employee Name :");

updateEmployee() concatenates the values returned by console.readline() into an INSERT statement and pass the result to the executeUpdate() method.

int rowcount = stmt.executeUpdate("INSERT INTO emp(empno, ename)
VALUES("+empno+",'"+ename+"')");

For example, if the user enters an employee number of 6000 and a name of Jones, the INSERT statement passed to executeUpdate() will look like this:

INSERT INTO emp(empno, ename) VALUES(6000, 'Jones');

The executeUpdate() method returns the number of rows affected by the SQL statement (an INSERT typically affects one row, but an UPDATE or DELETE statement can affect more). If executeUpdate() returns without throwing an error, the call to System.out.println displays a message to the user that shows the number of rows affected.

System.out.println("");
System.out.println("Success - "+rowcount+" rows affected.");

The catch block displays an appropriate error message to the user if the program encounters an exception:

{
System.out.println("An error has occurred.");
System.out.println("See full details below.");
err.printStackTrace();
}

executeUpdate() Syntax Examples

You can use executeUpdate() with any SQL command that does not return a result set. Some simple syntax examples using executeUpdate() with SQL commands follow:

To use the UPDATE command with executeUpdate() to update a row:

stmt.executeUpdate("UPDATE emp SET ename='"+ename+"' WHERE empno="+empno);

To use the DELETE command with executeUpdate() to remove a row from a table:

stmt.executeUpdate("DELETE FROM emp WHERE empno="+empno);

To use the DROP TABLE command with executeUpdate() to delete a table from a database:

stmt.executeUpdate("DROP TABLE tablename");

To use the CREATE TABLE command with executeUpdate() to add a new table to a database:

stmt.executeUpdate("CREATE TABLE tablename (fieldname NUMBER(4,2),
fieldname2 VARCHAR2(30))");

To use the ALTER TABLE command with executeUpdate() to change the attributes of a table:

stmt.executeUpdate("ALTER TABLE tablename ADD COLUMN colname BOOLEAN");