Data manipulation using INSERT, UPDATE, DELETE

You can perform SQL data manipulation using the execute method of a PreparedStatement. A PreparedStatement queries the database with a user-defined SQL statement.

When applying a SQL statement to a PreparedStatement, query parameters are indicated by the ? character. For any INSERT, UPDATE or DELETE statement, each ? parameter is referenced according to its ordinal position in the SQL statement. For example, the first ? is referred as parameter 1, and the second as parameter 2.

To INSERT a row in a table

  1. Prepare a new SQL statement as a String.

    String sql_string = 
        "INSERT INTO Department(dept_no, name) VALUES( ?, ? )";
  2. Assign the String to the PreparedStatement.

    PreparedStatement inserter = 
        conn.prepareStatement(sql_string);
    
  3. Assign input parameter values for the statement.

    This example sets 101 for the dept_no, referenced as parameter 1, and "Electronics" for the name, referenced as parameter 2.

    inserter.set(1, 101);
    inserter.set(2, "Electronics");
  4. Execute the statement.

    inserter.execute();
  5. Close the PreparedStatement to free resources.

    inserter.close()
  6. Commit all changes to the database.

    conn.commit();

To UPDATE a row in a table

  1. Prepare a new SQL statement as a String.

    String sql_string = 
        "UPDATE Department SET dept_no = ? WHERE dept_no = ?";
  2. Assign the String to the PreparedStatement.

    PreparedStatement inserter = 
        conn.prepareStatement(sql_string);
    
  3. Assign input parameter values for the statement.

    inserter.set(1, 102);
    inserter.set(2, 101);

    The example above is the equivalent of the following SQL statement:

    UPDATE Department SET dept_no = 102 WHERE dept_no = 101
  4. Execute the statement.

    inserter.execute();
  5. Close the PreparedStatement to free resources.

    inserter.close()
  6. Commit all changes to the database.

    conn.commit();

To DELETE a row in a table

  1. Prepare a new SQL statement as a String.

    String sql_string = 
        "DELETE FROM Department WHERE dept_no = ?";
  2. Assign the String to the PreparedStatement.

    PreparedStatement inserter = 
        conn.prepareStatement(sql_string);
    
  3. Assign input parameter values for the statement.

    inserter.set(1, 102);

    The example above is the equivalent of the following SQL statement:

    DELETE FROM Department WHERE dept_no = 102
  4. Execute the statement.

    inserter.execute();
  5. Close the PreparedStatement to free resources.

    inserter.close()
  6. Commit all changes to the database.

    conn.commit();