CRUD (Create, Read, Update, Delete) are the four basic operations supported by any database. This section demonstrates how to perform SQL CRUD operations using Exposed's DAO (Data Access Object) API.
These operations can be performed directly through the methods and properties of the Entity class associated with the table. For more information, see Entity definition.
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 CompositeID:
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
To retrieve entities, use one of the following methods:
all
To get all the entity instances associated with this entity class, use the all() function:
val allMovies = StarWarsFilmEntity.all()
find
To get all the entity instances that conform to the conditional expression, use the find() function:
val specificMovie = StarWarsFilmEntity.find { StarWarsFilmsTable.sequelId eq MOVIE_SEQUELID }
findById
To get an entity by its id value, use the findById() function:
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')
Read an entity with a join to another table
Suppose that you want to find all users who rated the second Star Wars film with a score greater than 5. First, you would write that query using Exposed DSL.
val query = UsersTable.innerJoin(UserRatingsTable).innerJoin(StarWarsFilmsTable)
.select(UsersTable.columns)
.where {
StarWarsFilmsTable.sequelId eq MOVIE_SEQUELID and (UserRatingsTable.value greater MIN_MOVIE_RATING)
}.withDistinct()
Once the query is defined, you can wrap the result in the User entity using the wrapRows() function to generate entities from the retrieved data:
val users = UserEntity.wrapRows(query).toList()
Sort results
sortedBy
To sort results in ascending order, use sortedBy:
val moviesByAscOrder = StarWarsFilmEntity.all().sortedBy { it.sequelId }
sortedByDescending
To sort results in descending order, use sortedByDescending:
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"
findByIdAndUpdate
To search for an entity by its id and apply an update, use the findByIdAndUpdate() function:
val updatedMovie = StarWarsFilmEntity.findByIdAndUpdate(2) {
it.name = "Episode VIII – The Last Jedi"
}
findSingleByAndUpdate
To search for a single entity by a query and apply an update, use the findSingleByAndUpdate() function:
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 searchQuery(). 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.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).
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.