Android Room - simple select query - Cannot access database on the main thread

I am trying a sample with Room Persistence Library. I created an Entity:

@Entity
public class Agent {
    @PrimaryKey
    public String guid;
    public String name;
    public String email;
    public String password;
    public String phone;
    public String licence;
}

Created a DAO class:

@Dao
public interface AgentDao {
    @Query("SELECT COUNT(*) FROM Agent where email = :email OR phone = :phone OR licence = :licence")
    int agentsCount(String email, String phone, String licence);

    @Insert
    void insertAgent(Agent agent);
}

Created the Database class:

@Database(entities = {Agent.class}, version = 1)
public abstract class AppDatabase extends RoomDatabase {
    public abstract AgentDao agentDao();
}

Exposed database using below subclass in Kotlin:

class MyApp : Application() {

    companion object DatabaseSetup {
        var database: AppDatabase? = null
    }

    override fun onCreate() {
        super.onCreate()
        MyApp.database =  Room.databaseBuilder(this, AppDatabase::class.java, "MyDatabase").build()
    }
}

Implemented below function in my activity:

void signUpAction(View view) {
        String email = editTextEmail.getText().toString();
        String phone = editTextPhone.getText().toString();
        String license = editTextLicence.getText().toString();

        AgentDao agentDao = MyApp.DatabaseSetup.getDatabase().agentDao();
        //1: Check if agent already exists
        int agentsCount = agentDao.agentsCount(email, phone, license);
        if (agentsCount > 0) {
            //2: If it already exists then prompt user
            Toast.makeText(this, "Agent already exists!", Toast.LENGTH_LONG).show();
        }
        else {
            Toast.makeText(this, "Agent does not exist! Hurray :)", Toast.LENGTH_LONG).show();
            onBackPressed();
        }
    }

Unfortunately on execution of above method it crashes with below stack trace:

    FATAL EXCEPTION: main
 Process: com.example.me.MyApp, PID: 31592
java.lang.IllegalStateException: Could not execute method for android:onClick
    at android.support.v7.app.AppCompatViewInflater$DeclaredOnClickListener.onClick(AppCompatViewInflater.java:293)
    at android.view.View.performClick(View.java:5612)
    at android.view.View$PerformClick.run(View.java:22288)
    at android.os.Handler.handleCallback(Handler.java:751)
    at android.os.Handler.dispatchMessage(Handler.java:95)
    at android.os.Looper.loop(Looper.java:154)
    at android.app.ActivityThread.main(ActivityThread.java:6123)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:867)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:757)
 Caused by: java.lang.reflect.InvocationTargetException
    at java.lang.reflect.Method.invoke(Native Method)
    at android.support.v7.app.AppCompatViewInflater$DeclaredOnClickListener.onClick(AppCompatViewInflater.java:288)
    at android.view.View.performClick(View.java:5612) 
    at android.view.View$PerformClick.run(View.java:22288) 
    at android.os.Handler.handleCallback(Handler.java:751) 
    at android.os.Handler.dispatchMessage(Handler.java:95) 
    at android.os.Looper.loop(Looper.java:154) 
    at android.app.ActivityThread.main(ActivityThread.java:6123) 
    at java.lang.reflect.Method.invoke(Native Method) 
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:867) 
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:757) 
 Caused by: java.lang.IllegalStateException: Cannot access database on the main thread since it may potentially lock the UI for a long periods of time.
    at android.arch.persistence.room.RoomDatabase.assertNotMainThread(RoomDatabase.java:137)
    at android.arch.persistence.room.RoomDatabase.query(RoomDatabase.java:165)
    at com.example.me.MyApp.RoomDb.Dao.AgentDao_Impl.agentsCount(AgentDao_Impl.java:94)
    at com.example.me.MyApp.View.SignUpActivity.signUpAction(SignUpActivity.java:58)
    at java.lang.reflect.Method.invoke(Native Method) 
    at android.support.v7.app.AppCompatViewInflater$DeclaredOnClickListener.onClick(AppCompatViewInflater.java:288) 
    at android.view.View.performClick(View.java:5612) 
    at android.view.View$PerformClick.run(View.java:22288) 
    at android.os.Handler.handleCallback(Handler.java:751) 
    at android.os.Handler.dispatchMessage(Handler.java:95) 
    at android.os.Looper.loop(Looper.java:154) 
    at android.app.ActivityThread.main(ActivityThread.java:6123) 
    at java.lang.reflect.Method.invoke(Native Method) 
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:867) 
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:757) 

Seems like that problem is related to execution of db operation on main thread. However the sample test code provided in above link does not run on a separate thread:

@Test
    public void writeUserAndReadInList() throws Exception {
        User user = TestUtil.createUser(3);
        user.setName("george");
        mUserDao.insert(user);
        List<User> byName = mUserDao.findUsersByName("george");
        assertThat(byName.get(0), equalTo(user));
    }

Am I missing anything over here? How can I make it execute without crash? Please suggest.

It's not recommended but you can access to database on main thread with allowMainThreadQueries()

MyApp.database =  Room.databaseBuilder(this, AppDatabase::class.java, "MyDatabase").allowMainThreadQueries().build()

Kotlin Coroutines (Clear & Concise)

AsyncTask is really clunky. Coroutines are a cleaner alternative (just sprinkle a couple of keywords and your sync code becomes async).

// Step 1: add `suspend` to your fun
suspend fun roomFun(...): Int
suspend fun notRoomFun(...) = withContext(Dispatchers.IO) { ... }

// Step 2: launch from coroutine scope
private fun myFun() {
    lifecycleScope.launch { // coroutine on Main
        val queryResult = roomFun(...) // coroutine on IO
        doStuff() // ...back on Main
    }
}

Dependencies (adds coroutine scopes for arch components):

// lifecycleScope:
implementation 'androidx.lifecycle:lifecycle-runtime-ktx:2.2.0-alpha04'

// viewModelScope:
implementation 'androidx.lifecycle:lifecycle-viewmodel-ktx:2.2.0-alpha04'

-- Updates:
08-May-2019: Room 2.1 now supports suspend
13-Sep-2019: Updated to use Architecture components scope

Database access on main thread locking the UI is the error, like Dale said.

--EDIT 2--

Since many people may come across this answer... The best option nowadays, generally speaking, is Kotlin Coroutines. Room now supports it directly (currently in beta). https://kotlinlang.org/docs/reference/coroutines-overview.html https://developer.android.com/jetpack/androidx/releases/room#2.1.0-beta01

--EDIT 1--

For people wondering... You have other options. I recommend taking a look into the new ViewModel and LiveData components. LiveData works great with Room. https://developer.android.com/topic/libraries/architecture/livedata.html

Another option is the RxJava/RxAndroid. More powerful but more complex than LiveData. https://github.com/ReactiveX/RxJava

--Original answer--

Create a static nested class (to prevent memory leak) in your Activity extending AsyncTask.

private static class AgentAsyncTask extends AsyncTask<Void, Void, Integer> {

    //Prevent leak
    private WeakReference<Activity> weakActivity;
    private String email;
    private String phone;
    private String license;

    public AgentAsyncTask(Activity activity, String email, String phone, String license) {
        weakActivity = new WeakReference<>(activity);
        this.email = email;
        this.phone = phone;
        this.license = license;
    }

    @Override
    protected Integer doInBackground(Void... params) {
        AgentDao agentDao = MyApp.DatabaseSetup.getDatabase().agentDao();
        return agentDao.agentsCount(email, phone, license);
    }

    @Override
    protected void onPostExecute(Integer agentsCount) {
        Activity activity = weakActivity.get();
        if(activity == null) {
            return;
        }

        if (agentsCount > 0) {
            //2: If it already exists then prompt user
            Toast.makeText(activity, "Agent already exists!", Toast.LENGTH_LONG).show();
        } else {
            Toast.makeText(activity, "Agent does not exist! Hurray :)", Toast.LENGTH_LONG).show();
            activity.onBackPressed();
        }
    }
}

Or you can create a final class on its own file.

Then execute it in the signUpAction(View view) method:

new AgentAsyncTask(this, email, phone, license).execute();

In some cases you might also want to hold a reference to the AgentAsyncTask in your activity so you can cancel it when the Activity is destroyed. But you would have to interrupt any transactions yourself.

Also, your question about the Google's test example... They state in that web page:

> The recommended approach for testing your database implementation is > writing a JUnit test that runs on an Android device. Because these > tests don't require creating an activity, they should be faster to > execute than your UI tests.

No Activity, no UI.

For all the RxJava or RxAndroid or RxKotlin lovers out there

Observable.just(db)
          .subscribeOn(Schedulers.io())
          .subscribe { db -> // database operation }

You cannot run it on main thread instead use handlers, async or working threads . A sample code is available here and read article over room library here : Android's Room Library

/**
 *  Insert and get data using Database Async way
 */
AsyncTask.execute(new Runnable() {
    @Override
    public void run() {
        // Insert Data
        AppDatabase.getInstance(context).userDao().insert(new User(1,"James","Mathew"));
        
        // Get Data
        AppDatabase.getInstance(context).userDao().getAllUsers();
    }
});

If you want to run it on main thread which is not preferred way .

You can use this method to achieve on main thread Room.inMemoryDatabaseBuilder()

With lambda its easy to run with AsyncTask

 AsyncTask.execute(() -> //run your query here );

Just do the database operations in a separate Thread. Like this (Kotlin):

Thread {
   //Do your database´s operations here
}.start()

With the Jetbrains Anko library, you can use the doAsync{..} method to automatically execute database calls. This takes care of the verbosity problem you seemed to have been having with mcastro's answer.

Example usage:

    doAsync { 
        Application.database.myDAO().insertUser(user) 
    }

I use this frequently for inserts and updates, however for select queries I reccommend using the RX workflow.

Simply you can use this code for solve it:

Executors.newSingleThreadExecutor().execute(new Runnable() {
                    @Override
                    public void run() {
                        appDb.daoAccess().someJobes();//replace with your code
                    }
                });

Or in lambda you can use this code:

Executors.newSingleThreadExecutor().execute(() -> appDb.daoAccess().someJobes());

You can replace appDb.daoAccess().someJobes() with your own code;

You have to execute request in background. A simple way could be using an Executors :

Executors.newSingleThreadExecutor().execute { 
   yourDb.yourDao.yourRequest() //Replace this by your request
}

As asyncTask are deprecated we may use executor service. OR you can also use ViewModel with LiveData as explained in other answers.

For using executor service, you may use something like below.

public class DbHelper {

    private final Executor executor = Executors.newSingleThreadExecutor();

    public void fetchData(DataFetchListener dataListener){
	    executor.execute(() -> {
		    	Object object = retrieveAgent(agentId);
			    new Handler(Looper.getMainLooper()).post(() -> {
				        dataListener.onFetchDataSuccess(object);
	            });
		});
    }
}

Main Looper is used, so that you can access UI element from onFetchDataSuccess callback.

For quick queries you can allow room to execute it on UI thread.

AppDatabase db = Room.databaseBuilder(context.getApplicationContext(),
        AppDatabase.class, DATABASE_NAME).allowMainThreadQueries().build();

In my case I had to figure out of the clicked user in list exists in database or not. If not then create the user and start another activity

       @Override
        public void onClick(View view) {



            int position = getAdapterPosition();

            User user = new User();
            String name = getName(position);
            user.setName(name);

            AppDatabase appDatabase = DatabaseCreator.getInstance(mContext).getDatabase();
            UserDao userDao = appDatabase.getUserDao();
            ArrayList<User> users = new ArrayList<User>();
            users.add(user);
            List<Long> ids = userDao.insertAll(users);

            Long id = ids.get(0);
            if(id == -1)
            {
                user = userDao.getUser(name);
                user.setId(user.getId());
            }
            else
            {
                user.setId(id);
            }

            Intent intent = new Intent(mContext, ChatActivity.class);
            intent.putExtra(ChatActivity.EXTRAS_USER, Parcels.wrap(user));
            mContext.startActivity(intent);
        }
    }

An elegant RxJava/Kotlin solution is to use Completable.fromCallable, which will give you an Observable which does not return a value, but can observed and subscribed on a different thread.

public Completable insert(Event event) {
    return Completable.fromCallable(new Callable<Void>() {
        @Override
        public Void call() throws Exception {
            return database.eventDao().insert(event)
        }
    }
}

Or in Kotlin:

fun insert(event: Event) : Completable = Completable.fromCallable {
    database.eventDao().insert(event)
}

You can the observe and subscribe as you would usually:

dataManager.insert(event)
    .subscribeOn(scheduler)
    .observeOn(AndroidSchedulers.mainThread())
    .subscribe(...)

You can allow database access on the main thread but only for debugging purpose, you shouldn't do this on production.

Here is the reason.

Note: Room doesn't support database access on the main thread unless you've called allowMainThreadQueries() on the builder because it might lock the UI for a long period of time. Asynchronous queries—queries that return instances of LiveData or Flowable—are exempt from this rule because they asynchronously run the query on a background thread when needed.

Room Database does not allow you to execute a database IO operation(Background operation) in Main thread, unless you use allowMainThreadQueries() with database builder. But it is a bad approach.

---

Recommended Approach:
Here I am using some code from my current project.

Add suspend keyword before your method in Repository

class FavRepository @Inject constructor(private val dao: WallpaperDao) {
    suspend fun getWallpapers(): List<Wallpaper> =  dao.getWallpapers()
}

In your viewmodel class first you need to execute your database operation with Coroutine Dispature IO for fetching data from room database. Then update your value with Coroutine Dispature MAIN.

@HiltViewModel
class FavViewModel @Inject constructor(repo: FavRepository, @ApplicationContext context: Context) : ViewModel() {
    var favData = MutableLiveData<List<Wallpaper>>()
    init {
        viewModelScope.launch(Dispatchers.IO){
            val favTmpData: List<Wallpaper> = repo.getWallpapers()
            withContext(Dispatchers.Main){
                favData.value = favTmpData
            }
        }
    }
}

Now you use your viewmodel's data by observing from Activity/ Fragment.

Hope this will be helpful for you :) .

The error message,

> Cannot access database on the main thread since it may potentially lock the UI for a long periods of time.

Is quite descriptive and accurate. The question is how should you avoid accessing the database on the main thread. That is a huge topic, but to get started, read about AsyncTask (click here)

-----EDIT----------

I see you are having problems when you run a unit test. You have a couple of choices to fix this:

1. Run the test directly on the development machine rather than on an Android device (or emulator). This works for tests that are database-centric and don't really care whether they are running on a device.

2. Use the annotation @RunWith(AndroidJUnit4.class) to run the test on the android device, but not in an activity with a UI. More details about this can be found in this tutorial

Update: I also got this message when I was trying to build a query using @RawQuery and SupportSQLiteQuery inside the DAO.

@Transaction
public LiveData<List<MyEntity>> getList(MySettings mySettings) {
    //return getMyList(); -->this is ok

    return getMyList(new SimpleSQLiteQuery("select * from mytable")); --> this is an error

Solution: build the query inside the ViewModel and pass it to the DAO.

public MyViewModel(Application application) {
...
        list = Transformations.switchMap(searchParams, params -> {

            StringBuilder sql;
            sql = new StringBuilder("select  ... ");

            return appDatabase.rawDao().getList(new SimpleSQLiteQuery(sql.toString()));

        });
    }

Or...

You should not access the database directly on the main thread, for example:

 public void add(MyEntity item) {
     appDatabase.myDao().add(item); 
 }

You should use AsyncTask for update, add, and delete operations.

Example:

public class MyViewModel extends AndroidViewModel {

    private LiveData<List<MyEntity>> list;

    private AppDatabase appDatabase;

    public MyViewModel(Application application) {
        super(application);

        appDatabase = AppDatabase.getDatabase(this.getApplication());
        list = appDatabase.myDao().getItems();
    }

    public LiveData<List<MyEntity>> getItems() {
        return list;
    }

    public void delete(Obj item) {
        new deleteAsyncTask(appDatabase).execute(item);
    }

    private static class deleteAsyncTask extends AsyncTask<MyEntity, Void, Void> {

        private AppDatabase db;

        deleteAsyncTask(AppDatabase appDatabase) {
            db = appDatabase;
        }

        @Override
        protected Void doInBackground(final MyEntity... params) {
            db.myDao().delete((params[0]));
            return null;
        }
    }

    public void add(final MyEntity item) {
        new addAsyncTask(appDatabase).execute(item);
    }

    private static class addAsyncTask extends AsyncTask<MyEntity, Void, Void> {

        private AppDatabase db;

        addAsyncTask(AppDatabase appDatabase) {
            db = appDatabase;
        }

        @Override
        protected Void doInBackground(final MyEntity... params) {
            db.myDao().add((params[0]));
            return null;
        }

    }
}

If you use LiveData for select operations, you don't need AsyncTask.

If you are more comfortable with Async task:

  new AsyncTask<Void, Void, Integer>() {
                @Override
                protected Integer doInBackground(Void... voids) {
                    return Room.databaseBuilder(getApplicationContext(),
                            AppDatabase.class, DATABASE_NAME)
                            .fallbackToDestructiveMigration()
                            .build()
                            .getRecordingDAO()
                            .getAll()
                            .size();
                }
    
                @Override
                protected void onPostExecute(Integer integer) {
                    super.onPostExecute(integer);
                    Toast.makeText(HomeActivity.this, "Found " + integer, Toast.LENGTH_LONG).show();
                }
            }.execute();

You can use Future and Callable. So you would not be required to write a long asynctask and can perform your queries without adding allowMainThreadQueries().

My dao query:-

@Query("SELECT * from user_data_table where SNO = 1")
UserData getDefaultData();

My repository method:-

public UserData getDefaultData() throws ExecutionException, InterruptedException {

    Callable<UserData> callable = new Callable<UserData>() {
        @Override
        public UserData call() throws Exception {
            return userDao.getDefaultData();
        }
    };

    Future<UserData> future = Executors.newSingleThreadExecutor().submit(callable);

    return future.get();
}

In my opinion the right thing to do is to delegate the query to an IO thread using RxJava.

I have an example of a solution to an equivalent problem I've just encountered.

((ProgressBar) view.findViewById(R.id.progressBar_home)).setVisibility(View.VISIBLE);//Always good to set some good feedback
        Completable.fromAction(() -> {
            //Creating view model requires DB access
            homeViewModel = new ViewModelProvider(this, factory).get(HomeViewModel.class);
        }).subscribeOn(Schedulers.io())//The DB access executes on a non-main-thread thread
        .observeOn(AndroidSchedulers.mainThread())//Upon completion of the DB-involved execution, the continuation runs on the main thread
        .subscribe(
                () ->
                {
                    mAdapter = new MyAdapter(homeViewModel.getExams());
                    recyclerView.setAdapter(mAdapter);
                    ((ProgressBar) view.findViewById(R.id.progressBar_home)).setVisibility(View.INVISIBLE);
                },
                error -> error.printStackTrace()
        );

And if we want to generalize the solution:

((ProgressBar) view.findViewById(R.id.progressBar_home)).setVisibility(View.VISIBLE);//Always good to set some good feedback
        Completable.fromAction(() -> {
            someTaskThatTakesTooMuchTime();
        }).subscribeOn(Schedulers.io())//The long task executes on a non-main-thread thread
        .observeOn(AndroidSchedulers.mainThread())//Upon completion of the DB-involved execution, the continuation runs on the main thread
        .subscribe(
                () ->
                {
                    taskIWantToDoOnTheMainThreadWhenTheLongTaskIsDone();
                },
                error -> error.printStackTrace()
        );

Add .allowMainThreadQueries() in database file

@Database(entities = [Country::class], version = 1)
abstract class CountryDatabase: RoomDatabase() {
    abstract fun getCountryDao(): CountryDao
    companion object {
        @Volatile
        private var instance: CountryDatabase? = null
        private val LOCK = Any()

        operator fun invoke(context: Context) = instance ?:
        synchronized(LOCK) {
            instance ?:
            createDatabase(context).also { instance = it }
        }
        private fun createDatabase(context: Context) =
            Room.databaseBuilder(
                context.applicationContext,
                CountryDatabase::class.java,
                "country_db"
            ).allowMainThreadQueries()
             .build()
    }
}

Add Dispatchers.IO at the end of flow like this:

flow { ... }.flowOn(Dispatchers.IO)