如何在 SQLite 数据库中存储 JSON 对象

如何在 SQLite 数据库中存储 JSON 对象? 正确的方法是什么？

一个地方是 blob 类型列。如果我可以将 JSON 对象转换成字节数组并使用 Fileoutputstream

另一个想法是以字符串的形式存储在文本列中

import org.json.JSONObject;


JSONObject jsonObject;


public void createJSONObject(Fields fields) {
jsonObject = new JSONObject();


try {
jsonObject.put("storedValue1", fields.storedValue1);
jsonObject.put("storedValue2", fields.storedValue2);
jsonObject.put("storedValue3", fields.storedValue3);
jsonObject.put("storedValue4", fields.storedValue4);
jsonObject.put("storedValue5", fields.storedValue5);
jsonObject.put("storedValue6", fields.storedValue6);
} catch (JSONException e) {
e.printStackTrace();
}
}
173747 次浏览
小开

There is no data types for that.. You need to store it as VARCHAR or TEXT only.. jsonObject.toString();

小开
最佳答案

Convert JSONObject into String and save as TEXT/ VARCHAR. While retrieving the same column convert the String into JSONObject.

For example

Write into DB 

String stringToBeInserted = jsonObject.toString();
//and insert this string into DB

Read from DB 

String json = Read_column_value_logic_here
JSONObject jsonObject = new JSONObject(json);
小开

https://github.com/app-z/Json-to-SQLite

At first generate Plain Old Java Objects from JSON http://www.jsonschema2pojo.org/

Main method

void createDb(String dbName, String tableName, List dataList, Field[] fields){ ...

Fields name will create dynamically

小开

An alternative could be to use the new JSON extension for SQLite. I've only just come across this myself: https://www.sqlite.org/json1.html This would allow you to perform a certain level of querying the stored JSON. If you used VARCHAR or TEXT to store a JSON string you would have no ability to query it. This is a great article showing its usage (in python) http://charlesleifer.com/blog/using-the-sqlite-json1-and-fts5-extensions-with-python/

小开

https://github.com/requery/sqlite-android allows you to query JSON fields (and arrays in them, I've tried it and am using it). Before that I was just storing JSON strings into a TEXT column. It supports FTS3, FTS4, & JSON1

As of July 2019, it still gets version bumps every now and then, so it isn't a dead project.


提交答案