How to store a list field in Android Room-CodePudding

I have a table named orders where one of the fields, invoicesInfo is a List of another data class, so I used a TypeConverter to store it in an AppDatabase. (InvoicesInfoEntity is not annotated as Entity)

But when I started to get this kind of error com.google.gson.JsonSyntaxException: java.lang.IllegalStateException: Expected BEGIN_ARRAY but was STRING at line 1 column 2 path $. I googled the problem, but couldn't figure out what is wrong. I think the cause might be related to type conversion from list to json. My json in a column looks like [{"name": type}, {"name": type}, {"name": type}].

CodePudding user response：

List's stored as a column can have issues, I believe dependant upon the JSON/GSON library involved and com.google.gson is such a library that has issues.

A solution is to not have a column (field) in an @Entity annotated class as List<whatever> but to instead have the column as the type an object that embeds the List.

As an example suppose that your Invoice class is:-

data class Invoice(
    var name: String,
    var type: String
)

Then instead of :-

@Entity
data class Order(
    @PrimaryKey
    var orderId: Long?=null,
    var orderTimestamp: Long=System.currentTimeMillis() / 1000,
    var invoiceList: List<Invoice>
)

You can introduce another class such as :-

data class InvoiceList(
    var invoices: List<Invoice>
)

And then have the Order class as :-

@Entity
data class Order(
    @PrimaryKey
    var orderId: Long?=null,
    var orderTimestamp: Long=System.currentTimeMillis() / 1000,
    var invoiceList: InvoiceList
)

Note that the invoiceList column/field has the single object which has the list of Invoice objects embedded.

So with TypeConverters such as:-

class RoomTypeConverters{
    @TypeConverter
    fun convertInvoiceListToJSONString(invoiceList: InvoiceList): String = Gson().toJson(invoiceList)
    @TypeConverter
    fun convertJSONStringToInvoiceList(jsonString: String): InvoiceList = Gson().fromJson(jsonString,InvoiceList::class.java)

}

And with an @Database annotated class such as :-

@TypeConverters(value = [RoomTypeConverters::class])
@Database(entities = [Order::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDao(): AllDao

    companion object {
        @Volatile
        private var instance: TheDatabase? = null
        fun getInstance(context: Context): TheDatabase {
            if (instance==null) {
                instance = Room.databaseBuilder(context,TheDatabase::class.java,"the_database.db")
                    .allowMainThreadQueries()
                    .build()
            }
            return  instance as TheDatabase
        }
    }
}

Note the @TypeConverters (defining them at the @Database level gives them the greatest scope)

Now with the following @Dao annotated interface :-

@Dao
interface AllDao {
    @Insert
    fun insert(order: Order): Long
    @Query("SELECT * FROM `order`")
    fun getAllOrders(): List<Order>
}

All the code exists to be able to use and test the above. All that is missing is some code in an activity to actually add and retrieve data from the database. So :-

class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: AllDao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = TheDatabase.getInstance(this)
        dao = db.getAllDao()
        dao.insert(Order(invoiceList = InvoiceList(listOf(Invoice("INV001","TYPEA")))))
        dao.insert(Order( invoiceList = InvoiceList(
            listOf(
                Invoice("INV002","TYPEB"),
                Invoice("INV003","TYPEC"),
                Invoice("INV004","TYPED"),
                Invoice("INV005","TYPEE")
            )
        )))
        var sb = StringBuilder()


        for(o: Order in dao.getAllOrders()) {
            sb.clear()
            for (i: Invoice in o.invoiceList.invoices) {
                sb.append("\n\t").append("Name=${i.name} Type=${i.type}")
            }
            Log.d("ORDERINFO","Order ID = ${o.orderId} Timestamp=${o.orderTimestamp} Order has ${o.invoiceList.invoices.size} Invoices. If any They are:- ${sb}")
        }
    }
}

Note that for brevity the test is run on the main thread.

When Run (for the first time, the code is not intended to be rerun) then the log contains:-

D/ORDERINFO: Order ID = 1 Timestamp=1661199399 Order has 1 Invoices. If any They are:- 
        Name=INV001 Type=TYPEA
D/ORDERINFO: Order ID = 2 Timestamp=1661199400 Order has 4 Invoices. If any They are:- 
        Name=INV002 Type=TYPEB
        Name=INV003 Type=TYPEC
        Name=INV004 Type=TYPED
        Name=INV005 Type=TYPEE

This exactly as expected

The database, as view via App Inspection, is:-

Of course from a database perspective, this is not really the way that it would be managed/designed. As the data is not normalised (e.g. the TYPEA would be repeated every time it is used) and storage is wasted storing the bloat (separators/names) along with the actual data.

Rather as a List, invoices would be another table and the invoice (assuming that an invoice can only refer to a single order) would have a column that references a unique column (or for more complex scenarios multiple columns) in the Order table and thus a one-many relationship.

Again for correctness, there is a likeliehood, that the Invoice Type would be yet another table where the type column in the Invoice references the single Type.