Room Java - Is it possible to run transaction in interface?

Question

I'm using room framework in my Android Studio project. I'm trying to create a transaction inside of an interface. I already read documentation from here: https://developer.android.com/reference/androidx/room/Transaction

I know that we should create transactions inside abstract classes and not in interfaces. I'm just wondering if it's even possible, cause I already have over a dozen interfaces in my project and don't really wanna to rewrite them to abstract classes.

Answer 1

What you are attempting is not possible in an interface as you cannot have methods with bodies in an interface.

More specifically, you are trying to execute multiple statements (an UPDATE and then a DELETE) when only one can be performed at once.

You options are either to define a TRIGGER (AFTER UPDATE, if the weight row can be determined from within the trigger) or probably more likely to use an abstract class and thus a function to perform the multiple statements or use methods that utilise (are passed/or retrieve) an SupportSQliteDatabase (simpler to use an abstract class).

• If you want a TRIGGER, then you would have to utilise a Callback to create the trigger as Room does not provide annotations for triggers.

Then to utilise a transaction you would have a dummy @Query preceding the function. e.g.

@Dao
abstract class TheClassForMethodsWithBodies {

    @Query("UPDATE visits SET date=:date WHERE id=5")
    void testUpdate(Date date);
    @Query("DELETE FROM wieght WHERE id_weight=1")
    void testDelete();

    @Query("")
    void test(Date date) {
        testUpdate(date);
        testDelete();
    }
}

• Note - the code is in principle code, it has not been compiled, run or tested and thus may contains some errors

Additional

Here's a working demo, designed just to run once, that uses all three methods.

First the @Entities, based upon what's available from your code but a long has been used for the date (rather than have type converters).

Visits

@Entity
class Visits {
   @PrimaryKey
   Long id=null;
   Long date = System.currentTimeMillis() / 1000;
}

Weight

@Entity
class Weight {
    @PrimaryKey
    Long id_weight=null;
}

The @Dao annotated abstract class that has normal abstract methods and methods with bodies (solution 1). The insert method allowing some data to be inserted (just the one row).

@Dao
abstract class AllDao {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract long insert(Visits visits);

    @Query("UPDATE visits SET date=:date WHERE id=1")
    abstract void resetVisitData(long date);
    @Query("DELETE FROM weight WHERE id_weight=5")
    abstract void deleteFromWeight();
    @Query("")
    void doBoth(long date) {
        resetVisitData(date);
        deleteFromWeight();
    }
}

Now, where it is a little more complex the @Database annotated class (using a singleton).

This has a callback to add the TRIGGER, the trigger is overly complex in that it not only does the delete after the UPDATE (not that there is anything that is deleted) BUT it also adds a new row into the visits table to show that TRIGGER is actually being triggered (solution 2).

Furthermore, for want of a better place (or not depending on style/practices) a function is included to get and use the SupportSQLiteDatabase (solution 3)

@Database(entities = {Weight.class,Visits.class}, version = 1,exportSchema = false)
abstract class TheDatabase extends RoomDatabase {
    abstract AllDao getAllDao();

    private static TheDatabase INSTANCE;
    static TheDatabase getINSTANCE(Context context) {
        if (INSTANCE==null) {
            INSTANCE = Room.databaseBuilder(
                            context,
                            TheDatabase.class,
                            "the_database.db"
                    )
                    .allowMainThreadQueries()
                    .addCallback(cb)
                    .build();
        }
        return INSTANCE;
    }

    /* Solution 2 - via SupportSQLiteDatabase */
    void viaSupportSB(long date) {
        SupportSQLiteDatabase db = this.getOpenHelper().getWritableDatabase();
        db.beginTransaction();
        db.execSQL("UPDATE visits SET date=? WHERE id=1",new String[]{String.valueOf(date)});
        db.execSQL("DELETE FROM weight WHERE id_weight=-600");
        db.setTransactionSuccessful();
        db.endTransaction();
    }


    /* USING a TRIGGER (not intended to make sense/do anything useful just demo) */
    private static final String CREATETRIGGERSQL = "CREATE TRIGGER IF NOT EXISTS theTrigger AFTER UPDATE ON visits BEGIN DELETE FROM weight WHERE id_weight=5; INSERT OR IGNORE INTO visits (date) VALUES(strftime('%s','now')); END";
    static Callback cb  = new Callback() {
        @Override
        public void onCreate(@NonNull SupportSQLiteDatabase db) {
            super.onCreate(db);
            db.execSQL(CREATETRIGGERSQL);
        }

        @Override
        public void onDestructiveMigration(@NonNull SupportSQLiteDatabase db) {
            super.onDestructiveMigration(db);
        }

        @Override
        public void onOpen(@NonNull SupportSQLiteDatabase db) {
            super.onOpen(db);
            db.execSQL(CREATETRIGGERSQL);
        }
    };
}

To actually utilise the above some activity code MainActivity

public class MainActivity extends AppCompatActivity {

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

        roomInstance = TheDatabase.getINSTANCE(this);
        dao = roomInstance.getAllDao();

        dao.insert(new Visits()); /* Insert a row */

        /* Solution 2 - via Trigger */
        dao.resetVisitData(System.currentTimeMillis() - (24 * 60 * 60 * 7 /* one week ago BUT OOOPS not divided by 1000 */));
        /* Solution 1 - via abstract class aka method with body */
        dao.doBoth(System.currentTimeMillis() / 1000);
        /* Solution 3 - via SupportSQLiteDatabase */
        roomInstance.viaSupportSB(System.currentTimeMillis() + (24 * 60 * 60 * 7 /*week in the future  again OOOPS not divided by 1000*/));
        
        /* Expected result
            1. sinlge row inserted into visits
            2. trigger adds another row into visits (row 2)
            3. doBoth updates so another row added to visits (row 3)
            4. via SupportSQLiteDatabase updates so another row added to visits (row 4)
            
            So 4 rows in visits no rows in weight
         */
    }
}

Result from Demo via SppInspection

As expected weight table is empty:-

As expected 4 rows in visits table:-

Finally the schema (i.e. sqlite_master) showing that the trigger exists (must do to add the extra 3 rows) :-