Android购物车模块数据库查询报错：_id列不存在

Hey there, let's break down and fix this frustrating issue you're hitting with your shopping cart!

1. The Core Typo That's Breaking Everything #

Looking at your Database Helper's table creation SQL, there's a critical spelling mistake that's preventing the _id column from being created properly:

public static final String SQL_CREATE_ENTRIES =
        "CREATE TABLE " + TABLE_NAME + " ( " +
                _ID + " INTEGER NON NULL, " +
                COLUMN_NAME_ORDERED_QUANTITY + " INTEGER)";

SQL uses NOT NULL for non-null constraints, not NON NULL. This typo is why SQLite isn't creating the _id column at all—leading to all those "no such column" errors.

2. Fix the Table Definition (And Follow Android Best Practices) #

While we're at it, let's update the table to follow Android's SQLite/Content Provider conventions. The _ID field (from BaseColumns) should be an auto-incrementing primary key, which plays nicely with Content Resolvers and adapters. Here's the corrected SQL:

public static final String SQL_CREATE_ENTRIES =
        "CREATE TABLE " + TABLE_NAME + " ( " +
                _ID + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
                COLUMN_NAME_ORDERED_QUANTITY + " INTEGER NOT NULL)";

I also added NOT NULL to the quantity column since a cart item should always have a valid quantity.

3. Clear Out the Old Database #

Since you've already run the app with the broken table definition, SQLite has already created a version of the cart table without the _id column. You have two options to fix this:

• Quick fix: Uninstall the app from your device/emulator, then re-run it. This will create a fresh database with the corrected table.
• Data-safe fix: If you need to preserve existing data, update your Database Helper's onUpgrade method to migrate the old table, then bump the database version number:

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
      // Note: This drops the old table—for production, you'd want to write a proper migration
      db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
      onCreate(db);
  }
  

  Then change the version number in your Database Helper constructor (e.g., from 1 to 2) to trigger the upgrade.

4. Fix a Hidden Bug in Your Adapter Query #

Looking at your cartAdapter code, there's a syntax error in your product query that would cause another crash once you fix the _id issue:

// Wrong selection format
Cursor productCursor = getContentResolver().query(ProductListContract.ProductEntry.CONTENT_URI, projection, ProductListContract.ProductEntry._ID, hoho, null);

The selection parameter needs to be a condition with a placeholder, not just the column name. Also, you need to check if the cursor is valid before accessing its data:

// Corrected query
Cursor productCursor = getContentResolver().query(
        ProductListContract.ProductEntry.CONTENT_URI,
        projection,
        ProductListContract.ProductEntry._ID + " = ?", // Condition with placeholder
        hoho,
        null
);

// Safely access cursor data
if (productCursor != null && productCursor.moveToFirst()) {
    // Use getString() for product name (it's a text column!)
    prodName.setText(productCursor.getString(productCursor.getColumnIndexOrThrow(ProductListContract.ProductEntry.COLUMN_NAME_PRODUCT_NAME)));
    int price = productCursor.getInt(productCursor.getColumnIndexOrThrow(ProductListContract.ProductEntry.COLUMN_NAME_PRODUCT_PRICE));
    prodPrice.setText(String.valueOf(price));
}
productCursor.close();

Notice I also changed getInt() to getString() for the product name—your original code would throw a type conversion error since product names are text, not integers.

Wrap-Up #

Fix the SQL typo first, clear the old database, then adjust the adapter query logic, and your shopping cart should start working properly!

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