Slick(1)Introduce and Documents

Slick(1)Introduce and Documents

The current release is Slick 0.11.2 for Scala 2.10.0-RC1.

Supported database systems
No Oracle but

Derby/JavaDB

H2

HSQLDB/HyperSQL

Microsoft Access

MySQL

PostgreSQL

SQLite


Getting Started
Start from the Example

>git clone https://github.com/slick/slick-examples.git

>cd slick-examples

>sbt update

>sbt run

>sbt eclipse

Import this project to eclipse and start to learn.


Overview
Four steps to use this

1. Add the Slick jar and dependencies

2. Pick a driver for a particular db

import scala.slick.driver.H2Driver.simple._

import Database.threadLocalSession


3. Describe the Database schema

object Coffees extends Table[(String, Double)]("COFFEES"){

def name = column[String]("COF_NAME", O.PrimaryKey)

def price = column[Double]("PRICE")

def * = name ~ price

}


4. Write queries

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

Coffees.filter(_.price < 10.0).map(_.name).list

}


Dependencies
I will just make this example happen on my easy spray project.

"com.typesafe" % "slick_2.10.0-RC2" % "0.11.2",

"org.slf4j" % "slf4j-nop" % "1.6.4",

"com.h2database" % "h2" % "1.3.170",

"org.xerial" % "sqlite-jdbc" % "3.6.20",

"mysql" % "mysql-connector-java" % "5.1.13"

/*
"org.apache.derby" % "derby" % "10.6.1.0",

"org.hsqldb" % "hsqldb" % "2.0.0",

"postgresql" % "postgresql" % "8.4-701.jdbc4",

"mysql" % "mysql-connector-java" % "5.1.13"

*/


Imports


Take the example in project slick-examples/src/main/scala/scala.slick.examples.lifted.FirstExample


And make sure we do not use the master, we need to use the tag branch 0.11.2.


// Use H2Driver to connect to an H2 database

import scala.slick.driver.H2Driver.simple._

// Use the implicit threadLocalSession

import Database.threadLocalSession


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

…snip…

}


Schema Definition with Forekey
object Suppliers extends Table[(Int, String, String, String, String, String)]("SUPPLIERS") {

def id = column[Int]("SUP_ID", O.PrimaryKey) // 1 This is the primary key column

def name = column[String]("SUP_NAME") // 2

def street = column[String]("STREET") //3

def city = column[String]("CITY") //4

def state = column[String]("STATE") //5

def zip = column[String]("ZIP") // 6

def * = id ~ name ~ street ~ city ~ state ~ zip

}


object Coffees extends Table[(String, Int, Double, Int, Int)]("COFFEES") {

def name = column[String]("COF_NAME", O.PrimaryKey) //1

def supID = column[Int]("SUP_ID") //2

def price = column[Double]("PRICE") //3

def sales = column[Int]("SALES") //4

def total = column[Int]("TOTAL") //5

def * = name ~ supID ~ price ~ sales ~ total

def supplier = foreignKey("SUP_FK", supID, Suppliers)(_.id)

}


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

(Suppliers.ddl ++ Coffees.ddl).create


Suppliers.insert(1, "Acme, Inc.", "99 Market Street", "Groundsville", "CA", "95199")

Suppliers.insert(2, "Superior Coffee", "1 Party Place", "Mendocino", "CA", "95460")

Suppliers.insert(3, "The High Ground", "100 Coffee Lane", "Meadows", "CA", "93966")


Coffees.insertAll(

("Colombian", 1, 7.99, 0, 0),

("French_Roast", 3, 8.99, 0, 0),

("Espresso", 2, 9.99, 0, 0),

("Colombian_Decaf", 2, 8.99, 0, 0),

("French_Roast_Decaf", 1, 9.99, 0, 0))

}


Querying
Query(TableName) foreach show up all the data in one table

println("Coffees:")

Query(Coffees) foreach {

case (name, supID, price, sales, total) =>

println(" " + name + "\t" + supID + "\t" + price + "\t" + sales + "\t" + total)

}


println("Manual join:")

val q2 = for {

c <- Coffees if c.price < 9.0 //table 1 with price condition

s <- Suppliers if s.id === c.supID //table 2 with join id

} yield (c.name, s.name)

for (t <- q2) println(" " + t._1 + " supplied by " + t._2)


println("Join by foreign key:")

val q3 = for {

c <- Coffees if c.price < 9.0

s <- c.supplier

} yield (c.name, s.name)

// This time we read the result set into a List

val l3: List[(String, String)] = q3.list

for ((s1, s2) <- l3) println(" " + s1 + " supplied by " + s2)
println(q3.selectStatement)


select x2."COF_NAME", x3."SUP_NAME" from "COFFEES" x2, "SUPPLIERS" x3 where (x2."PRICE" < 9.0) and (x3."SUP_ID" = x2."SUP_ID")


References:
http://slick.typesafe.com/

http://slick.typesafe.com/docs/

http://slick.typesafe.com/doc/0.11.2/

http://slick.typesafe.com/doc/0.11.2/gettingstarted.html