Query Interpolation

Queries

Basic

Queries are created like so:

import com.lucidchart.open.relate.interp._

sql"SELECT * FROM pokemon"

Single parameters

Parameterized queries are created by using interpolated parameters:

val id = 5
sql"SELECT * FROM pokemon WHERE id = $id"

Relate uses JDBC's PreparedStatement to correctly escape inputs.

val name = "Robert'; DROP TABLES Student;--"
sql"SELECT * FROM pokemon WHERE name = $name"

Parameter sequences

Sequences of values are inserted as comma-separated lists.

val ids = Seq(1, 2, 3)
sql"SELECT * FROM pokemon WHERE ids IN ($ids)"
// becomes "SELECT * FROM pokemon WHERE ids IN (1,2,3)"

Sequences of sequences of values are inserted as comma-separated tuples.

val followers = Seq(Seq(1, 2), Seq(3, 4))
sql"INSERT INTO followers (a, b) VALUES $followers"
// becomes "INSERT INTO followers (a, b) VALUES (1,2), (3,4)"

Types must be visible at compile time and correspond to parameterizable types. The following does not compile, as Relate cannot parameterize Seq[Seq[Any]].

val users = Seq(Seq(4, "[email protected]"), Seq(5, "[email protected]"))
sql"INSERT INTO users (id, email) VALUES $users" // DOES NOT COMPILE

Tuples should be used instead.

val users = Seq((4, "[email protected]"), (5, "[email protected]"))
sql"INSERT INTO users (id, email) VALUES $users"

Query composition

For more dynamic queries, SQL statements can be composed with interpolation

val sql1 = sql"SELECT * FROM users"
val sql2 = sql"$sql1 LIMIT 5"

or concatenation

val sql1 = sql"SELECT * FROM users"
val sql2 = sql" LIMIT 5"
val sql3 = sql1 + sql2

Next Steps

• Retrieve Data from the Database
  • Parsers
  • Single Column Parsers
  • Single Value Parsers
  • Auto Increment Values
  • Data Extraction Methods