Is there a way when Migrating a Room Database to use Variables that are calculated during the Migration?

Question

I'm writing an Android App in Java and I've changed my Database and now I want to migrate it to the new version but to do that I need to compute some of the old values and then insert those into one of the new tables into the db but when I call

database.execSQL()

with my variables and then access them it appears to not have set them as every value is 0.

Is it just not possible or did I simply miss something?

public void migrate(@NonNull SupportSQLiteDatabase database) {
             database.execSQL("CREATE TABLE 'season' ('id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
                    'spring' INTEGER DEFAULT 0, 'summer' INTEGER DEFAULT 0, 'autumn' INTEGER 
                    DEFAULT 0, 'winter' 
                    INTEGER DEFAULT 0, 'spook' INTEGER DEFAULT 0)");
            database.execSQL("INSERT INTO 'Clothes' (type_of_clothing,clothing_name,description,seasonId,in_laundry,TypeList) " +
                    "SELECT type_of_clothing,clothing_name,description,type_of_clothing,in_laundry,TypeList " +
                    "FROM ClothesNew");
            Cursor cursor = database.query("SELECT * FROM Clothes");
            Cursor secondCursor = database.query("SELECT * FROM ClothesSeason");
            ArrayList<Season> seasonsToBeAdded = new ArrayList<>();
            while (cursor.moveToNext()) {
                secondCursor.moveToNext();
                int Sid = cursor.getInt(0);
                String seasonString = secondCursor.getString(2);
                Season season = new Season(seasonString);
                int tempId = Sid;
                boolean isInList = false;
                for (Season tempSeason : seasonsToBeAdded) {
                    if (tempSeason.equals(season)) {
                        tempId = seasonsToBeAdded.indexOf(tempSeason);
                        isInList = true;
                        break;
                    }
                }
                if (!isInList) {
                    season.setId(seasonsToBeAdded.size());
                    tempId = season.getId();
                    seasonsToBeAdded.add(season);
                }
                if (seasonsToBeAdded.size() == 0) {seasonsToBeAdded.add(season);}
                
                database.execSQL("UPDATE Clothes SET seasonId = :tempId WHERE uid = :Sid");
            }

            int id, spring, summer, autumn, winter, spook;

            for (Season season : seasonsToBeAdded) {
                id = season.getId();
                spring = season.getSpring();
                summer = season.getSummer();
                autumn = season.getAutumn();
                winter = season.getWinter();
                spook = season.getSpook();
                database.execSQL("INSERT INTO season " +
                        "VALUES(:id, :spring, :summer, :autumn, :winter, :spook) ");
            }
            Cursor otherCursor = database.query("SELECT * FROM season");
            ArrayList<Integer> springValues= new ArrayList<>();
            while (otherCursor.moveToNext()) {
                springValues.add(otherCursor.getInt(1));
            }
        }

When I look at the Values stored in springValues they are all 0 even though I add the correct ones in this query

database.execSQL("INSERT INTO season " +
                 "VALUES(:id, :spring, :summer, :autumn, :winter, :spook) ");

Can I just not use the :var notation here?

score 1 · Accepted Answer · answered Mar 19 '21 at 21:18

I'd suggest utilising The SQLiteDatabase Insert convenience method along with a ContentValues object.

Instead of :-

        for (Season season : seasonsToBeAdded) {
            id = season.getId();
            spring = season.getSpring();
            summer = season.getSummer();
            autumn = season.getAutumn();
            winter = season.getWinter();
            spook = season.getSpook();
            database.execSQL("INSERT INTO season " +
                    "VALUES(:id, :spring, :summer, :autumn, :winter, :spook) ");
        }

Your code could then be :-

    ContentValues cv = new ContentValues(); // Instantiate ContentValues object
    for (Season season : seasonsToBeAdded) {
        cv.clear();
        cv.put("`id`",season.getId());
        cv.put("`spring`",season.getSpring());
        cv.put("`summer`",season.getSummer());
        cv.put("`autumn`",season.getAutumn());
        cv.put("`winter`",season.getWinter());
        cv.put("`spook`",season.getSpook());
        database.insert("season",null,cv); //<<<< Recommended Uses the convenience Insert Method

Note there should be no need to enclose the column names in grave accents `` as used above.
The above code is in-principle code and has not been tested, as such it may contain some errors.

As you can see the SQL is built for you. The insert method returns the rowid (id column) of the inserted row or -1 if the insert failed, you may wish to take advantage of this value.

You may also wish to utilise other insert methods e.g. insertWithOnConflict e.g. database.insertWithOnConflict("season",null,cv,SQLiteDatabase.CONFLICT_IGNORE);

It would also be advisable to utilise transactions by utilising the appropriate SQLiteDatabase transaction handling methods (see beginTransaction)

It also also recommended that you utilise constants for column names so the name is coded in just on place. So little chance of getting the column names wrong.

Is there a way when Migrating a Room Database to use Variables that are calculated during the Migration?

1 Answers1