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.