Data modification using INSERT, UPDATE, 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.

To 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, when only a single INSERT is to be performed, the statement INSERT INTO Department(dept_no, name) VALUES(2, 'Electronics') could be PREPAREd, EXECUTEd and CLOSEd.

The statement could be created using Java String concatenation.

To 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 declaring 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, when only a single INSERT is to be performed, the statement INSERT INTO Department(dept_no, name) VALUES(2, 'Electronics') could be PREPAREd, EXECUTEd and CLOSEd.

The statement could be created using Java String concatenation.

To 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 declaring 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();

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();

Concatination 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 concatination 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.
    }
}

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

Alternatively, when only a single INSERT is to be performed, the statement INSERT INTO Department(dept_no, name) VALUES(2, 'Electronics') could be PREPAREd, EXECUTEd and CLOSEd as described in the section above.

The statement could be created using Java String concatenation.

Discuss this page in DocCommentXchange.