Database
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
Types
Properties
Whether we need to always quote SQL identifiers in the generated SQLs.
The dialect, auto-detects an implementation by default using JDK ServiceLoader facility.
Function used to translate SQL exceptions to rethrow them to users.
All the "extra" characters that can be used in unquoted identifier names (those beyond a-z, A-Z, 0-9 and _).
Whether we need to output the generated SQLs in upper case.
The string used to quote SQL identifiers, returns an empty string if identifier quoting is not supported.
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.
The name of the connected database product, e.g. MySQL, H2.
The version of the connected database product.
Whether this database treats mixed case unquoted SQL identifiers as case-insensitive and stores them in lower case.
Whether this database treats mixed case quoted SQL identifiers as case-insensitive and stores them in lower case.
Whether this database treats mixed case unquoted SQL identifiers as case-insensitive and stores them in mixed case.
Whether this database treats mixed case quoted SQL identifiers as case-insensitive and stores them in mixed case.
Whether this database treats mixed case unquoted SQL identifiers as case-insensitive and stores them in upper case.
Whether this database treats mixed case quoted SQL identifiers as case-insensitive and stores them in upper case.
Whether this database treats mixed case unquoted SQL identifiers as case-sensitive and as a result stores them in mixed case.
Whether this database treats mixed case quoted SQL identifiers as case-sensitive and as a result stores them in mixed case.
The transaction manager used to manage connections and transactions.
Functions
Construct insert expressions in the given closure, then batch execute them and return the effected row counts for each expression.
Construct update expressions in the given closure, then batch execute them and return the effected row counts for each expression.
Batch execute the given SQL expressions and return the effected row counts for each expression.
Format the given expression to a SQL string with its execution arguments, then create a PreparedStatement for the database using the SQL string and execute the specific callback function with it. After the callback function completes, the statement will be closed automatically.
Format the given expression to a SQL string with its execution arguments, then execute it via PreparedStatement.executeQuery and return the result CachedRowSet.
Format the given expression to a SQL string with its execution arguments, then execute it via PreparedStatement.executeUpdate and return the effected row count.
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.
Format the specific SqlExpression to an executable SQL string with execution arguments.
Wrap the specific table as a QuerySource.
Construct an insert expression in the given closure, then execute it and return the auto-generated key.
Create an EntitySequence from the specific table.
Obtain a connection and invoke the callback function with it.
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.
