Room Java - Is it possible to run transactions in the interface?

Question

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

I know we should create transactions in abstract classes instead of interfaces. I'm just wondering if this is possible since I already have more than a dozen interfaces in my project and don't want to rewrite them as abstract classes.

Answer 1

What you are trying to do is not possible in an interface because you cannot use a method with a body in an interface.

More specifically, you are trying to execute multiple statements (an UPDATE, then a DELETE), but only one statement can be executed at a time.

Your options are to define a trigger (updated, if the weight row can be determined from within the trigger) or probably more likely use an abstract class and thus use a function to execute multiple statements or use exploit methods (pass / or retrieve) SupportSQliteDatabase (using an abstract class is simpler).

• If you need a trigger, you must use a callback to create the trigger because Room does not provide trigger annotations.

Then, to take advantage of transactions, you would have a dummy @Query before the function. For example

@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 - Code is essentially code and has not been compiled, run or tested and therefore may contain some errors

Additional

This is a working demo, designed to be run only once, which uses all three methods.

First is @Entities, based on what is available in the code, but has used long to represent the date (instead of using a type converter).

access

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

weight

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

@Dao Annotated abstract class with normal abstract methods and methods with bodies (Solution 1). The insert method allows inserting some data (just 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, @Database annotated classes (using singletons) are a little more complicated.

This has a callback to add the trigger, the trigger is overly complex as it not only deletes after the update (not deleting anything), but also adds a new row to the access table showing that the TRIGGER is actually being triggered ( Solution 2).

Also, due to need of better place (or not depending on style/practice), include a function to get and use 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 utilize some of the activity codes aboveMainActivity

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
         */
    }
}

Demo results By SppInspection

As expected the weight table is empty: -

As expected, there are 4 rows in the access table: -

Finally, the schema (i.e. sqlite_master) shows that the trigger exists (additional 3 lines had to be added): -