Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 740
Nimitz14 Avatar asked Aug 28 '18 11:08

Nimitz14


People also ask

How to get number of rows in SQLite Android?

In SQLite we can use WHERE clause with COUNT() function to return the number of rows from a table based on the defined conditions.

How to get number of rows in SQLite?

The COUNT(*) function returns the number of rows in a table, including the rows including NULL and duplicates.

How do you determine the size of a room in database?

Use getDatabasePath() on Context to get a File that points to your database. Then, call length() to get its size. This will be approximate while the database is open, due to write-ahead logging, but it should be close.

What is DAO in Room database?

When you use the Room persistence library to store your app's data, you interact with the stored data by defining data access objects, or DAOs. Each DAO includes methods that offer abstract access to your app's database. At compile time, Room automatically generates implementations of the DAOs that you define.


4 Answers

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();
like image 110
Shomu Avatar answered Oct 20 '22 12:10

Shomu


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()
like image 38
Nimitz14 Avatar answered Oct 20 '22 12:10

Nimitz14


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

like image 17
M S Martens Avatar answered Oct 20 '22 13:10

M S Martens


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
}
like image 5
Bill Mote Avatar answered Oct 20 '22 12:10

Bill Mote