Slick uses SQL statements directly

Use SQL statements directly

If you need to use SQL statements directly, Slick also supports you to use SQL statements directly.

First you need to introduce some reference packages:

import scala.slick.jdbc.{GetResult, StaticQuery => Q}
import scala.slick.jdbc.JdbcBackend.Database
import Q.interpolation

One of the most important related similar to StaticQuery, we use Q as its alias for brevity. Connecting a database is still the same as before Slick programming (4): database connectivity and transaction processing

DDL and DML statements

StaticQuery's method updateNA, which represents no parameters, returns the number of rows affected by the DDL instruction, such as using the H2 database

Connect to the database:

case class Supplier(id:Int, name:String, street:String, city:String, state:String, zip:String)
case class Coffee(name:String, supID:Int, price:Double, sales:Int, total:Int)

Database.forURL("jdbc:h2:mem:test1", driver = "org.h2.Driver") withDynSession {

}

Create a database table:

// Create the tables, including primary and foreign keys
Q.updateNA("create table suppliers("+
  "id int not null primary key, "+
  "name varchar not null, "+
  "street varchar not null, "+
  "city varchar not null, "+
  "state varchar not null, "+
  "zip varchar not null)").execute
Q.updateNA("create table coffees("+
  "name varchar not null, "+
  "sup_id int not null, "+
  "price double not null, "+
  "sales int not null, "+
  "total int not null, "+
  "foreign key(sup_id) references suppliers(id))").execute

You can use strings and a StaticQuery object to add up to a new StaticQuery object, an easy way to do this is to use Q.u and a string to add, Q.u for a fairly similar StaticQuery.updateNA ("")

For example, we insert some data into a table:

// Insert some suppliers
(Q.u + "insert into suppliers values(101, 'Acme, Inc.', '99 Market Street', 'Groundsville', 'CA', '95199')").execute
(Q.u + "insert into suppliers values(49, 'Superior Coffee', '1 Party Place', 'Mendocino', 'CA', '95460')").execute
(Q.u + "insert into suppliers values(150, 'The High Ground', '100 Coffee Lane', 'Meadows', 'CA', '93966')").execute

Using literal amounts in SQL query statements is not a recommended approach, especially when the user provides data (not very secure), at which point you can use the . . . T he operator binds an argument to a query statement, such as:

def insert(c: Coffee) = (Q.u + "insert into coffees values (" +? c.name +
  "," +? c.supID + "," +? c.price + "," +? c.sales + "," +? c.total + ")").execute

// Insert some coffees
Seq(
  Coffee("Colombian", 101, 7.99, 0, 0),
  Coffee("French_Roast", 49, 8.99, 0, 0),
  Coffee("Espresso", 150, 9.99, 0, 0),
  Coffee("Colombian_Decaf", 101, 8.99, 0, 0),
  Coffee("French_Roast_Decaf", 49, 9.99, 0, 0)
).foreach(insert)

This code is relative to insert into coffees values (?,?,?,?,?)

The query statement

Similar to updateNA, StaticQuery also has a queryNA method that supports a type parameter (representing a row of tables), such as:

Q.queryNA[AlbumRow]("select * from Album") foreach { a => 
    println(" " + a.albumid + " " + a.title + " " + a.artistid)
}

This code works because it is defined in Tables.scala

/** GetResult implicit for fetching AlbumRow objects using plain SQL queries */
implicit def GetResultAlbumRow(implicit e0: GR[Int], e1: GR[String]): GR[AlbumRow] = GR{
    prs => import prs._
    AlbumRow.tupled((<<[Int], <<[String], <<[Int]))
}

An implicit transformation from the JDBC type to GetResult is defined, and GetResult is a encapsulation for the function PositionedResult. The values expected at the specified location are returned.

Query with parameters corresponding to queryNA defines two type parameters, one is the type of the argument and the other is the type of each row of the returned result, for example:

val q2 = Q.query[Int,(Int,String,Int)] ( """
 select albumid,title,artistid from Album where artistid < ?
""")
val l2 = q2.list(10)
for(t <- l2) println( " " + t._1 + " " + t._2 + " " + t._3)

The results are as follows:

1 For Those About To Rock We Salute You 1
4 Let There Be Rock 1
2 Balls to the Wall 2
3 Restless and Wild 2
5 Big Ones 3
6 Jagged Little Pill 4
7 Facelift 5
8 Warner 25 Anos 6
34 Chill: Brazil (Disc 2) 6
9 Plays Metallica By Four Cellos 7
10 Audioslave 8
11 Out Of Exile 8
271 Revelations 8
12 BackBeat Soundtrack 9

Q.interpolation supports string interpolation, such as:

def albumByTitle(title: String) = sql"select * from Album where title = $title".as[AlbumRow]
println("Album: " + albumByTitle("Let There Be Rock").firstOption)

Using sql as a prefix string, you can replace a variable that starts with $ with the value of that variable, and for update/delete statements, you can use the sqlu prefix.