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

The question:

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

public class Agent {
    public String guid;
    public String name;
    public String email;
    public String password;
    public String phone;
    public String licence;

Created a DAO class:

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);

    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() {
        MyApp.database =  Room.databaseBuilder(this,, "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();

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

 Process:, PID: 31592
java.lang.IllegalStateException: Could not execute method for android:onClick
    at android.view.View.performClick(
    at android.view.View$
    at android.os.Handler.handleCallback(
    at android.os.Handler.dispatchMessage(
    at android.os.Looper.loop(
    at java.lang.reflect.Method.invoke(Native Method)
 Caused by: java.lang.reflect.InvocationTargetException
    at java.lang.reflect.Method.invoke(Native Method)
    at android.view.View.performClick( 
    at android.view.View$ 
    at android.os.Handler.handleCallback( 
    at android.os.Handler.dispatchMessage( 
    at android.os.Looper.loop( 
    at java.lang.reflect.Method.invoke(Native Method) 
 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 java.lang.reflect.Method.invoke(Native Method) 
    at android.view.View.performClick( 
    at android.view.View$ 
    at android.os.Handler.handleCallback( 
    at android.os.Handler.dispatchMessage( 
    at android.os.Looper.loop( 
    at java.lang.reflect.Method.invoke(Native Method) 

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:

    public void writeUserAndReadInList() throws Exception {
        User user = TestUtil.createUser(3);
        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.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

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

MyApp.database =  Room.databaseBuilder(this,, "MyDatabase").allowMainThreadQueries().build()

Method 2

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

Method 3

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).

–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.

Another option is the RxJava/RxAndroid. More powerful but more complex than LiveData.

–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); = email; = phone;
        this.license = license;

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

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

        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();

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.

Method 4

For all the RxJava or RxAndroid or RxKotlin lovers out there

          .subscribe { db -> // database operation }

Method 5

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() {
    public void run() {
        // Insert Data
        AppDatabase.getInstance(context).userDao().insert(new User(1,"James","Mathew"));

        // Get Data

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()

Method 6

With lambda its easy to run with AsyncTask

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

Method 7

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

Thread {
   //Do your database´s operations here

Method 8

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 { 

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

Method 9

Simply you can use this code for solve it:

Executors.newSingleThreadExecutor().execute(new Runnable() {
                    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;

Method 10

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

Method 11

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(() -> {

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

Method 12

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

        public void onClick(View view) {

            int position = getAdapterPosition();

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

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

            Long id = ids.get(0);
            if(id == -1)
                user = userDao.getUser(name);

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

Method 13

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>() {
        public Void call() throws Exception {
            return database.eventDao().insert(event)

Or in Kotlin:

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

You can the observe and subscribe as you would usually:


Method 14

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.

Method 15

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.

class FavViewModel @Inject constructor(repo: FavRepository, @ApplicationContext context: Context) : ViewModel() {
    var favData = MutableLiveData<List<Wallpaper>>()
    init {
            val favTmpData: List<Wallpaper> = repo.getWallpapers()
                favData.value = favTmpData

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

Hope this will be helpful for you 🙂 .

Method 16

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)


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
    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

Method 17

If you are more comfortable with Async task:

  new AsyncTask<Void, Void, Integer>() {
                protected Integer doInBackground(Void... voids) {
                    return Room.databaseBuilder(getApplicationContext(),
                            AppDatabase.class, DATABASE_NAME)

                protected void onPostExecute(Integer integer) {
                    Toast.makeText(HomeActivity.this, "Found " + integer, Toast.LENGTH_LONG).show();

Method 18

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>() {
        public UserData call() throws Exception {
            return userDao.getDefaultData();

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

    return future.get();

Method 19

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

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()));



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

 public void add(MyEntity item) {

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


public class MyViewModel extends AndroidViewModel {

    private LiveData<List<MyEntity>> list;

    private AppDatabase appDatabase;

    public MyViewModel(Application 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;

        protected Void doInBackground(final MyEntity... params) {
            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;

        protected Void doInBackground(final MyEntity... params) {
            return null;


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

Method 20

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(;//Always good to set some good feedback
        Completable.fromAction(() -> {
            //Creating view model requires DB access
            homeViewModel = new ViewModelProvider(this, factory).get(HomeViewModel.class);
        }).subscribeOn( 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
                () ->
                    mAdapter = new MyAdapter(homeViewModel.getExams());
                    ((ProgressBar) view.findViewById(;
                error -> error.printStackTrace()

And if we want to generalize the solution:

((ProgressBar) view.findViewById(;//Always good to set some good feedback
        Completable.fromAction(() -> {
        }).subscribeOn( 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
                () ->
                error -> error.printStackTrace()

Method 21

Add .allowMainThreadQueries() in database file

@Database(entities = [Country::class], version = 1)
abstract class CountryDatabase: RoomDatabase() {
    abstract fun getCountryDao(): CountryDao
    companion object {
        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) =

Method 22

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

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

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment