Executing SQL commands with executeUpdate() or through PrepareStatement objects v42.7.3.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 with 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 addOneEmployee(Connection con)
    {
        try (Statement stmt=con.createStatement();)
        {
            int rowcount = stmt.executeUpdate("INSERT INTO emp(empno, ename) VALUES(6000,'Jones')");
            System.out.println();
            System.out.printf("Success - %d - rows affected.\n",rowcount);
        } catch(Exception err) {
            System.out.println("An error has occurred.");
            System.out.println("See full details below.");
            err.printStackTrace();
        }
    }

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

    public void addOneEmployee(Connection con);

A Statement object is needed to run ExecuteUpdate(). This can be obtained by using createStatement() on the Connection object. We use the try-resource style here to ensure the statement object is released when the try block is exited.

    try (Statement stmt=con.createStatement()) {

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.printf displays a message to the user that shows the number of rows affected.

    System.out.println(); 
    System.out.printf("Success - %d - rows affected.\n",rowcount);

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

    } catch (Exception err){
        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. It is best suited to situations where a specific command needs to be executed and that command takes no parameters.

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

   Statement stmt=con.createStatement();
   stmt.executeUpdate("DROP TABLE tableName");     

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

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

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

    Statement stmt=con.createStatement();
    stmt.executeUpdate("ALTER TABLE tablename ADD COLUMN colname BOOLEAN "; 

However, you should use PreparedStatement when passing values to an SQL insert or update statement, especially if those values have come from user input.

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, Integer id, String name)
    {
        String command = "INSERT INTO emp(empno,ename) VALUES(?,?)";
        try(PreparedStatement addstmt = con.prepareStatement(command) {
            addstmt.setObject(1,id);
            addstmt.setObject(2,name);
            addstmt.execute();
            System.out.println("Employee added");
        } catch(Exception err) {
            System.out.println("An error has occurred.");
            System.out.println("See full details below.");
            err.printStackTrace();
        }
    }

This version of an add employee method takes as parameters the connection and values for the employee number (an integer) and name (a string).

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:

     try(PreparedStatement addstmt = 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 in the parameters:

    addstmt.setObject(1,id);
    addstmt.setObject(2,name);

It then asks the PreparedStatement object to execute the statement:

    addstmt.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:

    public static void updateEmployee(Connection con, Integer id, String name)
    {
        String command = "UPDATE emp SET ename=? where empno=?"; 
        try (PreparedStatement updateStmt = con.prepareStatement(command)) {    
            updateStmt.setObject(1,id);
            updateStmt.setObject(2,name);
            updateStmt.execute();
        } catch(Exception err) {
            System.out.println("An error has occurred.");
            System.out.println("See full details below.");
            err.printStackTrace();
        }
    }

For regularly and repeatedly used statements, the prepared statement can be initialized and reused.

    PreparedStatement preparedAddStmt;

    public void prepareStatements(Connection con) {
        try {
            preparedAddStmt=con.prepareStatement("INSERT INTO emp(empno,ename) VALUES(?,?)");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public void addPreparedEmployee(Integer id, String name)
    {
        try {
            preparedAddStmt.setObject(1,id);
            preparedAddStmt.setObject(2,name);
            preparedAddStmt.execute();
        } catch(Exception err) {
            System.out.println("An error has occurred.");
            System.out.println("See full details below.");
            err.printStackTrace();
        }
    }

This saves the system having to reparse and initialize the statement every time it is executed. Note that the prepared statement is prepared without a try-with-resource wrapper to ensure it is not closed when it leaves the prepareStatements method.