Slick(1)Introduce and Documents
Slick(1)IntroduceandDocuments
ThecurrentreleaseisSlick0.11.2forScala2.10.0-RC1.
Supporteddatabasesystems
NoOraclebut
Derby/JavaDB
H2
HSQLDB/HyperSQL
MicrosoftAccess
MySQL
PostgreSQL
SQLite
GettingStarted
StartfromtheExample
>gitclonehttps://github.com/slick/slick-examples.git
>cdslick-examples
>sbtupdate
>sbtrun
>sbteclipse
Importthisprojecttoeclipseandstarttolearn.
Overview
Fourstepstousethis
1.AddtheSlickjaranddependencies
2.Pickadriverforaparticulardb
importscala.slick.driver.H2Driver.simple._
importDatabase.threadLocalSession
3.DescribetheDatabaseschema
objectCoffeesextendsTable[(String,Double)]("COFFEES"){
defname=column[String]("COF_NAME",O.PrimaryKey)
defprice=column[Double]("PRICE")
def*=name~price
}
4.Writequeries
Database.forURL("jdbc:h2:mem:test1",driver="org.h2.Driver")withSession{
Coffees.filter(_.price<10.0).map(_.name).list
}
Dependencies
Iwilljustmakethisexamplehappenonmyeasysprayproject.
"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
Taketheexampleinprojectslick-examples/src/main/scala/scala.slick.examples.lifted.FirstExample
Andmakesurewedonotusethemaster,weneedtousethetagbranch0.11.2.
//UseH2DrivertoconnecttoanH2database
importscala.slick.driver.H2Driver.simple._
//UsetheimplicitthreadLocalSession
importDatabase.threadLocalSession
DatabaseConnection
Database.forURL("jdbc:h2:mem:test1",driver="org.h2.Driver")withSession{
…snip…
}
SchemaDefinitionwithForekey
objectSuppliersextendsTable[(Int,String,String,String,String,String)]("SUPPLIERS"){
defid=column[Int]("SUP_ID",O.PrimaryKey)//1Thisistheprimarykeycolumn
defname=column[String]("SUP_NAME")//2
defstreet=column[String]("STREET")//3
defcity=column[String]("CITY")//4
defstate=column[String]("STATE")//5
defzip=column[String]("ZIP")//6
def*=id~name~street~city~state~zip
}
objectCoffeesextendsTable[(String,Int,Double,Int,Int)]("COFFEES"){
defname=column[String]("COF_NAME",O.PrimaryKey)//1
defsupID=column[Int]("SUP_ID")//2
defprice=column[Double]("PRICE")//3
defsales=column[Int]("SALES")//4
deftotal=column[Int]("TOTAL")//5
def*=name~supID~price~sales~total
defsupplier=foreignKey("SUP_FK",supID,Suppliers)(_.id)
}
PopulatingtheDatabase
Database.forURL("jdbc:h2:mem:test1",driver="org.h2.Driver")withSession{
(Suppliers.ddl++Coffees.ddl).create
Suppliers.insert(1,"Acme,Inc.","99MarketStreet","Groundsville","CA","95199")
Suppliers.insert(2,"SuperiorCoffee","1PartyPlace","Mendocino","CA","95460")
Suppliers.insert(3,"TheHighGround","100CoffeeLane","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)foreachshowupallthedatainonetable
println("Coffees:")
Query(Coffees)foreach{
case(name,supID,price,sales,total)=>
println(""+name+"\t"+supID+"\t"+price+"\t"+sales+"\t"+total)
}
println("Manualjoin:")
valq2=for{
c<-Coffeesifc.price<9.0//table1withpricecondition
s<-Suppliersifs.id===c.supID//table2withjoinid
}yield(c.name,s.name)
for(t<-q2)println(""+t._1+"suppliedby"+t._2)
println("Joinbyforeignkey:")
valq3=for{
c<-Coffeesifc.price<9.0
s<-c.supplier
}yield(c.name,s.name)
//ThistimewereadtheresultsetintoaList
vall3:List[(String,String)]=q3.list
for((s1,s2)<-l3)println(""+s1+"suppliedby"+s2)
println(q3.selectStatement)
selectx2."COF_NAME",x3."SUP_NAME"from"COFFEES"x2,"SUPPLIERS"x3where(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