Pay attention to official account and learn more knowledge
summary
sqlite is a relational database, so object relationship is an important part. For example, the data we define includes forests and trees, and forests contain trees. At this time, the defined forest should contain a tree object, which is the object relationship between forest and trees
Relationship type
There are four types of relationships between multiple objects:
- one-on-one
- One to many
- Many to many
- nesting
one-on-one
For example: an only child a father has a son, which is a one-to-one relationship
code
class RoomRelativeActivity : CustomAdapterActivity() {
private lateinit var database: OurRoomDatabase
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
database=Room.databaseBuilder(this,OurRoomDatabase::class.java,"parentsun").build()
}
override fun getAdapterDatas(): MutableList<ItemData> = mutableListOf(
ItemData(title = "Insert a father") {
lifecycleScope.launch {
val parent = Parent("Little daddy")
database.parentSunDao().insertParent(parent)
it.itemData.content="Insert successful"
it.itemData.notifyDataSetChange()
}
},
ItemData(title = "Insert a son"){
lifecycleScope.launch {
val sun=Sun("Big head son",1)
database.parentSunDao().insertSun(sun)
it.itemData.run {
content="Insert successful"
notifyDataSetChange()
}
}
},
ItemData(title = "Query data"){
lifecycleScope.launch {
var parentAndSun = database.parentSunDao().queryParent(1)
val content = "The father is: ${parentAndSun.parent} \n Son is: ${parentAndSun.sun}"
it.itemData.content=content
it.itemData.notifyDataSetChange()
}
}
)
override fun showFirstItem(): Boolean = false
}
@Dao
interface ParentSonDao {
@Transaction
@Insert
suspend fun insertParent(parent: Parent)//Insert father
@Insert
suspend fun insertSun(sun: Sun)//Insert son
/**
* Query the combination of father and son
*/
@Transaction
@Query("select * from Parent where parentId=:parentId")//Query the collection of father and son. Note that we cannot insert ParentAndSunRef directly
suspend fun queryParent(parentId: Long): ParentAndSunRef
}
/**
* It is used to represent the collection of Parent and Sun. If we want colleagues to query father and son, we can use father id to query
*/
data class ParentAndSunRef(//We don't need to add @ Entity annotation to ParentAndSunRef
@Embedded
val parent: Parent,//
@Relation(
parentColumn = "parentId",//The primary key parentId of the parent associated with the son
entityColumn = "hisparentId"//The father must store a unique id field hisparentId of the son
)
val sun: Sun
)
/**
* father
*/
@Entity
data class Parent(var name: String) {
@PrimaryKey(autoGenerate = true)
var parentId: Long? = null
}
/**
* Son
*/
@Entity
data class Sun(var name: String,var hisparentId:Long) {
@PrimaryKey(autoGenerate = true)
var sunId: Long? = null
}
Realization effect
database structure
- Parent table
- Sun table
One to many
In the previous section, it is an only child, so a father corresponds to a son. In this example, it is not an only child. A father can have multiple sons.
For example, the apron mother gave birth to a brother to the big head son. At this time, the small head father has two sons. The son option in the relationship category should be a list instead of an object.
Therefore, the use of one to many is basically similar to the one-to-one relationship, except that the child classes in the corresponding relationship are represented by a list.
code
class OneToManyActivity : CustomAdapterActivity() {
private lateinit var database: OurRoomDatabase
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
database = Room.databaseBuilder(this,OurRoomDatabase::class.java,"parent1andsun1").build()
}
override fun getAdapterDatas(): MutableList<ItemData> = mutableListOf(
ItemData("Insert a dad"){
lifecycleScope.launch {
val parent=Parent1("Little daddy")
database.parent1AndSun1Dao().insertParent1(parent)
it.itemData.run {
content="Insert successful"
notifyDataSetChange()
}
}
},
ItemData("Insert a son"){
lifecycleScope.launch {
val sun = Sun1("Big head son",1)
database.parent1AndSun1Dao().insertSun1(sun)
}
it.itemData.run {
content="Insert successful"
notifyDataSetChange()
}
},
ItemData("Insert the second son"){
lifecycleScope.launch {
val sun = Sun1("Big head son's brother",1)
database.parent1AndSun1Dao().insertSun1(sun)
}
it.itemData.run {
content="Insert successful"
notifyDataSetChange()
}
},
ItemData(title = "One father and two sons"){
lifecycleScope.launch {
val parentAndSunRef = database.parent1AndSun1Dao().queryParent1AndSun1(1)
val con = "The father is: ${parentAndSunRef.parent.name} " +
"\n The first son is: ${parentAndSunRef.sun1s[0].name} " +
"\n The second son is: ${parentAndSunRef.sun1s[1].name}"
it.itemData.run {
content=con
notifyDataSetChange()
}
}
}
)
override fun showFirstItem(): Boolean =false
}
@Dao
interface Parent1AndSun1Dao{
@Insert
suspend fun insertParent1(parent: Parent1)
@Insert
suspend fun insertSun1(parent: Sun1)
/**
* When querying the collection of father and son, there must be @ Transaction annotation to ensure accurate atomic operation
*/
@Transaction
@Query("select * from Parent1 where parentId=:parentId")
suspend fun queryParent1AndSun1(parentId:Long):Parent1AndSun1Ref
}
@Entity
data class Parent1(var name:String){
@PrimaryKey(autoGenerate = true)
var parentId:Long?=null
}
@Entity
data class Sun1(var name:String,var refparentId:Long){
@PrimaryKey(autoGenerate = true)
var sunId:Long?=null
}
data class Parent1AndSun1Ref(
@Embedded
val parent:Parent1,
@Relation(
parentColumn = "parentId",//parentColumn is used here
entityColumn = "refparentId"
)
val sun1s:List<Sun1>
)
effect
Many to many
The above example is that one father corresponds to multiple sons, but we all know that the relationship between big head son, small head father and Lao Wang next door is well known. There may be a many to many relationship. This example will talk about this many to many relationship.
To realize this many to many relationship, you need to define multiple relationships,
For example, if a father has multiple sons, it is necessary to define the relationship class of Parent2Suns;
If a son has two fathers, it is necessary to define the relationship class of Sun2Parents;
code
class ManyToManyActivity : CustomAdapterActivity() {
private lateinit var database: OurRoomDatabase
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
database = Room.databaseBuilder(this, OurRoomDatabase::class.java, "parent2AndSun2").build()
}
override fun getAdapterDatas(): MutableList<ItemData> = mutableListOf(
ItemData(title = "Insert the little head, Dad") {
lifecycleScope.launch {
val parent = Parent2("Little daddy", 1)
database.parent2AndSun2Dao().insertParent2(parent)
}
},
ItemData(title = "Insert Lao Wang next door") {
lifecycleScope.launch {
val parent = Parent2("Lao Wang next door", 1)
database.parent2AndSun2Dao().insertParent2(parent)
}
},
ItemData(title = "Insert big head son") {
lifecycleScope.launch {
val sun = Sun2("Big head son", 1)
database.parent2AndSun2Dao().insertSun2(sun)
}
},
ItemData(title = "Insert the brother of the big head son") {
lifecycleScope.launch {
val sun = Sun2("Big head son's brother", 1)
database.parent2AndSun2Dao().insertSun2(sun)
}
},
ItemData(title = "Query son") {
lifecycleScope.launch {
val parent = database.parent2AndSun2Dao().queryParent(1)
logEE(parent.toString())
}
}
)
override fun showFirstItem(): Boolean = false
}
@Dao
interface Parent2AndSun2Dao {
@Insert
suspend fun insertParent2(parent: Parent2)
@Insert
suspend fun insertSun2(sun: Sun2)
@Transaction
@Query("select * from Parent2 where combineId=:combineId")
suspend fun queryParent(combineId: Long): Parent2Sun2Ref
@Transaction
@Query("select * from Sun2 where combineId=:combineId")
suspend fun querySun(combineId: Long): Sun2Parent2Ref
}
@Entity
data class Parent2(var name: String, var combineId: Long) {
@PrimaryKey(autoGenerate = true)
var parentId: Long? = null
}
@Entity
data class Sun2(var name: String, var combineId: Long) {
@PrimaryKey(autoGenerate = true)
var sunId: Long? = null
}
data class Parent2Sun2Ref(
@Embedded
var parent: Parent2,
@Relation(
parentColumn = "combineId",
entityColumn = "combineId"
)
var sun: List<Sun2>
)
data class Sun2Parent2Ref(
@Embedded
var sun: Sun2,
@Relation(
parentColumn = "combineId",
entityColumn = "combineId"
)
var parent: List<Parent2>
)
design sketch
nesting
In the above three summaries, we use @ Embdded and @ Relation annotations to realize the relationship mapping between databases,
In fact, @ embedded can still realize the nesting of objects without @ Relation.
For example, in this example, we use @ Embdded to nest Sun3 in Parent3, and the fields in Sun3 will be copied to the table in Parent3,
code
package com.ananananzhuo.roomdemo.embdded
import android.os.Bundle
import androidx.lifecycle.lifecycleScope
import androidx.room.*
import com.ananananzhuo.mvvm.activity.CustomAdapterActivity
import com.ananananzhuo.mvvm.bean.bean.ItemData
import com.ananananzhuo.roomdemo.OurRoomDatabase
import kotlinx.coroutines.launch
/**
* author :mayong
* function:Nesting between objects
* date :2021/9/12
**/
class EmbddedActivity : CustomAdapterActivity() {
private lateinit var database: OurRoomDatabase
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
database = Room.databaseBuilder(this,OurRoomDatabase::class.java,"parent3AndSun3").build()
}
override fun getAdapterDatas(): MutableList<ItemData> = mutableListOf(
ItemData(title = "Insert the little head, Dad"){
lifecycleScope.launch {
database.parent3AndSun3Dao().insertParent(Parent3("Little daddy", Sun3("Big head son")))
}
},
ItemData(title = "Little daddy"){
lifecycleScope.launch {
val parent3 = database.parent3AndSun3Dao().queryParent(1)
it.itemData.run {
content="Father's name: ${parent3.parentName} \n Son's name: ${parent3.sun.sunName}"
notifyDataSetChange()
}
}
}
)
override fun showFirstItem(): Boolean=false
}
@Dao
interface Parent3Sun3Dao {
@Insert
suspend fun insertParent(parent3: Parent3)
@Insert
suspend fun insertSun(sun: Sun3)
@Query("select * from Parent3 where parentId=:parentId")
suspend fun queryParent(parentId: Long): Parent3
}
@Entity
data class Parent3(
var parentName: String,
@Embedded
var sun: Sun3
){
@PrimaryKey(autoGenerate = true)
var parentId: Long? = null
}
@Entity
data class Sun3(
var sunName: String
){
@PrimaryKey(autoGenerate = false)
var sunId: Long? = null
}
Table structure after inserting data
Table structure after inserting data:
Table structure:
Son's table will not be inserted with real data
