Exposed

Exposed

Language
Kotlin
Category
4
Version
0.38.2
Authors
JetBrains
Description
Exposed is an ORM framework for Kotlin. Exposed offers two levels of database access: typesafe SQL wrapping DSL and lightweight data access objects. Our official mascot is Cuttlefish, which is best known for its outstanding mimicry abilities letting it blend seamlessly in any environment. Just like our mascot, Exposed can mimic a variety of database engines and help you build database applications without hard dependencies on any specific database engine, and switch between them with very little or no changes in your code.
Install Instructions
  • Supported Databases:
  • H2
  • MySQL
  • MariaDB
  • Oracle
  • PostgreSQL
  • PostgreSQL using the pgjdbc-ng JDBC driver
  • SQL Server
  • SQLite
  • Using Gradle Kotlin, add the following to build.gradle file:
repositories {
// Versions after 0.30.1
// Versions before 0.30.1 is unavailable for now
mavenCentral()
}
dependencies {
implementation("org.jetbrains.exposed", "exposed-core", "0.38.2")
implementation("org.jetbrains.exposed", "exposed-dao", "0.38.2")
implementation("org.jetbrains.exposed", "exposed-jdbc", "0.38.2")
}
Usage
  • SQL DSL
import org.jetbrains.exposed.sql.
import org.jetbrains.exposed.sql.transactions.transaction
object Users : Table() {
val id = varchar("id", 10) // Column<String>
val name = varchar("name", length = 50) // Column<String>
val cityId = (integer("city_id") references Cities.id).nullable() // Column<Int?>
override val primaryKey = PrimaryKey(id, name = "PK_User_ID") // name is optional here
}
object Cities : Table() {
val id = integer("id").autoIncrement() // Column<Int>
val name = varchar("name", 50) // Column<String>
override val primaryKey = PrimaryKey(id, name = "PK_Cities_ID")
}
fun main() {
Database.connect("jdbc:h2:mem:test", driver = "org.h2.Driver", user = "root", password = "")
transaction {
addLogger(StdOutSqlLogger)
SchemaUtils.create (Cities, Users)
val saintPetersburgId = Cities.insert {
it[name] = "St. Petersburg"
} get Cities.id
val munichId = Cities.insert {
it[name] = "Munich"
} get Cities.id
val pragueId = Cities.insert {
it.update(name, stringLiteral(" Prague ").trim().substring(1, 2))
}[Cities.id]
val pragueName = Cities.select { Cities.id eq pragueId }.single()[Cities.name]
assertEquals(pragueName, "Pr")
Users.insert {
it[id] = "andrey"
it[name] = "Andrey"
it[Users.cityId] = saintPetersburgId
}
Users.update({ Users.id eq "alex"}) {
it[name] = "Alexey"
}
Users.deleteWhere{ Users.name like "%thing"}
println("All cities:")
for (city in Cities.selectAll()) {
println("${city[Cities.id]}: ${city[Cities.name]}")
}
println("Manual join:")
(Users innerJoin Cities).slice(Users.name, Cities.name).
select {(Users.id.eq("andrey") or Users.name.eq("Sergey")) and
Users.id.eq("sergey") and Users.cityId.eq(Cities.id)}.forEach {
println("${it[Users.name]} lives in ${it[Cities.name]}")
}
println("Join with foreign key:")
(Users innerJoin Cities).slice(Users.name, Users.cityId, Cities.name).
select { Cities.name.eq("St. Petersburg") or Users.cityId.isNull()}.forEach {
if (it[Users.cityId] != null) {
println("${it[Users.name]} lives in ${it[Cities.name]}")
}
else {
println("${it[Users.name]} lives nowhere")
}
}
println("Functions and group by:")
((Cities innerJoin Users).slice(Cities.name, Users.id.count()).selectAll().groupBy(Cities.name)).forEach {
val cityName = it[Cities.name]
val userCount = it[Users.id.count()]
if (userCount > 0) {
println("$userCount user(s) live(s) in $cityName")
} else {
println("Nobody lives in $cityName")
}
}
SchemaUtils.drop (Users, Cities)
}
}
Links