CODEWITHSUNDEEP
scalaplayframeworkanorm
Tim Pope
Tim Pope

Reputation: 73

Play Scala Anorm dynamic SQL for UPDATE query

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

Upvotes: 1

Views: 1437

Answers (2)

ThePianoDentist
ThePianoDentist

Reputation: 73

Since roughly 2.6.0 this is possible directly with anorm using their macros, http://playframework.github.io/anorm/#generated-parameter-conversions

Here is my example:

case class UpdateLeagueFormInput(transferLimit: Option[Int], transferWildcard: Option[Boolean], transferOpen: Option[Boolean])
val input = UpdateLeagueFormInput(None, None, Some(true))

val toParams: ToParameterList[UpdateLeagueFormInput] = Macro.toParameters[UpdateLeagueFormInput]
val params = toParams(input)
val dynamicUpdates = params.map(p => {
  val snakeName = camelToSnake(p.name)
  s"$snakeName = CASE WHEN {${p.name}} IS NULL THEN l.$snakeName ELSE {${p.name}} END"
})
val generatedStmt = s"""UPDATE league l set ${dynamicUpdates.mkString(", ")} where league_id = ${league.leagueId}"""
SQL(generatedStmt).on(params: _*).executeUpdate()

producing:

UPDATE league l set transfer_limit = CASE WHEN null IS NULL THEN l.transfer_limit ELSE null END, transfer_wildcard = CASE WHEN null IS NULL THEN l.transfer_wildcard ELSE null END, transfer_open = CASE WHEN true IS NULL THEN l.transfer_open ELSE true END where league_id = 26;

Notes:

  • The camelToSnake function is just my own (There is an obvious ColumnNaming.SnakeCase available for parser rows, but I couldn't find something similar for parameter parsing)

  • My example string interpolates {league.leagueId}, when it could treat this as a parameter as well

  • Would be nice to avoid the redundant sets for null fields, however I don't think it's possible, and in my opinion clean code/messy auto-generated sql > messy code/clean auto-generated sql

Upvotes: 0

Tim Pope
Tim Pope

Reputation: 73

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

Upvotes: 1

Related Questions