Android SQLite日期查询与数据增改功能实现求助

Hey there! Let's get your SQLite "upsert" (update or insert) logic working properly—you're already close, just a few key issues in your code are holding you back. Let's break this down step by step.

First: What's Wrong with Your Current Code? #

Let's go through the bugs that are making your code ineffective:

• Incorrect Cursor check: A Cursor returned by db.query() is never null—even if there are no matching rows, you get an empty (but non-null) Cursor. You need to check if it has rows using cursor.moveToFirst() or cursor.getCount() == 0.
• Missing insert/update calls: You create ContentValues but never actually tell the database to insert a new row or update an existing one with db.insert() or db.update().
• Early database closure: You close the database inside your if/else blocks, which cuts off access before you can execute the insert/update. Always close resources after all operations are done.
• Hardcoded column name: You used "KEY_ANZAHL" as a string instead of your KEY_ANZAHL variable—this will cause a column-not-found error if your actual column name is different.

Fixed Implementation #

Here's the corrected version of your addBiereintrag method that handles both insert and update correctly:

void addBiereintrag(Biereintrag biereintrag) { 
    SQLiteDatabase db = this.getWritableDatabase(); 
    ContentValues values = new ContentValues(); 
    String targetDate = biereintrag.getDatum();

    // Query for existing entry with the target date
    Cursor cursor = db.query(
        TABLE_BIER, 
        new String[]{KEY_ANZAHL}, 
        KEY_DATUM + " = ?", 
        new String[]{targetDate}, 
        null, null, null
    );

    try {
        if (cursor.moveToFirst()) { 
            // Entry exists: add the new amount to the existing one
            int currentAmount = cursor.getInt(cursor.getColumnIndex(KEY_ANZAHL));
            int newAmount = currentAmount + biereintrag.getAnzahl();
            
            values.put(KEY_ANZAHL, newAmount);
            db.update(TABLE_BIER, values, KEY_DATUM + " = ?", new String[]{targetDate});
            Log.d("test", "Updated entry for date: " + targetDate);
        } else { 
            // No entry exists: insert a new one
            values.put(KEY_DATUM, targetDate);
            values.put(KEY_ANZAHL, biereintrag.getAnzahl());
            db.insert(TABLE_BIER, null, values);
            Log.d("test", "Added new entry for date: " + targetDate);
        }
    } finally {
        // Ensure resources are closed even if an error occurs
        cursor.close();
        db.close();
    }
}

Using strftime() with Your Date Strings #

Since your KEY_DATUM is stored as a YYYY-MM-DD string, direct equality checks work fine for your current use case. But strftime() becomes super useful when you need to query by date ranges (e.g., all entries in a month or year).

For example, to get all entries from May 2024:

String monthQuery = "SELECT * FROM " + TABLE_BIER + 
                    " WHERE strftime('%Y-%m', " + KEY_DATUM + ") = '2024-05'";
Cursor monthCursor = db.rawQuery(monthQuery, null);

strftime('%Y-%m', KEY_DATUM) extracts the year and month from your date string, letting you filter without messy string slicing. You can use other format specifiers too:

• %Y: 4-digit year
• %m: 2-digit month
• %d: 2-digit day

Quick Best Practices #

• Stick with parameterized queries (using ? placeholders) like you did—this prevents SQL injection and makes your code cleaner.
• If you plan to do a lot of date calculations, consider storing dates as Unix timestamps (Long values) instead of strings—it's faster for comparisons and arithmetic.
• Always use try-finally to close Cursors and Databases to avoid resource leaks.

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