<show-structure for="chapter,procedure" depth="2"/>

Building SQL Statements

Exposed DSL provides various functions to perform database operations, such as . If you need to access the SQL generated by this DSL without executing it, Exposed provides this functionality through Statement.prepareSQL().

The following examples refer to the StarWarsFilmsTable defined previously, and any generated SQL is based on output from the H2 database.

<note> This functionality still requires a connection and transaction context, since Exposed adapts its statement and query building dynamically based on the underlying database, primarily for identifier and syntax specifics. </note>

Read operations

<tldr> <p>API References: <a href="https://jetbrains.github.io/Exposed/api/exposed-jdbc/org.jetbrains.exposed.v1.jdbc/-query/index.html"><code>Query</code> (JDBC)</a>, <a href="https://jetbrains.github.io/Exposed/api/exposed-r2dbc/org.jetbrains.exposed.v1.r2dbc/-query/index.html"><code>Query</code> (R2DBC)</a> </p> </tldr>

An instance of a Query is not executed until its results are consumed, for example through iteration. For this reason, queries can be built and stored for later use:

<code-block lang="kotlin" src="exposed-dsl/src/main/kotlin/org/example/examples/BuildStatementExamples.kt" include-symbol="filmQuery"/>

You can then call .prepareSQL() to check the SQL string representation of the query that would be sent to the database:

<code-block lang="kotlin" src="exposed-dsl/src/main/kotlin/org/example/examples/BuildStatementExamples.kt" include-symbol="querySql"/>

<code-block lang="kotlin" src="exposed-dsl/src/main/kotlin/org/example/examples/BuildStatementExamples.kt" include-lines="27-29"/>

By default, a parameterized SQL string is prepared and returned. To generate a SQL string without parameter placeholders, set the prepared argument to false:

<code-block lang="kotlin" src="exposed-dsl/src/main/kotlin/org/example/examples/BuildStatementExamples.kt" include-symbol="fullQuerySql"/>

<code-block lang="kotlin" src="exposed-dsl/src/main/kotlin/org/example/examples/BuildStatementExamples.kt" include-lines="38-40"/>

Other operations

When calling a function like .insert() on a table, Exposed automatically sends the generated SQL to the database to create a new row. To avoid automatic execution, you can instantiate the underlying statement class directly, namely InsertStatement.

Since version 1.0.0, you can create instances of these underlying statements without automatic execution, by using the same DSL inside a buildStatement {} block:

<code-block lang="kotlin" src="exposed-dsl/src/main/kotlin/org/example/examples/BuildStatementExamples.kt" include-symbol="insertFilm"/>

As for queries, you can access the SQL string to be executed using .prepareSQL():

<code-block lang="kotlin" src="exposed-dsl/src/main/kotlin/org/example/examples/BuildStatementExamples.kt" include-symbol="preparedSql"/>

<code-block lang="kotlin" src="exposed-dsl/src/main/kotlin/org/example/examples/BuildStatementExamples.kt" include-lines="57"/>

<code-block lang="kotlin" src="exposed-dsl/src/main/kotlin/org/example/examples/BuildStatementExamples.kt" include-symbol="fullSql"/>

<code-block lang="kotlin" src="exposed-dsl/src/main/kotlin/org/example/examples/BuildStatementExamples.kt" include-lines="64"/>

Executing a statement

<tldr> <p>API References: <a href="https://jetbrains.github.io/Exposed/api/exposed-jdbc/org.jetbrains.exposed.v1.jdbc/-jdbc-transaction/exec.html"><code>exec</code> (JDBC)</a>, <a href="https://jetbrains.github.io/Exposed/api/exposed-r2dbc/org.jetbrains.exposed.v1.r2dbc/-r2dbc-transaction/exec.html"><code>exec</code> (R2DBC)</a> </p> </tldr>

A stored Statement can be sent to the database by first passing it to an executable class, either a subclass of BlockingExecutable (JDBC) or SuspendExecutable (R2DBC). The executable can then be sent to the database using exec() in a transaction block.

This can be done manually, if the appropriate class is known or a custom statement or executable class is being used:

kotlin

exec(InsertBlockingExecutable(insertFilm))

Alternatively, if the statement is created using the Exposed API, you can use Statement.toExecutable() (JDBC, R2DBC) to resolve the appropriate executable class instance for the calling statement type:

kotlin

exec(insertFilm.toExecutable())