Search code examples
androidsqlandroid-room

How to insert and query nested entities in Room database received from API that contains one to many relationships


I have an API that returns a DTO containing entities with the one-to-many relationships filled in a list.

I'm struggling to figure out how work with that data in Room.

I need to map this DTO to the corresponding Entities. I then need to insert them in the database. And later on, I want to query them and retrieve a BoardEntity with a list of its corresponding BoardChildEntities

fun getBoards() = networkBoundResource(query = {
    // I need to query all boards from Room and add to that its corresponding children in a list
}, 
fetch = {
    api.getBoards()
},
saveFetchResult = { dtos ->
    // how can I save all the data in the DTOs in their corresponding tables
    // without writing a lot of nested loops
})

The DTO returned from the api:

data class BoardDto(
    val id: Int,
    val name: String,
    val boardChildren: List<BoardChildDto>,
) {
    data class BoardChildDto(
        val id: Int,
        val boardId: Int, // foreign key
        val name: String,
        val boardElements: List<BoardElementDto>,
    ) {
        data class BoardElementDto(
            val id: Int,
            val boardChildId: Int, // foreign key
            val name: String,
            val type: String,
            val hyperlinks: List<BoardElementHyperlinkDto>,
        ) {
            data class BoardElementHyperlinkDto(
                val id: Int,
                val boardElementId: Int, // foreign key
                val name: String,
            )
        }
    }
}

The Room entities:

@Entity
data class BoardEntity(
    @PrimaryKey(autoGenerate = false) val id: Int,
    val icon: String,
    val name: String,
)

@Entity(
    foreignKeys = [ForeignKey(
        entity = BoardEntity::class,
        parentColumns = ["id"],
        childColumns = ["boardId"],
        onDelete = ForeignKey.CASCADE
    )]
)
data class BoardChildEntity(
    @PrimaryKey(autoGenerate = false) val id: Int,
    val boardId: Int,
    val name: String,
)

@Entity(
    foreignKeys = [ForeignKey(
        entity = BoardChildEntity::class,
        parentColumns = ["id"],
        childColumns = ["boardChildId"],
        onDelete = ForeignKey.CASCADE
    )]
)
data class BoardElementEntity(
    @PrimaryKey(autoGenerate = false) val id: Int,
    val boardChildId: Int,
    val name: String,
)

@Entity(
    foreignKeys = [ForeignKey(
        entity = BoardElementEntity::class,
        parentColumns = ["id"],
        childColumns = ["boardElementId"],
        onDelete = ForeignKey.CASCADE
    )]
)
data class BoardElementHyperlinkEntity(
    @PrimaryKey(autoGenerate = false) val id: Int,
    val boardElementId: Int,
    val name: String,
)

The mappers from DTOs to Room entities

fun BoardDto.toEntityModel(): BoardEntity {
    return BoardEntity(
        id = id,
        name = name,
    )
}

fun BoardChildDto.toEntityModel(): BoardChildEntity {
    return BoardChildEntity(
        id = id,
        boardId = boardId,
        name = name,
    )
}

fun BoardElementDto.toEntityModel(): BoardElementEntity {
    return BoardElementEntity(
        id = id,
        boardChildId = boardChildId,
        name = name,
    )
}

fun BoardElementHyperlinkDto.toEntityModel(): BoardElementHyperlinkEntity {
    return BoardElementHyperlinkEntity(
        id = id,
        boardElementId = boardElementId,
        name = name,
    )
}

Solution

  • // how can I save all the data in the DTOs in their corresponding tables

    // without writing a lot of nested loops

    Depends upon what you call lots, some cannot really be avoided.

    Here's is an example of how this can be achieved (from an @Dao annotated interface):-

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(boardEntity: BoardEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(boardChildEntity: BoardChildEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(boardElementEntity: BoardElementEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(boardElementHyperlinkEntity: BoardElementHyperlinkEntity): Long
    
    
    @Transaction
    @Query("")
    fun insertDTO(dto: BoardDto, icon: String): Int {
        val boardId = insert(BoardEntity( id = dto.id,name = dto.name, icon = icon))
        if (boardId < 0) return TheDatabase.BOARD_NOT_INSERTED
        for (bc in dto.boardChildren) {
            val boardChildId = insert(BoardChildEntity(id = bc.id, boardId = boardId.toInt(), name = bc.name))
            if (boardChildId < 0) return TheDatabase.BOARDCHILD_NOT_INSERTED
            for (be in bc.boardElements) {
                val boardElementId = insert(BoardElementEntity(id = be.id, boardChildId = boardChildId.toInt(), name = be.name))
                if (boardElementId < 0) return TheDatabase.BOARDELEMENT_NOT_INSERTED
                for (beh in be.hyperlinks) {
                    val boardElementHyperlinkId = insert(BoardElementHyperlinkEntity(id = beh.id, boardElementId = boardElementId.toInt(), beh.name))
                    if (boardElementHyperlinkId < 0) return TheDatabase.BOARDHYPERLINK_NOT_INESRTED
                }
            }
        }
        return boardId.toInt()
    }
    

    So initially the 4 standard convenience inserts

    Then a function with a body that 3 nested loops (noting that for the sake of less hassle demonstrating that the foreign keys are cascaded and overwrite the hard code values (see demo and results)). Of the actual values if correct could be used (if not then you would encounter foreign key conflicts). In a similar vein if an insert is IGNORED at whatever level then an immediate return is made, an Int is returned according to come constants (-99 through to -96 inclusive) (this behaviour can easily be removed).

    And later on, I want to query them and retrieve a BoardEntity with a list of its corresponding BoardChildEntities

    and I assume the with the child BoardElements and also with the HyperLink children (down through all the hierarchy).

    For this you use a hierarchy of POJO's each getting the parent and the children. e.g. the following POJO's with the parent having the @Embed annotation and the list of children having the @Relation annotation.

    data class BoardElementEntityWithHyperlinkEntity(
        @Embedded
        val boardElementEntity: BoardElementEntity,
        @Relation( entity = BoardElementHyperlinkEntity::class, parentColumn = "id", entityColumn = "boardElementId")
        val boardElementHyperlinkEntityList: List<BoardElementHyperlinkEntity>
    )
    
    
    data class BoardChildEntityWithElementEntity(
        @Embedded
        val boardChildEntity: BoardChildEntity,
        @Relation( entity = BoardElementEntity::class, parentColumn = "id", entityColumn = "boardChildId")
        val boardElementEntityList: List<BoardElementEntityWithHyperlinkEntity>
    )
    
    
    data class BoardEntityWithBoardChildList(
        @Embedded
        val boardEntity: BoardEntity,
        @Relation(entity = BoardChildEntity::class, parentColumn = "id", entityColumn = "boardId")
        val BoardChildEntityList: List<BoardChildEntityWithElementEntity>
    )
    
    • Note in the @Relation how the entity= is the @Entity annotated class not the class of the field.
    • The POJO's are listed in reverse order as experience has shown that it is easier to create them from the bottom up.

    DEMO

    Here's a working demo that when run takes adds the data from a BoardDTO and then extracts it (albeit that some references, such as boardId, are replaced).

    First the entire code for the Database Stuff and also your BoardDTo (your mappers haven't been used):-

    data class BoardDto(
        val id: Int,
        val name: String,
        val boardChildren: List<BoardChildDto>,
    ) {
        data class BoardChildDto(
            val id: Int,
            val boardId: Int, // foreign key
            val name: String,
            val boardElements: List<BoardElementDto>,
        ) {
            data class BoardElementDto(
                val id: Int,
                val boardChildId: Int, // foreign key
                val name: String,
                val type: String,
                val hyperlinks: List<BoardElementHyperlinkDto>,
            ) {
                data class BoardElementHyperlinkDto(
                    val id: Int,
                    val boardElementId: Int, // foreign key
                    val name: String,
                )
            }
        }
    }
    
    @Entity
    data class BoardEntity(
        @PrimaryKey(autoGenerate = false) val id: Int,
        val icon: String,
        val name: String,
    )
    
    @Entity(
        foreignKeys = [ForeignKey(
            entity = BoardEntity::class,
            parentColumns = ["id"],
            childColumns = ["boardId"],
            onDelete = ForeignKey.CASCADE
        )]
    )
    data class BoardChildEntity(
        @PrimaryKey(autoGenerate = false) val id: Int,
        val boardId: Int,
        val name: String,
    )
    data class BoardEntityWithBoardChildList(
        @Embedded
        val boardEntity: BoardEntity,
        @Relation(entity = BoardChildEntity::class, parentColumn = "id", entityColumn = "boardId")
        val BoardChildEntityList: List<BoardChildEntityWithElementEntity>
    )
    
    @Entity(
        foreignKeys = [ForeignKey(
            entity = BoardChildEntity::class,
            parentColumns = ["id"],
            childColumns = ["boardChildId"],
            onDelete = ForeignKey.CASCADE
        )]
    )
    data class BoardElementEntity(
        @PrimaryKey(autoGenerate = false) val id: Int,
        val boardChildId: Int,
        val name: String,
    )
    data class BoardChildEntityWithElementEntity(
        @Embedded
        val boardChildEntity: BoardChildEntity,
        @Relation( entity = BoardElementEntity::class, parentColumn = "id", entityColumn = "boardChildId")
        val boardElementEntityList: List<BoardElementEntityWithHyperlinkEntity>
    )
    
    @Entity(
        foreignKeys = [ForeignKey(
            entity = BoardElementEntity::class,
            parentColumns = ["id"],
            childColumns = ["boardElementId"],
            onDelete = ForeignKey.CASCADE
        )]
    )
    data class BoardElementHyperlinkEntity(
        @PrimaryKey(autoGenerate = false) val id: Int,
        val boardElementId: Int,
        val name: String,
    )
    
    data class BoardElementEntityWithHyperlinkEntity(
        @Embedded
        val boardElementEntity: BoardElementEntity,
        @Relation( entity = BoardElementHyperlinkEntity::class, parentColumn = "id", entityColumn = "boardElementId")
        val boardElementHyperlinkEntityList: List<BoardElementHyperlinkEntity>
    )
    
    @Dao
    interface TheDAOs {
        @Insert(onConflict = OnConflictStrategy.IGNORE)
        fun insert(boardEntity: BoardEntity): Long
        @Insert(onConflict = OnConflictStrategy.IGNORE)
        fun insert(boardChildEntity: BoardChildEntity): Long
        @Insert(onConflict = OnConflictStrategy.IGNORE)
        fun insert(boardElementEntity: BoardElementEntity): Long
        @Insert(onConflict = OnConflictStrategy.IGNORE)
        fun insert(boardElementHyperlinkEntity: BoardElementHyperlinkEntity): Long
    
    
        @Transaction
        @Query("")
        fun insertDTO(dto: BoardDto, icon: String): Int {
            val boardId = insert(BoardEntity( id = dto.id,name = dto.name, icon = icon))
            if (boardId < 0) return TheDatabase.BOARD_NOT_INSERTED
            for (bc in dto.boardChildren) {
                val boardChildId = insert(BoardChildEntity(id = bc.id, boardId = boardId.toInt(), name = bc.name))
                if (boardChildId < 0) return TheDatabase.BOARDCHILD_NOT_INSERTED
                for (be in bc.boardElements) {
                    val boardElementId = insert(BoardElementEntity(id = be.id, boardChildId = boardChildId.toInt(), name = be.name))
                    if (boardElementId < 0) return TheDatabase.BOARDELEMENT_NOT_INSERTED
                    for (beh in be.hyperlinks) {
                        val boardElementHyperlinkId = insert(BoardElementHyperlinkEntity(id = beh.id, boardElementId = boardElementId.toInt(), beh.name))
                        if (boardElementHyperlinkId < 0) return TheDatabase.BOARDHYPERLINK_NOT_INESRTED
                    }
                }
            }
            return boardId.toInt()
        }
    
        @Transaction
        @Query("SELECT * FROM boardentity")
        fun getAllBoardsWithFamily(): List<BoardEntityWithBoardChildList>
    }
    
    
    @Database(
        entities = [
            BoardElementHyperlinkEntity::class,
            BoardElementEntity::class,
            BoardChildEntity::class,
            BoardEntity::class
                   ],
        exportSchema = false,
        version = 1
    )
    abstract class TheDatabase: RoomDatabase() {
        abstract fun getTheDAOs(): TheDAOs
        companion object {
            const val BOARD_NOT_INSERTED = -99
            const val BOARDCHILD_NOT_INSERTED = -98
            const val BOARDELEMENT_NOT_INSERTED = -97
            const val BOARDHYPERLINK_NOT_INESRTED = -96
    
            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
            }
        }
    }
    

    To actually test then the following activtiy code (MainActivity):-

    class MainActivity : AppCompatActivity() {
        lateinit var db: TheDatabase
        lateinit var dao: TheDAOs
        override fun onCreate(savedInstanceState: Bundle?) {
            super.onCreate(savedInstanceState)
            setContentView(R.layout.activity_main)
    
            db = TheDatabase.getInstance(this)
            dao = db.getTheDAOs()
    
            val beh = BoardDto.BoardChildDto.BoardElementDto.BoardElementHyperlinkDto(11100,9999,"BEH0001")
            val behlist1 = listOf(
                beh,
                BoardDto.BoardChildDto.BoardElementDto.BoardElementHyperlinkDto(22200,9999,"BEH0002"),
                BoardDto.BoardChildDto.BoardElementDto.BoardElementHyperlinkDto(33300,9999,"BEH0003")
            )
            val behlist2 = listOf(
                BoardDto.BoardChildDto.BoardElementDto.BoardElementHyperlinkDto(44400,9999,"BEH0004"),
                BoardDto.BoardChildDto.BoardElementDto.BoardElementHyperlinkDto(55500,9999,"BEH0005")
            )
            val belist1 = listOf(
                BoardDto.BoardChildDto.BoardElementDto(id = 1100, boardChildId = 999, name = "BE0001", type = "A", hyperlinks = behlist1),
                BoardDto.BoardChildDto.BoardElementDto(id= 2200, boardChildId = 999, name = "BE0002", type = "B", hyperlinks = behlist2)
            )
            val bclist = listOf(
                BoardDto.BoardChildDto(id = 110, boardId = 99, name = "BC0001", boardElements = belist1 ),
                BoardDto.BoardChildDto(id = 220, boardId = 99, name = "BC0002", boardElements = belist1 ),
            )
    
            dao.insertDTO(BoardDto(id = 11, boardChildren = bclist, name = "B00001"), icon = "unsure")
    
            for (b in dao.getAllBoardsWithFamily()) {
                Log.d(TAG,"Board Name is ${b.boardEntity.name} ICON is ${b.boardEntity.icon} ID is ${b.boardEntity.id}. Board has ${b.BoardChildEntityList.size} BoardChildren. They are:-")
                for (bc in b.BoardChildEntityList) {
                    Log.d(TAG,"\tBC Name is ${bc.boardChildEntity.name} ID is ${bc.boardChildEntity.id} Mapped To Board ${bc.boardChildEntity.boardId}. ${bc.boardElementEntityList.size} elements:-")
                    for(be in bc.boardElementEntityList) {
                        Log.d(TAG,"\t\tBE Name is ${be.boardElementEntity.name} ${be.boardElementHyperlinkEntityList.size} elemets:-")
                        for (beh in be.boardElementHyperlinkEntityList) {
                            Log.wtf(TAG,"\t\t\tBEH name is ${beh.name} ID is ${beh.id} maps to ${beh.boardElementId}")
                        }
                    }
                }
            }
        }
        companion object {
            const val TAG = "DBINFO"
        }
    }
    
    • Note the code is only intended to run the once

    When run then the result output to the log is:-

    D/DBINFO: Board Name is B00001 ICON is unsure ID is 11. Board has 2 BoardChildren. They are:-
    D/DBINFO:   BC Name is BC0001 ID is 110 Mapped To Board 11. 2 elements:-
    D/DBINFO:       BE Name is BE0001 3 elemets:-
    D/DBINFO:           BEH name is BEH0001 ID is 11100 maps to 1100
    D/DBINFO:           BEH name is BEH0002 ID is 22200 maps to 1100
    D/DBINFO:           BEH name is BEH0003 ID is 33300 maps to 1100
    D/DBINFO:       BE Name is BE0002 2 elemets:-
    D/DBINFO:           BEH name is BEH0004 ID is 44400 maps to 2200
    D/DBINFO:           BEH name is BEH0005 ID is 55500 maps to 2200
    D/DBINFO:   BC Name is BC0002 ID is 220 Mapped To Board 11. 0 elements:-
    
    • note BC0002 will have no children as the list was the same as for BC0001 and thus the duplicates would have been skipped.

    The database via App inspection:-

    enter image description here

    enter image description here

    enter image description here

    enter image description here