android - 使用 onUpgrade() 创建新的数据库表 SQLite Android

Question

我是 Android 中 SQLite 数据库的新手，所以我需要一些帮助。我正在尝试制作的应用程序将存储用户的锻炼练习和每次练习的重复次数。我有一个数据库设置和工作，将存储一个练习和它的代表，但我无法让 onUpgrade() 方法工作，以便我可以创建一个更大的数据库来处理其余的练习。

原代码做了一个数据库，有以下几列

public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" + 
                KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 
                KEY_EXERCISE + " TEXT NOT NULL, " +
                KEY_EX1REPS1 + " TEXT NOT NULL," +
                KEY_EX1REPS2 + " TEXT NOT NULL," +
                KEY_EX1REPS3 +  " TEXT NOT NULL);"

                );

    }

但是我想添加下面的例子

                KEY_EXERCISE2 + " TEXT NOT NULL, " +
                KEY_EX2REPS1 + " TEXT NOT NULL," +
                KEY_EX2REPS2 + " TEXT NOT NULL," +
                KEY_EX2REPS3 + " TEXT NOT NULL);

我已经尝试使用下面的 onUpgrade 方法来执行此操作，但它不起作用

        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        db.execSQL("DROP TABLE IF EXISTS" + DATABASE_TABLE); 
        onCreate(db);

    }

如果有人能告诉我如何添加附加列，将不胜感激。以下是完整的database.java文件。

public class Database {

public static final String KEY_ROWID = "_id";

public static final String KEY_EXERCISE = "exercise_name";
public static final String KEY_EX1REPS1 = "exercise_rep1";
public static final String KEY_EX1REPS2 = "exercise_rep2";
public static final String KEY_EX1REPS3 = "exercise_rep3";


public static final String KEY_EXERCISE2 = "exercise2_name";
public static final String KEY_EX2REPS1 = "exercise2_rep1";
public static final String KEY_EX2REPS2 = "exercise2_rep2";
public static final String KEY_EX2REPS3 = "exercise2_rep3";


private DbHelper ourHelper; //just a insince of the DbHelper class
private final Context ourContext;
public static SQLiteDatabase ourDatabase;

public static final int COL_ROWID = 0;

private static final String DATABASE_NAME = "Workouts";
static final String DATABASE_TABLE = "exercisesandreps";
private static final int DATABASE_VERSION = 2;

public static final String[] ALL_KEYS = new String[] {KEY_ROWID, KEY_EXERCISE, KEY_EX1REPS1, KEY_EX1REPS2, KEY_EX1REPS3, KEY_EXERCISE2, KEY_EX2REPS1, KEY_EX2REPS2, KEY_EX2REPS3};


private static class DbHelper extends SQLiteOpenHelper{

    public DbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        //context, name, factory, version
        // TODO Auto-generated constructor stub
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" + 
                KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + //each one of these lines defines a column of the database
                KEY_EXERCISE + " TEXT NOT NULL, " +
                KEY_EX1REPS1 + " TEXT NOT NULL," +
                KEY_EX1REPS2 + " TEXT NOT NULL," +
                KEY_EX1REPS3 + " TEXT NOT NULL," +
                KEY_EXERCISE2 + " TEXT NOT NULL, " +
                KEY_EX2REPS1 + " TEXT NOT NULL," +
                KEY_EX2REPS2 + " TEXT NOT NULL," +
                KEY_EX2REPS3 + " TEXT NOT NULL);"

                );

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        db.execSQL("DROP TABLE IF EXISTS" + DATABASE_TABLE); 
        onCreate(db);

    }   
}
public Database(Context c){
    ourContext = c;
}

public Database open() throws SQLException{
    ourHelper = new DbHelper(ourContext);
    ourDatabase = ourHelper.getWritableDatabase(); //use writable because if you can write to it, you can read it
    return this;
}

public void close(){
    ourHelper.close();
}

public long createEntry(String Exercise, String Ex1R1, String Ex1R2, String Ex1R3, String Exercise2, String Ex2R1, String Ex2R2, String Ex2R3) {
    // TODO Auto-generated method stub
    ContentValues cv = new ContentValues();

    //String Exercise1 = convertArrayToString(ExerciseArray);

    cv.put(KEY_EXERCISE, Exercise);
    cv.put(KEY_EX1REPS1, Ex1R1);
    cv.put(KEY_EX1REPS2, Ex1R2);
    cv.put(KEY_EX1REPS3, Ex1R3);


    cv.put(KEY_EXERCISE2, Exercise2);
    cv.put(KEY_EX2REPS1, Ex2R1);
    cv.put(KEY_EX2REPS2, Ex2R2);
    cv.put(KEY_EX2REPS3, Ex2R3);

    return ourDatabase.insert(DATABASE_TABLE, null, cv); //this will crash if you haven't set up a table with rows + columns

}

public String getData() {
    // TODO Auto-generated method stub
    String[] columns = new String[]{KEY_ROWID, KEY_EXERCISE, KEY_EX1REPS1, KEY_EX1REPS2, KEY_EX1REPS3, KEY_EXERCISE2, KEY_EX2REPS1, KEY_EX2REPS2, KEY_EX2REPS3};
    //you read information through a cursor
    Cursor c = ourDatabase.query(DATABASE_TABLE, columns, null, null, null, null, null);
    String result = "";

    //these keep track of the current row, name, hotness
    int iRow = c.getColumnIndex(KEY_ROWID);//position 0
    int iExercise1 = c.getColumnIndex(KEY_EXERCISE);//position 1
    int iEx1R1 = c.getColumnIndex(KEY_EX1REPS1);//position 2
    int iEx1R2 = c.getColumnIndex(KEY_EX1REPS2);//position 3
    int iEx1R3 = c.getColumnIndex(KEY_EX1REPS3);//position 4

    int iExercise2 = c.getColumnIndex(KEY_EXERCISE);//position 1
    int iEx2R1 = c.getColumnIndex(KEY_EX1REPS1);//position 2
    int iEx2R2 = c.getColumnIndex(KEY_EX1REPS2);//position 3
    int iEx2R3 = c.getColumnIndex(KEY_EX1REPS3);//position 4

    for(c.moveToFirst(); !c.isAfterLast(); c.moveToNext()){  //!c.isAfterLast = if our cursor is not after the last entry in our database 
        //starting at the first position, it will move one each go unless it hasn't exceeded the last entry
        result = result + c.getString(iRow) + " " + c.getString(iExercise1) + " " + c.getString(iEx1R1) + " " + c.getString(iEx1R2) + " " + c.getString(iEx1R3) + 
                                                    c.getString(iExercise2) + " " + c.getString(iEx2R1) + " " + c.getString(iEx2R2) + " " + c.getString(iEx2R3) + 

                "\n";

    }
    return result;
}

public String getExercise(long l) throws SQLException{
    // TODO Auto-generated method stub
    String[] columns = new String[]{KEY_ROWID, KEY_EXERCISE, KEY_EX1REPS1, KEY_EX1REPS2, KEY_EX1REPS3, KEY_EXERCISE2, KEY_EX2REPS1, KEY_EX2REPS2, KEY_EX2REPS3};
    Cursor c = ourDatabase.query(DATABASE_TABLE, columns, KEY_ROWID + "=" + l,null, null, null, null);

    if(c != null){
        c.moveToFirst();
        String exercise = c.getString(1);//we know the name column is the second column in the database so it will have a position 1
        return exercise;//the code will only return the name if the cursor is set to null
    }

    return null;
}



public String[] getReps1(long l) throws SQLException{
    // TODO Auto-generated method stub
    String[] columns = new String[]{KEY_ROWID, KEY_EXERCISE, KEY_EX1REPS1, KEY_EX1REPS2, KEY_EX1REPS3};
    Cursor c = ourDatabase.query(DATABASE_TABLE, columns, KEY_ROWID + "=" + l,null, null, null, null);

    if(c != null){
        c.moveToFirst();
        String reps1 = c.getString(2);//we know the name column is the second column in the database so it will have a position 1
        String reps2 = c.getString(3);
        String reps3 = c.getString(4);
        String[] result = new String[]{reps1, reps2, reps3};
        return result;//the code will only return the name if the cursor is set to null
    }
    return null;
}


public void updateEntry (long lRow, String mExercise, String mEx1Reps1, String mEx1Reps2, String mEx1Reps3) throws SQLException{
    // TODO Auto-generated method stub
    ContentValues cvUpdate = new ContentValues();
    cvUpdate.put(KEY_EXERCISE, mExercise);
    cvUpdate.put(KEY_EX1REPS1, mEx1Reps1);
    cvUpdate.put(KEY_EX1REPS2, mEx1Reps2);
    cvUpdate.put(KEY_EX1REPS3, mEx1Reps3);

    ourDatabase.update(DATABASE_TABLE, cvUpdate, KEY_ROWID + "=" + lRow, null);
}

public void deleteEntry(long lRow1) throws SQLException{
    // TODO Auto-generated method stub
    ourDatabase.delete(DATABASE_TABLE, KEY_ROWID + "=" + lRow1, null);

}


public static String convertArrayToString(String[] array){
    String str = "";
    for (int i = 0;i<array.length; i++) {
        str = str+array[i];
        // Do not append comma at the end of last element
        if(i<array.length-1){
            str = str+",";
        }
    }
    return str;
}
public static String[] convertStringToArray(String str){
    String[] arr = str.split(",");
    return arr;
}

public void deleteAll() {
    Cursor c = getAllRows();
    long rowId = c.getColumnIndexOrThrow(KEY_ROWID);
    if (c.moveToFirst()) {
        do {
            deleteRow(c.getLong((int) rowId));              
        } while (c.moveToNext());
    }
    c.close();
}

public Cursor getAllRows() {
    String where = null;
    Cursor c =  ourDatabase.query(true, DATABASE_TABLE, ALL_KEYS, 
                        where, null, null, null, null, null);
    if (c != null) {
        c.moveToFirst();
    }
    return c;
}
public boolean deleteRow(long rowId) {
    String where = KEY_ROWID + "=" + rowId;
    return ourDatabase.delete(DATABASE_TABLE, where, null) != 0;
}
}

Answer 1

1. 如果您还没有完成，请增加您的 DATABASE_VERSION。
2. 通常情况下，你需要提取onCreate方法中的db.execSQL("...")进入一个新方法，例如名为 upgradeDB()，然后您可以调用onUpgrade 方法中的 upgradeDB() 方法。

否则，您可以在您的设备中完全卸载您的应用程序，然后重新安装它，它将重新创建整个数据库。