Play Scala Anorm dynamic SQL for UPDATE query

Question

My Google-fu is letting me down, so I'm hoping you can help

I'm building some webservices is the play framework using scala and anorm for database access

One of my calls is to update an existing row in a database - i.e run a query like

UPDATE [Clerks]
   SET [firstName] = {firstName}
  ,[lastName] = {lastName}
  ,[login] = {login}
  ,[password] = {password}
 WHERE [id] = {id}

My method receives a clerk object BUT all the parameters are optional (except the id of course) as they may only wish to update a single column of the row like so

UPDATE [Clerks]
   SET [firstName] = {firstName}
 WHERE [id] = {id}

So I want the method to check which clerk params are defined and build the 'SET' part of the update statement accordingly

It seems like there should be a better way than to go through each param of the clerk object, check if it is defined and build the query string - but I've been unable to find anything on the topic so far.

Does anyone have any suggestions how this is best handled

I'm not sure if there is one, since for batch update/insert people tend to also build the query string.. — Bla...
You can dynamically prepare the string of the SQL statement according available values, pass this statement to SQL(...), then pass parameters. Look at the doc. — cchantep

Tim Pope Tim Pope · Accepted Answer · 2015-04-21T13:52:48

As the commenters mentioned it appears to not be possible - you must build the query string yourself.

I found that examples around this lacking and it took more time to resolve this than it should have (I'm new to scala and the play framework, so this has been a common theme)

in the end this is what I implemented:

override def updateClerk(clerk: Clerk) = {
  var setString: String = "[modified] = {modified}"
  var params: collection.mutable.Seq[NamedParameter] = 
     collection.mutable.Seq(
            NamedParameter("modified", toParameterValue(System.currentTimeMillis / 1000)), 
            NamedParameter("id", toParameterValue(clerk.id.get)))

  if (clerk.firstName.isDefined) {
    setString += ", [firstName] = {firstName}"
    params = params :+ NamedParameter("firstName", toParameterValue(clerk.firstName.getOrElse("")))
  }
  if (clerk.lastName.isDefined) {
    setString += ", [lastName] = {lastName}"
    params = params :+ NamedParameter("lastName", toParameterValue(clerk.lastName.getOrElse("")))
  }
  if (clerk.login.isDefined) {
    setString += ", [login] = {login}"
    params = params :+ NamedParameter("login", toParameterValue(clerk.login.getOrElse("")))
  }
  if (clerk.password.isDefined) {
    setString += ", [password] = {password}"
    params = params :+ NamedParameter("password", toParameterValue(clerk.password.getOrElse("")))
  }
  if (clerk.supervisor.isDefined) {
    setString += ", [isSupervisor] = {supervisor}"
    params = params :+ NamedParameter("supervisor", toParameterValue(clerk.supervisor.getOrElse(false)))
  }

  val result = DB.withConnection { implicit c =>
    SQL("UPDATE [Clerks] SET " + setString + " WHERE [id] = {id}").on(params:_*).executeUpdate()
  }
}

it likely isn't the best way to do this, however I found it quite readable and the parameters are properly handled in the prepared statement.

Hopefully this can benefit someone running into a similar issue

If anyone wants to offer up improvements, they'd be gratefully received

Play Scala Anorm dynamic SQL for UPDATE query

2 Answers