rollback transcation in mysql if exception comes in scala. i am using scala with play framework and mysql as backend

Question

i am using scala and mysql. i want to apply rollback in my code if any exception comes how can i solve this problem. select query is giving exception so i want roll back insert command.

def demoRollback(user_id:String)={
try{
  DB.withConnection{ implicit c=>
    val roll=SQL("start transaction").execute()
    val result=SQL("insert into demo value('50','jjjjj')").executeInsert()
     println("the select query..........="+result)
    val user=SQL("select user_id from demo where user_id=1")
    val result2=user().map(row=>
    row[String]("user_id")).toList


    result
  }
}
catch{
  case e :Exception =>{
    DB.withConnection{implicit c=>
    val back=SQL("rollback").execute
    }
    println(e)
  }
}

}

Answer 1

I think in this case easy way disable autocommit option. Then you can execute a few queries in same transaction but you should manually set commit and rollback (we dont need finally block because withConnection automaticaly close connection for us):

  val log = Logger(controllers.Application.getClass)

  def demoRollback(user_id: String) = {
    var r: Option[Long] = None
    DB.withConnection {
      implicit c =>
        c.setAutoCommit(false)
        try {
          val result = SQL("insert into demo value('50','jjjjj')").executeInsert()
          log.debug(s"the select query..........= $result")
          val user = SQL("select user_id from demo where user_id=1")
          val result2 = user().map(row => row[String]("user_id")).toList
          c.commit()
          r = result
        } catch {
          case e: Exception => c.rollback()
        }
    }
    r
  }