Traducción elaborada como parte del reclutamiento de estudiantes para el curso " Kotlin Backend Developer ".
También invitamos a todos a una lección de demostración "Programación orientada a objetos en Kotlin" . Objetivos de la lección:
- aprender sobre los elementos del modelo de objetos de Kotlin;
- crear varias clases y objetos;
- realizar herencia y delegación;
- use captadores y definidores.
1. Introducción
En este artículo, veremos cómo enrutar consultas a una base de datos relacional usando Exposed .
Exposed — , JetBrains. Apache API Kotlin .
Exposed DSL SQL, ORM (- ). .
2.
Exposed Maven Central, :
<repositories>
<repository>
<id>exposed</id>
<name>exposed</name>
<url>https://dl.bintray.com/kotlin/exposed</url>
</repository>
</repositories>
:
<dependency>
<groupId>org.jetbrains.exposed</groupId>
<artifactId>exposed</artifactId>
<version>0.10.4</version>
</dependency>
H2 :
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>1.4.197</version>
</dependency>
Exposed Bintray, H2 — Maven Central.
3.
, Database
:
Database.connect("jdbc:h2:mem:test", driver = "org.h2.Driver")
(user) (password) :
Database.connect(
"jdbc:h2:mem:test", driver = "org.h2.Driver",
user = "myself", password = "secret")
: connect
. .
3.1.
, connect
, :
Database.connect({ DriverManager.getConnection("jdbc:h2:mem:test;MODE=MySQL") })
. Exposed .
3.2. DataSource
DataSource
( , , ), connect
:
Database.connect(datasource)
4.
Exposed .
transaction
.
transaction {
//Do cool stuff
}
transaction
, . Exposed .
4.1.
transaction Exposed . , .
. Kotlin , transaction
, Transaction
.
, commit
rollback
:
transaction {
//Do some stuff
commit()
//Do other stuff
}
4.2.
SQL, Exposed .
:
transaction {
addLogger(StdOutSqlLogger)
//Do stuff
}
5.
, Exposed SQL. , , , . . DSL.
AD
Table
:
object StarWarsFilms : Table()
Exposed , :
object StarWarsFilms : Table("STAR_WARS_FILMS")
5.1.
. Table
:
object StarWarsFilms : Table() {
val id = integer("id").autoIncrement().primaryKey()
val sequelId = integer("sequel_id").uniqueIndex()
val name = varchar("name", 50)
val director = varchar("director", 50)
}
— Kotlin . Column<T>
, , , , .
5.2.
, API.
, , Exposed IntIdTable
LongIdTable
:
object StarWarsFilms : IntIdTable() {
val sequelId = integer("sequel_id").uniqueIndex()
val name = varchar("name", 50)
val director = varchar("director", 50)
}
UUIDTable
, , IdTable
.
5.3.
. , , .
, , :
object Players : Table() {
val sequelId = integer("sequel_id")
.uniqueIndex()
.references(StarWarsFilms.sequelId)
val name = varchar("name", 50)
}
( — integer), , reference
:
val sequelId = reference("sequel_id", StarWarsFilms.sequelId).uniqueIndex()
, :
val filmId = reference("film_id", StarWarsFilms)
5.4.
, :
transaction {
SchemaUtils.create(StarWarsFilms, Players)
//Do stuff
}
, . .
6.
, , , .
6.1.
, Query
, . :
val query = StarWarsFilms.selectAll()
forEach
:
query.forEach {
assertTrue { it[StarWarsFilms.sequelId] >= 7 }
}
, it
, — ResultRow
. .
6.2.
, , slice
:
StarWarsFilms.slice(StarWarsFilms.name, StarWarsFilms.director).selectAll()
.forEach {
assertTrue { it[StarWarsFilms.name].startsWith("The") }
}
:
StarWarsFilms.slice(StarWarsFilms.name.countDistinct())
, count
avg
, , . 6.5.
6.3. where
Exposed where, , DSL. - , , .
where
:
{ (StarWarsFilms.director like "J.J.%") and (StarWarsFilms.sequelId eq 7) }
SqlExpressionBuilder
, , like
, eq
, and
. , , and
or
.
select
, :
val select = StarWarsFilms.select { ... }
assertEquals(1, select.count())
, complex
where
, select
, .
Kotlin where , . :
val sequelNo = 7
StarWarsFilms.select { StarWarsFilms.sequelId >= sequelNo }
6.4.
, select
.
, :
query.withDistinct(true).forEach { ... }
, :
query.limit(20, offset = 40).forEach { ... }
Query, .
6.5. orderBy groupBy
Query.orderBy
, SortOrder
, — :
query.orderBy(StarWarsFilms.name to SortOrder.ASC)
(. 6.2). groupBy
:
StarWarsFilms .slice(StarWarsFilms.sequelId.count(), StarWarsFilms.director) .selectAll() .groupBy(StarWarsFilms.director)
6.6.
— , , . . , :
(StarWarsFilms innerJoin Players).selectAll()
innerJoin
, LEFT JOIN
, RIGHT JOIN
CROSS JOIN
.
, where
; , , :
(StarWarsFilms innerJoin Players) .select { StarWarsFilms.sequelId eq Players.sequelId }
:
val complexJoin = Join(
StarWarsFilms, Players,
onColumn = StarWarsFilms.sequelId, otherColumn = Players.sequelId,
joinType = JoinType.INNER,
additionalConstraint = { StarWarsFilms.sequelId eq 8 })
complexJoin.selectAll()
6.7.
, , :
(StarWarsFilms innerJoin Players) .selectAll() .forEach { assertEquals(it[StarWarsFilms.sequelId], it[Players.sequelId]) }
StarWarsFilms.sequelId
Players.sequelId
— .
, , . alias
:
val sequel = StarWarsFilms.alias("sequel")
:
Join(StarWarsFilms, sequel,
additionalConstraint = {
sequel[StarWarsFilms.sequelId] eq StarWarsFilms.sequelId + 1
}).selectAll().forEach {
assertEquals(
it[sequel[StarWarsFilms.sequelId]], it[StarWarsFilms.sequelId] + 1)
}
sequel
— , . , , :
sequel[StarWarsFilms.sequelId]
7.
, . DML-.
7.1.
, , insert. :
StarWarsFilms.insert {
it[name] = "The Last Jedi"
it[sequelId] = 8
it[director] = "Rian Johnson"
}
:
this
( ) —StarWarsFilms
; , , ;
it
( ) —InsertStatement
; , /, .
7.2.
insert ( ), .
. insertAndGetId
:
val id = StarWarsFilms.insertAndGetId {
it[name] = "The Last Jedi"
it[sequelId] = 8
it[director] = "Rian Johnson"
}
assertEquals(1, id.value)
, :
val insert = StarWarsFilms.insert {
it[name] = "The Force Awakens"
it[sequelId] = 7
it[director] = "J.J. Abrams"
}
assertEquals(2, insert[StarWarsFilms.id]?.value)
7.3.
. , . select insert:
StarWarsFilms.update ({ StarWarsFilms.sequelId eq 8 }) {
it[name] = "Episode VIII – The Last Jedi"
}
where UpdateStatement
. UpdateStatement
InsertStatement
— UpdateBuilder
, API . .
, SqlExpressionBuilder
:
StarWarsFilms.update ({ StarWarsFilms.sequelId eq 8 }) {
with(SqlExpressionBuilder) {
it.update(StarWarsFilms.sequelId, StarWarsFilms.sequelId + 1)
}
}
(, plus, minus . .) .
7.4.
, deleteWhere
:
StarWarsFilms.deleteWhere ({ StarWarsFilms.sequelId eq 8 })
8. API DAO, ORM
Exposed , Kotlin SQL . , insert
, update
, select
. ., SQL .
Exposed API DAO, ORM. .
8.1.
.
, :
class StarWarsFilm(id: EntityID<Int>) : Entity<Int>(id) {
companion object : EntityClass<Int, StarWarsFilm>(StarWarsFilms)
var sequelId by StarWarsFilms.sequelId
var name by StarWarsFilms.name
var director by StarWarsFilms.director
}
.
, — , Entity
. ID , — Int
.
class StarWarsFilm(id: EntityID<Int>) : Entity<Int>(id) {
-. - — , , , .
-, StarWarsFilm
( ), , StarWarsFilms
( ), .
companion object : EntityClass<Int, StarWarsFilm>(StarWarsFilms)
, .
var sequelId by StarWarsFilms.sequelId
var name by StarWarsFilms.name
var director by StarWarsFilms.director
, val, . var, .
8.2.
, new
:
val theLastJedi = StarWarsFilm.new {
name = "The Last Jedi"
sequelId = 8
director = "Rian Johnson"
}
: , warm cache. Hibernate, , (session).
. , , Exposed
insert
:
assertEquals(1, theLastJedi.id.value) //Reading the ID causes a flush
insert
, 7.1, — . .
8.3.
:
theLastJedi.name = "Episode VIII – The Last Jedi"
delete
:
theLastJedi.delete()
, new
, .
. API , API (. 7). API.
8.4.
API DAO .
, , all
:
val movies = StarWarsFilm.all()
ID findById
:
val theLastJedi = StarWarsFilm.findById(1)
ID , findById
null
.
find where:
val movies = StarWarsFilm.find { StarWarsFilms.sequelId eq 8 }
8.5. « »
ORM , . , Exposed.
, . :
object Users: IntIdTable() {
val name = varchar("name", 50)
}
object UserRatings: IntIdTable() {
val value = long("value")
val film = reference("film", StarWarsFilms)
val user = reference("user", Users)
}
. User ( ) UserRating
:
class UserRating(id: EntityID<Int>): IntEntity(id) {
companion object : IntEntityClass<UserRating>(UserRatings)
var value by UserRatings.value
var film by StarWarsFilm referencedOn UserRatings.film
var user by User referencedOn UserRatings.user
}
: referencedOn , . : var
(by
) (referencedOn
).
, , , :
val someUser = User.new {
name = "Some User"
}
val rating = UserRating.new {
value = 9
user = someUser
film = theLastJedi
}
assertEquals(theLastJedi, rating.film)
8.6.
— .
, null
:
val user = reference("user", Users).nullable()
referencedOn
optionalReferencedOn
:
var user by User optionalReferencedOn UserRatings.user
, user
null
.
8.7. « »
. ; .
, «» . film
:
class StarWarsFilm(id: EntityID<Int>) : Entity<Int>(id) {
//Other properties elided
val ratings by UserRating referrersOn UserRatings.film
}
« », referrersOn
. , , , forEach
:
theLastJedi.ratings.forEach { ... }
, ratings
val
. , .
API . , :
UserRating.new {
value = 8
user = someUser
film = theLastJedi
}
.
8.8. « »
« ». , StarWarsFilm
Actors
:
object Actors: IntIdTable() {
val firstname = varchar("firstname", 50)
val lastname = varchar("lastname", 50)
}
class Actor(id: EntityID<Int>): IntEntity(id) {
companion object : IntEntityClass<Actor>(Actors)
var firstname by Actors.firstname
var lastname by Actors.lastname
}
, :
object StarWarsFilmActors : Table() {
val starWarsFilm = reference("starWarsFilm", StarWarsFilms).primaryKey(0)
val actor = reference("actor", Actors).primaryKey(1)
}
, , .
StarWarsFilm
:
class StarWarsFilm(id: EntityID<Int>) : IntEntity(id) {
companion object : IntEntityClass<StarWarsFilm>(StarWarsFilms)
//Other properties elided
var actors by Actor via StarWarsFilmActors
}
« » .
:
//First, create the film
val film = transaction {
StarWarsFilm.new {
name = "The Last Jedi"
sequelId = 8
director = "Rian Johnson"r
}
}
//Then, create the actor
val actor = transaction {
Actor.new {
firstname = "Daisy"
lastname = "Ridley"
}
}
//Finally, link the two together
transaction {
film.actors = SizedCollection(listOf(actor))
}
, .
9.
Kotlin Exposed. - Exposed.