Android Room Embedded Field Not Compiling

Question

I am trying to create an embedded field. This is a simple example but I can't get this simple example to work. Eventually I need to have 3 levels of embedded items but trying to get this test case to work.

@Entity(tableName = "userItemsEntity")
@Parcelize
data class Item(
    var objecttype: String?,
    @PrimaryKey(autoGenerate = false)
    var objectid: Int?,
    var subtype: String?,
    var collid: Int?,
    @Embedded
    var name: Name?
) : Parcelable

@Parcelize
data class Name(
    var primary: Boolean? = true,
    var sortindex: Int? = null,
    var content: String? = null) : Parcelable

When I try and compile it it complains on the DAO that the updateItem()

SQL error or missing database (no such column: name) 

DAO function

@Query("UPDATE userItemsEntity SET " +
    "objecttype=:objecttype, objectid=:objectid, subtype=:subtype, collid=:collid, name=:name " +
    "WHERE objectid=:objectid")
fun updateItem(
    objecttype: String?,
    objectid: Int,
    subtype: String?,
    collid: Int?,
    name: Name?)

Answer 1

The reason is as it says there is no name column. Rather the table consists of the columns, as per the member variables of the EMBEDDED class (i.e. primary, sortindex and content).

i.e. the table create SQL is/will be :-

CREATE TABLE IF NOT EXISTS `userItemsEntity` (`objecttype` TEXT, `objectid` INTEGER, `subtype` TEXT, `collid` INTEGER, `primary` INTEGER, `sortindex` INTEGER, `content` TEXT, PRIMARY KEY(`objectid`))

Room knows to build the respective Name object from those columns when extracting rows.

So you could use :-

@Query("UPDATE userItemsEntity SET " +
        "objecttype=:objecttype, objectid=:objectid, subtype=:subtype, collid=:collid, `primary`=:primary, sortindex=:sortindex, content=:content " +
        "WHERE objectid=:objectid")
fun updateItem(
    objecttype: String?,
    objectid: Int,
    subtype: String?,
    collid: Int?,
    primary: Boolean?,
    sortindex: Int?,
    content: String?
)

• note that primary is an SQLite token and thus enclosed in grave accents to ensure that it is not treated as a token. Otherwise you would get :-

  There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (near "primary": syntax error)
  

However, as you are using a WHERE clause based upon the primary key (objectid) then the update will only apply to a single row and as such you can simply use:-

@Update
fun update(item: Item): Int

• obviously the function's name need not be update it could be any valid name that suits.
• this has the advantage of not only being simpler but of returning the number of rows updated (would be 1 if the row exists, otherwise 0)

---

Impementing a name column

If you want a name column and for that name column to hold a Name object. Then, as SQLite does not have storage/column types for objects then you would not EMBED the Name class.

You would have var name: Name? with an appropriate TypeConverter that would convert the Name object into a type that SQLite caters for :-

• TEXT (String),

• REAL (Float, Double...),

• INTEGER (Long, Int ...) or

• BLOB (ByteArray)).

• Typically String is used and typically GSON is used to convert from an object to a JOSN String.

• SQlite does have a NUMERIC type. However, Room doesn't support it's use. I believe because the other types cover all types of data and NUMERIC is a catch-all/default.

However, using a JSON representation of an object, introduces bloat and reduces the usefulness of the converted data from an SQL aspect.

For example say you had :-

@Entity(tableName = "userOtherItemsEntity")
@Parcelize
data class OtherItem (
    var objecttype: String?,
    @PrimaryKey(autoGenerate = false)
    var objectid: Int?,
    var subtype: String?,
    var collid: Int?,
    var name: OtherName?) : Parcelable
@Parcelize
data class OtherName(
    var primary: Boolean? = true,
    var sortindex: Int? = null,
    var content: String? = null) : Parcelable

Then the underlying table does have the name column. The CREATE SQL, generated by Room, would be :-

CREATE TABLE IF NOT EXISTS `userOtherItemsEntity` (`objecttype` TEXT, `objectid` INTEGER, `subtype` TEXT, `collid` INTEGER, `name` TEXT, PRIMARY KEY(`objectid`))

However, you would need TypeConverters which could be :-

@TypeConverter
fun fromOtherName(othername: OtherName ): String {
    return Gson().toJson(othername)
}
@TypeConverter
fun toOtherName(json: String): OtherName {
    return Gson().fromJson(json,OtherName::class.java)
}

• the first using Gson to convert the object to a JSON string, e.g. when inserting data
• the second converts the JSON string to an OtherName object.

using Item with Name embedded then data would be stored along the lines of :-

Whilst with the OtherItem with OtherName being converted then the data (similar data) would be along the lines of :-

• in the former the 3 Name columns would take up about (1 + 1 + 12) = 16 bytes.

• in the latter, The OtherName columns (discounting the word Other whenever used) would take uo some 55 bytes.

• the latter may require more complex and resource expensive searches if the components of the OtherName are to be included in searches.

  • e.g. @Query("SELECT * FROM userItemsEntity WHERE primary") as opposed to @Query("SELECT * FROM userOtherItemsEntity WHERE instr(name,'primary\":true') > 0")