How to get the row count of Room database in android?

Question

I'm following the practice of having a Repository and a Dao and so on. I was trying to get the row count in my database repository by having a function

int getNumFiles() {
    List<AFile> lst = files.getValue();  // files is of type LiveData<List<AFile>> files;
    if (lst == null) {
        return 0;
    } else {
        return lst.size();
    }
}

But lst always evaluates to null. I guess it has something to do with me not being allowed to query the DB from the UI thread or something? Should I implement it like one implements adding or deleting an element? In other words have a function in the Dao which is called via an AsyncTask in the Database repository? I'm confused about how to do this very simple thing.

There is this answer which shows what one would write in the Dao to find out the number of rows, but it does not explain how the repository should call this.

Answer 1

Room database Count Table Row

@Query("SELECT COUNT(column_name) FROM tableName")
LiveData<Integer> getRowCount(); //with LiveData

@Query("SELECT COUNT(column_name) FROM tableName")
int getRowCount();

Answer 2

I ended up doing it like this (using a new thread for the query).

In the Dao

@Query("SELECT COUNT(id) FROM table")
int getCount();

In the repository

int getNumFiles() {
    return afileDao.getCount();
}

Where I need it

    final AtomicInteger fcount = new AtomicInteger();
    Thread t = new Thread(new Runnable() {
        @Override
        public void run() {
            int num = f_repo.getNumFiles();
            fcount.set(num);
        }
    });
    t.setPriority(10);
    t.start();
    t.join();
    // use as fcount.get()

Answer 3

Let's see if this works. I may be off base, but I have struggled with this same issue trying to learn Room databases and most recently trying to get the row count of the table I was working with.

(This is my first post, so I apologize for the shortness of it and welcome constructive thought to make it better.)

Starting with the Dao, I declared the method with the @Query() annotation. This is the point where we will define the query we will be using to retrieve the desired information.

@Query("SELECT COUNT(*) FROM word_table")
LiveData<Integer> getCount();

Second, carry this through the Repository. The Repository will be calling our Dao class to retrieve information and essentially pass the query.

public LiveData<Integer> getCount() {
    return mWordDao.getCount();
}

Third, bring it into the ViewModel. The ViewModel will be called by the (in this case) MainActivity and in turn will call the getCount() method from the Repository and back down the chain.

// count
public LiveData<Integer> getCount() { return mRepository.getCount(); }

Finally, create the observable in the MainActivity, seeing as I encased the value with a LiveData<> wrapper.

    mWordViewModel.getCount().observe(this, new Observer<Integer>() {
        @Override
        public void onChanged(@Nullable Integer integer) {
            word_count.setText(String.valueOf(integer));
        }
    });

I know that this is simplistic, short and leaves out a lot of detail, but after going over the Room Database code a large number of times, this worked for me to be able to display the number of rows in the database table I was referencing. And it seems to be the way that the Room databases are intended to work.

(The code I was using as a base for branching out into retrieving the row count was grabbed from the codebase labs provided by Google for Room Databases part I.)

You can reach them with the following link and click on the one for Room Databases - Part 1: Codelabs for Android Developers

Scott

Answer 4

I didn't need LiveData and I used a Coroutine:

// DAO
@Query("SELECT COUNT(*) FROM some_table")
suspend fun getCount(): Int

// REPOSITORY
fun getCount(): Int = runBlocking {
    val count = async {
        dao.getCount()
    }
    count.start()
    count.await()
}

// VIEWMODEL
when (val count = repository.getCount()) {
  // do stuff with count
}

Answer 5

I think a nicer way to do miniature things in the background thread is to create a Handler & HandlerThread and use them to perform one liner tasks.

//The handlers to perform tasks on the background threads
override lateinit var mHandler: Handler
override lateinit var mHandlerThread: HandlerThread

override fun start() {
    //Instantiate the handlerThread
    mHandlerThread = HandlerThread(MainPresenter::class.java.simpleName)

    //A call to the start method has to be executed manually
    mHandlerThread.start()
    mHandler = Handler(mHandlerThread.looper)
}

And wherever you want to call something in the background thread, simply :

mHandler.post { getTableCountInBg() }

I was in the midst of typing what @Sameer Donga linked to, but refer that instead. Call it like above.

P.S. Ignore the override annotations. They're there because I enforce it on a presenter.

Answer 6

@Query("SELECT  COUNT(DISTINCT column_name) FROM table)
LiveData<Integer> getTotalNumberOfRows();

Add DISTINCT as an argument to the COUNT function.

Answer 7

@Query("SELECT COUNT(column_name) FROM table)

LiveData getTotalNumberOfColumns();

or do this if you don't want multiple occurences of a value in the column

@Query("SELECT COUNT(DISTINCT column_name) FROM table)

LiveData getTotalNumberOfColumns();