Exporting Room Database to csv file in android

Question

There are many tutorials available for exporting SQLite database to csv file but not enough stuff for exporting from room database.

Using sqlite export reference Exporting SQLite Database to csv file in android parsing each column of row manually for room. Following is my code:

     @Dao
     interface CategoryDao {
         @Query("SELECT * FROM Category")
         fun getAllCategory(): List<Category>
     }

//   Export csv logic

      val categoryList = categoryDao.getAllCategory()
      val csvWrite = CSVWriter(FileWriter(file))

      for (item in categoryList) {
         val arrStr = arrayOf<String>(item.categoryId, item.categoryName)
         csvWrite.writeNext(arrStr)
      }

Is there any other way to export csv. Even in room not getting columns name of table pragmatically so not able to create dynamically common logic for all table.

Hanshaw

room database is sqlite database - there is no difference – Ra

You could use Jackson CSV library to map Room classes into files – Glutelin

Answer 1

Try this
get Column data from Cursor

@Query("SELECT * FROM Category")
Cursor getAllCategory();

Outsell

Answer 2

To Export the Room Database data into mydb.sqlite file and store into External storage, follow the steps.

  fun exportDatabase(){
      val sd = Environment.getExternalStorageDirectory()

      // Get the Room database storage path using SupportSQLiteOpenHelper 
      AppDatabase.getDatabase(applicationContext)!!.openHelper.writableDatabase.path

            if (sd.canWrite()) {
                val currentDBPath = AppDatabase.getDatabase(applicationContext)!!.openHelper.writableDatabase.path
                val backupDBPath = "mydb.sqlite"      //you can modify the file type you need to export
                val currentDB = File(currentDBPath)
                val backupDB = File(sd, backupDBPath)
                if (currentDB.exists()) {
                    try {
                        val src = FileInputStream(currentDB).channel
                        val dst = FileOutputStream(backupDB).channel
                        dst.transferFrom(src, 0, src.size())
                        src.close()
                        dst.close()
                    } catch (e: IOException) {
                        e.printStackTrace()
                    }
                }
            }
       }

Medeah

Answer 3

There are two ways i have tried to iterate over large Room database:

1- Get Cursor and iterate over that:

import android.database.Cursor
...
@Query("SELECT * FROM Category")
fun getAllCategory(): Cursor<Category>
...
val success = cursor.moveToFirst()
if (success) {
     while (!cursor.isAfterLast) {
         // Process items
         cursor.moveToNext()
     }
} else {
    // Empty
}
cursor.close()

2- Use PagedList to get pageSize amount of items at once and process. Then query another page and process:

@Query("SELECT * FROM Category")
fun getAllCategory(): DataSource.Factory<Int, Category>

// Here i will return Flowable. You can return LiveData with 'LivePagedListBuilder'
fun getCategories(pageSize: Int): Flowable<PagedList<Category>> {
        val config = PagedList.Config.Builder()
                .setPageSize(pageSize)
                .setPrefetchDistance(pageSize / 4)
                .setEnablePlaceholders(true)
                .setInitialLoadSizeHint(pageSize)
                .build()
        return RxPagedListBuilder(categoryDao.getAllCategory(), config)
                .buildFlowable(BackpressureStrategy.BUFFER)
}

Now above getCategories() function will return pagedList inside Flowable or LiveData. Since we have set setEnablePlaceholders(true), pagedList.size will show the whole size even if it is not in memory. So, if pageSize is 50 and all data size is 1000, pagedList.size will return 1000, but most of them will be null. To query next page and process:

// Callback is triggered when next page is loaded
pagedList.addWeakCallback(pagedList.snapshot(), object : PagedList.Callback() {
    override fun onChanged(position: Int, count: Int) {
        for (index in position until (position + count)) {
            if (index == (position + count - 1)) {
                if (index < (pagedList.size - 1)) 
                    pagedList.loadAround(index + 1)
                else{ 
                    // Last item is processed.
                }
            } else
                processCurrentValue(index, pagedList[index]!!)
        }
    }
    override fun onInserted(position: Int, count: Int) {
        // You better not change database while iterating over it 
    }    
    override fun onRemoved(position: Int, count: Int) {
        // You better not change database while iterating over it
    }
})

// Start to iterate and query next page when item is null.
for (index in 0 until pagedList.size) {
     if (pagedList[index] != null) {
            processCurrentValue(index, pagedList[index]!!)
     } else {
            // Query next page
            pagedList.loadAround(index)
            break
     }
}

---

Conclusion: In PagedList approach, you can get thousands of rows at once and process, while in Cursor approach you iterate row by row. I found PagedList unstable when pageSize > 3000. It does not return the page sometimes. So i used Cursor. It takes roughly about 5 minutes to iterate over (and process) 900k rows on both approaches on Android 8 phone.

Farreaching

Question is about exporting database and not about HOW TO USE PAGING LIBRARY! – Vikiviking

It is about exorting db by iterating overvrows of tables. Because you might one to transform data while exporting. Exporting does not only mean dumping db and creating file from it with everything inside – Farreaching