Using an SQL string to perform a database operation is possible from inside a transaction block with .exec(). This function accepts and executes a String value argument, which may be useful when specific database commands are required:
val secretCode = "abc"
exec("CREATE USER IF NOT EXISTS GUEST PASSWORD '$secretCode'")
exec("GRANT ALL PRIVILEGES ON ${FilmsTable.nameInDatabaseCase()} TO GUEST")
Transforming results
The SQL string sent to the database may return a result, in which case a transformation block can be optionally provided. The following example retrieves a single result for the current database version:
val version = exec("SELECT H2VERSION()") { result ->
result.next()
result.getString(1)
}
This example iterates over the result and returns a collection of database schema information:
val schema = "TABLE_SCHEMA"
val name = "TABLE_NAME"
val rowCount = "ROW_COUNT_ESTIMATE"
val tableInfo = exec("SELECT $schema, $name, $rowCount FROM INFORMATION_SCHEMA.TABLES") { result ->
val info = mutableListOf<Triple<String, String, Int>>()
while (result.next()) {
info += Triple(result.getString(schema), result.getString(name), result.getInt(rowCount))
}
info
} ?: emptyList()
Alternatively, a convenience extension function could be created to use SQL strings directly, for example:
package org.example.examples
import org.jetbrains.exposed.v1.jdbc.transactions.TransactionManager
import java.sql.ResultSet
fun <T : Any> String.execAndMap(transform: (ResultSet) -> T): List<T> {
val result = mutableListOf<T>()
TransactionManager.current().exec(this) { rs ->
while (rs.next()) {
result += transform(rs)
}
}
return result
}
This function could then be called in the following way:
val toIgnore = "SELECT FILMS.ID, FILMS.TITLE FROM FILMS WHERE FILMS.RATING <= 3.0".execAndMap { result ->
result.getInt("FILMS.ID") to result.getString("FILMS.TITLE")
}
Parameterized statements
SQL strings can be parameterized by substituting values with a ? placeholder in the string and providing associated column types for each parameter argument:
val toWatch = exec(
stmt = "SELECT FILMS.ID, FILMS.TITLE FROM FILMS WHERE (FILMS.NOMINATED = ?) AND (FILMS.RATING >= ?)",
args = listOf(BooleanColumnType() to true, DoubleColumnType() to GOOD_RATING)
) { result ->
val films = mutableListOf<Pair<Int, String>>()
while (result.next()) {
films += result.getInt(1) to result.getString(2)
}
films
}
Explicit statement types
By default, .exec() uses the first keyword of an SQL string to determine how the string should be executed and whether results are expected to be returned by the database. The function attempts to find a match between this keyword and one of the StatementType enum constants.
An argument can always be passed to the parameter explicitStatementType to avoid searching for a match and risking unexpected behavior:
exec(
stmt = "DROP USER IF EXISTS GUEST",
explicitStatementType = StatementType.DROP
)
Of all the defined StatementTypes, only four prompt the function to execute the statement in such a way that results are expected to be returned. These types are:
StatementType.SELECT
StatementType.EXEC
StatementType.SHOW
StatementType.PRAGMA
All other types expect only an affected row count to be returned. This means that an argument can be provided to explicitStatementType in order to override default behavior.
For example, an SQL string that starts with EXPLAIN would default to StatementType.OTHER because no match would be found. This would cause the .exec() to fail because this type of operation prompts the database to return an execution plan for a query. The .exec() would only succeed if a statement type override that expects a result, like StatementType.EXEC, is provided instead:
val plan = exec(
stmt = "EXPLAIN SELECT * FROM FILMS WHERE FILMS.ID = 1",
explicitStatementType = StatementType.EXEC
) { result ->
val data = mutableListOf<Pair<String, Any?>>()
while (result.next()) {
repeat(result.metaData.columnCount) {
data += result.metaData.getColumnName(it + 1) to result.getObject(it + 1)
}
}
data
} ?: emptyList()
Multiple SQL strings
Some databases allow multiple SQL strings of different operation types to be executed together in a single prepared statement, which can be enabled by choosing StatementType.MULTI.
The following example uses a MySQL database to perform an insert operation immediately followed by a query that returns the id column value of the last inserted row:
val insertStmt = "INSERT INTO Films (title, rating, nominated) VALUES (?, ?, ?)"
val lastIdAlias = "last_id"
val selectStmt = "SELECT LAST_INSERT_ID() AS $lastIdAlias"
val lastId = exec(
stmt = "$insertStmt; $selectStmt;",
args = listOf(
VarCharColumnType(MAX_TITLE_LENGTH) to NEW_TITLE,
DoubleColumnType() to NEW_RATING,
BooleanColumnType() to false
),
explicitStatementType = StatementType.MULTI
) { result ->
result.next()
result.getInt(lastIdAlias)
}