Inserting multiple values into table with anorm

Question

I want to insert multiple values into a table from a SQL query in Anorm. In the following snippet, is there a way to bind a list of usernames as values instead of just one username?

SQL("insert into users (username) " +
      "values ({username})").on(
      'username -> username,
    ).executeUpdate()

As an alternative, I can create a concatenated string from my inputs, but that's prone to SQL injection and isn't quite as clean.

Answer 1

You're wanting to repeat the insert command for all of the values?

How about wrapping it in a foreach:

usernames.foreach(username =>
  SQL("insert into users (username) " +
    "values ({username})").on(
    'username -> username,
  ).executeUpdate())

Or something of that sort.

Anorm is a relatively simple library--check out the code here: https://github.com/playframework/Play20/tree/master/framework/src/anorm/src/main/scala/anorm

Answer 2

A possible way to insert multiple values at once, using Anorm:

var fields: List[String] = Nil
var values: List[(String,ParameterValue[_])] = Nil

for ((username,i) <- usernames.zipWithIndex) {
  fields ::= "({username%s})".format(i)
  values ::= ("username" + i, username)
}

SQL("INSERT INTO users (username) VALUES %s".format(fields.mkString(",")))
  .on(values: _*)
  .executeUpdate()