English | 简体中文

api-docs / org.ktorm.database / Database

Database

class Database (source code)

The entry class of Ktorm, represents a physical database, used to manage connections and transactions.

Connect with a URL

The simplest way to create a database instance, using a JDBC URL:

val database = Database.connect("jdbc:mysql://localhost:3306/ktorm", user = "root", password = "123")

Easy to know what we do in the connect function. Just like any JDBC boilerplate code, Ktorm loads the MySQL
database driver first, then calls DriverManager.getConnection with your URL to obtain a connection.

Of course, Ktorm doesn’t call DriverManager.getConnection in the beginning. Instead, we obtain connections
only when it’s really needed (such as executing a SQL), then close them after they are not useful anymore.
Therefore, database objects created by this way won’t reuse any connections, creating connections frequently
can lead to huge performance costs. It’s highly recommended to use connection pools in your production environment.

Connect with a Pool

Ktorm doesn’t limit you, you can use any connection pool you like, such as DBCP, C3P0 or Druid. The connect
function provides an overloaded version which accepts a DataSource parameter, you just need to create a
DataSource object and call that function with it:

val dataSource = SingleConnectionDataSource() // Any DataSource implementation is OK.
val database = Database.connect(dataSource)

Now, Ktorm will obtain connections from the DataSource when necessary, then return them to the pool after they
are not useful. This avoids the performance costs of frequent connection creation.

Connection pools are applicative and effective in most cases, we highly recommend you manage your connections
in this way.

Use SQL DSL & Sequence APIs

Now that we’ve connected to the database, we can perform many operations on it. Ktorm’s APIs are mainly divided
into two parts, they are SQL DSL and sequence APIs.

Here, we use SQL DSL to obtains the names of all engineers in department 1:

database
.from(Employees)
.select(Employees.name)
.where { (Employees.departmentId eq 1) and (Employees.job eq "engineer") }
.forEach { row ->
println(row[Employees.name])
}

Equivalent code using sequence APIs:

database
.sequenceOf(Employees)
.filter { it.departmentId eq 1 }
.filter { it.job eq "engineer" }
.mapColumns { it.name }
.forEach { name ->
println(name)
}

More details about SQL DSL, see Query, about sequence APIs, see EntitySequence.

Constructors

NameSummary

<init>

Database(
    transactionManager: TransactionManager,
    dialect: SqlDialect = detectDialectImplementation(),
    logger: Logger = detectLoggerImplementation(),
    exceptionTranslator: ((SQLException) -> Throwable)? = null,
    alwaysQuoteIdentifiers: Boolean = false,
    generateSqlInUpperCase: Boolean? = null)

The entry class of Ktorm, represents a physical database, used to manage connections and transactions.

Properties

NameSummary

alwaysQuoteIdentifiers

val alwaysQuoteIdentifiers: Boolean

Whether we need to always quote SQL identifiers in the generated SQLs.

dialect

val dialect: SqlDialect

The dialect, auto detects an implementation by default using JDK ServiceLoader facility.

exceptionTranslator

val exceptionTranslator: ((SQLException) -> Throwable)?

Function used to translate SQL exceptions so as to rethrow them to users.

extraNameCharacters

val extraNameCharacters: String

All the “extra” characters that can be used in unquoted identifier names (those beyond a-z, A-Z, 0-9 and _).

generateSqlInUpperCase

val generateSqlInUpperCase: Boolean?

Whether we need to output the generated SQLs in upper case.

identifierQuoteString

val identifierQuoteString: String

The string used to quote SQL identifiers, returns an empty string if identifier quoting is not supported.

keywords

val keywords: Set<String>

A set of all of this database’s SQL keywords (including SQL:2003 keywords), all in uppercase.

logger

val logger: Logger

The logger used to output logs, auto detects an implementation by default.

maxColumnNameLength

val maxColumnNameLength: Int

The maximum number of characters this database allows for a column name. Zero means that there is no limit
or the limit is not known.

name

val name: String

The name of the connected database.

productName

val productName: String

The name of the connected database product, eg. MySQL, H2.

productVersion

val productVersion: String

The version of the connected database product.

storesLowerCaseIdentifiers

val storesLowerCaseIdentifiers: Boolean

Whether this database treats mixed case unquoted SQL identifiers as case insensitive and
stores them in lower case.

storesLowerCaseQuotedIdentifiers

val storesLowerCaseQuotedIdentifiers: Boolean

Whether this database treats mixed case quoted SQL identifiers as case insensitive and
stores them in lower case.

storesMixedCaseIdentifiers

val storesMixedCaseIdentifiers: Boolean

Whether this database treats mixed case unquoted SQL identifiers as case insensitive and
stores them in mixed case.

storesMixedCaseQuotedIdentifiers

val storesMixedCaseQuotedIdentifiers: Boolean

Whether this database treats mixed case quoted SQL identifiers as case insensitive and
stores them in mixed case.

storesUpperCaseIdentifiers

val storesUpperCaseIdentifiers: Boolean

Whether this database treats mixed case unquoted SQL identifiers as case insensitive and
stores them in upper case.

storesUpperCaseQuotedIdentifiers

val storesUpperCaseQuotedIdentifiers: Boolean

Whether this database treats mixed case quoted SQL identifiers as case insensitive and
stores them in upper case.

supportsMixedCaseIdentifiers

val supportsMixedCaseIdentifiers: Boolean

Whether this database treats mixed case unquoted SQL identifiers as case sensitive and as a result
stores them in mixed case.

supportsMixedCaseQuotedIdentifiers

val supportsMixedCaseQuotedIdentifiers: Boolean

Whether this database treats mixed case quoted SQL identifiers as case sensitive and as a result
stores them in mixed case.

transactionManager

val transactionManager: TransactionManager

The transaction manager used to manage connections and transactions.

url

val url: String

The URL of the connected database.

Functions

NameSummary

executeBatch

fun executeBatch(expressions: List<SqlExpression>): IntArray

Batch execute the given SQL expressions and return the effected row counts for each expression.

executeExpression

fun <T> executeExpression(
    expression: SqlExpression,
    func: (PreparedStatement) -> T
): T

Format the given expression to a SQL string with its execution arguments, then create
a PreparedStatement from the this database using the SQL string and execute the specific
callback function with it. After the callback function completes, the statement will be
closed automatically.

executeQuery

fun executeQuery(expression: SqlExpression): CachedRowSet

Format the given expression to a SQL string with its execution arguments, then execute it via
PreparedStatement.executeQuery and return the result CachedRowSet.

executeUpdate

fun executeUpdate(expression: SqlExpression): Int

Format the given expression to a SQL string with its execution arguments, then execute it via
PreparedStatement.executeUpdate and return the effected row count.

executeUpdateAndRetrieveKeys

fun executeUpdateAndRetrieveKeys(
    expression: SqlExpression
): Pair<Int, CachedRowSet>

Format the given expression to a SQL string with its execution arguments, execute it via
PreparedStatement.executeUpdate, then return the effected row count along with the generated keys.

formatExpression

fun formatExpression(
    expression: SqlExpression,
    beautifySql: Boolean = false,
    indentSize: Int = 2
): Pair<String, List<ArgumentExpression<*>>>

Format the specific SqlExpression to an executable SQL string with execution arguments.

useConnection

fun <T> useConnection(func: (Connection) -> T): T

Obtain a connection and invoke the callback function with it.

useTransaction

fun <T> useTransaction(
    isolation: TransactionIsolation? = null,
    func: (Transaction) -> T
): T

Execute the specific callback function in a transaction and returns its result if the execution succeeds,
otherwise, if the execution fails, the transaction will be rollback.

Companion Object Functions

NameSummary

connect

fun connect(
    dialect: SqlDialect = detectDialectImplementation(),
    logger: Logger = detectLoggerImplementation(),
    alwaysQuoteIdentifiers: Boolean = false,
    generateSqlInUpperCase: Boolean? = null,
    connector: () -> Connection
): Database

Connect to a database by a specific connector function.

fun connect(
    dataSource: DataSource,
    dialect: SqlDialect = detectDialectImplementation(),
    logger: Logger = detectLoggerImplementation(),
    alwaysQuoteIdentifiers: Boolean = false,
    generateSqlInUpperCase: Boolean? = null
): Database

Connect to a database using a DataSource.

fun connect(
    url: String,
    driver: String? = null,
    user: String? = null,
    password: String? = null,
    dialect: SqlDialect = detectDialectImplementation(),
    logger: Logger = detectLoggerImplementation(),
    alwaysQuoteIdentifiers: Boolean = false,
    generateSqlInUpperCase: Boolean? = null
): Database

Connect to a database using the specific connection arguments.

connectWithSpringSupport

fun connectWithSpringSupport(
    dataSource: DataSource,
    dialect: SqlDialect = detectDialectImplementation(),
    logger: Logger = detectLoggerImplementation(),
    alwaysQuoteIdentifiers: Boolean = false,
    generateSqlInUpperCase: Boolean? = null
): Database

Connect to a database using a DataSource with the Spring support enabled.

Extension Functions

NameSummary

batchInsert

fun <T : BaseTable<*>> Database.batchInsert(
    table: T,
    block: BatchInsertStatementBuilder<T>.() -> Unit
): IntArray

Construct insert expressions in the given closure, then batch execute them and return the effected
row counts for each expression.

batchUpdate

fun <T : BaseTable<*>> Database.batchUpdate(
    table: T,
    block: BatchUpdateStatementBuilder<T>.() -> Unit
): IntArray

Construct update expressions in the given closure, then batch execute them and return the effected
row counts for each expression.

bulkInsert

fun <T : BaseTable<*>> Database.bulkInsert(
    table: T,
    block: BulkInsertStatementBuilder<T>.() -> Unit
): Int

Construct a bulk insert expression in the given closure, then execute it and return the effected row count.

bulkInsert

fun <T : BaseTable<*>> Database.bulkInsert(
    table: T,
    block: BulkInsertStatementBuilder<T>.(T) -> Unit
): Int

Bulk insert records to the table and return the effected row count.

bulkInsertOrUpdate

fun <T : BaseTable<*>> Database.bulkInsertOrUpdate(
    table: T,
    block: BulkInsertOrUpdateStatementBuilder<T>.() -> Unit
): Int

Bulk insert records to the table, determining if there is a key conflict while inserting each of them,
and automatically performs updates if any conflict exists.

bulkInsertOrUpdate

fun <T : BaseTable<*>> Database.bulkInsertOrUpdate(
    table: T,
    block: BulkInsertOrUpdateStatementBuilder<T>.(T) -> Unit
): Int

Bulk insert records to the table, determining if there is a key conflict while inserting each of them,
and automatically performs updates if any conflict exists.

bulkInsertOrUpdateReturning

fun <T : BaseTable<*>, C : Any> Database.bulkInsertOrUpdateReturning(
    table: T,
    returning: Column<C>,
    block: BulkInsertOrUpdateStatementBuilder<T>.(T) -> Unit
): List<C?>

Bulk insert records to the table, determining if there is a key conflict while inserting each of them,
automatically performs updates if any conflict exists, and finally returns the specific column.

fun <T : BaseTable<*>, C1 : Any, C2 : Any> Database.bulkInsertOrUpdateReturning(
    table: T,
    returning: Pair<Column<C1>, Column<C2>>,
    block: BulkInsertOrUpdateStatementBuilder<T>.(T) -> Unit
): List<Pair<C1?, C2?>>

fun <T : BaseTable<*>, C1 : Any, C2 : Any, C3 : Any> Database.bulkInsertOrUpdateReturning(
    table: T,
    returning: Triple<Column<C1>, Column<C2>, Column<C3>>,
    block: BulkInsertOrUpdateStatementBuilder<T>.(T) -> Unit
): List<Triple<C1?, C2?, C3?>>

Bulk insert records to the table, determining if there is a key conflict while inserting each of them,
automatically performs updates if any conflict exists, and finally returns the specific columns.

bulkInsertReturning

fun <T : BaseTable<*>, C : Any> Database.bulkInsertReturning(
    table: T,
    returning: Column<C>,
    block: BulkInsertStatementBuilder<T>.(T) -> Unit
): List<C?>

Bulk insert records to the table and return the specific column’s values.

fun <T : BaseTable<*>, C1 : Any, C2 : Any> Database.bulkInsertReturning(
    table: T,
    returning: Pair<Column<C1>, Column<C2>>,
    block: BulkInsertStatementBuilder<T>.(T) -> Unit
): List<Pair<C1?, C2?>>

fun <T : BaseTable<*>, C1 : Any, C2 : Any, C3 : Any> Database.bulkInsertReturning(
    table: T,
    returning: Triple<Column<C1>, Column<C2>, Column<C3>>,
    block: BulkInsertStatementBuilder<T>.(T) -> Unit
): List<Triple<C1?, C2?, C3?>>

Bulk insert records to the table and return the specific columns’ values.

delete

fun <T : BaseTable<*>> Database.delete(
    table: T,
    predicate: (T) -> ColumnDeclaring<Boolean>
): Int

Delete the records in the table that matches the given predicate.

deleteAll

fun Database.deleteAll(table: BaseTable<*>): Int

Delete all the records in the table.

from

fun Database.from(table: BaseTable<*>): QuerySource

Wrap the specific table as a QuerySource.

insert

fun <T : BaseTable<*>> Database.insert(
    table: T,
    block: AssignmentsBuilder.(T) -> Unit
): Int

Construct an insert expression in the given closure, then execute it and return the effected row count.

insertAndGenerateKey

fun <T : BaseTable<*>> Database.insertAndGenerateKey(
    table: T,
    block: AssignmentsBuilder.(T) -> Unit
): Any

Construct an insert expression in the given closure, then execute it and return the auto-generated key.

insertOrUpdate

fun <T : BaseTable<*>> Database.insertOrUpdate(
    table: T,
    block: InsertOrUpdateStatementBuilder.(T) -> Unit
): Int

Insert a record to the table, determining if there is a key conflict while it’s being inserted, and automatically
performs an update if any conflict exists.

insertOrUpdate

fun <T : BaseTable<*>> Database.insertOrUpdate(
    table: T,
    block: InsertOrUpdateStatementBuilder.(T) -> Unit
): Int

Insert a record to the table, determining if there is a key conflict while it’s being inserted, and automatically
performs an update if any conflict exists.

insertOrUpdateReturning

fun <T : BaseTable<*>, C : Any> Database.insertOrUpdateReturning(
    table: T,
    returning: Column<C>,
    block: InsertOrUpdateStatementBuilder.(T) -> Unit
): C?

Insert a record to the table, determining if there is a key conflict while it’s being inserted, automatically
performs an update if any conflict exists, and finally returns the specific column.

fun <T : BaseTable<*>, C1 : Any, C2 : Any> Database.insertOrUpdateReturning(
    table: T,
    returning: Pair<Column<C1>, Column<C2>>,
    block: InsertOrUpdateStatementBuilder.(T) -> Unit
): Pair<C1?, C2?>

fun <T : BaseTable<*>, C1 : Any, C2 : Any, C3 : Any> Database.insertOrUpdateReturning(
    table: T,
    returning: Triple<Column<C1>, Column<C2>, Column<C3>>,
    block: InsertOrUpdateStatementBuilder.(T) -> Unit
): Triple<C1?, C2?, C3?>

Insert a record to the table, determining if there is a key conflict while it’s being inserted, automatically
performs an update if any conflict exists, and finally returns the specific columns.

insertReturning

fun <T : BaseTable<*>, C : Any> Database.insertReturning(
    table: T,
    returning: Column<C>,
    block: AssignmentsBuilder.(T) -> Unit
): C?

Insert a record to the table and return the specific column.

fun <T : BaseTable<*>, C1 : Any, C2 : Any> Database.insertReturning(
    table: T,
    returning: Pair<Column<C1>, Column<C2>>,
    block: AssignmentsBuilder.(T) -> Unit
): Pair<C1?, C2?>

fun <T : BaseTable<*>, C1 : Any, C2 : Any, C3 : Any> Database.insertReturning(
    table: T,
    returning: Triple<Column<C1>, Column<C2>, Column<C3>>,
    block: AssignmentsBuilder.(T) -> Unit
): Triple<C1?, C2?, C3?>

Insert a record to the table and return the specific columns.

invoke

operator fun <T> Database.invoke(func: Database.() -> T): T

Execute the callback function using the current database instance.

sequenceOf

fun <E : Any, T : BaseTable<E>> Database.sequenceOf(
    table: T,
    withReferences: Boolean = true
): EntitySequence<E, T>

Create an EntitySequence from the specific table.

update

fun <T : BaseTable<*>> Database.update(
    table: T,
    block: UpdateStatementBuilder.(T) -> Unit
): Int

Construct an update expression in the given closure, then execute it and return the effected row count.

Companion Object Extension Properties

NameSummary

global

val Database.Companion.global: Database

The global database instance, Ktorm uses this property to obtain a database when any SQL is executed.

Companion Object Extension Functions

NameSummary

connectGlobally

fun Database.Companion.connectGlobally(
    dialect: SqlDialect = detectDialectImplementation(),
    logger: Logger = detectLoggerImplementation(),
    alwaysQuoteIdentifiers: Boolean = false,
    generateSqlInUpperCase: Boolean? = null,
    connector: () -> Connection
): Database

Connect to a database by a specific connector function and save the returned database instance
to Database.Companion.global.

fun Database.Companion.connectGlobally(
    dataSource: DataSource,
    dialect: SqlDialect = detectDialectImplementation(),
    logger: Logger = detectLoggerImplementation(),
    alwaysQuoteIdentifiers: Boolean = false,
    generateSqlInUpperCase: Boolean? = null
): Database

Connect to a database using a DataSource and save the returned database instance to Database.Companion.global.

fun Database.Companion.connectGlobally(
    url: String,
    driver: String? = null,
    user: String? = null,
    password: String? = null,
    dialect: SqlDialect = detectDialectImplementation(),
    logger: Logger = detectLoggerImplementation(),
    alwaysQuoteIdentifiers: Boolean = false,
    generateSqlInUpperCase: Boolean? = null
): Database

Connect to a database using the specific connection arguments and save the returned database instance
to Database.Companion.global.

connectWithSpringSupportGlobally

fun Database.Companion.connectWithSpringSupportGlobally(
    dataSource: DataSource,
    dialect: SqlDialect = detectDialectImplementation(),
    logger: Logger = detectLoggerImplementation(),
    alwaysQuoteIdentifiers: Boolean = false,
    generateSqlInUpperCase: Boolean? = null
): Database

Connect to a database using a DataSource with the Spring support enabled and save the returned database
instance to Database.Companion.global.