JetBrains/Exposed

import org.jetbrains.exposed.v1.core.*
import org.jetbrains.exposed.v1.core.SqlExpressionBuilder.like
import org.jetbrains.exposed.v1.jdbc.*
import org.jetbrains.exposed.v1.jdbc.transactions.transaction

object Cities : Table() {
    val id = integer("id").autoIncrement()
    val name = varchar("name", 50)

    override val primaryKey = PrimaryKey(id)
}

object Users : Table() {
    val id = varchar("id", 10)
    val name = varchar("name", length = 50)
    val cityId = integer("city_id").references(Cities.id).nullable()

    override val primaryKey = PrimaryKey(id, name = "PK_User_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
            .selectAll()
            .where { Cities.id eq pragueId }
            .single()[Cities.name]
        println("pragueName = $pragueName")

        Users.insert {
            it[id] = "andrey"
            it[name] = "Andrey"
            it[cityId] = saintPetersburgId
        }

        Users.insert {
            it[id] = "sergey"
            it[name] = "Sergey"
            it[cityId] = munichId
        }

        Users.insert {
            it[id] = "eugene"
            it[name] = "Eugene"
            it[cityId] = munichId
        }

        Users.insert {
            it[id] = "alex"
            it[name] = "Alex"
            it[cityId] = null
        }

        Users.insert {
            it[id] = "smth"
            it[name] = "Something"
            it[cityId] = null
        }

        Users.update(where = { Users.id eq "alex" }) {
            it[name] = "Alexey"
        }

        Users.deleteWhere { Users.name like "%thing" }

        println("All cities:")

        Cities
            .selectAll()
            .forEach { result ->
                println("${result[Cities.id]}: ${result[Cities.name]}")
            }

        println("Manual join:")

        (Users innerJoin Cities)
            .select(Users.name, Cities.name)
            .where {
                (Users.id.eq("andrey") or Users.name.eq("Sergey")) and
                    Users.id.eq("sergey") and Users.cityId.eq(Cities.id)
            }.forEach { result ->
                println("${result[Users.name]} lives in ${result[Cities.name]}")
            }

        println("Join with foreign key:")

        (Users innerJoin Cities)
            .select(Users.name, Users.cityId, Cities.name)
            .where { Cities.name.eq("St. Petersburg") or Users.cityId.isNull() }
            .forEach { result ->
                if (result[Users.cityId] != null) {
                    println("${result[Users.name]} lives in ${result[Cities.name]}")
                } else {
                    println("${result[Users.name]} lives nowhere")
                }
            }

        println("Functions and group by:")

        (Cities innerJoin Users)
            .select(Cities.name, Users.id.count())
            .groupBy(Cities.name)
            .forEach { result ->
                val cityName = result[Cities.name]
                val userCount = result[Users.id.count()]

                if (userCount > 0) {
                    println("$userCount user(s) live(s) in $cityName")
                } else {
                    println("Nobody lives in $cityName")
                }
            }

        SchemaUtils.drop(Users, Cities)
    }
}

    SQL: CREATE TABLE IF NOT EXISTS CITIES (ID INT AUTO_INCREMENT PRIMARY KEY, "name" VARCHAR(50) NOT NULL)
    SQL: CREATE TABLE IF NOT EXISTS USERS (ID VARCHAR(10), "name" VARCHAR(50) NOT NULL, CITY_ID INT NULL, CONSTRAINT PK_User_ID PRIMARY KEY (ID), CONSTRAINT FK_USERS_CITY_ID__ID FOREIGN KEY (CITY_ID) REFERENCES CITIES(ID) ON DELETE RESTRICT ON UPDATE RESTRICT)
    SQL: INSERT INTO CITIES ("name") VALUES ('St. Petersburg')
    SQL: INSERT INTO CITIES ("name") VALUES ('Munich')
    SQL: INSERT INTO CITIES ("name") VALUES (SUBSTRING(TRIM('   Prague   '), 1, 2))
    SQL: SELECT CITIES.ID, CITIES."name" FROM CITIES WHERE CITIES.ID = 3
    pragueName = Pr
    SQL: INSERT INTO USERS (ID, "name", CITY_ID) VALUES ('andrey', 'Andrey', 1)
    SQL: INSERT INTO USERS (ID, "name", CITY_ID) VALUES ('sergey', 'Sergey', 2)
    SQL: INSERT INTO USERS (ID, "name", CITY_ID) VALUES ('eugene', 'Eugene', 2)
    SQL: INSERT INTO USERS (ID, "name", CITY_ID) VALUES ('alex', 'Alex', NULL)
    SQL: INSERT INTO USERS (ID, "name", CITY_ID) VALUES ('smth', 'Something', NULL)
    SQL: UPDATE USERS SET "name"='Alexey' WHERE USERS.ID = 'alex'
    SQL: DELETE FROM USERS WHERE USERS."name" LIKE '%thing'
    All cities:
    SQL: SELECT CITIES.ID, CITIES."name" FROM CITIES
    1: St. Petersburg
    2: Munich
    3: Pr
    Manual join:
    SQL: SELECT USERS."name", CITIES."name" FROM USERS INNER JOIN CITIES ON CITIES.ID = USERS.CITY_ID WHERE ((USERS.ID = 'andrey') OR (USERS."name" = 'Sergey')) AND (USERS.ID = 'sergey') AND (USERS.CITY_ID = CITIES.ID)
    Sergey lives in Munich
    Join with foreign key:
    SQL: SELECT USERS."name", USERS.CITY_ID, CITIES."name" FROM USERS INNER JOIN CITIES ON CITIES.ID = USERS.CITY_ID WHERE (CITIES."name" = 'St. Petersburg') OR (USERS.CITY_ID IS NULL)
    Andrey lives in St. Petersburg
    Functions and group by:
    SQL: SELECT CITIES."name", COUNT(USERS.ID) FROM CITIES INNER JOIN USERS ON CITIES.ID = USERS.CITY_ID GROUP BY CITIES."name"
    2 user(s) live(s) in Munich
    1 user(s) live(s) in St. Petersburg
    SQL: DROP TABLE IF EXISTS USERS
    SQL: DROP TABLE IF EXISTS CITIES

import org.jetbrains.exposed.v1.core.StdOutSqlLogger
import org.jetbrains.exposed.v1.core.dao.id.*
import org.jetbrains.exposed.v1.dao.*
import org.jetbrains.exposed.v1.jdbc.*
import org.jetbrains.exposed.v1.jdbc.transactions.transaction

object Cities: IntIdTable() {
    val name = varchar("name", 50)
}

object Users : IntIdTable() {
    val name = varchar("name", length = 50).index()
    val city = reference("city", Cities)
    val age = integer("age")
}

class City(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<City>(Cities)

    var name by Cities.name
    val users by User referrersOn Users.city
}

class User(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<User>(Users)

    var name by Users.name
    var city by City referencedOn Users.city
    var age by Users.age
}

fun main() {
    Database.connect("jdbc:h2:mem:test", driver = "org.h2.Driver", user = "root", password = "")

    transaction {
        addLogger(StdOutSqlLogger)

        val saintPetersburg = City.new {
            name = "St. Petersburg"
        }

        val munich = City.new {
            name = "Munich"
        }

        User.new {
            name = "Andrey"
            city = saintPetersburg
            age = 5
        }

        User.new {
            name = "Sergey"
            city = saintPetersburg
            age = 27
        }

        User.new {
            name = "Eugene"
            city = munich
            age = 42
        }

        val alex = User.new {
            name = "alex"
            city = munich
            age = 11
        }

        alex.name = "Alexey"

        println("Cities: ${City.all().joinToString { it.name }}")

        println("Users in ${saintPetersburg.name}: ${saintPetersburg.users.joinToString { it.name }}")

        println("Adults: ${User.find { Users.age greaterEq 18 }.joinToString { it.name }}")

        SchemaUtils.drop(Users, Cities)
    }
}

    SQL: CREATE TABLE IF NOT EXISTS CITIES (ID INT AUTO_INCREMENT PRIMARY KEY, "name" VARCHAR(50) NOT NULL)
    SQL: CREATE TABLE IF NOT EXISTS USERS (ID INT AUTO_INCREMENT PRIMARY KEY, "name" VARCHAR(50) NOT NULL, CITY INT NOT NULL, AGE INT NOT NULL, CONSTRAINT FK_USERS_CITY__ID FOREIGN KEY (CITY) REFERENCES CITIES(ID) ON DELETE RESTRICT ON UPDATE RESTRICT)
    SQL: CREATE INDEX USERS_NAME ON USERS ("name")
    SQL: INSERT INTO CITIES ("name") VALUES ('St. Petersburg')
    SQL: INSERT INTO CITIES ("name") VALUES ('Munich')
    SQL: SELECT CITIES.ID, CITIES."name" FROM CITIES
    Cities: St. Petersburg, Munich
    SQL: INSERT INTO USERS ("name", CITY, AGE) VALUES ('Andrey', 1, 5)
    SQL: INSERT INTO USERS ("name", CITY, AGE) VALUES ('Sergey', 1, 27)
    SQL: INSERT INTO USERS ("name", CITY, AGE) VALUES ('Eugene', 2, 42)
    SQL: INSERT INTO USERS ("name", CITY, AGE) VALUES ('Alexey', 2, 11)
    SQL: SELECT USERS.ID, USERS."name", USERS.CITY, USERS.AGE FROM USERS WHERE USERS.CITY = 1
    Users in St. Petersburg: Andrey, Sergey
    SQL: SELECT USERS.ID, USERS."name", USERS.CITY, USERS.AGE FROM USERS WHERE USERS.AGE >= 18
    Adults: Sergey, Eugene
    SQL: DROP TABLE IF EXISTS USERS
    SQL: DROP TABLE IF EXISTS CITIES