How to give column name which is reserved keyword in Kotlin data class (Room entity)?-CodePudding

I have an Entity data class with a variable name 'abstract' . As it is a reserved keyword its showing error while saving to the database. How can I save such column names that are reserved keywords?

Below given is the entity class

package com.nyt.model

import androidx.room.ColumnInfo
import androidx.room.Entity
import androidx.room.PrimaryKey
import java.io.Serializable

@Entity
data class Article (
    val `abstract`: String?="",
    val adx_keywords: String?="",
    val asset_id: Long?=0,
    val byline: String?="",
    val column: String?="",
    val des_facet: List<String>?= emptyList(),
    val eta_id: Int?=0,
    val geo_facet: List<String>?= emptyList(),
    @PrimaryKey
    val id: Long?=0,
    val media: List<Media>?= emptyList(),
    val nytdsection: String?="",
    val org_facet: List<String>?= emptyList(),
    val per_facet: List<String>?= emptyList(),
    val published_date: String?="",
    val section: String?="",
    val source: String?="",
    val subsection: String?="",
    val title: String?="",
    val type: String?="",
    val updated: String?="",
    val uri: String?="",
    val url: String?=""
):Serializable

CodePudding user response：

You can use back ticks to escape the special meaning of various symbols.

For example, you can create all these fancy names:

val `while` = 5

class `class name with spaces` {

}

fun `do fun while in class`() {

}

In your case, you can do it like this:

data class MyEntity(
    val `abstract`: ItsDataType
)

CodePudding user response：

Although you can place back ticks around the abstract this will then introduce issues. It is strongly suggested that you use an alternative e.g. abstrct or a synonym.

You could always have the column name itself as abstract by using the @ColumnInfo annotation e.g. :-

@ColumnInfo(name = "abstract")
val abstrct: String?="",

From a little testing if abstract (enclosed in back ticks) is used then the column itself does not get generated in the underlying generated code and at least part of why using the back ticks is not the solution.
The ongoing issue is perhaps that room wraps column names in back ticks so the back ticks appear twice and are thus ignored and then the abstract keyword comes into play and is thus then ignored as being a column. The signature of the Article class is then not what Room expects so expects getters and setters.
- Room wraps column names in back ticks so that the column names do not clash with SQLite keywords.
with abstrct enclosed in back ticks then Room creates the following SQL (only part of the QL shown) to create the Article table CREATE TABLE IF NOT EXISTS Article (abstrctTEXT,adx_keywordsTEXT,asset_id INTEGER.
However with abstract enclosed in back ticks then the SQL is CREATE TABLE IF NOT EXISTS Article (adx_keywordsTEXT,asset_id INTEGER, (i.e. the abstract column has been omitted).

Changing to use abstrct then highlights other issues, all basically the same. That is, you cannot have columns (vals/vars in an Entity) that are directly List/Array etc.

As such you are going to have ongoing issues with des_facet, geo_facet, org_facet, per_facet and media vals.

If you want to store a List/Array etc then you can utilise a class that contains the List/Array etc.

So if you had for example:-

data class ListStringHolder (
    val listString: List<String> = emptyList()
)

then you could have :-

val des_facet: ListStringHolder = ListStringHolder()

However, you then need TypeConverters to convert the List/Array into a value that can be stored in the database

limited at the SQLite level to INTEGER (e.g. Long,Int,Byte,Short and Boolean), REAL (e.g. Float/Double), TEXT (String) or BLOB (ByteArray)
- SQlite does have a NUMERIC catch-all type but Room does not support it's use.

thus you need:-

a ListStringHolder into a type that can be stored by Room,
from the stored value to a ListStringHolder object

For objects typically a String which could be a JSON representation of the ListStringHolder object.

e.g. you could have a class named Converters :-

@TypeConverter
fun fromListStringHolder(lsh: ListStringHolder): String {
    return Gson().toJson(lsh)
}

@TypeConverter
fun toListStringHolder(lsh: String): ListStringHolder {
    return Gson().fromJson(lsh,ListStringHolder::class.java)
}

You need to define the TypeConverters to Room using an @TypeConverters (note plural) annotation. You can use the @TypeConverters annotation at various levels (this sets the scope of the converters). The most convenient level is at the @Database level (scope is the entire database) so you would have something along the lines of:-

@Database(entities = [Article::class], version = DB_Constants.DB_VERSION)
@TypeConverters(Converters::class)
....

So using :-

@Entity
data class Article (
    val abstrct: String?="",
    val adx_keywords: String?="",
    val asset_id: Long?=0,
    val byline: String?="",
    val column: String?="",
    //val des_facet: List<String>?= emptyList(),
    val des_facet: ListStringHolder = ListStringHolder(), //<<<<<
    val eta_id: Int?=0,
    //val geo_facet: List<String>?= emptyList(),
    val geo_facet: ListStringHolder = ListStringHolder(), //<<<<<
    @PrimaryKey
    val id: Long?=0,
    //val media: List<Media>?= emptyList(),
    val media: ListMediaHolder = ListMediaHolder(), //<<<<<
    val nytdsection: String?="",
    //val org_facet: List<String>?= emptyList(),
    val org_facet: ListStringHolder = ListStringHolder(), //<<<<<
    //val per_facet: List<String>?= emptyList(),
    val per_facet: ListStringHolder = ListStringHolder(), //<<<<<
    val published_date: String?="",
    val section: String?="",
    val source: String?="",
    val subsection: String?="",
    val title: String?="",
    val type: String?="",
    val updated: String?="",
    val uri: String?="",
    val url: String?=""
):Serializable

ListMediaHolder being treated in a similar way, with TypeConverters

Working Example

Then with the above and ArticleDao as :-

@Dao
interface ArticleDao {

    @Insert
    fun insert(article: Article): Long
    @Query("SELECT * FROM article")
    fun getAllArticles(): List<Article>
}

And the following in an activity (noting that for convenience and brevity .allowMainThreadQueries has been used) :-

    articleDao = db.getArticleDao()

    var lsh1 = ListStringHolder(listOf<String>("A"))
    val lsh2 = ListStringHolder(listOf("D","E","F"))

    articleDao.insert(
        Article(abstrct = "abs1",
            adx_keywords = "adxkey1",
            byline = "byline1",
            column = "column1",
            des_facet = lsh1,
            geo_facet = lsh1,id = null, media = ListMediaHolder(emptyList()), org_facet = lsh1,per_facet = lsh1))
    articleDao.insert(
        Article(abstrct = "abs2",
            adx_keywords = "adxkey2",
            byline = "byline2",
            column = "column2",
            des_facet = lsh2,
            geo_facet = lsh1,id = null, media = ListMediaHolder(emptyList()), org_facet = lsh2,per_facet = lsh1))

    for(a: Article in articleDao.getAllArticles()) {
        Log.d("ARTICLEINFO","Article abstract = ${a.abstrct} number of des_facets = ${a.des_facet.listString.size} they are:-")
        for(s: String in a.des_facet.listString) {
            Log.d("ARTICLEINFO","\t$s")
        }

When run the Log contains :-

D/ARTICLEINFO: Article abstract = abs1 number of des_facets = 1 they are:-
D/ARTICLEINFO:  A
D/ARTICLEINFO: Article abstract = abs2 number of des_facets = 3 they are:-
D/ARTICLEINFO:  D
D/ARTICLEINFO:  E
D/ARTICLEINFO:  F

The database itself contains the following data (extracted using AppInspection aka Database Inspector):-