Transactions in Play Framework

Question

I'm using play with DB and anorm and I have quite common methods like these:

def insert(user: User) = {
 DB.withConnection { implicit connection =>
  SQL"""
      insert into user(email, password) values (
       ${user.email}, ${user.password}
      )
    """
    .executeInsert()
  }
}

and

def insert(notification: Notification) = {
   DB.withConnection { implicit connection =>
    SQL"""
      insert into notification(date, text) values (
       ${notification.date}, ${notification.text}
      )
    """
    .executeInsert()
  }
}

So, I have 2 methods which use autocommits whitin their connections. What I want to do is to write something like this, i.e. create user, create notification and have a chance to rollback the all stuff in the case of errors:

def createUserAndSendNotification(user:User) = {
 DB.withTransaction {
   insert(user)
   insert(new Notification)
  }
}

, but methods insert(user) and insert(notification) already have set autocommit = 1, so if the notification inserting will fail, user will be still persisted.

I could write plain SQL in createUserAndSendNotification(user:User), but I would use existing methods like insert(user) and insert(notification). Is it possible?

PS1

Possible solution - make the auxiliary method for the each method used in transaction blocks:

def insert(notification: Notification) = {
 DB.withConnection { implicit connection =>
   insertSql(notification)
  }
}

def insertSql(notification: Notification)(implicit connection: Connection) = {
 SQL"""
      insert into notification(date, text) values (
       ${notification.date}, ${notification.text}
      )
    """.executeInsert[Option[Long]]()
}

So:

def createUserAndSendNotification(user:User) = {
 DB.withTransaction {implicit connection =>
   insertSql(user)
   insertSql(new Notification)
  }
}

Answer 1

Try supplying the Connection as an implicit arguments to your fine-grained functions, and only acquiring the Connection once in DB.withTransaction:

def insert(user: User)(implicit connection: Connection) = {
  SQL"""
      insert into user(email, password) values (
       ${user.email}, ${user.password}
      )
    """
    .executeInsert()
}

def insert(notification: Notification)(implicit connection: Connection) = {
    SQL"""
      insert into notification(date, text) values (
       ${notification.date}, ${notification.text}
      )
    """
    .executeInsert()
}

def createUserAndSendNotification(user:User) = {
 DB.withTransaction { implicit connection =>
   insert(user)
   insert(new Notification)
  }
}