How to DELETE data from a MySQL database using Java

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

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

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

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

        String deleteQuery = "DELETE FROM students WHERE id = ? AND name = ?";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement pstmt = conn.prepareStatement(deleteQuery)) {
             
            // Set parameters
            pstmt.setInt(1, 1);
            pstmt.setString(2, "John Doe");

            // Execute the DELETE statement
            int rowsAffected = pstmt.executeUpdate();

            // Print the number of records deleted
            System.out.println(rowsAffected + " record(s) deleted.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Detailed explanation:

1. import java.sql.*: Imports the necessary classes to work with JDBC.
2. String url = "jdbc:mysql://localhost:3306/test_db";: Defines the connection URL to the MySQL database.
3. String user = "root"; and String password = "password";: Defines the login credentials to connect to the database.
4. String deleteQuery = "DELETE FROM students WHERE id = ? AND name = ?";: Defines the DELETE statement with parameters.
5. try (Connection conn = DriverManager.getConnection(url, user, password); PreparedStatement pstmt = conn.prepareStatement(deleteQuery)): Connects to the database and creates a PreparedStatement object.
6. pstmt.setInt(1, 1); and pstmt.setString(2, "John Doe");: Sets the parameters for the DELETE statement.
7. int rowsAffected = pstmt.executeUpdate();: Executes the DELETE statement and retrieves the number of records deleted.
8. System.out.println(rowsAffected + " record(s) deleted.");: Prints the number of records that were deleted.
9. catch (SQLException e) { e.printStackTrace(); }: Catches and handles any SQL exceptions that may occur.

System Requirements:

• Java 8 or higher
• JDBC MySQL Connector library

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

Download the JDBC MySQL Connector library from the official website and add it to your Java project.

Tips:

• Make sure 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.