CRUD operations

Create

To create a new table row, use the .new() function on the entity class:

val movie = StarWarsFilmEntity.new {
    name = "The Last Jedi"
    sequelId = MOVIE_SEQUEL_ID
    director = "Rian Johnson"
}

In the above example StarWarsFilmEntity would be the entity instance linked to the StarWarsFilmsTable table.

To provide a manual id value to a new entity, pass the value as an argument to the id parameter:

val movie2 = StarWarsFilmEntity.new(id = 2) {
    name = "The Rise of Skywalker"
    sequelId = MOVIE2_SEQUEL_ID
    director = "J.J. Abrams"
}

If the entity is a CompositeEntity, the ID value can be constructed by creating a component column-to-value association using the CompositeID class:

val directorId = CompositeID {
    it[DirectorsTable.name] = "J.J. Abrams"
    it[DirectorsTable.guildId] = UUID.randomUUID()
}

val director = DirectorEntity.new(directorId) {
    genre = Genre.SCI_FI
}

If CompositeID contains autoincrement or autogenerated columns, values for such columns are not required to be provided in CompositeID building block. In this case they will be normally generated by database.

Read

To read a value from a property, simply access it as you would with any property in a Kotlin class:

val movieName = movie.name

note

An entity's id property is wrapped as an instance of the EntityID class. To access the actual wrapped value, for example the stored Int from a StarWarsFilm entity, use EntityID.value:

val movieId: Int = movie.id.value

To retrieve entities, use one of the following methods:

val allMovies = StarWarsFilmEntity.all()

val specificMovie = StarWarsFilmEntity.find { StarWarsFilmsTable.sequelId eq MOVIE_SEQUELID }

val movie = StarWarsFilmEntity.findById(2)

If the entity is a CompositeEntity, its id property can be used to refer to all composite columns and to get entities, much like the id column of its associated CompositeIdTable:

val directorId = CompositeID {
    it[DirectorsTable.name] = "J.J. Abrams"
    it[DirectorsTable.guildId] = UUID.randomUUID()
}

val director = DirectorEntity.findById(directorId)

val directors = DirectorEntity.find { DirectorsTable.id eq directorId }

The SQL query would result in something like the following:

SELECT DIRECTORS."name", DIRECTORS.GUILD_ID, DIRECTORS.GENRE
FROM DIRECTORS
WHERE (DIRECTORS."name" = 'J.J. Abrams')
AND (DIRECTORS.GUILD_ID = '2cc64f4f-1a2c-41ce-bda1-ee492f787f4b')

tip

For a list of available predicates, see DSL Where expression.

val query = UsersTable.innerJoin(UserRatingsTable).innerJoin(StarWarsFilmsTable)
    .select(UsersTable.columns)
    .where {
        StarWarsFilmsTable.sequelId eq MOVIE_SEQUELID and (UserRatingsTable.value greater MIN_MOVIE_RATING.toLong())
    }.withDistinct()

val users = UserEntity.wrapRows(query).toList()

val moviesByAscOrder = StarWarsFilmEntity.all().sortedBy { it.sequelId }

val moviesByDescOrder = StarWarsFilmEntity.all().sortedByDescending { it.sequelId }

Update

You can update the value of a property just as you would with any property in a Kotlin class:

movie.name = "Episode VIII – The Last Jedi"

note

Exposed doesn't make an immediate update when you set a new value for Entity, it just stores it on the inner map. "Flushing" values to the database occurs at the end of the transaction, or before the next SELECT * from the database.

val updatedMovie = StarWarsFilmEntity.findByIdAndUpdate(2) {
    it.name = "Episode VIII – The Last Jedi"
}

val updatedMovie2 = StarWarsFilmEntity.findSingleByAndUpdate(StarWarsFilmsTable.name eq "The Last Jedi") {
    it.name = "Episode VIII – The Last Jedi"
}

Delete

To delete a record, use the .delete() function:

movie.delete()

Use queries as expressions

Suppose that you want to sort cities by the number of users in each city. To achieve this, you can write a subquery which counts the users in each city and then order the result by that count. To do so, however, you need to convert the Query to an Expression.

This can be done using the wrapAsExpression() function:

val expression = wrapAsExpression<Int>(
    UsersTable.select(UsersTable.id.count())
        .where { CitiesTable.id eq UsersTable.cityId }
)

val cities = CitiesTable.selectAll()
    .orderBy(expression, SortOrder.DESC)
    .toList()

Add computed fields to entity class

Suppose that you want to use a window function to rank films with each entity fetch. The companion object of the entity class can override any open function in EntityClass. However, to achieve this functionality, you only need to override the searchQuery() function. The results of the function can then be accessed through a property of the entity class:

package org.example.entities

import org.example.tables.StarWarsFilmsWithRankTable
import org.jetbrains.exposed.dao.IntEntity
import org.jetbrains.exposed.dao.IntEntityClass
import org.jetbrains.exposed.dao.id.EntityID
import org.jetbrains.exposed.sql.Op
import org.jetbrains.exposed.sql.Query

class StarWarsFilmWithRankEntity(id: EntityID<Int>) : IntEntity(id) {
    var sequelId by StarWarsFilmsWithRankTable.sequelId
    var name by StarWarsFilmsWithRankTable.name
    var rating by StarWarsFilmsWithRankTable.rating

    val rank: Long
        get() = readValues[StarWarsFilmsWithRankTable.rank]

    companion object : IntEntityClass<StarWarsFilmWithRankEntity>(StarWarsFilmsWithRankTable) {
        override fun searchQuery(op: Op<Boolean>): Query {
            return super.searchQuery(op).adjustSelect {
                select(columns + StarWarsFilmsWithRankTable.rank)
            }
        }
    }
}

package org.example.tables

import org.jetbrains.exposed.dao.id.IntIdTable
import org.jetbrains.exposed.sql.Rank
import org.jetbrains.exposed.sql.SortOrder

const val MAX_NAME_LENGTH = 32

object StarWarsFilmsWithRankTable : IntIdTable() {
    val sequelId = integer("sequel_id").uniqueIndex()
    val name = varchar("name", MAX_NAME_LENGTH)
    val rating = double("rating")

    val rank = Rank().over().orderBy(rating, SortOrder.DESC)
}

Then, creating and fetching entities would look like this:

fun readComputedField() {
    transaction {
        StarWarsFilmWithRankEntity.new {
            sequelId = MOVIE_SEQUELID
            name = "The Last Jedi"
            rating = MOVIE_RATING
        }
    }

    transaction {
        StarWarsFilmWithRankEntity
            .find { StarWarsFilmsWithRankTable.name like "The%" }
            .map { it.name to it.rank }
    }
}

Auto-fill columns on entity change

Suppose that you need all your table objects to have at minimum two columns for storing the date and time when a record is created and modified. You could define a BaseTable to automatically handle registering these columns to any table that extends this abstract class. An abstract BaseEntity could also be defined to automatically associate fields to the relevant columns:

package org.example.tables

import kotlinx.datetime.LocalDateTime
import org.jetbrains.exposed.dao.id.IntIdTable
import org.jetbrains.exposed.sql.Column
import org.jetbrains.exposed.sql.kotlin.datetime.CurrentDateTime
import org.jetbrains.exposed.sql.kotlin.datetime.datetime

abstract class BaseTable(name: String = "") : IntIdTable(name) {
    val created: Column<LocalDateTime> = datetime("created")
        .defaultExpression(CurrentDateTime)
    val modified: Column<LocalDateTime?> = datetime("updated").nullable()
}

package org.example.entities

import kotlinx.datetime.LocalDateTime
import org.example.tables.BaseTable
import org.jetbrains.exposed.dao.IntEntity
import org.jetbrains.exposed.dao.id.EntityID

abstract class BaseEntity(id: EntityID<Int>, table: BaseTable) : IntEntity(id) {
    val created: LocalDateTime by table.created
    var modified: LocalDateTime? by table.modified
}

Whenever a new entity is instantiated, the created column will be filled with the database default defined by CurrentDateTime, while the modified column will be filled with a null value.

An entity lifecycle interceptor, EntityHook, can then be subscribed to in order to automatically populate the modified field whenever an existing entity is later updated:

package org.example.entities

import kotlinx.datetime.Clock
import kotlinx.datetime.LocalDateTime
import kotlinx.datetime.TimeZone
import kotlinx.datetime.toLocalDateTime
import org.example.tables.BaseTable
import org.jetbrains.exposed.dao.*
import org.jetbrains.exposed.sql.Column
import org.jetbrains.exposed.sql.exposedLogger

abstract class BaseEntityClass<out E : BaseEntity>(
    table: BaseTable
) : IntEntityClass<E>(table) {
    init {
        EntityHook.subscribe { change ->
            val changedEntity = change.toEntity(this)
            when (val type = change.changeType) {
                EntityChangeType.Updated -> {
                    val now = nowUTC()
                    changedEntity?.let {
                        if (it.writeValues[table.modified as Column<Any?>] == null) {
                            it.modified = now
                        }
                    }
                    logChange(changedEntity, type, now)
                }
                else -> logChange(changedEntity, type)
            }
        }
    }

    private fun nowUTC() = Clock.System.now().toLocalDateTime(TimeZone.UTC)

    private fun logChange(entity: E?, type: EntityChangeType, dateTime: LocalDateTime? = null) {
        entity?.let {
            val entityClassName = this::class.java.enclosingClass.simpleName
            exposedLogger.info(
                "$entityClassName(${it.id}) ${type.name.lowercase()} at ${dateTime ?: nowUTC()}"
            )
        }
    }
}

The example above invokes EntityHook.subscribe() in an abstract BaseEntityClass that can be used as a companion object for any BaseEntity implementation, but the interceptor could be subscribed to (and unsubscribed from) on the transaction level as well.

The subscription only performs an action when a record is updated (detected by EntityChangeType.Updated).

tip

Other actions could be detected for when an entity is created (EntityChangeType.Created) or removed (EntityChangeType.Removed).

When an update occurs, the modified column is populated with the current UTC time using the nowUTC() method. However, the update only happens if the modified field has not already been set. This is checked by using Entity.writeValues, which holds the column-value mappings for an entity instance before being flushed to the database. Performing this check ensures that filling the modified column does not trigger the interceptor itself.

Additionally, every change (creation, update, or deletion) is logged using the exposedLogger.

tip

For more details about using statement interceptors, see DSL Statement Interceptors.