Transaction
#readOnly block / session
Executes query in read-only mode.
val names = DB readOnly { implicit session =>
sql"select name from emp".map { rs => rs.string("name") }.list.apply()
}
implicit val session = DB.readOnlySession
try {
val names = sql"select name from emp".map { rs => rs.string("name") }.list.apply()
// do something
} finally {
session.close()
}
Of course, update
operations in read-only mode will cause java.sql.SQLException
.
DB readOnly { implicit session =>
sql"update emp set name = ${name} where id = ${id}".update.apply()
} // will throw java.sql.SQLException
#autoCommit block / session
Executes query / update in auto-commit mode.
val count = DB autoCommit { implicit session =>
sql"update emp set name = ${name} where id = ${id}".update.apply()
}
When using autoCommitSession, every operation will be executed in auto-commit mode.
implicit val session = DB.autoCommitSession
try {
sql"update emp set name = ${name1} where id = ${id1}".update.apply() // auto-commit
sql"update emp set name = ${name2} where id = ${id2}".update.apply() // auto-commit
} finally { session.close() }
#localTx block
Executes query / update in block-scoped transactions.
If an Exception was thrown in the block, the transaction will perform rollback automatically.
val count = DB localTx { implicit session =>
// --- transcation scope start ---
sql"update emp set name = ${name1} where id = ${id1}".update.apply()
sql"update emp set name = ${name2} where id = ${id2}".update.apply()
// --- transaction scope end ---
}
TxBoundary
provides other transaction boundary instead of Exception as follows (2.2.0 or higher):
import scalikejdbc._
import scala.util.Try
import scalikejdbc.TxBoundary.Try._
val result: Try[Result] = DB localTx { implicit session =>
Try { doSomeStaff() }
}
// localTx rolls back when `result` is `Failure`
// https://www.scala-lang.org/api/current/scala/util/Try.html
Built-in type class instances are Try
, Either
and Future
. You can use them by import scalikejdbc.TxBoundary,***._
.
#futureLocalTx block
futureLocalTx
use Future
‘s state as transaction boundary. If one of the Future operations was failed, the transaction will perform rollback automatically.
object FutureDB {
implicit val ec = myOwnExecutorContext
def updateFirstName(id: Int, firstName: String)(implicit session: DBSession): Future[Int] = {
Future {
blocking {
session.update("update users set first_name = ? where id = ?", firstName, id)
}
}
}
def updateLastName(id: Int, lastName: String)(implicit session: DBSession): Future[Int] = {
Future {
blocking {
session.update("update users set last_name = ? where id = ?", lastName, id)
}
}
}
}
object Example {
import FutureDB._
val fResult = DB futureLocalTx { implicit s =>
updateFirstName(3, "John").flatMap(_ => updateLastName(3, "Smith"))
}
}
Example.fResult.foreach(println(_))
// #=> 1
or TxBoundary[Future[A]]
is also available.
import scalikejdbc.TxBoundary.Future._
val fResult = DB localTx { implicit s =>
updateFirstName(3, "John").flatMap(_ => updateLastName(3, "Smith"))
}
Working with IO monads
IO monad minimal example
MyIO
sealed abstract class MyIO[+A] {
import MyIO._
def flatMap[B](f: A => MyIO[B]): MyIO[B] = {
this match {
case Delay(thunk) => Delay(() => f(thunk()).run())
}
}
def map[B](f: A => B): MyIO[B] = flatMap(x => MyIO(f(x)))
def run(): A = {
this match {
case Delay(f) => f.apply()
}
}
def attempt: MyIO[Either[Throwable, A]] =
MyIO(try {
Right(run())
} catch {
case scala.util.control.NonFatal(t) => Left(t)
})
}
object MyIO {
def apply[A](a: => A): MyIO[A] = Delay(() => a)
final case class Delay[+A](thunk: () => A) extends MyIO[A]
}
TxBoundary typeclass instance for MyIO[A]
import scalikejdbc._
implicit def myIOTxBoundary[A]: TxBoundary[MyIO[A]] = new TxBoundary[MyIO[A]] {
def finishTx(result: MyIO[A], tx: Tx): MyIO[A] = {
result.attempt.flatMap {
case Right(a) => MyIO(tx.commit()).flatMap(_ => MyIO(a))
case Left(e) => MyIO(tx.rollback()).flatMap(_ => MyIO(throw e))
}
}
override def closeConnection(result: MyIO[A], doClose: () => Unit): MyIO[A] = {
for {
x <- result.attempt
_ <- MyIO(doClose())
a <- MyIO(x.fold(throw _, identity))
} yield a
}
}
localTx
To use scalikejdbc
with IO monads, you cannot use localTx
out of the box.
You must use it with a TxBoundary
instance for MyIO[A]
import scalikejdbc._
type A = ???
def asyncExecution: DBSession => MyIO[A] = ???
// default
DB.localTx(asyncExecution)(boundary = myIOTxBoundary)
// named
NamedDB("named").localTx(asyncExecution)(boundary = myIOTxBoundary)
Cats Effect IO example
TxBoundary typeclass instance for cats.effect.IO[A]
cats.effect.IO
offers two ways to handle completion/cancellation as below. You can use guaranteeCase
for commit/rollback operations while going with guarantee
for connection closure.
import scalikejdbc._
import cats.effect._
implicit def catsEffectIOTxBoundary[A]: TxBoundary[IO[A]] = new TxBoundary[IO[A]] {
def finishTx(result: IO[A], tx: Tx): IO[A] =
result.guaranteeCase {
case ExitCase.Completed => IO(tx.commit())
case _ => IO(tx.rollback())
}
override def closeConnection(result: IO[A], doClose: () => Unit): IO[A] =
result.guarantee(IO(doClose()))
}
localTx
To take control of all the side-effects that happen in localTx
method, you can use suspend
method to wrap the code blocks using localTx
.
import scalikejdbc._
import cats.effect._
type A = ???
def ioExecution: DBSession => IO[A] = ???
// default
IO.suspend {
DB.localTx(ioExecution)(boundary = catsEffectIOTxBoundary)
}
// named
IO.suspend {
NamedDB("named").localTx(ioExecution)(boundary = catsEffectIOTxBoundary)
}
#withinTx block / session
Executes query / update in already existing transactions.
In this case, all the transactional operations (such as Tx#begin()
, Tx#rollback()
or Tx#commit()
) should be managed by users of ScalikeJDBC.
val db = DB(conn)
try {
db.begin()
val names = db withinTx { implicit session =>
// if a transaction has not been started, IllegalStateException will be thrown
sql"select name from emp".map { rs => rs.string("name") }.list.apply()
}
db.rollback() // it might throw Exception
} finally { db.close() }
val db = DB(conn)
try {
db.begin()
implicit val session = db.withinTxSession()
val names = sql"select name from emp".map { rs => rs.string("name") }.list.apply()
db.rollbackIfActive() // it NEVER throws Exception
} finally { db.close() }