JDBC Update ​

Database operations can be summarized with four characters: 增删改查, commonly known as CRUD in technical jargon: Create, Retrieve, Update, and Delete.

Retrieve refers to querying, which we have already covered using PreparedStatement for various SELECT statements and processing the result sets. Now, let's look at how to perform Create, Update, and Delete operations using JDBC.

Insert ​

The insert operation uses the INSERT statement to add a new record. Performing an insert with JDBC essentially involves using a PreparedStatement to execute an SQL statement, but instead of executeQuery(), you use executeUpdate(). Here is an example:

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    try (PreparedStatement ps = conn.prepareStatement(
            "INSERT INTO students (id, grade, name, gender) VALUES (?,?,?,?)")) {
        ps.setObject(1, 999); // Note: Index starts at 1
        ps.setObject(2, 1); // grade
        ps.setObject(3, "Bob"); // name
        ps.setObject(4, "M"); // gender
        int n = ps.executeUpdate(); // 1
    }
}

Setting parameters is the same as with queries; for each ? placeholder, you must set the corresponding parameter. Although Statement can also perform insert operations, we must strictly adhere to the principle of never manually concatenating SQL strings to avoid security vulnerabilities.

When executeUpdate() is successfully executed, it returns an int representing the number of records inserted. In this case, it always returns 1 because only one record is inserted.

Insert and Retrieve Generated Keys ​

If the table in the database is set up with an auto-increment primary key, there is no need to specify the primary key when executing the INSERT statement; the database will automatically assign it. For programs using auto-increment keys, there is an additional step to retrieve the value of the auto-generated primary key after insertion.

To retrieve the auto-generated key, you should not insert first and then query, as another program might insert into the same table between the two SQL executions. The correct way to retrieve the auto-generated key is to specify the RETURN_GENERATED_KEYS flag when creating the PreparedStatement. Here is an example:

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    try (PreparedStatement ps = conn.prepareStatement(
            "INSERT INTO students (grade, name, gender) VALUES (?,?,?)",
            Statement.RETURN_GENERATED_KEYS)) {
        ps.setObject(1, 1); // grade
        ps.setObject(2, "Bob"); // name
        ps.setObject(3, "M"); // gender
        int n = ps.executeUpdate(); // 1
        try (ResultSet rs = ps.getGeneratedKeys()) {
            if (rs.next()) {
                long id = rs.getLong(1); // Note: Index starts at 1
            }
        }
    }
}

There are two important points to observe in the above code:

1. When calling prepareStatement(), the second parameter must be Statement.RETURN_GENERATED_KEYS; otherwise, the JDBC driver will not return the auto-generated keys.

2. After executing the executeUpdate() method, you must call getGeneratedKeys() to obtain a ResultSet object. This object contains the values of the auto-generated keys. You can read each row of this ResultSet to get the auto-generated key values. If multiple records are inserted at once, the ResultSet will contain multiple rows. If multiple auto-increment columns are present during insertion, each row in the ResultSet will correspond to multiple auto-generated values (auto-increment columns are not necessarily primary keys).

Update ​

The update operation uses the UPDATE statement, which can update one or more columns of a record. The JDBC code for updating is essentially the same as for inserting, except for the different SQL statement:

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    try (PreparedStatement ps = conn.prepareStatement("UPDATE students SET name=? WHERE id=?")) {
        ps.setObject(1, "Bob"); // Note: Index starts at 1
        ps.setObject(2, 999);
        int n = ps.executeUpdate(); // Returns the number of rows updated
    }
}

The executeUpdate() method returns the actual number of rows updated in the database. The result can be a positive number or 0 (indicating that no records were updated).

Delete ​

The delete operation uses the DELETE statement, which can delete one or more rows at a time. Similar to the update operation, the JDBC code is the same except for the different SQL statement:

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    try (PreparedStatement ps = conn.prepareStatement("DELETE FROM students WHERE id=?")) {
        ps.setObject(1, 999); // Note: Index starts at 1
        int n = ps.executeUpdate(); // Number of rows deleted
    }
}

Practice Exercise ​

Use JDBC to update the database.

Summary ​

• Executing INSERT, UPDATE, and DELETE operations with JDBC can be considered as update operations.
• Update operations use the executeUpdate() method of PreparedStatement and return the number of affected rows.