Exposed

https://www.jetbrains.com/help/exposed/
Admin
Exposed is a Kotlin SQL database library offering a lightweight ORM (using DAO) and type-safe SQL...
Tokens:154,326
Snippets:1,407
Trust Score:10
Update:6 days ago
Show doc for...
Tokens:
Raw
### New select() and where() DSL migration examples

Source: https://www.jetbrains.com/help/exposed/migration-guide-0-46-0.html

Shows before/after examples for the new query DSL: slice() replaced by select(), and select { } replaced by selectAll().where { }.

```kotlin
// Example 1
// before
TestTable
    .slice(TestTable.columnA)
    .select { TestTable.columnA eq 1 }

// after
TestTable
    .select(TestTable.columnA)
    .where { TestTable.columnA eq 1 }

// Example 2
// before
TestTable
    .slice(TestTable.columnA)
    .selectAll()

// after
TestTable
    .select(TestTable.columnA)

// Example 3
// before
TestTable
    .select { TestTable.columnA eq 1 }

// after
TestTable
    .selectAll()
    .where { TestTable.columnA eq 1 }

// Example 4 - no change
TestTable.selectAll()
```

--------------------------------

### 0.61.0 UUIDTable and UUIDEntity example

Source: https://www.jetbrains.com/help/exposed/migration-guide-1-0-0.html

Shows the original table and entity definitions using java.util.UUID with UUIDTable, UUIDEntity, and UUIDEntityClass. This is the starting point for migration to 1.0.0.

```kotlin
import org.jetbrains.exposed.dao.id.*
import org.jetbrains.exposed.dao.*
import java.util.UUID

object TestTable : UUIDTable("tester") {
    val secondaryId = uuid("secondary_id").uniqueIndex()
}

class TestEntity(id: EntityID<UUID>) : UUIDEntity(id) {
    companion object : UUIDEntityClass<TestEntity>(TestTable)

    var secondaryId by TestTable.secondaryId
}
```

--------------------------------

### Full transaction example with table creation and queries

Source: https://www.jetbrains.com/help/exposed/get-started-with-exposed.html

Complete example showing how to connect to a database, create a table, insert records, and run a grouped query within a transaction. Requires the Tasks table to be defined elsewhere.

```kotlin
package org.example

import Tasks
import org.jetbrains.exposed.v1.core.*
import org.jetbrains.exposed.v1.jdbc.Database
import org.jetbrains.exposed.v1.jdbc.SchemaUtils
import org.jetbrains.exposed.v1.jdbc.deleteWhere
import org.jetbrains.exposed.v1.jdbc.insert
import org.jetbrains.exposed.v1.jdbc.select
import org.jetbrains.exposed.v1.jdbc.selectAll
import org.jetbrains.exposed.v1.jdbc.transactions.transaction
import org.jetbrains.exposed.v1.jdbc.update

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

    transaction {
        SchemaUtils.create(Tasks)

        val taskId = Tasks.insert {
            it[title] = "Learn Exposed"
            it[description] = "Go through the Get started with Exposed tutorial"
        } get Tasks.id

        val secondTaskId = Tasks.insert {
            it[title] = "Read The Hobbit"
            it[description] = "Read the first two chapters of The Hobbit"
            it[isCompleted] = true
        } get Tasks.id

        println("Created new tasks with ids $taskId and $secondTaskId.")

        Tasks.select(Tasks.id.count(), Tasks.isCompleted).groupBy(Tasks.isCompleted).forEach {
            println("${it[Tasks.isCompleted]}: ${it[Tasks.id.count()]} ")
        }

        println("Remaining tasks: ${Tasks.selectAll().toList()}")
    }
}
```

--------------------------------

### Example migration filename

Source: https://www.jetbrains.com/help/exposed/exposed-gradle-plugin.html

Example of a generated migration filename following the default naming pattern.

```text
V20260417195521__CREATE_TABLE_USERS.sql
```

--------------------------------

### Compute min, max, and average with .min(), .max(), .avg()

Source: https://www.jetbrains.com/help/exposed/sql-functions.html

Use .min(), .max(), and .avg() to get minimum, maximum, and average values for a comparable expression. The example computes these for the revenue column and groups by region.

```kotlin
val minRevenue = FilmBoxOfficeTable.revenue.min()
val maxRevenue = FilmBoxOfficeTable.revenue.max()
val averageRevenue = FilmBoxOfficeTable.revenue.avg()
val revenueStats = FilmBoxOfficeTable
    .select(minRevenue, maxRevenue, averageRevenue, FilmBoxOfficeTable.region)
    .groupBy(FilmBoxOfficeTable.region)
    .map {
        Triple(it[minRevenue], it[maxRevenue], it[averageRevenue])
    }
```

--------------------------------

### fetchBatchedResults() migration examples

Source: https://www.jetbrains.com/help/exposed/migration-guide-0-46-0.html

Shows before/after examples for replacing selectBatched() and selectAllBatched() with fetchBatchedResults() as a terminal operation on a Query.

```kotlin
// Example 1
// before
TestTable
    .selectBatched(50) { TestTable.columnA eq 1 }

// after
TestTable
    .selectAll()
    .where { TestTable.columnA eq 1 }
    .fetchBatchedResults(50)

// Example 2
// before
TestTable
    .slice(TestTable.columnA)
    .selectAllBatched(50)

// after
TestTable
    .select(TestTable.columnA)
    .fetchBatchedResults(50)
```

--------------------------------

### Gradle init output

Source: https://www.jetbrains.com/help/exposed/get-started-with-exposed-dao.html

Example output from running gradle init with the recommended selections, showing the prompts and successful build.

```text
Select type of build to generate:
    1: Application
    2: Library
    3: Gradle plugin
    4: Basic (build structure only)
Enter selection (default: Application) [1..4]
Select implementation language:
    1: Java
    2: Kotlin
    3: Groovy
    4: Scala
    5: C++
    6: Swift
Enter selection (default: Java) [1..6] 2
Enter target Java version (min: 7, default: 21):
Project name (default: exposed-kotlin-app):
Select application structure:
    1: Single application project
    2: Application and library project
Enter selection (default: Single application project) [1..2]
Select build script DSL:
    1: Kotlin
    2: Groovy
Enter selection (default: Kotlin) [1..2]
Select test framework:
    1: kotlin.test
    2: JUnit Jupiter
Enter selection (default: kotlin.test) [1..2]
Generate build using new APIs and behavior (some features may change in the next minor release)? (default: no) [yes, no]
> Task :init
To learn more about Gradle by exploring our Samples at https://docs.gradle.org/8.8/samples/sample_building_kotlin_applications.html
BUILD SUCCESSFUL in 28s
1 actionable task: 1 executed
```

--------------------------------

### Install Colima for Oracle XE tests on Apple Silicon

Source: https://www.jetbrains.com/help/exposed/contributing.html

Install the Colima container runtime to run Oracle XE tests on Apple Silicon. This works alongside your Docker installation.

```bash
brew install colima
```

--------------------------------

### SQL statement for insertIgnoreAndGetId example

Source: https://www.jetbrains.com/help/exposed/dsl-crud-operations.html

The SQL statement generated by the insertIgnoreAndGetId example.

```sql
INSERT IGNORE INTO STAR_WARS_FILMS_TABLE (SEQUEL_ID, "name", DIRECTOR)
VALUES (8, 'The Last Jedi', 'Rian Johnson')
```

--------------------------------

### ANY and ALL operators example

Source: https://www.jetbrains.com/help/exposed/dsl-querying-data.html

Shows the use of the `ANY` operator with a preceding comparison operator. The example uses `eq anyFrom(arrayOf(...))` to select films with `sequelId` equal to any of the given values.

```kotlin
val anyFromArray = StarWarsFilmsTable.selectAll()
    .where {
        StarWarsFilmsTable.sequelId eq anyFrom(arrayOf(MOVIE_SEQUEL_2_ID, MOVIE_ORIGINAL_ID))
    }
```

--------------------------------

### 1.0.0: Using top-level less and selectAll

Source: https://www.jetbrains.com/help/exposed/migration-guide-1-0-0.html

Example from version 1.0.0 showing the new import paths for `less` and `selectAll` after migration.

```kotlin
import org.jetbrains.exposed.v1.core.less
import org.jetbrains.exposed.v1.jdbc.selectAll
 
val amountIsLow = TableA.amount less 10
TableA
    .selectAll()
    .where(amountIsLow)
```

--------------------------------

### Basic Usage Example

Source: https://www.jetbrains.com/help/exposed/binary-types.html

Shows how to define a table with binary columns and perform insert and read operations using ExposedBlob and ByteArray.

```APIDOC
## Basic Usage Example

### Description
Demonstrates defining a table with binary columns and performing insert and read operations.

### Table Definition
```kotlin
object Files : Table() {
    val id = integer("id").autoIncrement()
    val name = varchar("name", 255)
    val content = blob("content")
    val simpleData = binary("simple_data") // simple version without length
    val thumbnail = binary("thumbnail", 1024) // length-specified version

    override val primaryKey = PrimaryKey(id)
}
```

### Insert Operation
```kotlin
transaction {
    SchemaUtils.create(Files)
    Files.insert {
        it[name] = "example.txt"
        it[content] = ExposedBlob("Hello, World!".toByteArray())
        it[simpleData] = "simple binary data".toByteArray()
        it[thumbnail] = "thumbnail data".toByteArray()
    }
}
```

### Read Operation
```kotlin
transaction {
    val file = Files.selectAll().first()
    val content = file[Files.content]
    val text = String(content.bytes) // for small files
    // or
    val text2 = content.inputStream.bufferedReader().readText() // for large files

    val simpleBytes = file[Files.simpleData] // returns ByteArray
    val thumbnailBytes = file[Files.thumbnail] // returns ByteArray
}
```
```

--------------------------------

### 0.61.0: Using SqlExpressionBuilder.less and selectAll

Source: https://www.jetbrains.com/help/exposed/migration-guide-1-0-0.html

Example from version 0.61.0 showing the old import paths for `less` and `selectAll` before migration to 1.0.0.

```kotlin
import org.jetbrains.exposed.sql.SqlExpressionBuilder.less
import org.jetbrains.exposed.sql.selectAll
 
val amountIsLow = TableA.amount less 10
TableA
    .selectAll()
    .where(amountIsLow)
```

--------------------------------

### 1.0.0: Using top-level greaterEq and isNotNull

Source: https://www.jetbrains.com/help/exposed/migration-guide-1-0-0.html

Example from version 1.0.0 showing the new import paths for `greaterEq` and `isNotNull` after migration.

```kotlin
import org.jetbrains.exposed.v1.core.*
import org.jetbrains.exposed.v1.jdbc.selectAll
 
val isValid = TableA.value.isNotNull() and
    (TableA.amount greaterEq 10)
TableA
    .selectAll()
    .where(isValid)
```

--------------------------------

### SQL statement for insertAndGetId example

Source: https://www.jetbrains.com/help/exposed/dsl-crud-operations.html

The SQL statement generated by the insertAndGetId example.

```sql
INSERT INTO STAR_WARS_FILMS_TABLE (SEQUEL_ID, "name", DIRECTOR)
VALUES (7, 'The Force Awakens', 'J.J. Abrams')
```

--------------------------------

### 0.61.0: Using SqlExpressionBuilder.concat, Op.build, Expression.build, and update

Source: https://www.jetbrains.com/help/exposed/migration-guide-1-0-0.html

Example from version 0.61.0 showing the old usage of builder methods and `concat` before migration.

```kotlin
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.SqlExpressionBuilder.concat
 
val calculatedAmount = Expression.build {
    (TableA.amount * 2) - 10
}
 
val detailsInvalid = Op.build {
    TableA.details like "% - N/A"
}
 
val newDetails = with(SqlExpressionBuilder) {
    Case()
        .When(TableA.amount eq 0, TableA.details + " - S/O")
        .When(TableA.warranty.isNull(), TableA.details + " - N/A")
        .Else(TableA.details)
}.alias("nl")
 
TableA.update {
    it[TableA.details] = concat(TableA.details.upperCase(), stringLiteral(" - UPDATED"))
    it.update(TableA.amount) { TableA.amount plus 3 }
}
```

--------------------------------

### Using range columns in a table

Source: https://www.jetbrains.com/help/exposed/custom-data-types.html

Example table definition using the new intRange and dateRange column types.

```kotlin
object TestTable : Table("test_table") {
    val amounts = intRange("amounts").default(1..10)
    val holidays = dateRange("holidays")
}

```

--------------------------------

### inList with Pairs or Triples example

Source: https://www.jetbrains.com/help/exposed/dsl-querying-data.html

Shows how `inList` can handle multiple expressions (pairs) to check equality across multiple columns. The example uses a list of pairs of `sequelId` and `name`.

```kotlin
val topRated = listOf(MOVIE_SEQUEL_ID to "Empire Strikes Back", MOVIE_ORIGINAL_ID to "A New Hope")

val multipleInList = StarWarsFilmsTable.selectAll()
    .where {
        StarWarsFilmsTable.sequelId to StarWarsFilmsTable.name inList topRated
    }
```

--------------------------------

### 0.61.0: Using SqlExpressionBuilder.greaterEq and isNotNull

Source: https://www.jetbrains.com/help/exposed/migration-guide-1-0-0.html

Example from version 0.61.0 showing the old import paths for `greaterEq` and `isNotNull` before migration.

```kotlin
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.SqlExpressionBuilder.greaterEq
import org.jetbrains.exposed.sql.SqlExpressionBuilder.isNotNull
 
val isValid = TableA.value.isNotNull() and
    (TableA.amount greaterEq 10)
TableA
    .selectAll()
    .where(isValid)
```

--------------------------------

### 1.0.0: Using top-level functions without SqlExpressionBuilder

Source: https://www.jetbrains.com/help/exposed/migration-guide-1-0-0.html

Example from version 1.0.0 showing the new usage without builder methods and with updated import paths.

```kotlin
import org.jetbrains.exposed.v1.core.*
import org.jetbrains.exposed.v1.jdbc.update
 
val calculatedAmount = (TableA.amount * 2) - 10
 
val detailsInvalid = TableA.details like "% - N/A"
 
val newDetails = Case()
    .When(TableA.amount eq 0, TableA.details + " - S/O")
    .When(TableA.warranty.isNull(), TableA.details + " - N/A")
    .Else(TableA.details)
    .alias("nl")
 
TableA.update {
    it[TableA.details] = concat(TableA.details.upperCase(), stringLiteral(" - UPDATED"))
    it[TableA.amount] = TableA.amount plus 3
}
```

--------------------------------

### Reset metadata caches manually with resetCaches

Source: https://www.jetbrains.com/help/exposed/migrations.html

Use this example to manually reset metadata caches after executing schema-altering SQL statements in the same transaction. Without this, subsequent metadata queries may return stale results. The example shows dropping a table and then checking existence after resetting caches.

```kotlin
transaction(db) {
    println(UsersTable.exists()) // true

    val statements: List<String> = UsersTable.dropStatement()
    execInBatch(statements)
    db.dialectMetadata.resetCaches()

    println(UsersTable.exists()) // false
}
```

--------------------------------

### 1.0.0: Using currentDialectMetadata for metadata queries

Source: https://www.jetbrains.com/help/exposed/migration-guide-1-0-0.html

Example from the 1.0.0 version showing the replacement of currentDialect with currentDialectMetadata. Imports have changed to org.jetbrains.exposed.v1.jdbc.

```kotlin
import org.jetbrains.exposed.v1.jdbc.transactions.transaction
import org.jetbrains.exposed.v1.jdbc.vendors.currentDialectMetadata
 
transaction {
    val tableKeys = currentDialectMetadata.existingPrimaryKeys(TableA)[TableA]
    if (TableA.tableName in currentDialectMetadata.allTablesNames()) {
        // do something
    }
}
```

--------------------------------

### Insert data into array columns

Source: https://www.jetbrains.com/help/exposed/array-types.html

Shows how to insert data into array columns using the `insert` function. The example uses `List` to generate values.

```kotlin
SimpleArrays.insert {
    it[memberIds] = List(TEAM_SIZE) { UUID.randomUUID() }
    it[memberNames] = List(TEAM_SIZE) { i -> "Member ${('A' + i)}" }
    it[budgets] = listOf(DEFAULT_BUDGET)
}
```

--------------------------------

### Get character length with .charLength()

Source: https://www.jetbrains.com/help/exposed/sql-functions.html

Use .charLength() to return the length in characters, or null if the value is null. The example gets the length of the title column.

```kotlin
val titleLength = FilmBoxOfficeTable.title.charLength()
```

--------------------------------

### SQL generated for IntIdTable

Source: https://www.jetbrains.com/help/exposed/dao-table-types.html

Shows the SQL CREATE TABLE statement generated when the IntIdTable example is created, including the auto-increment primary key column.

```SQL
CREATE TABLE IF NOT EXISTS STARWARSFILMS
(ID INT AUTO_INCREMENT PRIMARY KEY,
SEQUEL_ID INT NOT NULL,
"name" VARCHAR(50) NOT NULL,
DIRECTOR VARCHAR(50) NOT NULL);
```

--------------------------------

### like condition example

Source: https://www.jetbrains.com/help/exposed/dsl-querying-data.html

Checks if an expression matches a specified pattern using the `like` operator. The example selects all films whose name starts with 'The'.

```kotlin
val allMoviesLike = StarWarsFilmsTable.selectAll()
    .where { StarWarsFilmsTable.name like "The %" }
```

--------------------------------

### Create project directory and navigate

Source: https://www.jetbrains.com/help/exposed/get-started-with-exposed-dao.html

Run these commands in a terminal to create a new folder named exposed-dao-kotlin-app and change into it.

```bash
$
mkdir exposed-dao-kotlin-app
cd exposed-dao-kotlin-app
```

--------------------------------

### notLike condition example

Source: https://www.jetbrains.com/help/exposed/dsl-querying-data.html

Checks if an expression doesn't match the specified pattern using the `notLike` operator. The example selects all films whose name does not start with 'The'.

```kotlin
val allMoviesNotLike = StarWarsFilmsTable.selectAll()
    .where { StarWarsFilmsTable.name notLike "The %" }
```

--------------------------------

### Create project directory and navigate

Source: https://www.jetbrains.com/help/exposed/get-started-with-exposed.html

Run these commands in a terminal to create a new folder named 'exposed-kotlin-app' and change into it.

```bash
mkdir exposed-kotlin-app
cd exposed-kotlin-app
```

--------------------------------

### Extract substring with .substring()

Source: https://www.jetbrains.com/help/exposed/sql-functions.html

Use .substring() to return a substring from a string expression, specifying start and length. The example extracts a substring starting at index 1 with length 3.

```kotlin
val shortenedTitle = FilmBoxOfficeTable.title.substring(start = 1, length = 3)
```

--------------------------------

### Case usage before migration (0.61.0)

Source: https://www.jetbrains.com/help/exposed/migration-guide-1-0-0.html

In 0.61.0, Case is used directly with a value. The example creates a status case based on Books.isOverdue.

```kotlin
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.SqlExpressionBuilder.less
import org.jetbrains.exposed.sql.kotlin.datetime.CurrentDate
 
val statusCase = Case(Books.isOverdue)
    .When(Books.dueDate less CurrentDate, booleanLiteral(true))
    .Else(booleanLiteral(false))
    .alias("status_valid")

```

--------------------------------

### Self-join with aliases

Source: https://www.jetbrains.com/help/exposed/dsl-querying-data.html

Uses an alias to join the same table multiple times. The example joins `StarWarsFilmsTable` with an alias `sequelTable` to get original and sequel names.

```kotlin
val sequelTable = StarWarsFilmsTable.alias("sql")

val originalAndSequelNames = StarWarsFilmsTable
    .join(sequelTable, JoinType.INNER, StarWarsFilmsTable.sequelId, sequelTable[StarWarsFilmsTable.id])
    .select(StarWarsFilmsTable.name, sequelTable[StarWarsFilmsTable.name])
    .map { it[StarWarsFilmsTable.name] to it[sequelTable[StarWarsFilmsTable.name]] }
```

--------------------------------

### Using OVER with PARTITION BY and ORDER BY

Source: https://www.jetbrains.com/help/exposed/sql-functions.html

Shows how to chain .over(), .partitionBy(), and .orderBy() to define window functions. The first example sums revenue partitioned by year and title, the second uses rowNumber(), and the third orders by year descending and title ascending with nulls first.

```kotlin
val window1 = FilmBoxOfficeTable.revenue.sum()
    .over()
    .partitionBy(FilmBoxOfficeTable.year, FilmBoxOfficeTable.title)
    .orderBy(FilmBoxOfficeTable.revenue)
val result1 = FilmBoxOfficeTable.select(window1).map { it[window1] }

val window2 = rowNumber()
    .over()
    .partitionBy(FilmBoxOfficeTable.year, FilmBoxOfficeTable.title)
    .orderBy(FilmBoxOfficeTable.revenue)
val result2 = FilmBoxOfficeTable.select(window2).map { it[window2] }

val window3 = FilmBoxOfficeTable.revenue.sum()
    .over()
    .orderBy(FilmBoxOfficeTable.year to SortOrder.DESC, FilmBoxOfficeTable.title to SortOrder.ASC_NULLS_FIRST)
val result3 = FilmBoxOfficeTable.select(window3).map { it[window3] }
```

--------------------------------

### Access table indices with Table.indices

Source: https://www.jetbrains.com/help/exposed/working-with-tables.html

After a table is created, use Table.indices to get a list of Index data class instances. The example maps each index to its name and create statement.

```kotlin
Table.indices.map { it.indexName to it.createStatement().first() }
```

--------------------------------

### Equivalent join with .join() and JoinType.INNER

Source: https://www.jetbrains.com/help/exposed/dsl-joining-tables.html

Shows the equivalent .join() call with JoinType.INNER and explicit onColumn and otherColumn for the foreign key join. Produces the same result as the innerJoin example.

```kotlin
ActorsIntIdTable.join(RolesTable, JoinType.INNER, onColumn = ActorsIntIdTable.id, otherColumn = RolesTable.actorId)
    .select(RolesTable.characterName.count(), ActorsIntIdTable.name)
    .groupBy(ActorsIntIdTable.name)
    .toList()
```

--------------------------------

### Compute population standard deviation with .stdDevPop()

Source: https://www.jetbrains.com/help/exposed/sql-functions.html

Use .stdDevPop() to retrieve the population standard deviation of a column. The example gets the standard deviation of the revenue column and retrieves it as a single result.

```kotlin
val revenueStdDev = FilmBoxOfficeTable.revenue.stdDevPop()
val stdDev = FilmBoxOfficeTable
    .select(revenueStdDev)
    .singleOrNull()
    ?.get(revenueStdDev)
```

--------------------------------

### 0.61.0: Using currentDialect for metadata queries

Source: https://www.jetbrains.com/help/exposed/migration-guide-1-0-0.html

Example from the 0.61.0 version showing how to access existingPrimaryKeys and allTablesNames via currentDialect. This pattern is replaced in 1.0.0 by currentDialectMetadata.

```kotlin
import org.jetbrains.exposed.sql.transactions.transaction
import org.jetbrains.exposed.sql.vendors.currentDialect
 
transaction {
    val tableKeys = currentDialect.existingPrimaryKeys(TableA)[TableA]
    if (TableA.tableName in currentDialect.allTablesNames()) {
        // do something
    }
}
```

--------------------------------

### Insert statement with default values

Source: https://www.jetbrains.com/help/exposed/breaking-changes.html

Example of an insert statement on a table with default values. Before 0.57.0, Exposed sent all default values in the SQL request; starting from 0.57.0, it relies on database defaults.

```kotlin
object TestTable : IntIdTable("test") {
  val number = integer("number").default(100)
  val expression = integer("exp")
      .defaultExpression(intLiteral(100) + intLiteral(200))
}

TestTable.insert { }
```

--------------------------------

### Window Functions Overview

Source: https://www.jetbrains.com/help/exposed/sql-functions.html

Lists the available window functions and demonstrates how to use the OVER clause with partitionBy and orderBy.

```APIDOC
## Window Functions

### Description
Window functions allow calculations across a set of table rows related to the current row. Existing aggregate functions (like sum(), avg()) can be used, as well as new rank and value functions.

### Available Functions
- cumeDist()
- denseRank()
- firstValue()
- lag()
- lastValue()
- lead()
- nthValue()
- nTile()
- percentRank()
- rank()
- rowNumber()

### Usage
To use a window function, include the OVER clause by chaining `.over()` after the function call. A PARTITION BY and ORDER BY clause can be optionally chained using `.partitionBy()` and `.orderBy()`, taking multiple arguments.

### Example
```kotlin
val window1 = FilmBoxOfficeTable.revenue.sum()
    .over()
    .partitionBy(FilmBoxOfficeTable.year, FilmBoxOfficeTable.title)
    .orderBy(FilmBoxOfficeTable.revenue)
val result1 = FilmBoxOfficeTable.select(window1).map { it[window1] }

val window2 = rowNumber()
    .over()
    .partitionBy(FilmBoxOfficeTable.year, FilmBoxOfficeTable.title)
    .orderBy(FilmBoxOfficeTable.revenue)
val result2 = FilmBoxOfficeTable.select(window2).map { it[window2] }

val window3 = FilmBoxOfficeTable.revenue.sum()
    .over()
    .orderBy(FilmBoxOfficeTable.year to SortOrder.DESC, FilmBoxOfficeTable.title to SortOrder.ASC_NULLS_FIRST)
val result3 = FilmBoxOfficeTable.select(window3).map { it[window3] }
```
```

--------------------------------

### Compute sum and count with .sum() and .count()

Source: https://www.jetbrains.com/help/exposed/sql-functions.html

Use .sum() and .count() directly with a column expression. The example computes sum and count for the revenue column and groups by region.

```kotlin
val revenueSum = FilmBoxOfficeTable.revenue.sum()
val revenueCount = FilmBoxOfficeTable.revenue.count()
val revenueReport = FilmBoxOfficeTable
    .select(revenueSum, revenueCount, FilmBoxOfficeTable.region)
    .groupBy(FilmBoxOfficeTable.region)
    .map {
        it[revenueSum] to it[revenueCount]
    }
```

--------------------------------

### Initialize Gradle project

Source: https://www.jetbrains.com/help/exposed/get-started-with-exposed-dao.html

Run the gradle init task to initialize a new Gradle project. When prompted, select Application project type and Kotlin implementation language, and press enter for other defaults.

```bash
$
gradle init
```

--------------------------------

### Open project in IntelliJ IDEA

Source: https://www.jetbrains.com/help/exposed/get-started-with-exposed-dao.html

Use this command to open the initialized project folder in IntelliJ IDEA.

```bash
idea .
```

--------------------------------

### Exposed Maven plugin configuration with optional parameters

Source: https://www.jetbrains.com/help/exposed/exposed-maven-plugin.html

Example Maven plugin configuration showing all optional parameters for migration generation and file naming. Defaults are overridden: fileDirectory, filePrefix, fileVersionFormat, fileSeparator, useUpperCaseDescription, and fileExtension.

```xml
<plugin>
    <groupId>org.jetbrains.exposed.plugin</groupId>
    <artifactId>exposed-maven-plugin</artifactId>
    <version>1.5.0</version>
    <configuration>
        <tablesPackage>com.example.db.tables</tablesPackage>
        <databaseUrl>jdbc:postgresql://localhost:5432/mydb</databaseUrl>
        <databaseUser>postgres</databaseUser>
        <databasePassword>password</databasePassword>

        <fileDirectory>${project.basedir}/src/main/resources/db/migration</fileDirectory>
        <filePrefix>V</filePrefix>
        <fileVersionFormat>TIMESTAMP_ONLY</fileVersionFormat>
        <fileSeparator>__</fileSeparator>
        <useUpperCaseDescription>true</useUpperCaseDescription>
        <fileExtension>.sql</fileExtension>
    </configuration>
</plugin>
```

--------------------------------

### Run the application - SQL logs and output

Source: https://www.jetbrains.com/help/exposed/get-started-with-exposed-dao.html

Expected output when running the application, showing SQL queries and printed results.

```text
SQL: SELECT SETTING_VALUE FROM INFORMATION_SCHEMA.SETTINGS WHERE SETTING_NAME = 'MODE'
SQL: CREATE TABLE IF NOT EXISTS TASKS (ID INT AUTO_INCREMENT PRIMARY KEY, "name" VARCHAR(128) NOT NULL, DESCRIPTION VARCHAR(128) NOT NULL, COMPLETED BOOLEAN DEFAULT FALSE NOT NULL)
SQL: INSERT INTO TASKS ("name", DESCRIPTION, COMPLETED) VALUES ('Learn Exposed DAO', 'Follow the DAO tutorial', FALSE)
SQL: INSERT INTO TASKS ("name", DESCRIPTION, COMPLETED) VALUES ('Read The Hobbit', 'Read chapter one', TRUE)
Created new tasks with ids 1 and 2
SQL: SELECT TASKS.ID, TASKS."name", TASKS.DESCRIPTION, TASKS.COMPLETED FROM TASKS WHERE TASKS.COMPLETED = TRUE
Completed tasks: 1
```

--------------------------------

### between range condition example

Source: https://www.jetbrains.com/help/exposed/dsl-querying-data.html

Checks if an expression is between two values (inclusive) using the `.between()` function. The example selects films with `sequelId` between `MOVIE_ORIGINAL_ID` and `MOVIE_SEQUEL_2_ID`.

```kotlin
val allBetween = StarWarsFilmsTable.selectAll()
    .where { StarWarsFilmsTable.sequelId.between(MOVIE_ORIGINAL_ID, MOVIE_SEQUEL_2_ID) }
```

--------------------------------

### List Docker contexts to verify default

Source: https://www.jetbrains.com/help/exposed/contributing.html

List Docker contexts to ensure that 'default' is set as the default Docker context.

```bash
docker context list
```

--------------------------------

### inList with lists example

Source: https://www.jetbrains.com/help/exposed/dsl-querying-data.html

Checks if an expression matches any value in a list using the `inList` operator. The example selects films with `sequelId` values of 6 or 4.

```kotlin
val allInList = StarWarsFilmsTable.selectAll()
    .where { StarWarsFilmsTable.sequelId inList listOf(MOVIE_SEQUEL_2_ID, MOVIE_ORIGINAL_ID) }
```

--------------------------------

### Import exposed-version-catalog in settings.gradle.kts (Kotlin DSL)

Source: https://www.jetbrains.com/help/exposed/adding-dependencies.html

Adds the Exposed version catalog to the Gradle settings file using the Kotlin DSL. The catalog is created with the name `exposedLibs` and points to the `org.jetbrains.exposed:exposed-version-catalog:1.5.0` artifact.

```kotlin
dependencyResolutionManagement {
    repositories {
        mavenCentral()
    }
    versionCatalogs {
        create("exposedLibs") {
            from("org.jetbrains.exposed:exposed-version-catalog:1.5.0")
        }
    }
}
```

--------------------------------

### Start Colima daemon in x86_64 mode

Source: https://www.jetbrains.com/help/exposed/contributing.html

Start the Colima daemon in x86_64 architecture mode with 4GB memory and network address. This is required for running Oracle XE tests.

```bash
colima start --arch x86_64 --memory 4 --network-address
```

--------------------------------

### regexp condition example

Source: https://www.jetbrains.com/help/exposed/dsl-querying-data.html

Checks if an expression matches a regular expression using the `regexp` operator. The example selects films whose name matches the pattern '^The(\s\w+){2}$'.

```kotlin
val allMatchingRegex = StarWarsFilmsTable.selectAll()
    .where { StarWarsFilmsTable.name regexp "^The(\\s\\w+){2}$" }
```

--------------------------------

### Build ConnectionFactoryOptions from Scratch

Source: https://www.jetbrains.com/help/exposed/working-with-connectionfactory.html

Constructs ConnectionFactoryOptions entirely from scratch without a URL, specifying driver, protocol, database, and an extra option. Uses the connectionFactoryOptions builder.

```kotlin
import io.r2dbc.spi.ConnectionFactoryOptions
import io.r2dbc.spi.IsolationLevel
import io.r2dbc.spi.Option
import org.jetbrains.exposed.v1.r2dbc.R2dbcDatabase

val database = R2dbcDatabase.connect {
    defaultMaxAttempts = 1
    defaultR2dbcIsolationLevel = IsolationLevel.READ_COMMITTED

    connectionFactoryOptions {
        option(ConnectionFactoryOptions.DRIVER, "h2")
        option(ConnectionFactoryOptions.PROTOCOL, "mem")
        option(ConnectionFactoryOptions.DATABASE, "test")
        option(Option.valueOf("DB_CLOSE_DELAY"), "-1")
    }
}
```

--------------------------------

### Check if JSON contains an expression with .contains()

Source: https://www.jetbrains.com/help/exposed/json-and-jsonb-types.html

Use .contains() to check whether a JSON expression contains a value. The first example checks for a JSON object. The second example limits the check to a specific JSON path, passing the value and path as arguments.

```kotlin
val usesKotlin = TeamsTable.project.contains("{\"language\":\"Kotlin\"}")
val kotlinTeams = TeamsTable.selectAll().where { usesKotlin }.count()
```

```kotlin
val usesKotlinWithPath = TeamsTable.project.contains(
    "\"Kotlin\"",
    ".language"
)
val kotlinTeams = TeamsTable
    .selectAll()
    .where { usesKotlinWithPath }
    .count()
```

--------------------------------

### Connect to PostgreSQL using JDBC

Source: https://www.jetbrains.com/help/exposed/working-with-database.html

Connects to a PostgreSQL database using JDBC with user and password.

```kotlin
val postgresqldb = Database.connect(
    "jdbc:postgresql://localhost:12346/test",
    driver = "org.postgresql.Driver",
    user = "user",
    password = "password"
)
```

--------------------------------

### Find entity by ID with .findById()

Source: https://www.jetbrains.com/help/exposed/dao-crud-operations.html

Get an entity by its ID value.

```kotlin
val movie = StarWarsFilmEntity.findById(2)
```

--------------------------------

### Retrieve all entities with .all()

Source: https://www.jetbrains.com/help/exposed/dao-crud-operations.html

Get all entity instances for an entity class.

```kotlin
val allMovies = StarWarsFilmEntity.all()
```

--------------------------------

### substring()

Source: https://www.jetbrains.com/help/exposed/sql-functions.html

Returns a substring value from the specified start and with the specified length.

```APIDOC
## substring()

### Description
Returns a substring value from the specified start and with the specified length.

### Method
Function call on a column expression.

### Parameters
- **receiver** (Column<String>) - The string column to extract from.
- **start** (Int) - The starting position (1-based).
- **length** (Int) - The length of the substring.

### Returns
- **Column<String>** - The substring expression.

### Example
```kotlin
val shortenedTitle = FilmBoxOfficeTable.title.substring(start = 1, length = 3)
```
```

--------------------------------

### Connect with URL and databaseConfig using R2dbcDatabase.connect()

Source: https://www.jetbrains.com/help/exposed/working-with-connectionfactory.html

Use this snippet to connect to an R2DBC database by passing a URL and a databaseConfig block. The example sets defaultMaxAttempts to 1 and defaultR2dbcIsolationLevel to READ_COMMITTED. This is the implicit way to connect when a URL is provided.

```kotlin
val database = R2dbcDatabase.connect(
    "r2dbc:h2:mem:///test;DB_CLOSE_DELAY=-1;",
    databaseConfig = R2dbcDatabaseConfig {
        defaultMaxAttempts = 1
        defaultR2dbcIsolationLevel = IsolationLevel.READ_COMMITTED
    }
)
```

--------------------------------

### Check if data exists with .exists()

Source: https://www.jetbrains.com/help/exposed/json-and-jsonb-types.html

Use .exists() to check whether data exists within a JSON expression. The first example checks for an '.active' path. The second example shows using filter expressions and optional variables in JSON paths, passing an optional parameter with a JSON string.

```kotlin
val hasActiveStatus = TeamsTable.project.exists(".active")
val activeProjects = TeamsTable.selectAll().where { hasActiveStatus }.count()
```

```kotlin
val mainId = "Main"
val hasMainProject = TeamsTable.project.exists(
    ".name ? (@ == \\$main)",
    optional = "{\"main\":\"$mainId\"}"
)
val mainProjects = TeamsTable
    .selectAll()
    .where { hasMainProject }
    .map { it[TeamsTable.groupId] }
```

--------------------------------

### Connect to MySQL using JDBC

Source: https://www.jetbrains.com/help/exposed/working-with-database.html

Connects to a MySQL database using JDBC with user and password.

```kotlin
val mysqldb = Database.connect(
    "jdbc:mysql://localhost:3306/test",
    driver = "com.mysql.cj.jdbc.Driver",
    user = "user",
    password = "password"
)
```

--------------------------------

### Connect to SQL Server using JDBC

Source: https://www.jetbrains.com/help/exposed/working-with-database.html

Connects to a SQL Server database using the JDBC driver.

```kotlin
val sqlserverdb = Database.connect(
    "jdbc:sqlserver://localhost:32768;databaseName=test",
    "com.microsoft.sqlserver.jdbc.SQLServerDriver",
    user = "user",
    password = "password"
)
```

--------------------------------

### Find entities by condition with .find()

Source: https://www.jetbrains.com/help/exposed/dao-crud-operations.html

Get entities that match a conditional expression.

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

--------------------------------

### SQL query for CompositeEntity find

Source: https://www.jetbrains.com/help/exposed/dao-crud-operations.html

Example SQL query generated for the CompositeEntity find operation.

```sql
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')
```

--------------------------------

### Configure database connection for migration generation

Source: https://www.jetbrains.com/help/exposed/exposed-maven-plugin.html

Configure the plugin with `tablesPackage`, `databaseUrl`, `databaseUser`, and `databasePassword` parameters to use a direct database connection. This is the minimum configuration for database-based schema generation.

```xml
<plugin>
    <groupId>org.jetbrains.exposed.plugin</groupId>
    <artifactId>exposed-maven-plugin</artifactId>
    <version>1.5.0</version>
    <configuration>
        <tablesPackage>com.example.db.tables</tablesPackage>
        <databaseUrl>jdbc:postgresql://localhost:5432/mydb</databaseUrl>
        <databaseUser>postgres</databaseUser>
        <databasePassword>password</databasePassword>
    </configuration>
</plugin>
```

--------------------------------

### SQL statement for inserting a single row

Source: https://www.jetbrains.com/help/exposed/dsl-crud-operations.html

The SQL statement generated by the previous Kotlin insert example.

```sql
INSERT INTO STARWARSFILMS (SEQUEL_ID, "name", DIRECTOR)
VALUES (7, 'The Force Awakens', 'J.J. Abrams')
```

--------------------------------

### Implement MySQL index hint with IndexHintQuery class

Source: https://www.jetbrains.com/help/exposed/dsl-querying-data.html

Defines a custom Query subclass that injects an index hint into the SQL after the table name. The init block copies stored properties from the original query, and prepareSQL replaces the FROM clause to insert the hint. Requires importing org.jetbrains.exposed.v1.core.QueryBuilder, Table, and org.jetbrains.exposed.v1.jdbc.Query.

```kotlin
import org.jetbrains.exposed.v1.core.QueryBuilder
import org.jetbrains.exposed.v1.core.Table
import org.jetbrains.exposed.v1.jdbc.Query

fun Query.indexHint(hint: String) = IndexHintQuery(this, hint)

class IndexHintQuery(
    val source: Query,
    val indexHint: String
) : Query(source.set, source.where) {

    init {
        // copies any stored properties from the original query
        source.copyTo(this)
    }

    override fun prepareSQL(builder: QueryBuilder): String {
        val originalSql = super.prepareSQL(builder)
        val fromTableSql = " FROM ${transaction.identity(set.source as Table)} "
        return originalSql.replace(fromTableSql, "$fromTableSql$indexHint ")
    }

    override fun copy(): IndexHintQuery = IndexHintQuery(source.copy(), indexHint).also { copy ->
        copyTo(copy)
    }
}
```

--------------------------------

### Access EntityID value

Source: https://www.jetbrains.com/help/exposed/dao-crud-operations.html

The id property is wrapped in EntityID; use .value to get the underlying value.

```kotlin
val movieId: Int = movie.id.value
```

--------------------------------

### Insert and read binary data

Source: https://www.jetbrains.com/help/exposed/binary-types.html

Shows how to insert binary data using ExposedBlob and ByteArray, and how to read it back. For large files, use the inputStream approach to avoid loading the entire content into memory.

```kotlin
transaction {
    SchemaUtils.create(Files)
    // Store binary data
    Files.insert {
        it[name] = "example.txt"
        it[content] = ExposedBlob("Hello, World!".toByteArray())
        it[simpleData] = "simple binary data".toByteArray() // using simple binary version
        it[thumbnail] = "thumbnail data".toByteArray() // using length-specified version
    }

    // Read binary data
    val file = Files.selectAll().first()
    val content = file[Files.content]
    val text = String(content.bytes) // for small files
    // or
    val text2 = content.inputStream.bufferedReader().readText() // for large files

    // Read both types of binary data
    val simpleBytes = file[Files.simpleData] // returns ByteArray from simple binary
    val thumbnailBytes = file[Files.thumbnail] // returns ByteArray from length-specified binary
}
```

--------------------------------

### Alias a sub-query

Source: https://www.jetbrains.com/help/exposed/dsl-querying-data.html

Aliases a sub-query to reference its columns. The example aliases a select of `id` and `name` as `swf` and then selects from it.

```kotlin
val starWarsFilms = StarWarsFilmsTable
    .select(StarWarsFilmsTable.id, StarWarsFilmsTable.name)
    .alias("swf")

val id = starWarsFilms[StarWarsFilmsTable.id]

val name = starWarsFilms[StarWarsFilmsTable.name]

val allStarWarsFilms = starWarsFilms
    .select(id, name)
    .map { it[id] to it[name] }
```

--------------------------------

### Pre-construct ConnectionFactoryOptions and R2dbcDatabaseConfig

Source: https://www.jetbrains.com/help/exposed/working-with-connectionfactory.html

Pre-builds a ConnectionFactoryOptions object and a custom R2dbcDatabaseConfig, then passes both to R2dbcDatabase.connect(). Useful for reusing configuration.

```kotlin
import io.r2dbc.spi.ConnectionFactoryOptions
import io.r2dbc.spi.IsolationLevel
import io.r2dbc.spi.Option
import org.jetbrains.exposed.v1.r2dbc.R2dbcDatabase
import org.jetbrains.exposed.v1.r2dbc.R2dbcDatabaseConfig

val options = ConnectionFactoryOptions.builder()
    .option(ConnectionFactoryOptions.DRIVER, "h2")
    .option(ConnectionFactoryOptions.PROTOCOL, "mem")
    .option(ConnectionFactoryOptions.DATABASE, "test")
    .option(Option.valueOf("DB_CLOSE_DELAY"), "-1")
    .build()

val databaseConfig = R2dbcDatabaseConfig {
    defaultMaxAttempts = 1
    defaultR2dbcIsolationLevel = IsolationLevel.READ_COMMITTED
    connectionFactoryOptions = options
}

val database = R2dbcDatabase.connect(databaseConfig = databaseConfig)
```

--------------------------------

### Count rows with .count()

Source: https://www.jetbrains.com/help/exposed/dsl-querying-data.html

Counts rows matching a query. The example counts all rows where `sequelId` equals `MOVIE_SEQUEL_3_ID`.

```kotlin
val count = StarWarsFilmsTable.selectAll()
    .where {
        StarWarsFilmsTable.sequelId eq MOVIE_SEQUEL_3_ID
    }
    .count()
```

--------------------------------

### Use ANY and ALL operators with array columns

Source: https://www.jetbrains.com/help/exposed/array-types.html

Shows how to use array columns with SQL's `ANY` and `ALL` operators via `allFrom` and `anyFrom`.

```kotlin
SimpleArrays
    .selectAll()
    .where { SimpleArrays.budgets[FIRST_MEMBER_INDEX] lessEq allFrom(SimpleArrays.budgets) }

SimpleArrays
    .selectAll()
    .where { stringParam("Member A") eq anyFrom(SimpleArrays.memberNames.slice(SLICE_START, SLICE_END_LARGE)) }
```

--------------------------------

### Using citext column in a table

Source: https://www.jetbrains.com/help/exposed/custom-data-types.html

Example table definition that uses the citext extension function to add a column.

```kotlin
object TestTable : Table("test_table") {
    val firstName = citext("first_name", 32)
}

```

--------------------------------

### Connect to MariaDB using JDBC

Source: https://www.jetbrains.com/help/exposed/working-with-database.html

Connects to a MariaDB database using JDBC with user and password.

```kotlin
val mariadb = Database.connect(
    "jdbc:mariadb://localhost:3306/test",
    driver = "org.mariadb.jdbc.Driver",
    user = "root",
    password = "your_pwd"
)
```

--------------------------------

### Connect to PostgreSQL using R2DBC

Source: https://www.jetbrains.com/help/exposed/working-with-database.html

Connects to a PostgreSQL database using R2DBC with user and password.

```kotlin
val postgresqldb = R2dbcDatabase.connect(
    url = "r2dbc:postgresql://db:5432/test",
    driver = "postgresql",
    user = "user",
    password = "password"
)
```

--------------------------------

### CommentPosition usage before migration (0.61.0)

Source: https://www.jetbrains.com/help/exposed/migration-guide-1-0-0.html

In 0.61.0, CommentPosition is accessed via Query.CommentPosition. The example adds a MySQL hint after SELECT.

```kotlin
import org.jetbrains.exposed.sql.Query
import org.jetbrains.exposed.sql.selectAll
 
val queryWithHint = TableA
    .selectAll()
    .comment(
        content = "+ MAX_EXECUTION_TIME(1000) ",
        position = Query.CommentPosition.AFTER_SELECT
    )

```

--------------------------------

### Gradle dependencies for Exposed 1.0.0 Spring Boot 4 starter

Source: https://www.jetbrains.com/help/exposed/migration-guide-1-0-0.html

Shows the replacement dependency for Spring Boot 4 migration. Use exposed-spring-boot4-starter version 1.0.0 only when migrating from Spring Boot 3 to Spring Boot 4.

```kotlin
dependencies {
    // Only if migrating from Spring Boot 3 to Spring Boot 4
    implementation("org.jetbrains.exposed:exposed-spring-boot4-starter:1.0.0")
}
```

--------------------------------

### 0.61.0: exec() with Query and ResultSet

Source: https://www.jetbrains.com/help/exposed/migration-guide-1-0-0.html

Before 1.0.0, exec() with a Query passed the java.sql.ResultSet directly to the lambda. This example processes the ResultSet directly.

```kotlin
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.transaction
 
transaction {
    val query = TablA
        .select(TableA.amount)
        .where { TableA.amount greater 100 }
 
    val result = exec(query) {
        val amounts = mutableListOf<Int>()
        while (it.next()) {
            amounts += it.getInt("amount") % 10
        }
        amounts
    }
}

```

--------------------------------

### Gradle dependencies for Exposed 0.61.0 Spring Boot starter

Source: https://www.jetbrains.com/help/exposed/migration-guide-1-0-0.html

Shows the dependency declaration for the original exposed-spring-boot-starter artifact in version 0.61.0. This is the starting point before migrating to Spring Boot 4.

```kotlin
dependencies {
    // ...
    implementation("org.jetbrains.exposed:exposed-spring-boot-starter:0.61.0")
}
```

--------------------------------

### Connect to SQL Server using R2DBC

Source: https://www.jetbrains.com/help/exposed/working-with-database.html

Connects to a SQL Server database using the R2DBC driver.

```kotlin
val sqlserverdb = R2dbcDatabase.connect(
    "r2dbc:mssql://localhost:32768;databaseName=test",
    driver = "sqlserver",
    user = "user",
    password = "password"
)
```
Tokens:
Raw