Executing SQL commands with executeUpdate() or through PrepareStatement objects v42.5.1.2

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 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 want to execute.

Using executeUpdate() to INSERT data

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

    public void updateEmployee(Connection con)
    {
      try
      {
        int rowcount = stmt.executeUpdate("INSERT INTO "
          + "emp(empno, ename) VALUES(6000,'Jones')");
        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 EDB Postgres Advanced Server database:

    public void updateEmployee(Connection con);

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).

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

If executeUpdate() returns without 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();
    }

You can use executeUpdate() with any SQL command that doesn't return a result set. However, you probably want to use PrepareStatements when the queries can be parameterized.

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.

The following shows invoking a PreparedStatement that accepts an employee ID and employee name and inserts that employee information in the emp table:

    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 to change with each iteration. The example 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 doesn't know the values to add to the table. Before executing 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:"));

It 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.

Some simple syntax examples using PreparedStatement sending SQL commands follow:

To use the UPDATE command to update a row:

    String command = " UPDATE emp SET ename=? WHERE empno=?";     
    PreparedStatement stmt = con.prepareStatement(command); 
    stmt.setObject(1, c.readLine("Name:"));
    stmt.setObject(2,new Integer(c.readLine("ID:"))); 
    stmt.execute();

To use the DROP TABLE command to delete a table from a database:

    String command = "DROP TABLE tableName";     
    PreparedStatement stmt = con.prepareStatement(command); 
    stmt.execute();

To use the CREATE TABLE command to add a new table to a database:

    String command = ("CREATE TABLE tablename (fieldname NUMBER(4,2), fieldname2 VARCHAR2(30))"; 
    PreparedStatement stmt = con.prepareStatement(command); 
    stmt.execute();

To use the ALTER TABLE command to change the attributes of a table:

    String command ="ALTER TABLE tablename ADD COLUMN colname BOOLEAN "; 
    PreparedStatement stmt = con.prepareStatement(command); 
    stmt.execute();

• On this page
• Using executeUpdate() to INSERT data
• Using PreparedStatements to send SQL commands