Skip to content

Instantly share code, notes, and snippets.

@nakamura-to

nakamura-to/entities.kt

Last active July 20, 2024 01:23
Show Gist options
  • Save nakamura-to/2f7f4baf3cec3d6729059e9b47e92385 to your computer and use it in GitHub Desktop.
Save nakamura-to/2f7f4baf3cec3d6729059e9b47e92385 to your computer and use it in GitHub Desktop.
Download ZIP
クエリの比較 - Komapper版
Raw
entities.kt
@KomapperEntity
@KomapperTable("users")
data class User(
@KomapperId
val id: String,
val name: String,
val age: Int
)
@KomapperEntity
@KomapperTable("user_purchase_histories")
data class UserPurchaseHistory(
@KomapperId
val id: String,
val userId: String,
val purchaseDate: java.time.LocalDate,
val price: Int
)
Raw
komapper.kt
// https://speakerdeck.com/n_takehata/kotlin-server-side-programming-practice-2024-makeup-classes?slide=37 以降を参考に
package integration.jdbc.mysql
import integration.jdbc.JdbcEnv
import integration.jdbc.User
import integration.jdbc.user
import integration.jdbc.userPurchaseHistory
import org.junit.jupiter.api.Test
import org.junit.jupiter.api.extension.ExtendWith
import org.komapper.core.dsl.Meta
import org.komapper.core.dsl.QueryDsl
import org.komapper.core.dsl.operator.count
import org.komapper.core.dsl.operator.desc
import org.komapper.core.dsl.operator.sum
import org.komapper.jdbc.JdbcDatabase
@ExtendWith(JdbcEnv::class)
class `クエリの比較 - Komapper版`(private val db: JdbcDatabase) {
private val dsl = QueryDsl
private val _user = Meta.user
private val _history = Meta.userPurchaseHistory
@Test
fun `基本的なCRUDのクエリ`() {
// Insert: カラムを指定する場合
dsl.insert(_user)
.values {
_user.id eq "kotlin"
_user.name eq "Kotlin Fest"
_user.age eq 3
}
.let(db::runQuery)
// Insert: エンティティを渡す場合
dsl.insert(_user)
.single(User("java", "Java Fest", 4))
.let(db::runQuery)
// Update
dsl.update(_user)
.set { _user.age eq 4 }
.where { _user.id eq "kotlin" }
.let(db::runQuery)
// Select
val users = dsl.from(_user)
.where { _user.age greaterEq 3 }
.select(_user.id, _user.name)
.let(db::runQuery)
// Delete
dsl.delete(_user)
.where { _user.id eq "kotlin" }
.let(db::runQuery)
}
@Test
fun `GROUP BY、ORDER BY、LIMIT、OFFSET`() {
val query = dsl.from(_user)
.groupBy(_user.age) // 次のテストケースで示すように省略可能
.orderBy(count(_user.id).desc())
.limit(3)
.offset(1)
.select(_user.age, count(_user.id))
db.runQuery(query)
}
@Test
fun `GROUP BY、ORDER BY、LIMIT、OFFSET - groupByの省略`() {
// groupBy関数を省略したとしてもselect関数の引数から自動的にGROUP BY句を生成する
val query = dsl.from(_user)
.orderBy(count(_user.id).desc())
.limit(3)
.offset(1)
.select(_user.age, count(_user.id))
db.runQuery(query)
}
@Test
fun `JOIN`() {
val query = dsl.from(_user)
.leftJoin(_history) { _user.id eq _history.userId }
db.runQuery(query)
}
@Test
fun ` サブクエリ`() {
val query = dsl.from(_user)
.where {
exists { dsl.from(_history).where { _user.id eq _history.userId } }
}
db.runQuery(query)
}
@Test
fun `JOINして絞り込む`() {
val query = dsl.from(_user)
.leftJoin(_history) { _user.id eq _history.userId }
.where { _user.age greaterEq 10 }
.groupBy(_user.id)
.having { sum(_history.price) greaterEq 3000 }
.select(_user.id, _user.name, sum(_history.price))
db.runQuery(query)
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment