Version v3.1 of the documentation is no longer actively maintained. The site that you are currently viewing is an archived snapshot. For up-to-date documentation, see the latest version.
Databases
Overview
To access a database with Komapper, an instance of JdbcDatabase or R2dbcDatabase is required.
Here, these are collectively referred to as Database instances.
The Database instances are responsible for transaction control and query execution.
Instantiation
The method of creating a Database instance differs when using JDBC or R2DBC.
When using JDBC
To create a Database instance from a URL, write as follows:
val db: JdbcDatabase = JdbcDatabase("jdbc:h2:mem:example;DB_CLOSE_DELAY=-1")
To specify a username and password in addition to the URL, write as follows:
val db: JdbcDatabase = JdbcDatabase(
url = "jdbc:h2:mem:example;DB_CLOSE_DELAY=-1",
user = "sa",
password = ""
)
javax.sql.DataSource can also be specified.
However, in that case, you must also specify dialect.
val dataSource: DataSource = ...
val db: JdbcDatabase = JdbcDatabase(
dataSource = dataSource,
dialect = H2JdbcDialect()
)
See also Dialects.
When using R2DBC
To create a Database instance from a URL, write as follows:
val db: R2dbcDatabase = R2dbcDatabase("r2dbc:h2:mem:///example;DB_CLOSE_DELAY=-1")
To create a Database instance from io.r2dbc.spi.ConnectionFactoryOptions write as follows:
val options = ConnectionFactoryOptions.builder()
.option(ConnectionFactoryOptions.DRIVER, "h2")
.option(ConnectionFactoryOptions.PROTOCOL, "mem")
.option(ConnectionFactoryOptions.DATABASE, "example")
.option(Option.valueOf("DB_CLOSE_DELAY"), "-1")
.build()
val db: R2dbcDatabase = R2dbcDatabase(options)
The options must contain a value whose key is ConnectionFactoryOptions.DRIVER.
You can also specify io.r2dbc.spi.ConnectionFactory. However, in that case, you must also specify dialect.
val connectionFactory: ConnectionFactory = ...
val db: R2dbcDatabase = R2dbcDatabase(
connectionFactory = connectionFactory,
dialect = H2R2dbcDialect()
)
See also Dialect.
Usage
Transaction Control
Transactions are controlled by the withTransaction function of the Database instance.
The transactional logic is passed to the withTransaction function as a lambda expression.
db.withTransaction {
...
}
See Transaction for details.
Query Execution
Queries are executed by calling the runQuery function of the Database instance.
val a = Meta.address
val query: Query<List<Address>> = QueryDsl.from(a)
val result: List<Address> = db.runQuery(query)
When the Database instance is R2dbcDatabase and the query type is org.komapper.core.dsl.query.FlowQuery,
the flowQuery function can be executed.
val a = Meta.address
val query: FlowQuery<Address> = QueryDsl.from(a)
val flow: Flow<Address> = db.flowQuery(query)
Database access is made only when the flow instance is collected.
See Queries for information on building queries.
Low-level API execution
If the Query DSL API does not meet your requirements, the Low-level APIs are available.
To use the JDBC API directly, call db.config.session.useConnection() to get java.sql.Connection.
val db: JdbcDatabase = ...
db.config.session.useConnection { con: java.sql.Connection ->
val sql = "select employee_name from employee where employee_id = ?"
con.prepareStatement(sql).use { ps ->
ps.setInt(1,10)
ps.executeQuery().use { rs ->
if (rs.next()) {
println(rs.getString(1))
}
}
}
}
Similarly, to use R2DBC API directly, call db.config.session.useConnection() to get io.r2dbc.spi.Connection.
val db: R2dbcDatabase = ...
db.config.session.useConnection { con: io.r2dbc.spi.Connection ->
val sql = "select employee_name from employee where employee_id = ?"
val statement = con.createStatement(sql)
statement.bind(0, 10)
statement.execute().collect { result ->
result.map { row -> row.get(0, String::class.java) }.collect {
println(it)
}
}
}