SQLite 3.15+Java 8实现仅删除查询结果中第一条记录

Let's break down how to fix your issue step by step, since you're working with an older SQLite version (3.15) that doesn't support DELETE ... LIMIT (that feature was added in 3.33.0) and your composite primary key was causing subquery column mismatch errors.

Why Your Previous Attempt Failed #

The error [SQLITE_ERROR] SQL error or missing database (sub-select returns 2 columns - expected 1) popped up because your subquery was returning both id and filename (the two columns of your composite PK), but the DELETE statement's WHERE clause was expecting a single column to match against. Additionally, some attempts deleted all matching records because they didn't target a specific unique row.

Correct Approach: Target the Unique Primary Key of the First Record #

Since your table uses a composite primary key (id, filename), we need to first find the id of the first record for the given filename (we'll define "first" by sorting on id ascending—adjust the sort if you need a different order), then delete only that specific row.

Step 1: The SQLite DELETE Query

Use a subquery that returns only the id of the first matching record, avoiding the multi-column error:

DELETE FROM fileBlock 
WHERE filename = ? 
AND id = (SELECT id FROM fileBlock WHERE filename = ? ORDER BY id ASC LIMIT 1);

• The subquery SELECT id FROM fileBlock WHERE filename = ? ORDER BY id ASC LIMIT 1 grabs the smallest id (our definition of the "first" record) for the target filename.
• Matching on both filename and this specific id ensures we only delete one unique row—thanks to your composite PK, this combination is guaranteed to be unique.

Step 2: Java 8 Implementation

Wrap this query in a prepared statement to avoid SQL injection and handle parameters safely:

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

public class YourDatabaseHandler {

    // Replace with your actual SQLite connection logic
    private Connection getConnection() throws SQLException {
        return java.sql.DriverManager.getConnection("jdbc:sqlite:your-database-file.db");
    }

    public void deleteRecord(String filename) throws SQLException {
        String deleteQuery = "DELETE FROM fileBlock " +
                             "WHERE filename = ? " +
                             "AND id = (SELECT id FROM fileBlock WHERE filename = ? ORDER BY id ASC LIMIT 1)";
        
        // Use try-with-resources to auto-close connections and statements
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(deleteQuery)) {
            
            // Set the filename parameter for both placeholders
            pstmt.setString(1, filename);
            pstmt.setString(2, filename);
            
            int affectedRows = pstmt.executeUpdate();
            if (affectedRows > 0) {
                System.out.println("Successfully deleted the first record for filename: " + filename);
            } else {
                System.out.println("No matching records found for filename: " + filename);
            }
        }
    }
}

Key Notes #

• Define "First" Clearly: The ORDER BY id ASC ensures we pick the record with the smallest id as the "first". If you need to prioritize a different order (e.g., by insertion time if you have a timestamp column), adjust the ORDER BY clause accordingly.
• Composite PK Safety: Since (id, filename) is your primary key, this query will never delete more than one row—each combination is unique.
• SQLite 3.15 Compatibility: This approach works with your older SQLite version because it uses only features supported in 3.15 (subqueries, LIMIT in SELECT, etc.).

内容的提问来源于stack exchange，提问作者bdkdavid