Modifying data using INSERT, UPDATE, and DELETE

You can perform SQL data modification 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 statement. For example, the first ? is referenced as parameter one, and the second as parameter two.

INSERT a row in a table

Prepare a new SQL statement as a String.

String sql_string = 
    "INSERT INTO Department(dept_no, name) VALUES( ?, ? )";

Pass the String to the PreparedStatement.

PreparedStatement inserter = 
    conn.prepareStatement(sql_string);

Pass input values to the PreparedStatement using the set method.

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");
Execute the statement.
inserter.execute();
Close the PreparedStatement to free resources.
inserter.close();
Commit all changes to the database.
conn.commit();

Steps 3 and 4 can be repeated as many times as needed.

Alternatively, you can prepare, execute, and close an INSERT statement when you only need to insert a single entry, as illustrated in the following example:

INSERT INTO Department(dept_no, name) VALUES(2, 'Electronics');

The statement can also be created using Java String concatenation.

UPDATE a row in a table

Prepare a new SQL statement as a String.

String sql_string = 
    "UPDATE Department SET dept_no = ? WHERE dept_no = ?";

Pass the String to the PreparedStatement.

PreparedStatement updater = 
    conn.prepareStatement(sql_string);

Pass input values to the PreparedStatement using the set method.
updater.set(1, 102); updater.set(2, 101);
The example above is the equivalent of executing the following SQL statement:
UPDATE Department SET dept_no = 102 WHERE dept_no = 101;
Execute the statement.
updater.execute();
Close the PreparedStatement to free resources.
updater.close();
Commit all changes to the database.
conn.commit();

Steps 3 and 4 can be repeated as many times as needed.

Alternatively, the following statement could be prepared, executed and closed when only a single update is to be performed:

UPDATE Department SET dept_no = 102 WHERE dept_no = 101;

This statement can also be created using Java String concatenation.

DELETE a row in a table

Prepare a new SQL statement as a String.

String sql_string = 
    "DELETE FROM Department WHERE dept_no = ?";

Pass the String to the PreparedStatement.

PreparedStatement deleter = 
    conn.prepareStatement(sql_string);

Pass input values to the PreparedStatement using the set method.
deleter.set(1, 102);
The example above is the equivalent of executing the following SQL statement:
DELETE FROM Department WHERE dept_no = 102;
Execute the statement.
deleter.execute();
Close the PreparedStatement to free resources.
deleter.close();
Commit all changes to the database.
conn.commit();
Steps 3 and 4 can be repeated as many times as needed.

Alternatively, the following statement could be prepared, executed and closed as described in the section above when only a single delete is to be performed:
DELETE FROM Department WHERE dept_no = 102;
This statement can also be created using Java String concatenation.

Example

Prepared statements can be executed several times. The following example demonstrates multiple row insertion using host variables:

String stmt = "INSERT INTO Department(dept_no, name) VALUES (?,?)";
PreparedStatement inserter = conn.prepareStatement(stmt);

inserter.set(1, 101);
inserter.set(2, "Electronics");
inserter.execute();

inserter.set(1, 105);
inserter.set(2, "Sales");
inserter.execute();

inserter.set(1, 109);
inserter.set(2, "Accounting");
inserter.execute();

inserter.close();

Concatenation is recommended over host variables when writing SQL statements that only need to be executed once. The following example demonstrates a row insertion method that uses concatenation to execute SQL statements:

void addRow(int id, String name, Connection conn) throws ULjException {
    PreparedStatement ps = conn.prepareStatement(
        "INSERT INTO Department(id, name) VALUES("
        + id
        + ", '"
        + name
        + "')";
    try {
        ps.execute();
    } 
    finally {
        ps.close(); \\ close the PreparedStatement even if an error occurs after execution.
    }
}

Discuss this page in DocCommentXchange.