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