logo标签列表

使用Kotlin在Android Room数据库中插入对象列表

androidkotlinandroid-room
3
一个在Android上的初学者，在使用Room数据库时遇到了一些限制。我正在使用两个表格，分别是“Clothing”和“Outfits”。用户可以通过插入它们提供的值来创建一个Outfit。然后在另一页上，用户可以插入一个Outfit，其中包含他们已经在Clothing.kt中创建的服装信息。为了应用程序的缘故，关系只需要是一对多的，也就是说，我只需要使用多个Clothing项创建一个Outfit。以下是我的代码：
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

我查看了许多Android开发文档，它们都提到了如何用相同的服装列表查询套装，但没有提到如何使用列表对象插入新的套装。
据我所知，SQLite无法处理列表。因此，我尝试了一种方法，即使用类型转换器，但由于我对GSON不熟悉，因此很难将其实现到我的代码中。
举个例子，我一直在尝试实现谷歌Android文档中的示例，但它似乎并不太好理解，但看起来可以按照POJO的方式插入对象列表：
Google插入示例：
@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>);
}

我假设我的目标是使用类似的方法复制这个，从列表中创建一套装备。据我所知，Google文档使用了3个表格（音乐，专辑和歌曲），所以我一直在努力寻找可以修改我的数据库的地方。我应该创建第三个表格吗？有没有人用Kotlin得出了类似的结论？
如果你们中任何人已经解决了这个问题或接近解决，任何建议都将不胜感激。
以下是各自表格的Dao，它们还没有完成，因为我无法找到一个存储服装项目的方法。
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>>


}
1个回答
8
据我所知，SQLite无法处理列表。因此，我尝试了一种方法，即使用类型转换器，但是，由于我对GSON不熟悉，我很难将其实现到我的代码中。
1). 将Gson库添加到您的项目中，例如在build.gradle（模块）中：
 implementation 'com.google.code.gson:gson:2.9.0'

2). 添加一个数据类，例如 ClothingList：

data class ClothingList(
    val clothingList: List<Clothing>
)

3). 修改 Outfit 类，使用 ClothingList 替代 List，并移除 @Ignore 注解，例如：

@Entity
data class Outfit (
    @PrimaryKey(autoGenerate = true) val id: Int, /* more correct to use Long */
    val outfitName: String,
    //@Ignore
    val ClothingItems: ClothingList
)
  • 自动生成的列更正确地是Long而不是Int，因为理论上存储的值可以高达64位有符号。

4). 添加一个新类用于类型转换器，例如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). 修改被 @Database 注释的类（具有最高作用域），使其具有 @TypeConverters 注释，例如：

@TypeConverters(value = [MyTypeConverters::class])
@Database(entities = [Clothing::class,Outfit::class], version = 1, exportSchema = false)
abstract class TheDatabase: RoomDatabase() {
....
}

您可以将服装列表放入一个装备中。但是，从关系数据库的角度来看，这并不是理想的方式，因为整个衣服列表是单个存储值，会引入复杂性。
您的第二个尝试（似乎是）将一件衣服与一个装备关联起来，因此如果您的“蓝色牛仔裤”在多个装备中使用，则必须重复。
建议的解决方案是实现多对多的关系，因此服装可以使用任意数量的服装，并且服装可以被任何数量的装备使用。因此，“蓝色牛仔裤”只需一行即可。
要利用多对多的关系，您需要一个中间表格，它是装备和服装之间的交叉引用。即一个用于装备ID的列和一个用于服装ID的列。然后就不需要类型转换器或者存储列表了。
考虑以下工作示例：- 装备类
@Entity
data class Outfit(
    @PrimaryKey
    @ColumnInfo(name = "outfitId")
    val id: Long?=null,
    val outfitName: String
)

和 服装 类

@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
)

一对多关系的中间表（映射、关联、引用和其他名称）
@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
)

一个POJO类 OutFitWithClothingList，用于获取与之相关的服装列表的外套。

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>
)

一个POJO，以相反的方式围绕着使用它的服装项的套装。
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>
)

一个带有TypeConverters的类，用于Date（将日期存储为整数即Long）：-
class TheTypeConverters {
    @TypeConverter
    fun fromDateToLong(date: Date): Long {
        return date.time
    }
    @TypeConverter
    fun fromLongToDate(date: Long): Date {
        return Date(date)
    }
}

一个（为了简洁/方便）用 @Dao 注释的类 Alldao，包括查询所有服装及其服装清单以及获取使用的所有服装项的所有服装和插入到表中的插入。
@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>
    
}

一个被 @Database 注释的类（为了简洁和方便使用 .allowMainThreadQueries）

@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

最后，展示插入Outfits、Clothing和映射以及提取所有Outfits和其包含的Clothing列表以及所有Clothing和使用该衣物的Outfits列表的activity代码。

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}")
            }
        }

    }
}

结果（输出到日志）

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

通过AppInspection即数据库中存储的数据。

enter image description here

enter image description here

和映射表

enter image description here

关于@Relation的补充说明

当您使用@Relation时，无论对象如何，所有子项都会被检索，并且它们将按照查询优化器适合的顺序排列。如果您指定了ORDER或WHERE子句，则可能会感到沮丧/困惑。

以下是一些示例查询

  • a) 您的查询很好，如果说在创建装备时，您只想选择上衣

  • b) 一个查询，您只想找到拥有上衣的装备，并列出所有服装（通过@Relation） -c）一个查询，您想要找到拥有上衣的装备，但仅列出是上衣的服装（演示如何绕过@Relation获取所有子项并仅获取某些子项）

  • 除了额外的@Dao函数和活动代码来演示它们之外，没有其他更改

因此，额外的@Dao函数是

@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
}

请注意，已使用tablename.column，但并非普遍使用，只有在列名不明确时才需要使用tablename.column（因此id列使用了@ColumnInfo(name = ??)以使它们不会产生歧义。
如果列名不明确且您使用tablename.column名称，则提取的列名将具有相同的名称，并且Room将仅选择最后一个，因此outfit.id将与clothing.id具有相同的值，这可以通过使用唯一的列名来避免。
因此，tablename.column仅用于展示其用途。
为了演示，活动可以包括：
    /* 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} ....")
        }
    }

输出结果将是（第一次运行）：

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 ....
  • 您的查询找到了Singlet
  • @Relation查询找到了使用Singlet的2个Outfits，并列出所有衣服
  • 最后一个查询找到了使用Singlet的2个Outfits，但只列出了Singlet而不是所有其他衣服（如所需）
谢谢Mike的回答。我很高兴你一开始没有推荐GSON作为解决方案，并且在我选择这条路时帮助了设置过程。从我所寻找的内容来看，GSON似乎是一个非常勉强的答案。直到现在我才回复你，因为我测试了你的解决方案以及其他方案。幸运的是它起作用了，它确实让我看到了将来使用Room的新可能性。我还要感谢你指出了Long vs. "auto-generate"的情况。根据我从Google文档中阅读的内容，自动生成是好的，但似乎Long是更清晰的解决方案。 - Kyle Gerken
@KyleGerken 每次看到GSON/JSON被扔进单个列中，我都会感到不安。自动生成并没有真正实现自动生成，而是使用INTEGER PRIMARY KEY并在插入时没有值或空值。自动生成添加了一个规则，即生成的值必须高于任何已使用的值，它使用另一个表sqlite_sequence，因此相对低效。请参见https://sqlite.org/autoinc.html，特别是第一句话。我不知道为什么Room指南不遵循这个建议。 - MikeT
@KyleGerken更新了答案，更深入地解释了@Relation的工作原理（并使一些人感到困惑）。 - MikeT
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接