Updating Data in MySQL Using JDBC PreparedStatement

Summary: in this tutorial, you will learn how to update data in the MySQL database using the JDBC PreparedStatement interface.

This tutorial picks up where the Querying Data From MySQL Using JDBC tutorial left off.

Introduction to PreparedStatement

The PreparedStatement interface extends the Statement interface that provides some more advanced features as follows:

• Add parameters into your SQL statement using placeholders in the form of question marks (?). This helps you avoid SQL injection.
• Reuse the PreparedStatement with new parameters in case you need to execute the SQL statement multiple times with different parameters.
• Help increase the performance of the executed statement by precompiling the SQL statement.

We will use the PreparedStatement to update the last names of candidates in the candidates table.

The following adds a new method called changeEmail() to the Candidate class that changes the email of a candidate specified by an ID:

import java.sql.SQLException;

public class Candidate {
    public static void changeEmail(int id, String email) {
        var sql = "UPDATE candidates "
                + "SET email = ? "
                + "WHERE id = ?";

        try (var conn = MySQLConnection.connect();
             var stmt = conn.prepareStatement(sql)) {

            // prepare data for update
            stmt.setString(1, email);
            stmt.setInt(2, id);

            // execute the update
            int rowAffected = stmt.executeUpdate();
            System.out.println("Row affected " + rowAffected);
        } catch (SQLException ex) {
            System.err.println(ex.getMessage());
        }
    }

    
    // other methods
    // ...
    
}Code language: Java (java)

How it works.

First, import the SQLException class from the java.sql package to handle database-related errors.

import java.sql.SQLException;Code language: Java (java)

Second, declare a static method named changeEmail() that takes two parameters, an integer id representing the candidate’s ID and a string email representing the new email address:

public static void changeEmail(int id, String email) {Code language: JavaScript (javascript)

Third, define a query that modifies the email of a candidate with a specific ID:

var sql = "UPDATE candidates "
        + "SET email = ? "
        + "WHERE id = ?";Code language: Java (java)

Fourth, call the connect() method of the MySQLConnection class to establish a connection to the MySQL database and prepare a SQL statement statement using the prepareStatement() method:

try (var conn = MySQLConnection.connect();
     var stmt = conn.prepareStatement(sql)) {Code language: Java (java)

The try-with-resources block ensures that the Connection and PreparedStatetment are closed automatically.

Fifth, set the parameters email and id in the prepared statement, then execute the update query using the executeUpdate():

stmt.setString(1, email);
stmt.setInt(2, id);
int rowAffected = stmt.executeUpdate();
System.out.println("Row affected " + rowAffected);Code language: Java (java)

The executeUpdate() method returns the number of rows affected by the update.

Sixth, catch any SQLException that might occur during the database operations. If an exception occurs, the method displays the error message to the standard error stream:

} catch (SQLException ex) {
    System.err.println(ex.getMessage());
}
Code language: Java (java)

The following illustrates how to call the changeEmail() method of the Candidate class to change the email of the candidate with the id 1:

public class Main {
    public static void main(String[] args){
        Candidate.changeEmail(1,"[email protected]");
    }
}Code language: Java (java)

Output:

Row affected 1

Summary

• Use the PrepareStatement object to update the data in a table.