How to Delete Multiple Rows with Composite Primary Keys Using Java PreparedStatement

When working with relational databases in Java, a common task is deleting multiple rows based on their composite primary key. A composite key consists of two or more columns that uniquely identify a row in a table. Deleting such rows efficiently and safely is crucial in enterprise-level applications, especially when using JDBC and PreparedStatement.

In this article, we’ll explore multiple strategies to delete multiple records with composite keys using PreparedStatement in Java, including a loop-based method and an optimized batch-style query using tuple conditions.

Understanding Composite Primary Keys

A composite primary key is made up of two or more columns that together form a unique identifier for table rows. For example, in a table orders_products, the composite key might consist of order_id and product_id. To delete a row, you must match both values.

DELETE FROM orders_products WHERE order_id = ? AND product_id = ?

Basic Method: Using a Loop with PreparedStatement

The most straightforward and portable approach is to loop over the keys you want to delete and run a parameterized DELETE for each one.

String sql = "DELETE FROM orders_products WHERE order_id = ? AND product_id = ?";
try (Connection conn = dataSource.getConnection();
     PreparedStatement pstmt = conn.prepareStatement(sql)) {

    for (Object[] key : keysToDelete) {
        pstmt.setInt(1, (Integer) key[0]);
        pstmt.setInt(2, (Integer) key[1]);
        pstmt.executeUpdate();
    }

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

✅ Pros:

• Easy to implement
• Fully safe against SQL injection
• Compatible with all JDBC-compliant databases

⚠️ Cons:

• Executes one SQL statement per row, which can be slow for large datasets

Advanced Method: Using Tuple IN Clause for Bulk Deletion

If your database supports tuple comparisons (e.g., PostgreSQL, MySQL 5.7+), you can batch your deletes into a single query using the IN clause with tuple values.

DELETE FROM orders_products 
WHERE (order_id, product_id) IN ((?, ?), (?, ?), (?, ?))

Java Example with Dynamic SQL Generation

StringBuilder sql = new StringBuilder(
    "DELETE FROM orders_products WHERE (order_id, product_id) IN (");

for (int i = 0; i < keysToDelete.length; i++) {
    sql.append("(?, ?)");
    if (i < keysToDelete.length - 1) {
        sql.append(", ");
    }
}
sql.append(")");

try (Connection conn = dataSource.getConnection();
     PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {

    int index = 1;
    for (Object[] key : keysToDelete) {
        pstmt.setInt(index++, (Integer) key[0]);
        pstmt.setInt(index++, (Integer) key[1]);
    }

    pstmt.executeUpdate();

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

✅ Pros:

• Much faster than individual deletes
• Keeps logic inside the database engine
• Still safe with prepared statements

⚠️ Cons:

• Limited by the maximum number of placeholders supported by your database
• Not supported in all DBMS (e.g., some versions of Oracle)

Best Practices and Performance Tips

• Use Batching for Massive Deletes: For thousands of entries, break the delete list into smaller chunks (e.g., 100 tuples per query).
• Monitor Execution Plans: Check how your database engine handles the IN clause with tuples for performance optimizations.
• Use Transactions When Needed: If atomicity is required, wrap deletes in a transaction to ensure consistency.
• Avoid Hardcoding Values: Always use PreparedStatement to prevent SQL injection.
• Test on Staging First: Before running bulk deletes on production data, validate your queries in a non-critical environment.

Conclusion

Deleting rows with composite primary keys in Java is straightforward once you understand the mechanisms available through JDBC and PreparedStatement. Whether you choose to delete rows one-by-one or use tuple IN clauses for bulk deletion, each approach has trade-offs in terms of performance and compatibility.

By using best practices such as parameterized queries and batching, you ensure that your application is both secure and scalable. Choose the method that best aligns with your use case and the capabilities of your database system.