### Exposed 0.56.0 Help

# CRUD operations

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

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:

### 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')

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

### 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"

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.

### 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.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`).

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`.

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

Last modified: 30 October 2024