How do you use an 'IN' clause?

[Jeff Shutt]

for example
broker.readOnly() { session =>
      session.selectAll(Token("select * from orders where id IN (:ids)", 'mySymbol, myExtractor), "ids" -> List(1, 2, 3))
}

[Eduardo M. Cavalcanti]

Hi,
You can make like this:

session.selectAll(Token("select * from orders where id IN (:ids)", 'mySymbol, myExtractor), "ids" -> List(1, 2, 3).mkString(","))

[Nils]

JDBC doesn't support dynamic parameters like that. Using IN is common use case for doing dynamic SQL through either FreeMarker or Velocity, or inline with Scala as your example does.

So, you'd have to do something like this:

val sql = new StringBuilder

sql += "select * from orders where id IN ("

for (i <- 0 until ids.size) {

if (i > 0) sql += ","

sql += ":ids[" + i + "]" 

}

sql += ")"

so that you end up with a SQL statement like this:

select * from orders where id IN (:ids[0],:ids[1],:ids[2])

HTH

Nils

[Nils]

Eduardo, that won't work, because O/R Broker uses prepared statements, not string substitution. So each parameter must be specified in JDBC.

[Nils]

Correction, use ++= instead of +=

[Eduardo M. Cavalcanti]

Understood, Nils.
Cheers.

[Jeff Shutt]

Ok thanks. I guess I have just been spoiled by using hibernate setParameterList feature.

[Eduardo M. Cavalcanti]

My apologies for the wrong tip, Jeff.

[Nils Kilden-Pedersen]

On Wed, Sep 28, 2011 at 10:26 AM, Jeff Shutt <shu...@gmail.com> wrote:

Ok thanks. I guess I have just been spoiled by using hibernate setParameterList feature.

Yeah, I've considered it. But it's a lot easier for Hibernate to do that, because it generates the SQL.

But you can probably easily write a reuseable helper function to fit your purpose. Hell, maybe I'll see if I can include it in the distribution.

[Jeff Shutt]

I ended up just creating these:

def buildParameterString(seq: Seq[_], name: String) = (0 until seq.size).map{ e => ":%s%d".format(name, e) }.mkString(",")

def buildValuesMapping(seq: Seq[_], name: String) = (0 until seq.size).map{e => "%s%d".format(name, e) -> seq(e)}.toSeq

def getParameterStringAndValuesMapping(seq: Seq[_], name: String) =  (buildParameterString(seq, name), buildValuesMapping(seq, name))

Usage:
val ids = List(1, 2, 3)
val (parameters, values) = getParameterStringAndValuesMapping(ids, "id")
val sql = "select * from orders where id in (%s)".format(parameters)

val orders = broker.readOnly() { session =>
    session.selectAll(Token(sql, 'getOrdersByIds, OrderExtractor), values: _*)
}

It's certainly not optimized as there are two iterations of the sequence size, but it works for my simple case.