Android 将 SQLite 转换为 JSON
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25722585/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert SQLite to JSON
提问by Lei Leyba
Is there a way to convert sqlite to json? All other questions are parsing json and saving to sqlite. I can't seem to find any reference on this, please help me.
有没有办法将sqlite转换为json？所有其他问题都是解析 json 并保存到 sqlite。我似乎无法找到有关此的任何参考，请帮助我。
I have a sqlite db inside the app and i need it to be converted to json, upgrade db version, parse earlier converted json and add another table. Any suggestions on how should I do this?
我在应用程序中有一个 sqlite db，我需要将它转换为 json，升级 db 版本，解析之前转换的 json 并添加另一个表。关于我应该如何做到这一点的任何建议？
Thanks in advance.
提前致谢。
回答by Sagar Pilkhwal
Reference Link
参考链接
private JSONArray getResults()
{
String myPath = DB_PATH + DB_NAME;// Set path to your database
String myTable = TABLE_NAME;//Set name of your table
//or you can use `context.getDatabasePath("my_db_test.db")`
SQLiteDatabase myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
String searchQuery = "SELECT * FROM " + myTable;
Cursor cursor = myDataBase.rawQuery(searchQuery, null );
JSONArray resultSet = new JSONArray();
cursor.moveToFirst();
while (cursor.isAfterLast() == false) {
int totalColumn = cursor.getColumnCount();
JSONObject rowObject = new JSONObject();
for( int i=0 ; i< totalColumn ; i++ )
{
if( cursor.getColumnName(i) != null )
{
try
{
if( cursor.getString(i) != null )
{
Log.d("TAG_NAME", cursor.getString(i) );
rowObject.put(cursor.getColumnName(i) , cursor.getString(i) );
}
else
{
rowObject.put( cursor.getColumnName(i) , "" );
}
}
catch( Exception e )
{
Log.d("TAG_NAME", e.getMessage() );
}
}
}
resultSet.put(rowObject);
cursor.moveToNext();
}
cursor.close();
Log.d("TAG_NAME", resultSet.toString() );
return resultSet;
}
回答by Kristof Van der Jonckheyd
Quick and easy way if you don't feel like coding it:
如果您不想编码，则可以使用快速简便的方法：
- Download DB Browser for SQLite: https://sqlitebrowser.org/(yes, it's free)
- Open the SQLite DB
- Go to File > Export > Table(s) to JSON
- Voila
- 下载 SQLite 的数据库浏览器：https: //sqlitebrowser.org/（是的，它是免费的）
- 打开 SQLite 数据库
- 转到文件 > 导出 > 表到 JSON
- 瞧
Beware, for some reason it does not correctly convert NULL values. It converts this to an empty string... Besides that it works like a charm as far as I now.
请注意，由于某种原因，它无法正确转换 NULL 值。它将它转换为一个空字符串......除此之外，就我现在而言，它就像一个魅力。
回答by hoogw
sqlite, already have json1 extension, you could use:
sqlite，已经有 json1 扩展名，你可以使用：
//https://www.sqlite.org/json1.html
//https://gist.github.com/akehrer/481a38477dd0518ec0086ac66e38e0e2
var _sql = "SELECT json_group_array( json_object('id', id, 'name', name) ) AS json_result FROM (SELECT * FROM ipfs ORDER BY id); ";
sql.js does not support json1 extension,
sql.js 不支持 json1 扩展名，
my working example convert raw record to json
我的工作示例将原始记录转换为 json
function json1_extension(_records){
var _json = [];
var _columns = _records[0].columns
var _values = _records[0].values
for (var i = 0; i < _values.length; i++) {
//console.log(_values[i]);
var _row_json = {};
var _row = _values[i];
for (var k = 0; k < _row.length; k++) {
_row_json[_columns[k]] = _row[k]
}
//console.log('_row_json...',_row_json);
_json.push(_row_json)
}
return _json
}
回答by GNewc
static JSONObject cursorToJson(Cursor c) {
JSONObject retVal = new JSONObject();
for(int i=0; i<c.getColumnCount(); i++) {
String cName = c.getColumnName(i);
try {
switch (c.getType(i)) {
case Cursor.FIELD_TYPE_INTEGER:
retVal.put(cName, c.getInt(i));
break;
case Cursor.FIELD_TYPE_FLOAT:
retVal.put(cName, c.getFloat(i));
break;
case Cursor.FIELD_TYPE_STRING:
retVal.put(cName, c.getString(i));
break;
case Cursor.FIELD_TYPE_BLOB:
retVal.put(cName, DataUtils.bytesToHexString(c.getBlob(i)));
break;
}
}
catch(Exception ex) {
Log.e(TAG, "Exception converting cursor column to json field: " + cName);
}
}
return retVal;
}
回答by Mano Haran
This npm package does the job. It can convert your sqlite file to json easy. It also provides functions to manipulate the output.
这个 npm 包可以完成这项工作。它可以轻松地将您的 sqlite 文件转换为 json。它还提供操作输出的函数。
回答by Austine Iyke
To convert SQLite to JSON, you could use this python tool: https://github.com/Austyns/sqlite-to-json-python
要将 SQLite 转换为 JSON，您可以使用这个 Python 工具：https: //github.com/Austyns/sqlite-to-json-python
you could also use this online tool https://data-converters.web.app/if you don't want to code
如果您不想编码，也可以使用此在线工具https://data-converters.web.app/
