暴露于Kotlin(KTOR)SELECT表中的最后一条记录

cgfeq70w  于 2023-03-13  发布在  Kotlin
关注(0)|答案(3)|浏览(153)

我如何从ktor的数据库中选择表中的最后一条记录?
我的数据类

data class ArticleInfoDTO(
    val id: Int,
    val likes: Int,
    val views: Int,
)

我的函数

object ArticlesInfo : Table("articles_info") {
    private val id = ArticlesInfo.integer("id")
    private val likes = ArticlesInfo.integer("likes")
    private val views = ArticlesInfo.integer("views")

    fun fetchArticleInfoLastRecord(): ArticleInfoDTO {
        return transaction {
            ArticlesInfo.select {  } // what should I write in curly braces?
        
        }
    }
mzillmmw

mzillmmw1#

我的建议如下:

val lastInfo = ArticlesInfo.selectAll().orderBy(ArticlesInfo.id, SortOrder.DESC).limit(1).singleOrNull()?.toArticleInfo()
bihw5rsg

bihw5rsg2#

data class ArticleInfoDTO(
    val id: Int,
    val likes: Int,
    val views: Int,
)

object ArticlesInfo : Table("articles_info") {
    private val id = ArticlesInfo.integer("id")
    private val likes = ArticlesInfo.integer("likes")
    private val views = ArticlesInfo.integer("views")

    fun fetchArticleInfoLastRecord(): ArticleInfoDTO? = transaction {
        ArticlesInfo.selectAll().lastOrNull()?.toArticleInfo()
    }

    private fun ResultRow.toArticleInfo() = ArticleInfoDTO(
        this[id],
        this[likes],
        this[views]
    )
}
eiee3dmh

eiee3dmh3#

最好的解决方案是运行一个原始查询。在您的情况下,它看起来像这样

object ArticlesInfoTable : IntIdTable("articles_info") {
    val likes = integer("likes")
    val views = integer("views")
}

class ArticlesInfoEntity(id: EntityID<Int>): IntEntity(id){
    companion object: IntEntityClass<ArticlesInfoEntity>(ArticlesInfoTable)
    var likes by ArticlesInfoTable.likes
    var views by ArticlesInfoTable.views
}

fun getLastItem(): ArticlesInfoEntity{
    return transaction {
        var maxId = 1
        TransactionManager.current().exec("SELECT MAX(id) as MAX from 
            articles_info;") { rs ->
            while (rs.next()) {
                maxId = rs.getString("MAX").toInt()
            }
        }
        ArticlesInfoEntity.findById(maxId)!!
    }
}

在本例中,原始查询针对MySql。

相关问题