Slick Queries (III)

Enquiries (III)

Slick's query actually executes a method defined by Invoker (UnitInvoker without parameters), and Slick defines a transformation implied from Query that allows you to perform query operations directly, most commonly by storing the entire query result into a Scala collection type (for example, using the list method)

val l = q.list
val v = q.buildColl[Vector]
val invoker = q.invoker
val statement = q.selectStatement

All query methods define an implied parameter, Session, which you can pass directly into if you prefer:

val l = q.list()(session)

If you only need a single query result, you can use the first or firstOption method, which can be used to traverse the query results without first copying the results to another Scala collection object.

Deleting

Deleting data is very similar to a query, you write a selection query first, and then call its delete method, just as Slick defines an implicit transformation from Query to DeleteInvoker, which defines the delete method

val affectedRowsCount = q.delete
val invoker = q.deleteInvoker
val statement = q.deleteStatement

Only a single table can be used when defining queries that are used to delete records.

Inserting

Insertion is based on a single table-defined field map, which is based on the "

coffees += ("Colombian", 101, 7.99, 0, 0)

coffees ++= Seq(
  ("French_Roast", 49, 8.99, 0, 0),
  ("Espresso",    150, 9.99, 0, 0)
)

// "sales" and "total" will use the default value 0:
coffees.map(c => (c.name, c.supID, c.price)) += ("Colombian_Decaf", 101, 8.99)

val statement = coffees.insertStatement
val invoker = coffees.insertInvoker

// compiles to SQL:
// INSERT INTO "COFFEES" ("COF_NAME","SUP_ID","PRICE","SALES","TOTAL") VALUES (?,?,?,?,?)

If your insertion defines an auto-add field, the field is automatically ignored and the value of the field is inserted by the database itself. T he default is to return the number of rows affected (usually 1 in total), while the operation gives the total number of rows (given in option type), and you can use returning to modify the returned value, such as returning the primary key of the inserted row:

val userId =
  (users returning users.map(_.id)) += User(None, "Stefan", "Zeiger")

Note that many databases only support returning the field that automatically adds one as the primary key, and if you want to return another field, you might throw a SlickException exception.

In addition to the above method of inserting records, you can also insert data using the square hair of a server-side expression:

class Users2(tag: Tag) extends Table[(Int, String)](tag, "users2") {
    def id = column[Int]("id", O.PrimaryKey)
    def name = column[String]("name")
    def * = (id, name)
}
val users2 = TableQuery[Users2]

users2.ddl.create

users2 insert (users.map { u => (u.id, u.first ++ " " ++ u.last) })

users2 insertExpr (users.length + 1, "admin")

Updating

Updating records also writes queries first, and then calls update methods, such as:

val q = for { c <- coffees if c.name === "Espresso" } yield c.price
q.update(10.49)

val statement = q.updateStatement
val invoker = q.updateInvoker

The update method is defined in UpdateInvoker Trait.

Compiled Queries

When you query a database, you often need to define some query parameters, such as finding the corresponding record based on the ID. Y ou can define a function with parameters to define a query object, but each time you call the function, you have to recompile the query statement, the system consumption is somewhat large, Slick supports the precompiled query function with parameters, such as:

def userNameByIDRange(min: Column[Int], max: Column[Int]) =
  for {
    u <- users if u.id >= min && u.id < max
  } yield u.first

val userNameByIDRangeCompiled = Compiled(userNameByIDRange _)

// The query will be compiled only once:
val names1 = userNameByIDRangeCompiled(2, 5).run
val names2 = userNameByIDRangeCompiled(1, 3).run

This approach supports querying, updating, and deleting data.