Saving Custom Object as JSON in SQLite Database

Question

I'm not very familiar with data storage using json. I have a pretty complex object that is basically just a hashmap, see below:

data class Axis (var BSSID: String, var RSSI: Int)
data class Label (var x: Int, var y: Int)

data class RadioMap(var map: HashMap<Label, ArrayList<Axis>> = HashMap())

and I'm trying to store the RadioMap object in a column as TEXT in an SQLite Database. I'm able to save it, but when I try to convert it back I get this error com.google.gson.JsonSyntaxException: java.lang.IllegalStateException: Expected BEGIN_OBJECT but was STRING at line 1 column 10 path $.map.

This is my code to save and retrieve the RadioMap from the database:

fun updateRadioMap(name: String, radioMap: RadioMap) {
    val gson = Gson()
    val rmString = gson.toJson(radioMap)
    val cv = ContentValues()
    cv.put(COLUMN_RADIO_MAP_JSON, rmString)
    val db = writableDatabase
    db.update(TABLE_FLOOR_MAPS, cv, "$COLUMN_FLOOR_NAME = '$name'", null)
    db.close()
}

fun getRadioMap(name: String): RadioMap {
    val db = this.writableDatabase
    val query = "SELECT * FROM $TABLE_FLOOR_MAPS WHERE $COLUMN_FLOOR_NAME =\"$name\";"
    val cursor = db.rawQuery(query, null)

    return if (cursor != null && cursor.moveToFirst()) {
        var rmString = cursor.getString(5)
        cursor.close()
        db.close()
        if (rmString != null) {
            val gson = Gson()
            gson.fromJson<RadioMap>(rmString, RadioMap::class.java) //APP CRASHES HERE
        } else {
            RadioMap()
        }
    } else {
        cursor.close()
        db.close()
        RadioMap()
    }
}

Edit:

This is the database's onCreate method:

override fun onCreate(sqLiteDatabase: SQLiteDatabase) {
    val query = "CREATE TABLE " + TABLE_FLOOR_MAPS + "(" +
            COLUMN_ID + " INTEGER PRIMARY KEY, " +
            COLUMN_FLOOR_NAME + " TEXT, " +
            COLUMN_FLOOR_IMAGE + " BLOB, " +
            COLUMN_FLOOR_WIDTH + " REAL, " +
            COLUMN_GRID_SIZE + " REAL, " +
            COLUMN_RADIO_MAP_JSON + " TEXT " +
            ");"
    sqLiteDatabase.execSQL(query)
}

but the only relevant column is the last one, COLUMN_RADIO_MAP_JSON.

Here is an example of the RadioMap I'm trying to save, with toString() called on it:

RadioMap(map={Label(x=0, y=0)=[Axis(BSSID=78:f2:9e:57:0d:03, RSSI=-93), Axis(BSSID=78:f2:9e:57:0d:04, RSSI=-92), Axis(BSSID=78:f2:9e:57:0d:01, RSSI=-92), Axis(BSSID=78:f2:9e:57:0d:00, RSSI=-91), Axis(BSSID=78:f2:9e:57:0d:02, RSSI=-91), Axis(BSSID=54:be:f7:dc:47:ea, RSSI=-90), Axis(BSSID=10:5f:06:83:f1:c5, RSSI=-90), Axis(BSSID=2c:7e:81:c4:70:ee, RSSI=-89), Axis(BSSID=10:da:43:86:9b:51, RSSI=-89), Axis(BSSID=4e:7e:81:c4:70:ee, RSSI=-89), Axis(BSSID=7e:8f:e0:0a:b6:d5, RSSI=-89), Axis(BSSID=3e:7e:81:c4:70:ee, RSSI=-88), Axis(BSSID=1c:49:7b:94:92:e1, RSSI=-88), Axis(BSSID=84:00:2d:65:a0:ba, RSSI=-88), Axis(BSSID=50:c7:bf:89:13:f6, RSSI=-87), Axis(BSSID=3e:7e:81:c4:70:ed, RSSI=-87), Axis(BSSID=ea:5d:df:92:49:28, RSSI=-86), Axis(BSSID=da:5d:df:92:49:28, RSSI=-84), Axis(BSSID=54:be:f7:dc:47:e8, RSSI=-76), Axis(BSSID=78:f2:9e:57:0c:fa, RSSI=-73), Axis(BSSID=78:f2:9e:57:0c:f8, RSSI=-70), Axis(BSSID=44:32:c8:2c:77:b2, RSSI=-54), Axis(BSSID=46:32:c8:2c:77:b4, RSSI=-53), Axis(BSSID=d4:5d:df:31:e4:20, RSSI=-51), Axis(BSSID=d4:5d:df:31:e4:24, RSSI=-51), Axis(BSSID=d4:5d:df:31:e4:1d, RSSI=-48), Axis(BSSID=d4:5d:df:31:e4:18, RSSI=-42), Axis(BSSID=d4:5d:df:31:e4:1e, RSSI=-42), Axis(BSSID=d4:5d:df:31:e4:1a, RSSI=-41), Axis(BSSID=d4:5d:df:31:e4:1b, RSSI=-40)]})

And then here is an example of the rmString that is being stored in the database:

{"map":{"Label(x\u003d0, y\u003d0)":
[{"BSSID":"10:da:43:84:cb:75","RSSI":-92},
{"BSSID":"52:86:8c:2f:0f:7d","RSSI":-91},
{"BSSID":"78:f2:9e:57:0d:00","RSSI":-90},
{"BSSID":"78:f2:9e:57:0d:04","RSSI":-90},
{"BSSID":"2c:7e:81:c4:70:ed","RSSI":-89},
{"BSSID":"10:da:43:86:9b:51","RSSI":-88},
{"BSSID":"ae:8f:e0:0a:b6:d5","RSSI":-88},
{"BSSID":"40:b0:34:79:75:0f","RSSI":-88},
{"BSSID":"a4:2b:8c:d3:02:4b","RSSI":-88},
{"BSSID":"9e:8f:e0:0a:b6:d5","RSSI":-87},
{"BSSID":"ee:5d:df:92:49:28","RSSI":-87},
{"BSSID":"d4:5d:df:31:e4:1e","RSSI":-47},
{"BSSID":"d4:5d:df:31:e4:18","RSSI":-42}]}}

Edit 2:

I put the rmString into a JSON validator and it is valid JSON, both before I insert it into the database and after I get it out. So for some reason the gson.fromJson(rmString, RadioMap::class.java) isn't able to convert the valid json back into my object. I'm thinking it has something to do with the way the RadioMap object is set up.

Should the data class RadioMap() be done differently?

What is the type of the RadioMap column in CREATE TABLE Sqlite command? `TEXT`? — Naetmul, Mar 12 '18 at 03:19
It seems you have to use `gson.toJson(object, type)` instead of `gson.toJson(object)` https://stackoverflow.com/questions/35601355/when-to-use-google-gsons-tojson-method-which-accepts-type-as-a-parameter-publi — Naetmul, Mar 12 '18 at 03:25
@Naetmul I just tried this and I'm still getting the same error — Tommy Jackson, Mar 12 '18 at 03:32
You can't use a composite object such as `Label` as the key of a map serialized to JSON. Also, to make it clearer what's going on, you should try serializing an object to a string and immediately deserializing it back - this will make it clear whether SQLite has anything to do with the problem (in this case it doesn't). — yole, Mar 12 '18 at 07:48
@yole yep, figured it out about 15 minutes ago during a grueling trial and error process. If you wanted to post an answer I would gladly accept it. — Tommy Jackson, Mar 12 '18 at 07:51

score 1 · Accepted Answer · answered Mar 12 '18 at 07:52

1

The object graph is not deserialized correctly because you're using a composite object (Label) as a map key. There is no standard way to represent such a map as a JSON data structure.

answered Mar 12 '18 at 07:52

yole

92,896
20
260
197

Saving Custom Object as JSON in SQLite Database

1 Answers1