我该如何将我的Room数据库导出到.CSV文件并保存到设备存储器中?我已经搜索了很多,但没有合适的答案。希望有一种方法可以实现这个功能。
@Query("SELECT postId||','||content FROM postDataLocal")
fun getPostDataLocalCSV(): List<String>
@Query("SELECT groupPostIdMap||','||groupId||','||groupName FROM groupDataLocal")
fun getGroupDataLocalCSV(): List<String>
@Query("SELECT adminGroupIdMap||','||userId||','||adminName||','||avatar FROM adminDataLocal")
fun getAdminDataLocalCSV(): List<String>
以下是一个函数,其中dao
是之前实例化的一个AllDao
实例:
private fun createCSV() {
val sb = StringBuilder()
var afterFirst = false
sb.append("{POSTDATALOCAL}")
for (s in dao.getPostDataLocalCSV()) {
if(afterFirst) sb.append(",")
afterFirst = true
sb.append(s)
}
afterFirst = false
sb.append("{GROUPDATALOCAL}")
for (s in dao.getGroupDataLocalCSV()) {
if (afterFirst) sb.append(",")
afterFirst = true
sb.append(s)
}
afterFirst = false
sb.append("{ADMINDATALOCAL}")
for (s in dao.getAdminDataLocalCSV()) {
if ((afterFirst)) sb.append(",")
afterFirst = true
sb.append(s)
}
Log.d("CSV_DATA","CSV is :-\n\t$sb")
}
然后在一个活动中(其中dao已经被实例化),进行以下操作:
createCSV()
然后,当数据库包含以下数据(通过应用程序检查提取):
PostDataLocal
GroupDataLocal
AdminDataLocal
将结果写入日志(也可以写入文件而不是日志)的内容为:
D/CSV_DATA: CSV is :-
{POSTDATALOCAL}1,Post001,2,Post002,3,Post003{GROUPDATALOCAL}1,1,Group001 (Post001),1,2,Group002 (Post001),1,3,Group003 (Post001),2,4,Group004 (Post002),2,5,Group005 (Post002),3,6,Group006 (Post003){ADMINDATALOCAL}1,1,Admin001,admin001.gif,1,2,Admin002,admin002.gif,1,3,Admin003,admin003.gif,2,4,Admin004,admin004.gif,2,5,Admin005,admin005.gif,3,6,Admin006,admin006.gif,4,7,Admin007,admin007.gif,5,8,Admin008,admin008.gif,6,9,Admin009,admin009.gif,6,10,Admin010,admin010.gif
附加信息
这是一个更自动化的版本,您不需要创建@Query注释函数,而是它通过查询sqlite_master来提取表并使用table_info pragma来确定列,构建相应的SQL。
因此,它应该适用于任何Room数据库。
它还允许将数据中的逗号替换为逗号指示符,然后可以在处理CSV时替换它们。
辅助(次要/由主要调用的)函数为:
private fun getTableColumnNames(tableName: String, suppDB: SupportSQLiteDatabase): List<String> {
val rv = arrayListOf<String>()
val csr = suppDB.query("SELECT name FROM pragma_table_info('${tableName}')",null)
while (csr.moveToNext()) {
rv.add(csr.getString(0))
}
csr.close()
return rv.toList()
}
主要功能是:
private fun AutoCreateCSV(): String {
val replaceCommaInData = "{COMMA}" /* commas in the data will be replaced by this */
val rv = StringBuilder()
val sql = StringBuilder()
var afterFirstTable = false
var afterFirstColumn = false
var afterFirstRow = false
val suppDb = db.getOpenHelper().writableDatabase
var currentTableName: String = ""
val csr = 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_%')", null)
while (csr.moveToNext()) {
sql.clear()
sql.append("SELECT ")
currentTableName = csr.getString(0)
if (afterFirstTable) rv.append(",")
afterFirstTable = true
afterFirstColumn = false
rv.append("{$currentTableName},")
for (columnName in getTableColumnNames(currentTableName,suppDb)) {
if (afterFirstColumn) sql.append("||','||")
afterFirstColumn = true
sql.append("replace(`$columnName`,',','$replaceCommaInData')")
}
sql.append(" FROM `${currentTableName}`")
val csr2 = db.query(sql.toString(),null)
afterFirstRow = false
while (csr2.moveToNext()) {
if (afterFirstRow) rv.append(",")
afterFirstRow = true
rv.append(csr2.getString(0))
}
csr2.close()
}
csr.close()
return rv.toString()
}
Log.d("CSV_DATA2",AutoCreateCSV())
的结果是:D/CSV_DATA2: {PostDataLocal},1,Post001,2,Post002,3,Post003,{GroupDataLocal},1,1,Group001 (Post001),1,2,Group002 (Post001),1,3,Group003 (Post001),2,4,Group004 (Post002),2,5,Group005 (Post002),3,6,Group006 (Post003),{AdminDataLocal},1,1,Admin001,admin001.gif,1,2,Admin002,admin002.gif,1,3,Admin003,admin003.gif,2,4,Admin004,admin004.gif,2,5,Admin005,admin005.gif,3,6,Admin006,admin006.gif,4,7,Admin007,admin007.gif,5,8,Admin008,admin008.gif,6,9,Admin009,admin009.gif,6,10,Admin010,admin010.gif
如果数据中包含逗号,例如Post001被更改为值Post001, <<注意数据中的逗号>>
那么:
D/CSV_DATA2: {PostDataLocal},1,Post001{COMMA} <<note the comma in the data>>,2,Post002,3 ....
从Room中以列表形式获取所有数据,并使用此库https://github.com/doyaaaaaken/kotlin-csv
它运行良好,这是我的用法
private fun exportDatabaseToCSVFile(context: Context, list: List<AppModel>) {
val csvFile = generateFile(context, getFileName())
if (csvFile != null) {
exportDirectorsToCSVFile(csvFile, list)
} else {
//
}
}
private fun generateFile(context: Context, fileName: String): File? {
val csvFile = File(context.filesDir, fileName)
csvFile.createNewFile()
return if (csvFile.exists()) {
csvFile
} else {
null
}
}
private fun getFileName(): String = "temp.csv"
fun exportDirectorsToCSVFile(csvFile: File, list: List<AppModel>) {
csvWriter().open(csvFile, append = false) {
// Header
writeRow(listOf("row1", "row2", "row3"))
list.forEachIndexed { index, appModel ->
writeRow(listOf(getRow1, getRow2, getRow3))
}
shareCsvFile(csvFile)
}
}
private fun shareCsvFile(csvFile: File) {
// share your file, don't forget adding provider in your Manifest
}