Returning a count of items from Room DB

Question

I am trying to prevent the deletion of a parent from the Room DB if it has children associated by foreign key.

I am working on a degree tracker. If there are courses for a term, the term cannot be deleted. If there are no courses for the term, the term can be deleted. I am trying to get the count of courses with the associated term id and using a simple if statement to either delete the term if it has no courses and use a popup alert if there are courses for the term and instruct the user to delete the courses prior to deleting the term.

From TermEditorActivity.java

switch(item.getItemId()){
...
case R.id.delete_term:

int coursecount = queryCourses(termIdSelected);

    if(coursecount > 0){
                    AlertDialog.Builder a_builder = new 
                    AlertDialog.Builder(TermEditorActivity.this);
                    a_builder.setMessage("Courses are assigned for this 
                    term!\n\nYou must remove all courses" +
                            "prior to deleting this term.")
                            .setCancelable(false)
                            .setPositiveButton("Okay", new 
                             DialogInterface.OnClickListener() {
                                @Override
                                public void onClick(DialogInterface dialog, 
                                int which) {

                                    finish();
                                }
                            });
                    AlertDialog deleteAllAlert = a_builder.create();
                    deleteAllAlert.setTitle("CANNOT DELETE TERM!!!");
                    deleteAllAlert.show();
                    return true;
                }else{
                    mViewModel.deleteTerm();
                    startActivity(new Intent(TermEditorActivity.this, 
                    MainActivity.class));
                }
...
public int queryCourses(int term) {
        int course = mViewModel.queryCourses(term);
        return course;
    }

From the ViewModel:

public int queryCourses(final int term) {
        int course = mRepository.queryCourses(term);
        return course;
    }

From AppRepository (this is where I think things fall apart):

public int queryCourses(final int term) {
//        executor.execute(new Runnable() {
//            @Override
//            public void run() {
              return count = courseDb.termDao().queryCourses(term);
//            }
//        });
//            return count;
//        }

or with threading:

 public int queryCourses(final int term) {
        executor.execute(new Runnable() {
            @Override
            public void run() {
              count = courseDb.termDao().queryCourses(term);
            }
        });
            return count;
        }

From TermDAO:

@Query("SELECT COUNT(*) FROM course WHERE term_id = :termIdSelected")
    int queryCourses(int termIdSelected);

This results in a runtime error where it crashes when the delete button is pressed. The concept is simple - use the id of the term to query the course DB for the count of the courses with the term id foreign key. If there are none, delete the term and return to the list of terms. If there are courses (count > 0) alert the user and finish without deleting.

Exception without threading:

java.lang.IllegalStateException: Cannot access database on the main thread since it may potentially lock the UI for a long period of time.

When the threading is used it deletes the term with or without courses and no alert appears when there is a course attached to the term. Running in debug mode, coursecount returns 0 when there is one course, so the query is not running properly.

Is there something I need to do to get the value out of the thread?

Here is the crash log of the runtime error when the SQLiteConstraintException is thrown for the RESTRICT constraint. It is not being caught even when using Exception.

E/AndroidRuntime: FATAL EXCEPTION: pool-1-thread-1
    Process: com.mattspriggs.termtest, PID: 23927
    android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 1811 SQLITE_CONSTRAINT_TRIGGER)
        at android.database.sqlite.SQLiteConnection.nativeExecuteForChangedRowCount(Native Method)
        at android.database.sqlite.SQLiteConnection.executeForChangedRowCount(SQLiteConnection.java:784)
        at android.database.sqlite.SQLiteSession.executeForChangedRowCount(SQLiteSession.java:754)
        at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:64)
        at android.arch.persistence.db.framework.FrameworkSQLiteStatement.executeUpdateDelete(FrameworkSQLiteStatement.java:45)
        at android.arch.persistence.room.EntityDeletionOrUpdateAdapter.handle(EntityDeletionOrUpdateAdapter.java:70)
        at com.mattspriggs.termtest.database.TermDao_Impl.deleteTerm(TermDao_Impl.java:144)
        at com.mattspriggs.termtest.database.AppRepository$4.run(AppRepository.java:83)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1167)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:641)
        at java.lang.Thread.run(Thread.java:764)

Answer 1

Room actually has support for this behavior :

when defining the foreign key for your child entity you just need to set action onDelete to RESTRICT this way while parent has children related to it can not be removed.

your child class should look like this :

@Entity(tableName = "child_table",foreignKeys ={
    @ForeignKey(onDelete = RESTRICT,entity = ParentEntity.class,
    parentColumns = "uid",childColumns = "parentId")},
    indices = {
            @Index("parentId"),
    })
public class ChildEntity {
    @PrimaryKey
    public String id;
    public String parentId;
}

your parent class looks like this :

@Entity
public class ParentEntity{
    @PrimaryKey
    public String uid;
}

you can check here for more information how to define foreign key