How to manage DB related Exceptions in Play! 2.0/Scala using Anorm

Question

I am currently Play!ing with Play 2.0 (Scala). I must admit that it's a lot of fun. I have a question though related to database operations exceptions.

Let's say I have Car as a domain class and that I have an integrity constraint on one of the field, let's say the model so that in the db I can not have two (2) rows having the same model name :

case class Car(id: Pk[Long], name: String, model: String)

I am trying to insert a record in the DB like this :

def create(car: Car): Option[Long] = {
    DB.withConnection { implicit connection =>
      try {
          SQL("insert into cars (name, model) values ({name},{model}").on("name" -> car.name, "model" -> car.model).executeInsert()
      } catch {
          case e: Exception => {
          Logger.debug(e.getMessage())
          None
      } 
    }
}

if I don't catch the exception like in the previous code, then when I call this method from my controller with model having a value already existing in the database, I have the following exception thrown :

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'Enzo' for key 'model'

Is there a way to catch the MySQLIntegrityConstraintViolationException instead of Exception so that I have a fine-grained control over what can go wrong and then provide a more concise feed-back to my user for example (in a browser or on a mobile device) ?

Is this the best way to handle DB-related operations and exceptions or is there any best-practices that everybody use ?

thanks in advance,

Answer 1

I think you are looking like something within these lines:

import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException

catch {
  case e:MySQLIntegrityConstraintViolationException => Logger.debug("Whoops")
  case e:Exception  => {
    Logger.debug(e.getMessage())
    None
  }
}

Important note: make sure you import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException, and not com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException. More precisely, make sure your import matches the exception in your stack trace.

As for best-practices, i dont know as i'm also playing with this framework :).

As for feedback to the user ... perhaps the Flash Scope is a good way to communicate one-liners to the 'next page' (e.g. if the car was succesfully stored or not). See: http://www.playframework.org/documentation/2.0/ScalaSessionFlash (Scroll down to 'Flash scope'.)

Answer 2

I'm working in a bit different playground but, as far as I understand, I solved the same problem. I'm working with liftweb, maven and scala 2.9.

The exception is wrapped with RuntimeException. In order to catch it I catch RuntimeException and examine its cause. In case it is a constraint violation, I do my business, otherwise I throw back the exception. See the following code:

import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException
...
  } catch {
      case e: RuntimeException => {
        e.getCause match {
          case cause: MySQLIntegrityConstraintViolationException => {
          ...
          }
          case _ => throw e
        }
      }
    }

If the build fails with the following error:

error: object mysql is not a member of package com

check the definition of mysql package on maven pom. In my case it was defined as runtime scope. Changing it to compile scope allowed the build to succeed and in runtime this catch is working properly. Here is mysql dependancy section in maven pom.xml:

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.18</version>
        <scope>runtime</scope>
    </dependency>