Skip to content

Instantly share code, notes, and snippets.

@LeandroSQ
Created July 15, 2021 01:05
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save LeandroSQ/2d091012387b0dfa3149bb3a425a6362 to your computer and use it in GitHub Desktop.
Save LeandroSQ/2d091012387b0dfa3149bb3a425a6362 to your computer and use it in GitHub Desktop.
Dump Room/SQLite database to JSON on Android
import android.content.Context
import android.database.Cursor
import androidx.core.database.getBlobOrNull
import androidx.core.database.getStringOrNull
import androidx.room.RoomDatabase
import org.json.JSONArray
import org.json.JSONObject
private fun listTables(db: RoomDatabase): ArrayList<String> {
val buffer = arrayListOf<String>()
val cursor = db.query("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' AND name NOT LIKE 'room_%' AND name NOT LIKE 'android_%'", arrayOf())
try {
if (cursor.moveToFirst()) {
val columnIndex = cursor.getColumnIndex("name")
do { buffer.add(cursor.getString(columnIndex)) } while (cursor.moveToNext())
}
} catch (e: Exception) {
e.printStackTrace()
} finally {
cursor.close()
}
return buffer
}
private fun stringifyTable(table: String, db: RoomDatabase): JSONArray {
val buffer = JSONArray()
val cursor = db.query("SELECT * FROM $table", arrayOf())
try {
if (cursor.moveToFirst()) {
do {
val obj = JSONObject()
cursor.columnNames.forEach { column ->
val index = cursor.getColumnIndex(column)
val type = cursor.getType(index)
when (type) {
Cursor.FIELD_TYPE_BLOB -> {
obj.put(column, cursor.getBlobOrNull(index))
}
Cursor.FIELD_TYPE_FLOAT -> {
obj.put(column, cursor.getFloat(index))
}
Cursor.FIELD_TYPE_INTEGER -> {
obj.put(column, cursor.getInt(index))
}
Cursor.FIELD_TYPE_NULL -> {
obj.put(column, null)
}
Cursor.FIELD_TYPE_STRING -> {
obj.put(column, cursor.getStringOrNull(index))
}
}
}
buffer.put(buffer.length(), obj)
} while (cursor.moveToNext())
}
} catch (e: Exception) {
e.printStackTrace()
} finally {
cursor.close()
}
return buffer
}
fun export(context: Context, db: RoomDatabase): String? {
try {
val tables = listTables(db)
val json = JSONObject()
tables.forEach { table ->
val entries = stringifyTable(table, db)
json.put(table, entries)
}
return json.toString(4)
} catch (e: Exception) {
e.printStackTrace()
}
return null
}
@LeandroSQ
Copy link
Author

Usage

Get your RoomDatabase or create one like bellow

val db = Room.databaseBuilder(context, AppDatabase::class.java, "database.db")
				.allowMainThreadQueries()
				.build()

Call the method passing a context and the db

 val json = export(this@MainActivity, db)

And you're done, the result will be a JSON string

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment