Working With Transactions - novus/novus-jdbc GitHub Wiki

Transactions provide a means of executing queries and code in the same context such that nothing is committed to the database until the last line of code is evaluated. The primary interface for working in a transaction is the SavePoint. SavePoints expose all the CRUD and [stored procedure] methods that the QueryExecutor exposes. In addition, it provides two other types of methods: rollback and save.

Creating a Save Point

A call to save, either the named or unnamed save point, produces another SavePoint object. While safe to use both the previous and now current SavePoint to construct queries, it is strongly discouraged. The save member function produces a stateful change recorded in a non-transparent way through the underlying database connection object.

  def run(name: String) = executor transaction{ point =>
    val id = point.insert("INSERT INTO foo VALUES(?, ?, ?)", name, 1, 2)
    val saved = point save ()    
    //from here on out, the insert will always have happened if we roll back to "saved"

Rolling Back

When the rollback method of a SavePoint is called, all changes to the database up to that point are undone. Like the save method, it produces a stateful change in a non-transparent way through the underlying database connection. Unlike the save method, it is not safe to use both the previous and new SavePoint objects. Rolling back a SavePoint invalidates any child SavePoints and can cause exceptions to be thrown if those SavePoints are used.

Due note, rolling back does not undo any stateful or side-effecting changes that might have occurred between the creation and the roll back of the SavePoint outside of the database. It is because of this fact that we strongly encourage no additional side-effects. If you must, please use some other means of accumulating these side effects (Reader/Writer/State Monad come to mind.)

def updateJob(name: String) = executor.transaction{ saved =>
  val job = id flatMap { fooId =>
    saved.select("SELECT FROM jobs JOIN foo ON jobs.type = foo.type WHERE foo.id = ?", fooId){ row =>
      Job(row getString "type", row getString "customer", row getDouble "cost")
    }
  }
  val updated = saved.update("UPDATE jobs SET customer = ? WHERE id = IN(?)", name, jobs.map(_.id).toList)
  val continue = if(updated > 5) saved rollback() else saved
  //and so on