A Beginner in Android struggling to get around this limitation with Room Database. I'm working with two tables, Clothing, and Outfits. A user can create an Outfit by inserting the values presented to them. Then on a separate page, a user can insert an Outfit with the previous clothing they already created in the Clothing.kt. For the sake of the application, the relationship will only be one-to-many, meaning I only need to create one Outfit using many Clothing Items. Here is my code so far:
Clothing.kt
@Parcelize
@Entity(foreignKeys = [
ForeignKey(entity = Outfit::class,
parentColumns = ["id"],
childColumns = ["outfitRefFK"]
)
]
)
data class Clothing (
//Sets all attributes and primary key
@PrimaryKey(autoGenerate = true) val id: Int,
val type: String,
val color: String,
val style: String,
val description: String,
val dateAdded: Date = Date(),
val brand: String,
val theme: String,
val image: String,
@Nullable val outfitRefFK: Int
): Parcelable
Outfit.kt
@Parcelize
@Entity
data class Outfit (
@PrimaryKey(autoGenerate = true) val id: Int,
val outfitName: String,
@Ignore
val ClothingItems: List<Clothing>
):Parcelable
I've looked at a number of Android Developer Documentations, and they all mention how to Query the Outfits with the same Clothing List, but NOT how to Insert a New outfit with a List objects.
To my knowledge, SQLite cannot handle Lists. So, one approach I tried was to use a Type Converter, however, I struggled to implement this into my code, mostly because I'm new to GSON.
An example, from Google Android Docs that I have been trying to implement, is not quite making sense to me but it seems that it's possible to insert a list of objects following POJO:
Google Insert Example:
@Dao
public interface MusicDao {
@Insert(onConflict = OnConflictStrategy.REPLACE)
public fun insertSongs(varargs songs: Song)
@Insert
public fun insertBoth(song1: Song, song2: Song)
@Insert
public fun insertAlbumWithSongs(album: Album, songs: List<Song>);
}
I'm assuming my goal is to replicate this with a similar approach, creating an Outfit from List. From what I can tell, Google Docs uses 3 Tables (Music, Album, and Song), so I've been struggling with where I can modify my DB. Should I create a third Table? Has anyone come to a similar conclusion with Kotlin? If any of you have solved this or come close, any suggestions are much appreciated.
For other sources here are my Dao's for the Tables, there not finished yet, as I couldn't figure out a way to store the Clothing Items.
Clothing.Dao
@Dao
interface ClothingDao {
//Ignores when the exact same data is put in
@Insert(onConflict = OnConflictStrategy.IGNORE)
suspend fun addClothing(clothing: Clothing)
@Update
suspend fun updateClothing(clothing: Clothing)
@Delete
suspend fun deleteClothing(clothing: Clothing)
@Query("DELETE FROM Clothing")
suspend fun deleteAllClothing()
@Query("SELECT * FROM Clothing ORDER BY id ASC")
fun readAllData(): LiveData<List<Clothing>>
@Query("SELECT * FROM Clothing WHERE type='Top' ORDER BY id ASC")
fun selectClothingTops(): LiveData<List<Clothing>>
//Called in ListFragment Searchbar. Queries Clothing Type or Clothing Color.
@Query("SELECT * FROM Clothing WHERE type LIKE :searchQuery OR color LIKE :searchQuery")
fun searchDatabase(searchQuery: String): LiveData<List<Clothing>>
}
OutfitDao.kt
@Dao
interface OutfitDao {
// Grabs data from Outfit Table, necessary for each other Query to read
// from in the Outfit Repository class
@Insert(onConflict = OnConflictStrategy.REPLACE)
suspend fun addOutfit(outfit: Outfit)
@Query("SELECT * FROM Outfit ORDER BY id ASC")
fun readAllData(): LiveData<List<Outfit>>
}
CodePudding user response：
To my knowledge, SQLite cannot handle Lists. So, one approach I tried was to use a Type Converter, however, I struggled to implement this into my code, mostly because I'm new to GSON.
1). Add the Gson library to your project e.g. in your build.gradle (module) :-
implementation 'com.google.code.gson:gson:2.9.0'
2). Add a data class e.g ClothingList :-
data class ClothingList(
val clothingList: List<Clothing>
)
3). Amend the Outfit class to use the ClothingList as opposed to List and also remove the @Ignore annotation e.g. :-
@Entity
data class Outfit (
@PrimaryKey(autoGenerate = true) val id: Int, /* more correct to use Long */
val outfitName: String,
//@Ignore
val ClothingItems: ClothingList
)
- autogenerated columns are more correctly Long's rather than Int's as in theory the stored value can be up to 64bits signed.
4). Add a new class for the TypeConverters e.g. MyTypeConverters :-
class MyTypeConverters {
@TypeConverter
fun fromDateToLong(date: Date): Long {
return date.time
}
@TypeConverter
fun fromLongToDate(date: Long): Date {
return Date(date)
}
@TypeConverter
fun fromClothingToJSON(clothinglist: ClothingList): String {
return Gson().toJson(clothinglist)
}
@TypeConverter
fun fromJSONToClothing(json: String): ClothingList {
return Gson().fromJson(json,ClothingList::class.java)
}
}
5). Amend the @Database annotated class (has the highest scope) to have the @TypeConverters annotation e.g.
@TypeConverters(value = [MyTypeConverters::class])
@Database(entities = [Clothing::class,Outfit::class], version = 1, exportSchema = false)
abstract class TheDatabase: RoomDatabase() {
....
}
You can them have a list of clothing within an outfit. However, that is not really the ideal way, from a relational database aspect as it will introduce complexities due to the whole list of clothes being a single stored value.
Your second attempt (what appears to be) ties an item of clothing to just one outfit, So your "blue jeans" if used in a number of outfits would have to be repeated.
Suggested Solution
I'd suggest that the better solution would be for a many-many relationship, so an outfit can use any number of clothing items and a clothing item can be used by any number of outfits. Thus your "blue jeans" would be a single row.
To utilise a many-many relationship you have an intermediate table that is a cross reference between the outfit and the item of clothing. i.e. a column for the id of the outfit and a column for the id of the item of clothing. There is then no need for Type Converters or storing Lists
Working Example
Consider the following working example:-
The OutFit class
@Entity
data class Outfit(
@PrimaryKey
@ColumnInfo(name = "outfitId")
val id: Long?=null,
val outfitName: String
)
And the Clothing Class
@Entity
data class Clothing (
//Sets all attributes and primary key
@PrimaryKey/*(autoGenerate = true) inefficient not needed*/
@ColumnInfo(name = "clothingId") /* suggest to have unique column names */
val id: Long?=null, /* Long rather than Int */
val type: String,
val color: String,
val style: String,
val description: String,
val dateAdded: Date = Date(),
val brand: String,
val theme: String,
val image: String
)
The intermediate (mapping, associative, reference and other names) table for a many-many relationship
@Entity(
primaryKeys = ["outfitIdRef","clothingIdRef"],
foreignKeys = [
ForeignKey(
entity = Outfit::class,
parentColumns = ["outfitId"],
childColumns = ["outfitIdRef"],
onUpdate = ForeignKey.CASCADE,
onDelete = ForeignKey.CASCADE
),
ForeignKey(
entity = Clothing::class,
parentColumns = ["clothingId"],
childColumns = ["clothingIdRef"],
onUpdate = ForeignKey.CASCADE,
onDelete = ForeignKey.CASCADE
)
]
)
data class OutFitClothingMappingTable (
val outfitIdRef: Long,
@ColumnInfo(index = true)
val clothingIdRef: Long
)
A POJO class OutFitWithClothingList for getting an Outfit with it's related List of clothing.
data class OutFitWithClothingList(
@Embedded
val outfit: Outfit,
@Relation(
entity = Clothing::class,
parentColumn = "outfitId",
entityColumn = "clothingId",
associateBy = Junction(
value = OutFitClothingMappingTable::class,
parentColumn = "outfitIdRef",
entityColumn = "clothingIdRef"
)
)
val clothingList: List<Clothing>
)
A POJO the opposite way around a Clothing Item with the Outfits that use it
data class ClothingWithOutFitsList(
@Embedded
val clothing: Clothing,
@Relation(
entity = Outfit::class,
parentColumn = "clothingId",
entityColumn = "outfitId",
associateBy = Junction(
value = OutFitClothingMappingTable::class,
parentColumn = "clothingIdRef",
entityColumn = "outfitIdRef"
)
)
val outfitList: List<Outfit>
)
A class with TypeConverters for the Date (stores date as a integer i.e. Long) :-
class TheTypeConverters {
@TypeConverter
fun fromDateToLong(date: Date): Long {
return date.time
}
@TypeConverter
fun fromLongToDate(date: Long): Date {
return Date(date)
}
}
A single (for brevity/convenience) @Dao annotated class Alldao including Queries to get all the Outfits with their List of clothing and also to get all the Clothing Items with the Outfits used, and of course inserts to insert into the tables.
@Dao
interface AllDao {
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun addOutfit(outfit: Outfit): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun addClothing(clothing: Clothing): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun addOutfitClothingMap(outFitClothingMappingTable: OutFitClothingMappingTable): Long /* value not of much use other than if 1 or greater insert, if -1 not inserted */
@Query("SELECT * FROM clothing")
fun getAllClothing(): List<Clothing>
@Query("SELECT * FROM outfit")
fun getAllOutfits(): List<Outfit>
@Query("SELECT * FROM outfit")
fun getAllOutfitsWithClothingList(): List<OutFitWithClothingList>
@Query("SELECT * FROM clothing")
fun getAllClothingWithOutfitList(): List<ClothingWithOutFitsList>
}
An @Database annotated class (note for brevity and convenience uses .allowMainThreadQuesries)
@TypeConverters(value = [TheTypeConverters::class])
@Database(entities = [Outfit::class,Clothing::class,OutFitClothingMappingTable::class], version = 1, exportSchema = false)
abstract class TheDatabase: RoomDatabase() {
abstract fun getAllDao(): AllDao
companion object {
@Volatile
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
}
}
}
- TypeConverters defined at the database level (highest scope)
Finally activity code to demonstrate inserting Outfits, Clothing and mappings and the extraction of All Outfits with the list if clothing and All Clothing with the List of Outfits that use the item of clothing.
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()
val outfit1 = dao.addOutfit(Outfit(outfitName = "Outfit1"))
val outfit2 = dao.addOutfit(Outfit(outfitName = "Outfit2"))
val clothing1 = dao.addClothing(Clothing(type = "Top", color = "Red", description = "Singlet",brand = "Fred's Clothing Inc", theme = "whatever", image = "image001", style = "style1"))
val clothing2 = dao.addClothing(Clothing(type = "Bottom", color = "Blue", description = "Shorts",brand = "AC", theme = "whatever", image = "image002", style = "style2"))
val clothing3 = dao.addClothing(Clothing(type = "Bottom", color = "White", description = "Skirt",brand = "AC", theme = "whatever", image = "image003", style = "style3"))
val clothing4 = dao.addClothing(Clothing(type = "Hat", color = "Brown", description = "Hat with feather",brand = "AC", theme = "whatever", image = "image003", style = "style4"))
// etc
dao.addOutfitClothingMap(OutFitClothingMappingTable(outfit1,clothing1))
dao.addOutfitClothingMap(OutFitClothingMappingTable(outfit1,clothing2))
dao.addOutfitClothingMap(OutFitClothingMappingTable(outfit2,clothing1))
dao.addOutfitClothingMap(OutFitClothingMappingTable(outfit2,clothing3))
dao.addOutfitClothingMap(OutFitClothingMappingTable(outfit2,clothing4))
for (owc in dao.getAllOutfitsWithClothingList()) {
Log.d("DBINFO","Outfit is ${owc.outfit.outfitName} ID is ${owc.outfit.id}, it has ${owc.clothingList.size} Items of Clothing, they are:-")
for (c in owc.clothingList) {
Log.d("DBINFO","\tClothing Item desc is ${c.description} Date is ${c.dateAdded} Brand is ${c.brand} type is ${c.type} etc")
}
}
for (cwo in dao.getAllClothingWithOutfitList()) {
Log.d("DBINFO","Clothing is ${cwo.clothing.description} color is ${cwo.clothing.color} it is used by ${cwo.outfitList.size } Outfits, they are:-")
for(o in cwo.outfitList) {
Log.d("DBINFO","\tOutfit is ${o.outfitName} it's ID is ${o.id}")
}
}
}
}
Result (output to the log)
2022-05-01 08:55:15.287 D/DBINFO: Outfit is Outfit1 ID is 1, it has 2 Items of Clothing, they are:-
2022-05-01 08:55:15.294 D/DBINFO: Clothing Item desc is Singlet Date is Sun May 01 08:55:15 GMT 10:00 2022 Brand is Fred's Clothing Inc type is Top etc
2022-05-01 08:55:15.294 D/DBINFO: Clothing Item desc is Shorts Date is Sun May 01 08:55:15 GMT 10:00 2022 Brand is AC type is Bottom etc
2022-05-01 08:55:15.294 D/DBINFO: Outfit is Outfit2 ID is 2, it has 3 Items of Clothing, they are:-
2022-05-01 08:55:15.294 D/DBINFO: Clothing Item desc is Singlet Date is Sun May 01 08:55:15 GMT 10:00 2022 Brand is Fred's Clothing Inc type is Top etc
2022-05-01 08:55:15.294 D/DBINFO: Clothing Item desc is Skirt Date is Sun May 01 08:55:15 GMT 10:00 2022 Brand is AC type is Bottom etc
2022-05-01 08:55:15.295 D/DBINFO: Clothing Item desc is Hat with feather Date is Sun May 01 08:55:15 GMT 10:00 2022 Brand is AC type is Hat etc
2022-05-01 08:55:15.298 D/DBINFO: Clothing is Singlet color is Red it is used by 2 Outfits, they are:-
2022-05-01 08:55:15.298 D/DBINFO: Outfit is Outfit1 it's ID is 1
2022-05-01 08:55:15.298 D/DBINFO: Outfit is Outfit2 it's ID is 2
2022-05-01 08:55:15.298 D/DBINFO: Clothing is Shorts color is Blue it is used by 1 Outfits, they are:-
2022-05-01 08:55:15.298 D/DBINFO: Outfit is Outfit1 it's ID is 1
2022-05-01 08:55:15.298 D/DBINFO: Clothing is Skirt color is White it is used by 1 Outfits, they are:-
2022-05-01 08:55:15.298 D/DBINFO: Outfit is Outfit2 it's ID is 2
2022-05-01 08:55:15.298 D/DBINFO: Clothing is Hat with feather color is Brown it is used by 1 Outfits, they are:-
2022-05-01 08:55:15.298 D/DBINFO: Outfit is Outfit2 it's ID is 2
Via AppInspection i.e. the data stored in the database
and the mapping table
Additional regrading @Relation
When you use @Relation, ALL the children are retrieved irrespective for the objects and they will be in whatever order suits the query optimizer. This can be frustrating/confusing if you have specified ORDER or WHERE clauses.
Here's some example queries that demonstrate
a) your query which is fine if say when creating an outfit you only want to select Tops
b) a query where you want only to find Outfits that have Tops and list all clothes (via @Relation) -c) a query where you want to find Outfits that have tops but to then only list the clothing that are Tops (demonstrates how to get around the @Relation get all children and get only some children)
No changes other than additional @Dao functions and the activity code to demo them
So the additional @Dao functions are
@Transaction
@Query("SELECT * FROM outfit "
" JOIN outfitclothingmappingtable ON outfit.outfitId = outfitclothingmappingtable.outfitIdRef "
" JOIN clothing ON clothingIdRef = clothingId "
"WHERE clothing.type LIKE :searchQuery OR color LIKE :searchQuery")
fun getOutfitsWithClothingSearchingClothing(searchQuery: String): List<OutFitWithClothingList>
/* NOTE */
/* As this uses @Relation the outfits returned will contain ALL related clothing items */
/* Things can get a little complicated though due to @Relation */
/* Say you wanted a List of the Outfits that include specific clothing and to only list those clothing items not ALL */
/* Then 2 queries and a final function that invokes the 2 queries is easiest */
/* However the first query (the actual SQL) has all the data but would need a loop to select apply the clothing to the outfits */
@Query("SELECT * FROM outfit "
" JOIN outfitclothingmappingtable ON outfit.outfitId = outfitclothingmappingtable.outfitIdRef "
" JOIN clothing ON clothingIdRef = clothingId "
"WHERE clothing.type LIKE :searchQuery OR color LIKE :searchQuery")
fun getOutfitsOnlySearchingClothing(searchQuery: String): List<Outfit>
@Query("SELECT * FROM outfitclothingmappingtable JOIN clothing ON clothingIdRef = clothingId WHERE (type LIKE :searchQuery OR color LIKE :searchQuery) AND outfitIdRef=:outfitId")
fun getClothingThatMatchesSearchForAnOutfit(searchQuery: String, outfitId: Long): List<Clothing>
@Transaction
@Query("")
fun getOutfitsWithOnlyClothingsThatMatchSearch(searchQuery: String): List<OutFitWithClothingList> {
val rv = mutableListOf<OutFitWithClothingList>()
val outfits = getOutfitsOnlySearchingClothing(searchQuery)
for (o in outfits) {
rv.addAll(listOf(OutFitWithClothingList(o,getClothingThatMatchesSearchForAnOutfit(searchQuery,o.id!!))))
}
return rv
}
- note that tablename.column has been used but not universally, the tablename.column is only required if the column names are ambiguous (hence why the @ColumnInfo(name = ??) was used for the id columns so they are not ambiguos.
- if the column names are ambiguous and you use tablename.column name, the columns names extracted will have the same name and Room will select only the last so outfit.id would be the same value as clothing.id, again avoided by using unique column names.
- So the tablename.column has only been used to show it's use.
The activity, to demonstrate, could then include :-
/* Your Query */
for (c in dao.searchDatabase("Top")) {
Log.d("SRCHINFO1","Clothing is ${c.description} ....")
}
/* @Relation Limited Search complete outfit (all clothing) that has type of Top */
for(owc in dao.getOutfitsWithClothingSearchingClothing("Top")) {
Log.d("SRCHINFO2","Outfit is ${owc.outfit.outfitName}")
for (c in owc.clothingList) {
Log.d("SRCHINFO2c","Clothing is ${c.description} ....")
}
}
/* Only the Outfits that match the search with the clothing that fits the search NOT ALL CLothing*/
for(owc in dao.getOutfitsWithOnlyClothingsThatMatchSearch("Top")) {
Log.d("SRCHINFO3","Outfit is ${owc.outfit.outfitName}")
for (c in owc.clothingList) {
Log.d("SRCHINFO3c","Clothing is ${c.description} ....")
}
}
And the output would be (first run) :-
2022-05-01 13:31:52.485 D/SRCHINFO1: Clothing is Singlet ....
2022-05-01 13:31:52.488 D/SRCHINFO2: Outfit is Outfit1
2022-05-01 13:31:52.488 D/SRCHINFO2c: Clothing is Singlet ....
2022-05-01 13:31:52.488 D/SRCHINFO2c: Clothing is Shorts ....
2022-05-01 13:31:52.489 D/SRCHINFO2: Outfit is Outfit2
2022-05-01 13:31:52.489 D/SRCHINFO2c: Clothing is Singlet ....
2022-05-01 13:31:52.489 D/SRCHINFO2c: Clothing is Skirt ....
2022-05-01 13:31:52.489 D/SRCHINFO2c: Clothing is Hat with feather ....
2022-05-01 13:31:52.494 D/SRCHINFO3: Outfit is Outfit1
2022-05-01 13:31:52.494 D/SRCHINFO3c: Clothing is Singlet ....
2022-05-01 13:31:52.494 D/SRCHINFO3: Outfit is Outfit2
2022-05-01 13:31:52.494 D/SRCHINFO3c: Clothing is Singlet ....
- Your query finds Singlet
- The @Relation query finds 2 Outfits that use Singlet and lists all of the clothing
- The last query finds the 2 OutFits that use Singlet but only lists the Singlet not all the other clothing (as wanted)