how do I store a JSON Object in an SQLite database? What is the correct way?
one place is the blob type column. if i can convert the JSON object into byte array and use Fileoutputstream
the other idea is to store in a text column as a String
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();
}
}
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.
There is no data types for that.. You need to store it as VARCHAR or TEXT only.. jsonObject.toString();
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
Source: Stackoverflow.com