I'm trying to save Lists and Books data in a local database using Room. where each list contains books. but have a problem implementing this.
Well, I used type converters & it worked but the google docs suggest that that's not an ideal way of saving complex objects and suggest using Relationships
So I tried to define a relationship between the two objects but I couldn't get it to work. can someone please help out.
@Entity(tableName = "lists_table")
data class Lists(
@PrimaryKey
@ColumnInfo(name = "display_name")
@Json(name = "display_name") val displayName: String,
val books: List<Books>
)
@Entity(tableName = "books_table")
data class Books(
@PrimaryKey
@ColumnInfo(name = "author") val author: String,
@ColumnInfo(name = "book_image") @Json(name = "book_image") val bookImage: String,
@ColumnInfo(name = "width") @Json(name = "book_image_width") val imageWidth: Int,
@ColumnInfo(name = "height") @Json(name = "book_image_height") val imageHeight: Int,
@ColumnInfo(name = "contributor") val contributor: String,
@ColumnInfo(name = "description") val description: String,
@ColumnInfo(name = "publisher") val publisher: String,
@ColumnInfo(name = "rank") val rank: Int,
@ColumnInfo(name = "rank_last_week") @Json(name = "rank_last_week") val rankLastWeek: Int,
@ColumnInfo(name = "title") val title: String,
@ColumnInfo(name = "weeks_on_list") @Json(name = "weeks_on_list") val weeksOnList: Int,
)
Assuming that a Book can exist in many lists, not just one list (a many-many relationship) then you have an table (know by many names such as a reference table, mapping table, associative table ....). Such a table has a map to one of the objects (list or book) an a map to the other (book or list).
To map an object then the map must uniquely identify the mapped object. As PRIMARY KEYS must be UNIQUE then in your case the display_name column is the primary key for a Lists object and the author column is the primary key for a book object.
So the mapping table would consist of these two columns (display_name and author). The display_name together with the author would be the primary key.
@PrimaryKey annotation to specify a composite primary key, so you use the primaryKeys parameter of the @Entity annotation, which takes a value that is an array of Strings (the respective column names) .So you could have:-
@Entity(
primaryKeys = ["display_name_map","bookId_map"],
/* Optional but suggested */
foreignKeys = [
ForeignKey(
entity = Lists::class,
parentColumns = ["display_name"],
childColumns = ["display_name_map"],
onDelete = ForeignKey.CASCADE,
onUpdate = ForeignKey.CASCADE
),
ForeignKey(
entity = Books::class,
parentColumns = ["bookId"],
childColumns = ["bookId_map"],
onDelete = ForeignKey.CASCADE,
onUpdate = ForeignKey.CASCADE
)
]
)
data class ListsBookMap(
val display_name_map: String,
@ColumnInfo(index = true) /* have an index associated with the table according to bookId */
val bookId_map: Long
)
bookId is the mapped column, thus an author can author many booksi.e. Books has changed to:-
@PrimaryKey
val bookId: Long?=null, /* ADDED as the primary key so that an author can have many books */
@ColumnInfo(name = "author") val author: String,
To facilitate retrieving the parent (Lists) with it's related children (Books) then you utilise a POJO with the parent annotated with @Embedded and the list of children annotated with the @Relation annotation that includes the associateBy parameter to specify the mapping table and the columns therein, i.e. the Junction.
So this could be:-
data class ListWithBooks(
@Embedded
val list: Lists,
@Relation(
entity = Books::class,
parentColumn = "display_name",
entityColumn = "bookId",
associateBy = Junction(
ListsBookMap::class,
parentColumn = "display_name_map",
entityColumn = "bookId_map"
)
)
val booksList: List<Books>
)
You would ned to be able to insert data, including the mappings so you could have an @Dao annotated interface such as :-
@Dao
interface ListAndBookDao {
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(books: Books): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(lists: Lists): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(listsBookMap: ListsBookMap): Long
@Transaction
@Query("SELECT * FROM lists_table")
fun getAllListsWithListOfBooks(): List<ListsWithBooks>
}
@Query this will retrieve Lists with the books for the current Lists.Demo
Using the above, and a suitable @Database annotated abstract class, and the following in an activity (note run on the main thread for convenience and brevity):-
class MainActivity : AppCompatActivity() {
lateinit var db: TheDatabase
lateinit var dao: ListAndBookDao
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
db = TheDatabase.getInstance(this)
dao = db.getListAndBookDao()
val b1Id = dao.insert(Books(null,"A1","B1IMAGE",10,12,"C","Desc","P",5,4,"The Book 1",10))
val b2Id = dao.insert(Books(null,"A2","B2IMAGE",10,12,"C","Desc","P",5,4,"The Book 2",10))
val b3Id = dao.insert(Books(null,"A3","B3IMAGE",10,12,"C","Desc","P",5,4,"The Book 3",10))
val b4Id = dao.insert(Books(null,"A1","B4IMAGE",10,12,"C","Desc","P",5,4,"The Book 4",10))
val b5Id = dao.insert(Books(null,"A4","B5IMAGE",10,12,"C","Desc","P",5,4,"The Book 5",10))
val list1Name = "BL001"
val list2Name = "BL002"
val list3Name = "BL003"
val list4Name = "BL004"
val list5Name ="BL005"
dao.insert(Lists(list1Name))
dao.insert(Lists(list2Name))
dao.insert(Lists(list3Name))
dao.insert(Lists(list4Name))
dao.insert(Lists(list5Name))
/* Add books to the lists (i.e. the mappings)*/
dao.insert(ListsBookMap(list1Name,b1Id))
dao.insert(ListsBookMap(list1Name,b3Id))
dao.insert(ListsBookMap(list1Name,b5Id))
dao.insert(ListsBookMap(list2Name,b2Id))
dao.insert(ListsBookMap(list2Name,b4Id))
dao.insert(ListsBookMap(list3Name,b1Id))
dao.insert(ListsBookMap(list3Name,b2Id))
dao.insert(ListsBookMap(list3Name,b3Id))
dao.insert(ListsBookMap(list3Name,b4Id))
dao.insert(ListsBookMap(list3Name,b5Id))
dao.insert(ListsBookMap(list4Name,b3Id))
val sb = StringBuilder()
for (lwb in dao.getAllListsWithListOfBooks()) {
sb.clear()
for (b in lwb.booksList) {
sb.append("\n\tBook is ${b.title}, Author is ${b.author} Image is ${b.bookImage} ....")
}
Log.d("DBINFO","List is ${lwb.list.displayName} it has ${lwb.booksList.size} books. They are:-$sb")
}
}
}
When run (only designed to run once) then the output to the log includes:-
D/DBINFO: List is BL001 it has 3 books. They are:-
Book is The Book 1, Author is A1 Image is B1IMAGE ....
Book is The Book 3, Author is A3 Image is B3IMAGE ....
Book is The Book 5, Author is A4 Image is B5IMAGE ....
D/DBINFO: List is BL002 it has 2 books. They are:-
Book is The Book 2, Author is A2 Image is B2IMAGE ....
Book is The Book 4, Author is A1 Image is B4IMAGE ....
D/DBINFO: List is BL003 it has 5 books. They are:-
Book is The Book 1, Author is A1 Image is B1IMAGE ....
Book is The Book 2, Author is A2 Image is B2IMAGE ....
Book is The Book 3, Author is A3 Image is B3IMAGE ....
Book is The Book 4, Author is A1 Image is B4IMAGE ....
Book is The Book 5, Author is A4 Image is B5IMAGE ....
D/DBINFO: List is BL004 it has 1 books. They are:-
Book is The Book 3, Author is A3 Image is B3IMAGE ....
D/DBINFO: List is BL005 it has 0 books. They are:-
i.e. as expected