Android room tells about object relationship through the story of big head son

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

  1. Parent table

  1. 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

Tags: Android Database SQLite

Posted on Fri, 19 Nov 2021 15:34:49 -0500 by mkosmosports