Kotlin Exposed Framework Guide

Translation prepared as part of the recruitment of students for the course " Kotlin Backend Developer ".



We also invite everyone to a demo lesson "Object-Oriented Programming in Kotlin" . Lesson objectives:

- to learn about the elements of the Kotlin object model;

- create various classes and objects;

- perform inheritance and delegation;

- use getters and setters.






1. Introduction

In this article, we'll look at how to route queries to a relational database using 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)
    }
}
      
      



(, plusminus  . .) .





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.





GitHub.






"Kotlin Backend Developer".





«- Kotlin».








All Articles