Database

Utility class to use connections given by the ConnectionSupplier, in a suspending style.

Use BlockingDatabase if you don't use Kotlin.

Nested transaction

Nested transactions are transactions that use an existing connection, enabling you to, for example, have a transactional inside another.

They are supported on methods that use callbacks and work with coroutines.

Nested transaction block

Queries executed in a nested transaction are not committed when exiting the block, only top-level transactions do.

Read-only status

A nested transaction cannot be created if the existing connection is read-only and the nested transaction is read-write.

• ✓ Read-write -> Read-only

• ✓ Read-only -> Read-only

• ✗ Read-only -> Read-write

Keep in mind that a read-only transaction will not prevent write operation, as they mostly enable optimizations, and only a few databases reject modifying queries.

Tracing

The connection could be wrapped depending on the configuration, for example, to log the queries (in which case a ParametrizedQuery is used), as well as timing them.

A SQL statement is traced if any of these conditions is met:

• BDatabaseConfig.logQueries is enabled, and the logger of the class that created the prepared statement has its TRACE logs enabled,

• BDatabaseConfig.queryLogThreshold is configured

The logged SQL statements will use the logger of the class that created the prepared statement. If a utility class creates statements, you should use @IgnoreStackFrame, which will instead take the logger of the class that called your utility class. You can also use PreparedStatement.withLogger if you wish to use a different logger.

Batching support

If you must run a lot of DML statements (INSERT, UPDATE, ...), you can batch them as to execute all of them in one go, massively improving performances on larger updates.

For that, you can use any function giving you a prepared statement, then, you can add statements by:

• Adding the parameters using SuspendingPreparedStatement.setParameters,

• Calling SuspendingPreparedStatement.addBatch.

Repeat those two steps for all your statements, then call SuspendingPreparedStatement.executeBatch to run all of them.

Note: To read returned columns (like an INSERT INTO ... RETURNING {column} in PostgreSQL), you must specify the column indexes/names when creating your statement, and read them back from SuspendingPreparedStatement.getGeneratedKeys.

See also