在SQLite数据库中添加新列

Question

我是Android开发的新手，我正在尝试为我的应用程序实现一个数据库。我一开始在数据库中只有一列(COLUMN_DATE)，然后添加了另一列(COLUMN_REPEAT)。这工作得很好，并按照预期打印了结果。但是，当我尝试添加另一个列(COLUMN_ACCOUNT)时，MainActivity中的printDatabase()没有打印任何内容。我知道你可以使用Android设备监视器查看数据库中的内容，但当我点击它时，我总是收到一个错误，所以我无法使用它(这是一个单独的问题，我一直无法解决)。因此，我不确定这是否只是打印数据库的问题，或者数据库中是否真的有任何数据。任何帮助都将不胜感激

-MainActivity.java

dbHandler = new DatabaseHandler(this, null, null, 1);
printDatabase();

//Print the database
public void printDatabase() {
    String dbString = dbHandler.databaseToString();
    recordsTextView.setText(dbString);
}

//Add an item to the database
public void addButtonClicked(View view){
    Income date = new Income(dateView.getText().toString());
    Income repeat = new Income(repeatSpinner.getSelectedItem().toString());
    Income account = new Income(accountSpinner.getSelectedItem().toString());
    dbHandler.addData(date, repeat, account);
   printDatabase();
}

//Delete items with input date from database
public void deleteButtonClicked(View view){
    String inputText = dateView.getText().toString();
    dbHandler.deleteData(inputText);
    printDatabase();
}

--DatabaseHandler.java

public class DatabaseHandler extends SQLiteOpenHelper{
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "IncomeExpenseDB.db";
public static final String TABLE_NAME = "income_expense";
public static final String COLUMN_ID = "_id";
public static final String COLUMN_DATE = "date";
public static final String COLUMN_REPEAT = "repeat";
public static final String COLUMN_ACCOUNT = "account";

public DatabaseHandler(Context context, String name, 
SQLiteDatabase.CursorFactory factory, int version) {
    super(context, DATABASE_NAME, factory, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    String query = "CREATE TABLE " + TABLE_NAME + "(" +
            COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_DATE + " TEXT, " + COLUMN_REPEAT + " TEXT, " + 
            COLUMN_ACCOUNT + " TEXT " +
            ");";
    db.execSQL(query);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
    onCreate(db);
}

//Add a new row to the database
public void addData(Income date, Income repeat, Income 
account){
    ContentValues values = new ContentValues();
    values.put(COLUMN_DATE, date.get_item());
    values.put(COLUMN_REPEAT, repeat.get_item());
    values.put(COLUMN_ACCOUNT, account.get_item());
    SQLiteDatabase db = getWritableDatabase();
    db.insert(TABLE_NAME, null, values);
    db.close();
}


//Delete data from the database
public void deleteData(String date){
    SQLiteDatabase db = getWritableDatabase();
    db.execSQL("DELETE FROM " + TABLE_NAME + " WHERE " + COLUMN_DATE + "=\"" 
    + date + "\";");
}

// Create a string to print out in MainActivity
public String databaseToString() {
    String dbString = "";
    SQLiteDatabase db = getWritableDatabase();
    String query = "SELECT * FROM " + TABLE_NAME + " WHERE 1";
    //Cursor points to a location in results
    Cursor c = db.rawQuery(query, null);
    //Move to first row in results
    c.moveToFirst();
    while (!c.isAfterLast()) {
        if (c.getString(c.getColumnIndex("date")) != null && 
        c.getString(c.getColumnIndex("repeat")) != null && 
        c.getString(c.getColumnIndex("account")) != null) {
            dbString += c.getString(c.getColumnIndex("date"));
            dbString += " ";
            dbString += c.getString(c.getColumnIndex("repeat"));
            dbString += " ";
            dbString += c.getString(c.getColumnIndex("account"));
            dbString += "\n";
        }
        c.moveToNext();
    }
    db.close();
    return dbString;
}

}

-Income.java

public class Income {
    private int _id;
    private String _item;

    public Income(){
    }
    public Income(String item) {
        this._item = item;
    }
    public int get_id() {
        return _id;
    }
    public void set_id(int _id) {
        this._id = _id;
    }
    public String get_item() {
        return _item;
    }
    public void set_item(String _item) {
        this._item = _item;
    }
}

Answer 1

卸载和重新安装是一种非常幼稚的方法，只会在开发阶段工作。当你的应用程序进入play store时，用户不会卸载并重新安装该应用程序。

更新已发布应用程序的数据库的正确方法是增加数据库版本，并使用onUpgrade方法更新数据库。

看看这个方法

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
    onCreate(db);
}

在当前情况下，如果您只是增加数据库版本，它将删除现有表，并使用新列和规范创建一个新表。缺点是您将丢失所有现有数据。

如果您想要保存现有数据并将新列添加到数据库中，则必须执行以下操作：

@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
  switch(oldVersion) {
    case 1:
      //add new column
      sqLiteDatabase.execSQL("ALTER TABLE "+ TABLE_NAME + " ADD COLUMN "+ NEW_COLUMN + " INTEGER/TEXT ");
  }
}

Answer 2

在表中添加任何列或进行任何更新时，只需更新数据库版本即可。..。这帮助我希望它也能为你工作。