Android Room - simple select query - Cannot access

问题:

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.

回答1:

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

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.

--EDIT--

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

回答2:

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

回答3:

For all the RxJava or RxAndroid or RxKotlin lovers out there

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

回答4:

Straight-forward code using Kotlin Coroutines

AsyncTask is really clunky. Kotlin coroutines is a cleaner alternative (essentially just your synchronous code with a couple of extra keywords).

gradle.properties:

# Coroutines opt-in
kotlin.coroutines=enable

UI thread (non-blocking):

private fun myFun() {
    launch(UI) {
        val query = async(CommonPool) { // Async stuff
            MyApp.DatabaseSetup.database.agentDao().agentsCount(email, phone, license)
        }

        val agentsCount = query.await()
        // do UI stuff
    }
}

The suspend keyword ensures async methods are only called from within async blocks, however (as noted by @Robin) this doesn't play nicely with Room annotated methods.

// Wrap API to use suspend (probably not worth it)
public suspend fun agentsCount(...): Int = agentsCountPrivate(...)

@Query("SELECT ...")
protected abstract fun agentsCountPrivate(...): Int

回答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() {
    @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()

回答6:

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.

回答7:

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

回答8:

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

回答9:

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

回答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
}

回答11:

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

回答12:

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.

回答13:

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

回答14:

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.

回答15:

you can handle all database access sequentially in another thread using HandlerThread ,check this for more info using handlerThread

basically what will you do is create a class that extends handlerThread and override onLooperPrepared as shown below

public class MyHandlerThread extends HandlerThread{


    private Handler handler;


    public SocketManager(){
        super("MyHandlerThread");
    }

    @Override
    protected void onLooperPrepared() {
        handler = new Handler(getLooper()) {
            @Override
            public void handleMessage(android.os.Message msg) {
                super.handleMessage(msg);
                //handle all your database tasks here 
                YourObject yourObject = (YourObject)msg.obj;

        }};

    }

    //send message to the background thread
    public void sendMessage(YourObject msg){

        android.os.Message msg = new android.os.Message();
        msg.obj = message;
        handler.sendMessage(msg);

    }

    public void onDestroy(){
           quit();
           interrupt();
    }



}

and then you add the following code to your activity class

MyHandlerThread myHandlerThread = new MyHandlerThread();
myHandlerThread.start();

in order to communicate with the thread you call the myHandlerThread.sendMessage() and you pass the data you want

in order to have two way communication between the main thread and background thread you can use another handler from the main thread and send messages it check this for more information two way communication