How to Delete Data from Database Using Java - Simple Guide

To delete data from a database using Java, use the DELETE SQL statement with PreparedStatement in JDBC. Connect to the database, prepare the delete query, set parameters, execute the update, and close the connection.

Syntax

The basic syntax to delete data in Java using JDBC is:

• Connection: Connects to the database.
• PreparedStatement: Prepares the SQL DELETE query with placeholders.
• executeUpdate(): Runs the delete command and returns the number of affected rows.
• Always close the PreparedStatement and Connection to free resources.

java

String sql = "DELETE FROM table_name WHERE condition_column = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setObject(1, value); // set parameter
int rowsDeleted = pstmt.executeUpdate();

Example

This example shows how to delete a user from a users table by their id. It connects to a database, deletes the record, and prints how many rows were deleted.

java

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

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

        String sql = "DELETE FROM users WHERE id = ?";

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

            pstmt.setInt(1, 5); // delete user with id=5
            int rowsDeleted = pstmt.executeUpdate();

            System.out.println("Rows deleted: " + rowsDeleted);

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

Output

Rows deleted: 1

Common Pitfalls

• Not closing Connection and PreparedStatement can cause resource leaks.
• Using string concatenation to build SQL queries can cause SQL injection vulnerabilities.
• Forgetting to set parameters in PreparedStatement leads to errors.
• Not handling SQLException properly can hide problems.

java

/* Wrong way: vulnerable to SQL injection and error-prone */
String sql = "DELETE FROM users WHERE id = " + userId;
Statement stmt = connection.createStatement();
stmt.executeUpdate(sql);

/* Right way: use PreparedStatement with parameters */
String sql = "DELETE FROM users WHERE id = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, userId);
pstmt.executeUpdate();

Quick Reference

Remember these key points when deleting data using Java:

• Use PreparedStatement to safely set parameters.
• Always close database resources using try-with-resources.
• Check the return value of executeUpdate() to know how many rows were deleted.
• Handle exceptions to catch database errors.

Key Takeaways

Use PreparedStatement with parameterized DELETE queries to avoid SQL injection.

Always close your database connections and statements to prevent resource leaks.

Check the number of rows affected by executeUpdate() to confirm deletion.

Handle SQLExceptions to debug and manage database errors effectively.