The core module is a fairly thin wrapper over JDBC, providing support for named parameters, logging
and transactions.
Sessions are kwery's core interface for querying
and updating databases. e.g.
val session = DefaultSession(connection, PostgresDialect(), LoggingInterceptor())
val sql = "select * from actor where first_name = :first_name"
class Actor(val firstName: String, val lastName: String, val lastUpdate: Timestamp)
val actors = session.select(sql, mapOf("first_name" to "Brad")) { row ->
Actor(row.string("first_name"), row.string("last_name"), row.timestamp("last_update"))
}A Session is bound to a single JDBC connection. For simple use cases DefaultSession can be used
supplying the connection directly (as shown above).
When using a pooled DataSource, as SessionFactory
allows for automatically obtaining and returning a connection from the pool. e.g.
val factory = SessionFactory(hsqlDataSource, PostgresDialect(), LoggingInterceptor())
factory.use { session ->
...
}A ThreadLocalSession is the most flexible option to use server-side.
A single instance of ThreadLocalSession can be shared globally. By default, it obtains and releases
connections from a pooled DataSource automatically for each statement.
However, when a transaction is started via Session.transaction the underlying connection
will be shared by all session uses on the thread for the duration of the transaction.
val session = ThreadLocalSession(hsqlDataSource, PostgresDialect(), LoggingInterceptor())
session.select(...)Finally, kwery supports annotation-based transaction management using
a ManagedThreadLocalSession.
These require interceptors to automatically manage transactions. They have the advantage of defaulting
to a sensible strategy, however they often hold connections open longer than necessary.
See the transactional-jersey module and transactional module for more details.
Kwery supports logging of SQL statements with parameters bound inline so statements can be copied and pasted into database terminal and executed without modification.
To do this it requires a database Dialect
to define the database specific literal formats for types such as timestamps and blobs.
Dialects also allow use of database specific features in the mapper module.
To use a dialect, specify it during Session creation as shown above.
StatementInterceptors allow
tracking (and potential modification) of statement execution.
The main use cases at present are to log statements and collection performance metrics. There's a couple of in-built logging interceptors.
LoggingInterceptor logs
full statements. Varying the log level controls when it logs statements and it includes a threshold to work
as a "slow query log".
19:50:40.049 [main] DEBUG c.g.a.k.c.i.LoggingInterceptor -
insert into film(title, release_year, language_id, original_language_id, length, rating, last_update, special_features)
values ('Underworld: Evolution', 2006, 1003, null, 6360000, 'R', '2015-07-22 19:50:40.038', array['Behind the Scenes']);
Successfully executed FilmDao.insert in 0.311 ms (0.437 ms). Rows affected: 1. TXN: 1
LoggingSummaryInterceptor
logs a summary of statements executed. It's designed to collect timings for complex requests, giving a
breakdown per query. See the LoggingListener
in the example project to see how it can be used to wrap http requests:
Executed 4 statements in 21.923 ms (closed in 52.205 ms) affecting 6,663 rows using 25.6% of request total (203.573 ms):
Calls Exec Close Rows
FilmDao.findAll 1 3.525 27.283 1,000 52.3%
FilmActorDao.findByFilmIds 1 15.701 21.679 5,462 41.5%
ActorDao.findByIds 1 1.339 1.748 200 3.3%
LanguageDao.findByIds 1 1.357 1.496 1 2.9%
Session provides several methods for querying data, but select is the most commonly used method.
select executes a query returning the results as List.
val sql = "select first_name, last_name from actor"
val actors = session.select(sql) { row ->
row.string("first_name") to row.string("last_name")
}As shown above, in addition to the query, select takes a row function that maps the result to objects.
The row function has a Row parameter to extract data from the underlying ResultSet.
Row is a thin wrapper over ResultSet providing a cleaner api for dealing with null results in Kotlin.
select also supports an optional Map of parameters.
val sql = "select first_name, last_name from actor where first_name = :name"
val params = mapOf("name" to "Bill")
val actors = session.select(sql, params) { row ->
row.string("first_name") to row.string("last_name")
}Finally, select accepts a StatementOptions
to set some of less frequently used JDBC settings.
asSequence executes a query, providing the results as a sequence for streaming.
This allows for flexible processing of large result sets without loading them into memory.
val sql = "select first_name, last_name from actor"
session.asSequence(sql) { rows ->
val actors = rows.map { row.string("first_name") to row.string("last_name") }
writeToFile(actors)
}
fun writeToFile(actors: Sequence<Pair<String, String>) {
...
}Sequences provide great flexibility for processing, in particular using map to lazily transform them into
a sequence of objects.
forEach is an alternative method for streaming results.
It is slightly more concise than asSequence, but less flexible as processing must be done inline.
val sql = "select first_name, last_name from actor"
val outputStream = ...
session.forEach(sql) { row ->
val actor = row.map { row.string("first_name") to row.string("last_name") }
writeToFile(outputStream, actor)
}update is essentially identical to select except that it returns the count of rows affected instead
of a result set.
val sql = "update actor set first_name = :first_name where last_name = 'Bennet'"
val count = session.update(sql, mapOf("first_name" to "Felicity"))insert is a variant of update that supports generated keys.
create table test (key serial, value varchar(1000))val sql = "insert into test(value) values (:value)"
val (count, key) = session.insert(insertSql, mapOf("value" to "foo")) { it.int("key") }To batch insert or update, use the batchInsert and batchUpdate functions respectively.
They work the same way as their single row counterparts, but accept and return lists instead.
Session's transaction function starts a transaction for the lifetime of the supplied function block.
session.transaction {
session.update(...)
session.update(...)
}In the above example, the transaction is implicitly committed unless an exception is thrown.
The transaction function also exposes a Transaction object that provides the ability
force a roll back without throwing an exception.
session.transaction { t ->
if (...) {
t.rollbackOnly = true
}
}The transaction object also allows the registration of handlers to be called back
pre commit, post commit and on rollback.
These allow for use cases like synchronising caches post commit, or inserting to audit tables pre commit.
val cache = ...
session.transaction { t ->
val actor = insert(Actor("Kate", "Beckinsale"))
t.postCommitHandler { committed, session ->
if (committed) {
cache[actor.id] = actor
}
}
}Transaction blocks are re-entrant. This means that if a transaction block is invoked inside
of another transaction block, it will join the existing transaction.
This, together with ThreadLocalSessions, is the recommended way of combining services
that use transactions.
A useful idiom for wrapping an entire function in a transaction is to use Kotlin's single expression function body syntax:
fun myTransactionalFunction = session.transaction {
...
}
This provides a transaction without having to nest the entire function body.
Building dynamic query strings becomes a little painful - having to keep track of which conditions and parameters to apply, or adding join tables as a result of a condition.
QueryBuilder
handles this kind of basic concatenation for you resulting in
an SQL statement and parameters that can then be executed.
Note: QueryBuilder is not a type safe builder - it merely concatenates
SQL strings and accumulates parameters.
A typical use case would be providing a search function where users may enter in one or more optional criteria to narrow down search results.
e.g. Given the following criteria object:
data class FilmCriteria(
val ratings: Set<FilmRating> = setOf(),
val title: String? = null,
val releaseYear: Int? = null,
val maxDuration: Duration? = null,
val actor: Actor? = null,
val limit: Int? = null,
val offset: Int? = null
)We can then define a dynamic search function that ANDs together any parameters
specified as follows:
fun findByCriteria(criteria: FilmCriteria): List<F> {
val query = query {
select("select f.* from film f")
whereGroup {
criteria.title?.let {
where("lower(f.title) like :title")
parameter("title", "%${it.toLowerCase()}%")
}
criteria.releaseYear?.let {
where("f.release_year = :release_year")
parameter("release_year", it)
}
criteria.actor?.let {
where("exists (select 1 from film_actor where film_id = f.film_id and actor_id = :actor_id)")
parameter("actor_id", it.id)
}
criteria.maxDuration?.let {
where("(f.length is null or f.length <= :max_length)")
parameter("max_length", durationConverter.to(session.connection, it))
}
if (criteria.ratings.isNotEmpty()) {
where("f.rating in (:ratings)")
parameter("ratings", criteria.ratings.map { ratingsConverter.to(session.connection, it) })
}
}
orderBy("title, release_year")
}
return session.select(query.sql, query.parameters, StatementOptions(limit = criteria.limit,
offset = criteria.offset), mapper = ...)
}See the tests
for more examples of builders.