Kotlin Exposed,避免使用 DAO 进行多次查询

分享于2022年07月17日 dao kotlin mysql orm sql 问答
【问题标题】:Kotlin Exposed, avoid multiple queries using DAOKotlin Exposed,避免使用 DAO 进行多次查询
【发布时间】:2022-07-05 17:51:21
【问题描述】:

我已经像这样配置了我的 Exposed ORM 和 DAO 类。


object Highlights: IntIdTable("Highlights") {
    val video = reference("videoId", Videos)
    val preview = reference("previewId", Files)
    val title = varchar("title", 255).nullable()
    val createdAt = datetime("createdAt").nullable()
    val createdBy = integer("createdBy").nullable()
    val updatedAt = datetime("updatedAt").nullable()
    val updatedBy = integer("updatedBy").nullable()
    val deletedAt = datetime("deletedAt").nullable()
    val deletedBy = integer("deletedBy").nullable()
    val active = bool("active").nullable()
    val empty = bool("empty").nullable()

    val matchId = reference("matchId", Matches)
    val homeTeamId = reference("homeTeamId", Teams)
    val awayTeamId = reference("awayTeamId", Teams)
    val homeScore = integer("homeScore").nullable()
    val awayScore = integer("awayScore").nullable()
}

class HighlightDAO(id: EntityID): IntEntity(id) {
    companion object : IntEntityClass(Highlights)
    val video by VideoDAO referencedOn Highlights.video
    val preview by FileDAO referencedOn Highlights.preview
    val title by Highlights.title
    var createdAt by Highlights.createdAt
    var match  by MatchDAO referencedOn Highlights.matchId

    var players by PlayerDAO via HighlightsPlayers
    val events by HighlightEventDAO referrersOn HighlightEvents.highlightId

    val homeScore by Highlights.homeScore
    val awayScore by Highlights.awayScore
    val empty by Highlights.empty
}

它包含对与此类似的其他 ORM 和 DAO 的引用。 当我尝试执行查询时出现问题。

...

HighlightDAO.findById(highlightId)
...

结果完全没问题,但是 Exposed 执行了大量的 SELECT 查询。这是日志。

SQL: SELECT Highlights.id, Highlights.videoId, Highlights.previewId, Highlights.title, Highlights.createdAt, Highlights.createdBy, Highlights.updatedAt, Highlights.updatedBy, Highlights.deletedAt, Highlights.deletedBy, Highlights.active, Highlights.empty, Highlights.matchId, Highlights.homeTeamId, Highlights.awayTeamId, Highlights.homeScore, Highlights.awayScore FROM Highlights WHERE Highlights.id = 6657
SQL: SELECT Highlights_Events.id, Highlights_Events.highlightId, Highlights_Events.playerId, Highlights_Events.teamId, Highlights_Events.matchId, Highlights_Events.homeTeamId, Highlights_Events.awayTeamId, Highlights_Events.`name`, Highlights_Events.nameId, Highlights_Events.actionId, Highlights_Events.standartId, Highlights_Events.matchSecond, Highlights_Events.matchPeriod, Highlights_Events.`second` FROM Highlights_Events WHERE Highlights_Events.highlightId = 6657
SQL: SELECT Matches.id, Matches.homeTeamId, Matches.awayTeamId, Matches.competitionSeasonId, Matches.turnId, Matches.GroupId, Matches.createdAt, Matches.createdBy, Matches.updatedAt, Matches.updatedBy, Matches.deletedAt, Matches.deletedBy, Matches.active FROM Matches WHERE Matches.id = 3115
SQL: SELECT Players.id, Players.createdAt, Players.createdBy, Players.updatedAt, Players.updatedBy, Players.deletedAt, Players.deletedBy, Players.active, Highlights_Players.id, Highlights_Players.playerId, Highlights_Players.highlightId FROM Players INNER JOIN Highlights_Players ON Highlights_Players.playerId = Players.id WHERE Highlights_Players.highlightId = 6657
SQL: SELECT Highlights_Events.id, Highlights_Events.highlightId, Highlights_Events.playerId, Highlights_Events.teamId, Highlights_Events.matchId, Highlights_Events.homeTeamId, Highlights_Events.awayTeamId, Highlights_Events.`name`, Highlights_Events.nameId, Highlights_Events.actionId, Highlights_Events.standartId, Highlights_Events.matchSecond, Highlights_Events.matchPeriod, Highlights_Events.`second` FROM Highlights_Events WHERE Highlights_Events.highlightId = 6657
SQL: SELECT Player_details.id, Player_details.playerId, Player_details.shortNameId, Player_details.firstNameId, Player_details.middleNameId, Player_details.lastNameId, Player_details.gender, Player_details.birthDate, Player_details.birthCityId, Player_details.height, Player_details.weight, Player_details.foot, Player_details.`role`, Player_details.status, Player_details.createdAt, Player_details.createdBy, Player_details.updatedAt, Player_details.updatedBy, Player_details.deletedAt, Player_details.deletedBy, Player_details.imageId, Player_details.active FROM Player_details WHERE Player_details.playerId IN (1193, 1246, 2454, 2458, 2536, 2552, 791)
SQL: SELECT Videos.id, Videos.`path`, Videos.pathWasabi, Videos.mimeType, Videos.createdAt, Videos.createdBy, Videos.updatedAt, Videos.updatedBy, Videos.deletedAt, Videos.deletedBy, Videos.active FROM Videos WHERE Videos.id = 25584
SQL: SELECT Images.id, Images.`path`, Images.mimeType, Images.transparentBackground, Images.createdAt, Images.createdBy, Images.updatedAt, Images.updatedBy, Images.deletedAt, Images.deletedBy, Images.active FROM Images WHERE Images.id = 47124
SQL: SELECT Match_details.id, Match_details.matchId, Match_details.`date`, Match_details.matchDay, Match_details.homeScore, Match_details.awayScore, Match_details.homeScore1T, Match_details.awayScore1T, Match_details.homeScore2T, Match_details.awayScore2T, Match_details.homeScore1HT, Match_details.awayScore1HT, Match_details.homeScore2HT, Match_details.awayScore2HT, Match_details.homeScoreP, Match_details.awayScoreP, Match_details.duration, Match_details.duration1T, Match_details.duration2T, Match_details.duration1HT, Match_details.duration2HT, Match_details.hasAnalytikAvailable, Match_details.createdAt, Match_details.createdBy, Match_details.updatedAt, Match_details.updatedBy, Match_details.deletedAt, Match_details.deletedBy, Match_details.active FROM Match_details WHERE Match_details.matchId = 3115
SQL: SELECT Match_details.id, Match_details.matchId, Match_details.`date`, Match_details.matchDay, Match_details.homeScore, Match_details.awayScore, Match_details.homeScore1T, Match_details.awayScore1T, Match_details.homeScore2T, Match_details.awayScore2T, Match_details.homeScore1HT, Match_details.awayScore1HT, Match_details.homeScore2HT, Match_details.awayScore2HT, Match_details.homeScoreP, Match_details.awayScoreP, Match_details.duration, Match_details.duration1T, Match_details.duration2T, Match_details.duration1HT, Match_details.duration2HT, Match_details.hasAnalytikAvailable, Match_details.createdAt, Match_details.createdBy, Match_details.updatedAt, Match_details.updatedBy, Match_details.deletedAt, Match_details.deletedBy, Match_details.active FROM Match_details WHERE Match_details.matchId = 3115
SQL: SELECT Match_details.id, Match_details.matchId, Match_details.`date`, Match_details.matchDay, Match_details.homeScore, Match_details.awayScore, Match_details.homeScore1T, Match_details.awayScore1T, Match_details.homeScore2T, Match_details.awayScore2T, Match_details.homeScore1HT, Match_details.awayScore1HT, Match_details.homeScore2HT, Match_details.awayScore2HT, Match_details.homeScoreP, Match_details.awayScoreP, Match_details.duration, Match_details.duration1T, Match_details.duration2T, Match_details.duration1HT, Match_details.duration2HT, Match_details.hasAnalytikAvailable, Match_details.createdAt, Match_details.createdBy, Match_details.updatedAt, Match_details.updatedBy, Match_details.deletedAt, Match_details.deletedBy, Match_details.active FROM Match_details WHERE Match_details.matchId = 3115
SQL: SELECT Teams.id, Teams.createdAt, Teams.createdBy, Teams.updatedAt, Teams.updatedBy, Teams.deletedAt, Teams.deletedBy, Teams.active FROM Teams WHERE Teams.id = 88

and so on...

有没有办法强制 Exposed 使用单个查询而不是多个查询,而不需要手动重写所有内容,使用单个复杂查询?

谢谢大家!


【解决方案1】:

【讨论】: