java - Android SQLite 表列未创建或识别。我缺少什么？

Question

我正在设置一个 sqlite 数据库来存储有关产品的信息。当需要将新项目添加到数据库时。当我确信第一个表列应该存在时，我收到错误消息，说不存在这样的表列。我目前的预感是问题出在 onCreate() 上。我是 sqlite 的新手，正在为其他人做这件事，如果我忽略了一些基本的东西，请原谅。

public class itemDB extends SQLiteOpenHelper {
    /**Information*/
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "ProductInfoDB";
    private static final String TABLE_NAME = "ProductInfo_Table";
    public static final String COL_PRODUCTNAME = "ProductName";
    public static final String COL_INITIALPRICE = "InitialPrice";
    public static final String COL_CURRENTPRICE = "CurrentPrice";
    public static final String COL_PRICECHANGE = "PriceChange";
    public static final String COL_ITEMWEBPAGE = "ItemWebPage";

    /** Constructor */
    public itemDB(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    /**Creates the table
     * @param db Takes the database and creates a table on it
     * */
    @Override
    public void onCreate(SQLiteDatabase db) {
        String table =("CREATE TABLE IF NOT EXISTS " + TABLE_NAME + "( "
                + COL_PRODUCTNAME  +  " TEXT PRIMARY KEY,"
                + COL_INITIALPRICE +  " REAL,"
                + COL_CURRENTPRICE +  " REAL,"
                + COL_PRICECHANGE  +  " REAL,"
                + COL_ITEMWEBPAGE  +  " TEXT " + ") ");
        db.execSQL(table);
    }
    /**needed for database
     * Checks if table exists or not
     * */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }
    /** This method adds items to the table of the database
     * @param item, takes an item and gets its info, then places it on the table of the database
     * */
    public void addItem(Item item) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(COL_PRODUCTNAME,item.getName());
        contentValues.put(COL_INITIALPRICE,item.getPriceInitial());
        contentValues.put(COL_CURRENTPRICE,item.getPriceCurrent());
        contentValues.put(COL_PRICECHANGE,item.getPriceChange());
        contentValues.put(COL_ITEMWEBPAGE,item.getUrl());
        db.insert(TABLE_NAME,null ,contentValues);
        db.close();
    }
    /** Methods aids in updating items on database
     * */
    public void updateItem(String oldName, Item item){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put(COL_PRODUCTNAME,item.getName());
        values.put(COL_INITIALPRICE,item.getPriceInitial());
        values.put(COL_CURRENTPRICE,item.getPriceCurrent());
        values.put(COL_PRICECHANGE,item.getPriceChange());
        values.put(COL_ITEMWEBPAGE,item.getUrl());

        db.update(TABLE_NAME, values, COL_PRODUCTNAME + " = ?", new String[]{oldName});
        db.close();

    }
    /** This method returns all the items found on the table of the database,
     * recurses the table and returns a list of items
     * */
    public List<Item> getAllItems() {
        List<Item> items = new ArrayList<>();
        String selectQuery = "SELECT * FROM " + TABLE_NAME;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        if(cursor.getCount() > 0) {
            if (cursor.moveToFirst()) {
                do {
                    String itemName = cursor.getString(0);
                    double initialP = cursor.getDouble( 1);
                    double currentP = cursor.getDouble(2);
                    double priceC = cursor.getDouble(3);
                    String itemUrl = cursor.getString(4);

                    Item holder = new Item(itemName, initialP, currentP, priceC, itemUrl);
                    items.add(holder);
                } while (cursor.moveToNext());
            }
        }
        return items;
    }
}

这是我遇到的错误

2019-04-19 14:14:55.490 11936-11936/edu.package E/SQLiteLog: (1) table ProductInfo_Table has no column named ProductName
2019-04-19 14:14:55.500 11936-11936/edu.packageE/SQLiteDatabase: Error inserting ProductName=tjastjtjjta PriceChange=0.0 CurrentPrice=1372.0450878788292 InitialPrice=1372.0450878788292 ItemWebPage=tuarhwrqhh
    android.database.sqlite.SQLiteException: table ProductInfo_Table has no column named ProductName (code 1): , while compiling: INSERT INTO ProductInfo_Table(ProductName,PriceChange,CurrentPrice,InitialPrice,ItemWebPage) VALUES (?,?,?,?,?)
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:895)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:506)
        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:726)
        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
        at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
        at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1580)
        at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1453)
        at edu.utep.cs.cs4330.homework3.itemDB.addItem(itemDB.java:68)
        at edu.utep.cs.cs4330.homework3.MainActivity.addItem(MainActivity.java:262)
        at edu.utep.cs.cs4330.homework3.EntryDialog$1.onClick(EntryDialog.java:66)
        at com.android.internal.app.AlertController$ButtonHandler.handleMessage(AlertController.java:164)
        at android.os.Handler.dispatchMessage(Handler.java:102)
        at android.os.Looper.loop(Looper.java:154)
        at android.app.ActivityThread.main(ActivityThread.java:6313)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:872)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:762)

Answer 1

我认为您提供的代码没有任何问题。

这是复制您的代码并在 MainActivity 中使用以下内容:-

public class MainActivity extends AppCompatActivity {

    itemDB ItemDBHlpr;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        ItemDBHlpr = new itemDB(this);
        Item myItem = new Item("tjastjtjjta",0.0,1372.0450878788292,1372.0450878788292,"tuarhwrqhh");
        ItemDBHlpr.addItem(myItem);
        List<Item> myItemList = ItemDBHlpr.getAllItems();
        for (Item i: myItemList) {
            Log.d("ITEMFROMLIST",
                    "Item Name is " + i.getName() +
                            "\n\tInitial Price = " + String.valueOf(i.getPriceInitial()) +
                            "\n\tCurrent Price = " + String.valueOf(i.getPriceCurrent()) +
                            "\n\tPrice Change = " + String.valueOf(i.getPriceChange()) +
                            "]n]tURL = " + i.getUrl()
            );
        }
    }
}

日志中产生以下结果(如预期):-

04-20 07:21:16.291 10005-10005/? D/ITEMFROMLIST: Item Name is tjastjtjjta
        Initial Price = 0.0
        Current Price = 1372.0450878788292
        Price Change = 1372.0450878788292]n]tURL = tuarhwrqhh

可能的问题

因此，问题很可能是应用程序已运行，然后进行了更改，将 ProductName 添加到表的定义中，但数据库未删除(通过卸载应用程序来实现) ，删除应用程序的数据，或者在您的情况下增加数据库版本(删除表并重新创建表)。

删除数据库有效的原因是onCreate方法仅在创建数据库时自动运行。

增加数据库版本号会导致调用 onUpgrade 方法。这将删除并重新创建表。

另一个原因

如果用于开发的设备具有工作/启用的备份管理器，并且应用程序的 list 具有 android:allowBackup="true"，那么应用程序的数据就有可能被恢复有效地阻止卸载应用程序或清除应用程序的数据。更改 list 以使用 android:allowBackup="false" 可以克服此类问题。

在这种情况下，增加数据库版本也将起作用，因为它不依赖于被删除的数据库。相反，它在调用 onUpgrade 方法时工作，此 DROP 是表并调用 onCreate 方法。

• 请注意，这确实依赖于相应编码的 onUpgrade(在本问题中是这样)。

附加

我还建议对 getAllItems 方法(注释解释)进行一些更改，例如:-

/** This method returns all the items found on the table of the database,
 * recurses the table and returns a list of items
 * */
public List<Item> getAllItems() {
    List<Item> items = new ArrayList<>();
    //String selectQuery = "SELECT * FROM " + TABLE_NAME; //<<<<<<<<<< COMMENTED OUT not needed as query method builds the SQL
    SQLiteDatabase db = this.getReadableDatabase();
    //Cursor cursor = db.rawQuery(selectQuery, null);
    // Generally recommended to use the query Convenience method rate than rawQuery
    Cursor cursor = db.query(TABLE_NAME,null,null,null,null,null,null);

    // No Need to do multiple checks for the same i.e. moveTo will return false if the move cannot be made
    //  if no rows then moveToNext returns false so loop ended
    //  if at last row then moveToNext will return false thus loop ended
    while (cursor.moveToNext()) {
                // Rather than hard coding offsets (which could change over time)
                // better to get the offset according to the column name
                String itemName = cursor.getString(cursor.getColumnIndex(COL_PRODUCTNAME));
                double initialP = cursor.getDouble( cursor.getColumnIndex(COL_INITIALPRICE));
                double currentP = cursor.getDouble(cursor.getColumnIndex(COL_CURRENTPRICE));
                double priceC = cursor.getDouble(cursor.getColumnIndex(COL_PRICECHANGE));
                String itemUrl = cursor.getString(cursor.getColumnIndex(COL_ITEMWEBPAGE));
                //Item holder = new Item(itemName, initialP, currentP, priceC, itemUrl); // No need for intermediate Item
                items.add(new Item(itemName, initialP, currentP, priceC, itemUrl)); //<<<<<<<<<< CHANGED
    }
    return items;
}