How to UPDATE data in a MySQL database using Java

A guide on how to use Prepared Statements in Java to update data in a MySQL database table safely and effectively.

In this article, you'll learn how to connect to a MySQL database and use Java with Prepared Statements to execute an UPDATE statement, allowing you to update records in the database using multiple parameters.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class UpdateDataExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/test_db";
        String user = "root";
        String password = "password";

        String updateQuery = "UPDATE students SET name = ? WHERE id = ?";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement pstmt = conn.prepareStatement(updateQuery)) {

            // Set values for parameters
            pstmt.setString(1, "Jane Doe");
            pstmt.setInt(2, 1);

            // Execute the UPDATE statement
            int rowsAffected = pstmt.executeUpdate();
            System.out.println(rowsAffected + " record(s) updated.");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Detailed explanation:

1. import java.sql.Connection;, import java.sql.DriverManager;, import java.sql.PreparedStatement;, import java.sql.SQLException;: Imports the necessary libraries for database connection and SQL queries.
2. String url = "jdbc:mysql://localhost:3306/test_db";: Defines the URL for connecting to the MySQL database.
3. String user = "root"; and String password = "password";: Defines the login credentials.
4. String updateQuery = "UPDATE students SET name = ? WHERE id = ?";: Defines the UPDATE statement with specified parameters.
5. try (Connection conn = DriverManager.getConnection(url, user, password); ...): Establishes a connection to the database and automatically closes it upon completion.
6. PreparedStatement pstmt = conn.prepareStatement(updateQuery): Creates a Prepared Statement from the defined UPDATE statement.
7. pstmt.setString(1, "Jane Doe"); and pstmt.setInt(2, 1);: Sets the parameter values for the UPDATE statement.
8. int rowsAffected = pstmt.executeUpdate();: Executes the UPDATE statement and stores the number of affected records.
9. System.out.println(rowsAffected + " record(s) updated.");: Prints the number of records that were updated.
10. catch (SQLException e): Catches any SQL exceptions and prints the error message if one occurs.

System Requirements:

• JDK 8 or higher
• MySQL Connector/J library

How to install the libraries needed to run the Java code above:

1. Download the MySQL Connector/J library from the official website.
2. Add the library to your Java project's classpath.

Tips:

• Ensure your MySQL server is running before attempting to connect.
• Double-check your connection details like URL, user, and password to avoid connection errors.
• Use Prepared Statements to protect against SQL injection attacks.