How to refresh DataBase in android app

Question

I have a database in my app. At previous version it consisted only of "score" column. In new version I want to add 2 new colunms - "level" and "difficulty".

How to refresh it without causing any mistakes?

The code of database class:

public class ScoreDataBase extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "score_database.db";
private static final int DATABASE_VERSION = 1;

public static final String UID = "_id";
public static final String SCORE = "score";
public static final String DIFF = "difficulty";
public static final String LEVEL = "level";

static final String TABLE_NAME = "score_table";

private static final String SQL_CREATE_ENTRIES = "CREATE TABLE "
        + TABLE_NAME + " (" + UID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
        + SCORE + " INT "+DIFF +" INT "+ LEVEL + " INT);";

private static final String SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS "
        + TABLE_NAME;

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

@Override
public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub
    db.execSQL(SQL_CREATE_ENTRIES);
}

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    db.execSQL(SQL_DELETE_ENTRIES);
    onCreate(db);
}

}

Inside my app I get data in the followin way:

sqh = new ScoreDataBase(this);
    sqdb = sqh.getWritableDatabase();
    best_score = 0;
    String query = "SELECT " + ScoreDataBase.SCORE + 
            " FROM " + ScoreDataBase.TABLE_NAME +
            " ORDER BY " + ScoreDataBase.SCORE + " DESC LIMIT 1";

    Cursor cursor2 = sqdb.rawQuery(query, null);
    while (cursor2.moveToNext()) {
        best_score = cursor2.getInt(cursor2
                .getColumnIndex(ScoreDataBase.SCORE));
        //Log.i("LOG_TAG", "ROW " + id + " HAS NAME " + name);
    }
    cursor2.close();

and it works. But if I change the query to:

String query = "SELECT " + ScoreDataBase.SCORE + 
            " FROM " + ScoreDataBase.TABLE_NAME +
            " WHERE " +ScoreDataBase.DIFF+" = "+ String.valueOf(diff)+
            " AND " +ScoreDataBase.LEVEL+" = "+ String.valueOf(lev_num)+
            " ORDER BY " + ScoreDataBase.SCORE + " DESC LIMIT 1";

It stopps working! What to do? How to refresh DataBase?

Thanks for all the answers!

score 1 · Accepted Answer · edited May 23 '17 at 10:31

First, there's syntax problems in your CREATE TABLE:

private static final String SQL_CREATE_ENTRIES = "CREATE TABLE "
    + TABLE_NAME + " (" + UID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
    + SCORE + " INT "+DIFF +" INT "+ LEVEL + " INT);";

Add commas between your column specifications:

private static final String SQL_CREATE_ENTRIES = "CREATE TABLE "
    + TABLE_NAME + " (" + UID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
    + SCORE + " INT, "+DIFF +" INT, "+ LEVEL + " INT);";

Then, to make the CREATE TABLE run, either increment your database version or just uninstall your app. See When is SQLiteOpenHelper onCreate() / onUpgrade() run?

How to refresh DataBase in android app

1 Answers1