概述

Slick 对于 Scala 来说，有如 LINQ 至于 C#，或者类似于其它平台上的 ORM 系统，它使用应用使用数据库有如使用 Scala 内置的集合类型（比如列表，集合等）一样方便。当然如有需要你还是可以直接使用 SQL 语句来查询数据库。

下面为使用 Slick 的代码片段： 

val limit = 10.0// Your query could look like this:( for( c <- coffees; if c.price < limit ) yield c.name ).list// Or using more plain SQL String Interpolation:sql"select COF_NAME from COFFEES where PRICE < $limit".as[String].list// Both queries result in SQL equivalent to:// select COF_NAME from COFFEES where PRICE < 10.0

使用 Slick 而不直接使用 SQL 语句，可以使用编译器帮助发现一些类型错误，同时 Slick 可以为不同的后台数据库类型生成查询。

它具有如下的一些特征：

Scala

所有查询，表格和字段映射，以及类型都采用普通的 Scala 语法。 

class Coffees(tag: Tag) extends Table[(String, Double)](tag, "COFFEES") {    def name = column[String]("COF_NAME", O.PrimaryKey)    def price = column[Double]("PRICE")    def * = (name, price)}val coffees = TableQuery[Coffees]

数据访问接口类型 Scala 的集合类型 

// Query that only returns the "name" columncoffees.map(_.name)// Query that does a "where price < 10.0"coffees.filter(_.price < 10.0)

类型安全

你使用的 IDE 可以帮助你写代码在编译时而无需到运行时就可以发现一些错误 

// The result of "select PRICE from COFFEES" is a Seq of Double// because of the type safe column definitionsval coffeeNames: Seq[Double] = coffees.map(_.price).list// Query builders are type safe:coffees.filter(_.price < 10.0)// Using a string in the filter would result in a compilation error

可以组合

查询接口为函数，这些函数可以多次组合和重用。 

// Create a query for coffee names with a price less than 10, sorted by namecoffees.filter(_.price < 10.0).sortBy(_.name).map(_.name)// The generated SQL is equivalent to:// select name from COFFEES where PRICE < 10.0 order by NAME

支持的数据库系统

  • DB2 (via slick-extensions)
  • Derby/JavaDB
  • H2
  • HSQLDB/HyperSQL
  • Microsoft Access
  • Microsoft SQL Server (via slick-extensions)
  • MySQL
  • Oracle (via slick-extensions)
  • PostgreSQL
  • SQLite

对于其它的一些数据库类型 Slick 也提供了有限的支持。

查询接口 Lifted Embedding

Sclick 使用 Lifted Embedding 作为标准的数据库查询接口，此外 Direct Embedding 接口正在开发测试当中。

Lifted Embedding 的名称来自于，你不是使用标准的 Scala 数据类型来访问查询数据库，而是使用 Rep 构造器来提升（Lift）Scala 的基本数据类型，然后使用提升后的数据类型来访问数据库，比如标准的 Scala 集合的例子： 

case class Coffee(name: String, price: Double)val coffees: List[Coffee] = //...val l = coffees.filter(_.price > 8.0).map(_.name)//                       ^       ^          ^//                       Double  Double     String

而对应的提升之后的例子： 

class Coffees(tag: Tag) extends Table[(String, Double)](tag, "COFFEES") {    def name = column[String]("COF_NAME")    def price = column[Double]("PRICE")    def * = (name, price)}val coffees = TableQuery[Coffees]val q = coffees.filter(_.price > 8.0).map(_.name)//                       ^       ^          ^//               Rep[Double]  Rep[Double]  Rep[String]

所有的基本 Scala 类型，都提升为 Rep。即使是 8.0 字面量也被提升为 Rep[Double] 类型。

后面的例子，我们会采用 Chinook 数据库作为例子。

Chinook 数据库前身为著名的 Northwind 数据库，它的数据模型如下：

图片

准备开发环境

本篇介绍如果设置使用 Slick 的 Scala 开发环境，这里我们使用 SBT 命令行，SBT 使用的目录结构和 Maven 一样，我们可以创建一个目录，比如 Slick ，然后创建如下的缺省目录结构：

  • src
    • main
      • java
      • resources
      • scala
    • test
    • java
    • resources
    • scala

因为我们打算使用 MySQL 数据库，并使用 Slick 来访问数据库，因此我们在 Slick 的根目录下创建一个 build.sbt，添加相关引用： 

name := "Scala Slick Examples"version := "1.0"scalaVersion := "2.10.4"libraryDependencies += "com.typesafe.slick" %% "slick" % "2.0.2"libraryDependencies += "org.slf4j" % "slf4j-nop" % "1.6.4"libraryDependencies += "mysql" % "mysql-connector-java" % "5.1.18"

Slick 使用 SLF4J 作为日志库文件。

我们的 MySQL 数据库 Chinook 安装在本地服务器上面，我们在使用 Slick 可以手工创建数据库表 Schema 的定义，也可以使用自动代码生成工具从已有的数据库创建 Table 的 Schema 定义。

我们在命令行输入 sbt，进入 SBT 控制台。

然后我们使用 console，进入 Scala 控制台，注意此时 SBT 自动把 build.sbt 中引用到的库比如 slick， mysql 添加到 Scala 控制台，我们使用如下命令： 

scala.slick.model.codegen.SourceCodeGenerator.main(    Array(slickDriver, jdbcDriver, url, outputFolder, pkg, user, password))

相关参数如下：

slickDriver Fully qualified name of Slick driver class, e.g. “scala.slick.driver.H2Driver”
jdbcDriver Fully qualified name of jdbc driver class, e.g. “org.h2.Driver”
url jdbc url, e.g. “jdbc:postgresql://localhost/test”
outputFolder Place where the package folder structure should be put
pkg Scala package the generated code should be places in
user database connection user name
password database connection password

例如对于本例，我们使用 mysql 数据库，可以在命令行输入如下命令：注意修改你的用户名和密码： 

scala.slick.model.codegen.SourceCodeGenerator.main(    Array("scala.slick.driver.MySQLDriver", "com.mysql.jdbc.Driver",     "jdbc:mysql://127.0.0.1/Chinook",     "./src/main/scala",    "com.guidebee.slick.example", "user", "password"))

这样自动代码生成工具，就在 /src/main/scala 目录下生成了 Tables.scala 文件 

package com.guidebee.slick.example// AUTO-GENERATED Slick data model/** Stand-alone Slick data model for immediate use */object Tables extends {    val profile = scala.slick.driver.MySQLDriver} with Tables/** Slick data model trait for extension, choice of backend or usage in the cake pattern. (Make sure to initialize this late.) */trait Tables {    val profile: scala.slick.driver.JdbcProfile    import profile.simple._    import scala.slick.model.ForeignKeyAction    // NOTE: GetResult mappers for plain SQL are only generated for tables where Slick knows how to map the types of all columns.    import scala.slick.jdbc.{GetResult => GR}    /** DDL for all tables. Call .create to execute. */    lazy val ddl = Album.ddl ++ Artist.ddl ++ Customer.ddl ++ Employee.ddl ++ Genre.ddl ++ Invoice.ddl ++ Invoiceline.ddl ++ Mediatype.ddl ++ Playlist.ddl ++ Playlisttrack.ddl ++ Track.ddl    /** Entity class storing rows of table Album     *  @param albumid Database column AlbumId PrimaryKey     *  @param title Database column Title      *  @param artistid Database column ArtistId  */    case class AlbumRow(albumid: Int, title: String, artistid: Int)    /** 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]))    }    /** Table description of table Album. Objects of this class serve as prototypes for rows in queries. */    class Album(tag: Tag) extends Table[AlbumRow](tag, "Album") {        def * = (albumid, title, artistid) <> (AlbumRow.tupled, AlbumRow.unapply)        /** Maps whole row to an option. Useful for outer joins. */        def ? = (albumid.?, title.?, artistid.?).shaped.<>({r=>import r._; _1.map(_=> AlbumRow.tupled((_1.get, _2.get, _3.get)))}, (_:Any) =>  throw new Exception("Inserting into ? projection not supported."))        /** Database column AlbumId PrimaryKey */        val albumid: Column[Int] = column[Int]("AlbumId", O.PrimaryKey)        /** Database column Title  */        val title: Column[String] = column[String]("Title")        /** Database column ArtistId  */        val artistid: Column[Int] = column[Int]("ArtistId")        /** Foreign key referencing Artist (database name FK_AlbumArtistId) */        lazy val artistFk = foreignKey("FK_AlbumArtistId", artistid, Artist)(r => r.artistid, onUpdate=ForeignKeyAction.NoAction, onDelete=ForeignKeyAction.NoAction)    }    /** Collection-like TableQuery object for table Album */    lazy val Album = new TableQuery(tag => new Album(tag))    /** Entity class storing rows of table Artist     *  @param artistid Database column ArtistId PrimaryKey     *  @param name Database column Name  */    case class ArtistRow(artistid: Int, name: Option[String])    /** GetResult implicit for fetching ArtistRow objects using plain SQL queries */    implicit def GetResultArtistRow(implicit e0: GR[Int], e1: GR[Option[String]]): GR[ArtistRow] = GR{        prs => import prs._        ArtistRow.tupled((<<[Int], <<?[String]))    }    /** Table description of table Artist. Objects of this class serve as prototypes for rows in queries. */    class Artist(tag: Tag) extends Table[ArtistRow](tag, "Artist") {        def * = (artistid, name) <> (ArtistRow.tupled, ArtistRow.unapply)        /** Maps whole row to an option. Useful for outer joins. */        def ? = (artistid.?, name).shaped.<>({r=>import r._; _1.map(_=> ArtistRow.tupled((_1.get, _2)))}, (_:Any) =>  throw new Exception("Inserting into ? projection not supported."))        /** Database column ArtistId PrimaryKey */        val artistid: Column[Int] = column[Int]("ArtistId", O.PrimaryKey)        /** Database column Name  */        val name: Column[Option[String]] = column[Option[String]]("Name")    }    /** Collection-like TableQuery object for table Artist */    lazy val Artist = new TableQuery(tag => new Artist(tag))    /** Entity class storing rows of table Customer      *  @param customerid Database column CustomerId PrimaryKey      *  @param firstname Database column FirstName       *  @param lastname Database column LastName       *  @param company Database column Company       *  @param address Database column Address       *  @param city Database column City       *  @param state Database column State       *  @param country Database column Country       *  @param postalcode Database column PostalCode       *  @param phone Database column Phone       *  @param fax Database column Fax       *  @param email Database column Email       *  @param supportrepid Database column SupportRepId  */    case class CustomerRow(customerid: Int, firstname: String, lastname: String, company: Option[String], address: Option[String], city: Option[String], state: Option[String], country: Option[String], postalcode: Option[String], phone: Option[String], fax: Option[String], email: String, supportrepid: Option[Int])    /** GetResult implicit for fetching CustomerRow objects using plain SQL queries */    implicit def GetResultCustomerRow(implicit e0: GR[Int], e1: GR[String], e2: GR[Option[String]], e3: GR[Option[Int]]): GR[CustomerRow] = GR{    prs => import prs._    CustomerRow.tupled((<<[Int], <<[String], <<[String], <<?[String], <<?[String], <<?[String], <<?[String], <<?[String], <<?[String], <<?[String], <<?[String], <<[String], <<?[Int]))    }  /** Table description of table Customer. Objects of this class serve as prototypes for rows in queries. */class Customer(tag: Tag) extends Table[CustomerRow](tag, "Customer") {    def * = (customerid, firstname, lastname, company, address, city, state, country, postalcode, phone, fax, email, supportrepid) <> (CustomerRow.tupled, CustomerRow.unapply)    /** Maps whole row to an option. Useful for outer joins. */    def ? = (customerid.?, firstname.?, lastname.?, company, address, city, state, country, postalcode, phone, fax, email.?, supportrepid).shaped.<>({r=>import r._; _1.map(_=> CustomerRow.tupled((_1.get, _2.get, _3.get, _4, _5, _6, _7, _8, _9, _10, _11, _12.get, _13)))}, (_:Any) =>  throw new Exception("Inserting into ? projection not supported."))    /** Database column CustomerId PrimaryKey */    val customerid: Column[Int] = column[Int]("CustomerId", O.PrimaryKey)    /** Database column FirstName  */    val firstname: Column[String] = column[String]("FirstName")    /** Database column LastName  */    val lastname: Column[String] = column[String]("LastName")    /** Database column Company  */    val company: Column[Option[String]] = column[Option[String]]("Company")    /** Database column Address  */    val address: Column[Option[String]] = column[Option[String]]("Address")    /** Database column City  */    val city: Column[Option[String]] = column[Option[String]]("City")    /** Database column State  */    val state: Column[Option[String]] = column[Option[String]]("State")    /** Database column Country  */    val country: Column[Option[String]] = column[Option[String]]("Country")    /** Database column PostalCode  */    val postalcode: Column[Option[String]] = column[Option[String]]("PostalCode")    /** Database column Phone  */    val phone: Column[Option[String]] = column[Option[String]]("Phone")    /** Database column Fax  */    val fax: Column[Option[String]] = column[Option[String]]("Fax")    /** Database column Email  */    val email: Column[String] = column[String]("Email")    /** Database column SupportRepId  */    val supportrepid: Column[Option[Int]] = column[Option[Int]]("SupportRepId")    /** Foreign key referencing Employee (database name FK_CustomerSupportRepId) */    lazy val employeeFk = foreignKey("FK_CustomerSupportRepId", supportrepid, Employee)(r => r.employeeid, onUpdate=ForeignKeyAction.NoAction, onDelete=ForeignKeyAction.NoAction)}    /** Collection-like TableQuery object for table Customer */    lazy val Customer = new TableQuery(tag => new Customer(tag))  /** Entity class storing rows of table Employee   *  @param employeeid Database column EmployeeId PrimaryKey   *  @param lastname Database column LastName    *  @param firstname Database column FirstName    *  @param title Database column Title    *  @param reportsto Database column ReportsTo    *  @param birthdate Database column BirthDate    *  @param hiredate Database column HireDate    *  @param address Database column Address    *  @param city Database column City    *  @param state Database column State    *  @param country Database column Country    *  @param postalcode Database column PostalCode    *  @param phone Database column Phone    *  @param fax Database column Fax    *  @param email Database column Email  */    case class EmployeeRow(employeeid: Int, lastname: String, firstname: String, title: Option[String], reportsto: Option[Int], birthdate: Option1, hiredate: Option1, address: Option[String], city: Option[String], state: Option[String], country: Option[String], postalcode: Option[String], phone: Option[String], fax: Option[String], email: Option[String])  /** GetResult implicit for fetching EmployeeRow objects using plain SQL queries */    implicit def GetResultEmployeeRow(implicit e0: GR[Int], e1: GR[String], e2: GR[Option[String]], e3: GR[Option[Int]], e4: GR[Option1]): GR[EmployeeRow] = GR{    prs => import prs._    EmployeeRow.tupled((<<[Int], <<[String], <<[String], <<?[String], <<?[Int], <<?1, <<?1, <<?[String], <<?[String], <<?[String], <<?[String], <<?[String], <<?[String], <<?[String], <<?[String]))}/** Table description of table Employee. Objects of this class serve as prototypes for rows in queries. */class Employee(tag: Tag) extends Table[EmployeeRow](tag, "Employee") {    def * = (employeeid, lastname, firstname, title, reportsto, birthdate, hiredate, address, city, state, country, postalcode, phone, fax, email) <> (EmployeeRow.tupled, EmployeeRow.unapply)    /** Maps whole row to an option. Useful for outer joins. */    def ? = (employeeid.?, lastname.?, firstname.?, title, reportsto, birthdate, hiredate, address, city, state, country, postalcode, phone, fax, email).shaped.<>({r=>import r._; _1.map(_=> EmployeeRow.tupled((_1.get, _2.get, _3.get, _4, _5, _6, _7, _8, _9, _10, _11, _12, _13, _14, _15)))}, (_:Any) =>  throw new Exception("Inserting into ? projection not supported."))    /** Database column EmployeeId PrimaryKey */    val employeeid: Column[Int] = column[Int]("EmployeeId", O.PrimaryKey)    /** Database column LastName  */    val lastname: Column[String] = column[String]("LastName")    /** Database column FirstName  */    val firstname: Column[String] = column[String]("FirstName")    /** Database column Title  */    val title: Column[Option[String]] = column[Option[String]]("Title")    /** Database column ReportsTo  */    val reportsto: Column[Option[Int]] = column[Option[Int]]("ReportsTo")    /** Database column BirthDate  */    val birthdate: Column[Option1] = column[Option1]("BirthDate")    /** Database column HireDate  */    val hiredate: Column[Option1] = column[Option1]("HireDate")    /** Database column Address  */    val address: Column[Option[String]] = column[Option[String]]("Address")    /** Database column City  */    val city: Column[Option[String]] = column[Option[String]]("City")    /** Database column State  */    val state: Column[Option[String]] = column[Option[String]]("State")    /** Database column Country  */    val country: Column[Option[String]] = column[Option[String]]("Country")    /** Database column PostalCode  */    val postalcode: Column[Option[String]] = column[Option[String]]("PostalCode")    /** Database column Phone  */    val phone: Column[Option[String]] = column[Option[String]]("Phone")    /** Database column Fax  */    val fax: Column[Option[String]] = column[Option[String]]("Fax")    /** Database column Email  */    val email: Column[Option[String]] = column[Option[String]]("Email")    /** Foreign key referencing Employee (database name FK_EmployeeReportsTo) */    lazy val employeeFk = foreignKey("FK_EmployeeReportsTo", reportsto, Employee)(r => r.employeeid, onUpdate=ForeignKeyAction.NoAction, onDelete=ForeignKeyAction.NoAction)  }  /** Collection-like TableQuery object for table Employee */  lazy val Employee = new TableQuery(tag => new Employee(tag))  /** Entity class storing rows of table Genre   *  @param genreid Database column GenreId PrimaryKey   *  @param name Database column Name  */  case class GenreRow(genreid: Int, name: Option[String])  /** GetResult implicit for fetching GenreRow objects using plain SQL queries */  implicit def GetResultGenreRow(implicit e0: GR[Int], e1: GR[Option[String]]): GR[GenreRow] = GR{    prs => import prs._    GenreRow.tupled((<<[Int], <<?[String]))  }  /** Table description of table Genre. Objects of this class serve as prototypes for rows in queries. */  class Genre(tag: Tag) extends Table[GenreRow](tag, "Genre") {    def * = (genreid, name) <> (GenreRow.tupled, GenreRow.unapply)    /** Maps whole row to an option. Useful for outer joins. */    def ? = (genreid.?, name).shaped.<>({r=>import r._; _1.map(_=> GenreRow.tupled((_1.get, _2)))}, (_:Any) =>  throw new Exception("Inserting into ? projection not supported."))    /** Database column GenreId PrimaryKey */    val genreid: Column[Int] = column[Int]("GenreId", O.PrimaryKey)    /** Database column Name  */    val name: Column[Option[String]] = column[Option[String]]("Name")  }  /** Collection-like TableQuery object for table Genre */  lazy val Genre = new TableQuery(tag => new Genre(tag))  /** Entity class storing rows of table Invoice   *  @param invoiceid Database column InvoiceId PrimaryKey   *  @param customerid Database column CustomerId    *  @param invoicedate Database column InvoiceDate    *  @param billingaddress Database column BillingAddress    *  @param billingcity Database column BillingCity    *  @param billingstate Database column BillingState    *  @param billingcountry Database column BillingCountry    *  @param billingpostalcode Database column BillingPostalCode    *  @param total Database column Total  */  case class InvoiceRow(invoiceid: Int, customerid: Int, invoicedate: java.sql.Timestamp, billingaddress: Option[String], billingcity: Option[String], billingstate: Option[String], billingcountry: Option[String], billingpostalcode: Option[String], total: scala.math.BigDecimal)  /** GetResult implicit for fetching InvoiceRow objects using plain SQL queries */  implicit def GetResultInvoiceRow(implicit e0: GR[Int], e1: GR1, e2: GR[Option[String]], e3: GR1): GR[InvoiceRow] = GR{    prs => import prs._    InvoiceRow.tupled((<<[Int], <<[Int], <<1, <<?[String], <<?[String], <<?[String], <<?[String], <<?[String], <<1))  }  /** Table description of table Invoice. Objects of this class serve as prototypes for rows in queries. */  class Invoice(tag: Tag) extends Table[InvoiceRow](tag, "Invoice") {    def * = (invoiceid, customerid, invoicedate, billingaddress, billingcity, billingstate, billingcountry, billingpostalcode, total) <> (InvoiceRow.tupled, InvoiceRow.unapply)    /** Maps whole row to an option. Useful for outer joins. */    def ? = (invoiceid.?, customerid.?, invoicedate.?, billingaddress, billingcity, billingstate, billingcountry, billingpostalcode, total.?).shaped.<>({r=>import r._; _1.map(_=> InvoiceRow.tupled((_1.get, _2.get, _3.get, _4, _5, _6, _7, _8, _9.get)))}, (_:Any) =>  throw new Exception("Inserting into ? projection not supported."))    /** Database column InvoiceId PrimaryKey */    val invoiceid: Column[Int] = column[Int]("InvoiceId", O.PrimaryKey)    /** Database column CustomerId  */    val customerid: Column[Int] = column[Int]("CustomerId")    /** Database column InvoiceDate  */    val invoicedate: Column1 = column1("InvoiceDate")    /** Database column BillingAddress  */    val billingaddress: Column[Option[String]] = column[Option[String]]("BillingAddress")    /** Database column BillingCity  */    val billingcity: Column[Option[String]] = column[Option[String]]("BillingCity")    /** Database column BillingState  */    val billingstate: Column[Option[String]] = column[Option[String]]("BillingState")    /** Database column BillingCountry  */    val billingcountry: Column[Option[String]] = column[Option[String]]("BillingCountry")    /** Database column BillingPostalCode  */    val billingpostalcode: Column[Option[String]] = column[Option[String]]("BillingPostalCode")    /** Database column Total  */    val total: Column1 = column1("Total")    /** Foreign key referencing Customer (database name FK_InvoiceCustomerId) */    lazy val customerFk = foreignKey("FK_InvoiceCustomerId", customerid, Customer)(r => r.customerid, onUpdate=ForeignKeyAction.NoAction, onDelete=ForeignKeyAction.NoAction)  }  /** Collection-like TableQuery object for table Invoice */  lazy val Invoice = new TableQuery(tag => new Invoice(tag))  /** Entity class storing rows of table Invoiceline   *  @param invoicelineid Database column InvoiceLineId PrimaryKey   *  @param invoiceid Database column InvoiceId    *  @param trackid Database column TrackId    *  @param unitprice Database column UnitPrice    *  @param quantity Database column Quantity  */  case class InvoicelineRow(invoicelineid: Int, invoiceid: Int, trackid: Int, unitprice: scala.math.BigDecimal, quantity: Int)  /** GetResult implicit for fetching InvoicelineRow objects using plain SQL queries */  implicit def GetResultInvoicelineRow(implicit e0: GR[Int], e1: GR1): GR[InvoicelineRow] = GR{    prs => import prs._    InvoicelineRow.tupled((<<[Int], <<[Int], <<[Int], <<1, <<[Int]))  }  /** Table description of table InvoiceLine. Objects of this class serve as prototypes for rows in queries. */  class Invoiceline(tag: Tag) extends Table[InvoicelineRow](tag, "InvoiceLine") {    def * = (invoicelineid, invoiceid, trackid, unitprice, quantity) <> (InvoicelineRow.tupled, InvoicelineRow.unapply)    /** Maps whole row to an option. Useful for outer joins. */    def ? = (invoicelineid.?, invoiceid.?, trackid.?, unitprice.?, quantity.?).shaped.<>({r=>import r._; _1.map(_=> InvoicelineRow.tupled((_1.get, _2.get, _3.get, _4.get, _5.get)))}, (_:Any) =>  throw new Exception("Inserting into ? projection not supported."))    /** Database column InvoiceLineId PrimaryKey */    val invoicelineid: Column[Int] = column[Int]("InvoiceLineId", O.PrimaryKey)    /** Database column InvoiceId  */    val invoiceid: Column[Int] = column[Int]("InvoiceId")    /** Database column TrackId  */    val trackid: Column[Int] = column[Int]("TrackId")    /** Database column UnitPrice  */    val unitprice: Column1 = column1("UnitPrice")    /** Database column Quantity  */    val quantity: Column[Int] = column[Int]("Quantity")    /** Foreign key referencing Invoice (database name FK_InvoiceLineInvoiceId) */    lazy val invoiceFk = foreignKey("FK_InvoiceLineInvoiceId", invoiceid, Invoice)(r => r.invoiceid, onUpdate=ForeignKeyAction.NoAction, onDelete=ForeignKeyAction.NoAction)    /** Foreign key referencing Track (database name FK_InvoiceLineTrackId) */    lazy val trackFk = foreignKey("FK_InvoiceLineTrackId", trackid, Track)(r => r.trackid, onUpdate=ForeignKeyAction.NoAction, onDelete=ForeignKeyAction.NoAction)  }  /** Collection-like TableQuery object for table Invoiceline */  lazy val Invoiceline = new TableQuery(tag => new Invoiceline(tag))  /** Entity class storing rows of table Mediatype   *  @param mediatypeid Database column MediaTypeId PrimaryKey   *  @param name Database column Name  */  case class MediatypeRow(mediatypeid: Int, name: Option[String])  /** GetResult implicit for fetching MediatypeRow objects using plain SQL queries */  implicit def GetResultMediatypeRow(implicit e0: GR[Int], e1: GR[Option[String]]): GR[MediatypeRow] = GR{    prs => import prs._    MediatypeRow.tupled((<<[Int], <<?[String]))  }  /** Table description of table MediaType. Objects of this class serve as prototypes for rows in queries. */  class Mediatype(tag: Tag) extends Table[MediatypeRow](tag, "MediaType") {    def * = (mediatypeid, name) <> (MediatypeRow.tupled, MediatypeRow.unapply)    /** Maps whole row to an option. Useful for outer joins. */    def ? = (mediatypeid.?, name).shaped.<>({r=>import r._; _1.map(_=> MediatypeRow.tupled((_1.get, _2)))}, (_:Any) =>  throw new Exception("Inserting into ? projection not supported."))    /** Database column MediaTypeId PrimaryKey */    val mediatypeid: Column[Int] = column[Int]("MediaTypeId", O.PrimaryKey)    /** Database column Name  */    val name: Column[Option[String]] = column[Option[String]]("Name")  }  /** Collection-like TableQuery object for table Mediatype */  lazy val Mediatype = new TableQuery(tag => new Mediatype(tag))  /** Entity class storing rows of table Playlist   *  @param playlistid Database column PlaylistId PrimaryKey   *  @param name Database column Name  */  case class PlaylistRow(playlistid: Int, name: Option[String])  /** GetResult implicit for fetching PlaylistRow objects using plain SQL queries */  implicit def GetResultPlaylistRow(implicit e0: GR[Int], e1: GR[Option[String]]): GR[PlaylistRow] = GR{    prs => import prs._    PlaylistRow.tupled((<<[Int], <<?[String]))  }  /** Table description of table Playlist. Objects of this class serve as prototypes for rows in queries. */  class Playlist(tag: Tag) extends Table[PlaylistRow](tag, "Playlist") {    def * = (playlistid, name) <> (PlaylistRow.tupled, PlaylistRow.unapply)    /** Maps whole row to an option. Useful for outer joins. */    def ? = (playlistid.?, name).shaped.<>({r=>import r._; _1.map(_=> PlaylistRow.tupled((_1.get, _2)))}, (_:Any) =>  throw new Exception("Inserting into ? projection not supported."))    /** Database column PlaylistId PrimaryKey */    val playlistid: Column[Int] = column[Int]("PlaylistId", O.PrimaryKey)    /** Database column Name  */    val name: Column[Option[String]] = column[Option[String]]("Name")  }  /** Collection-like TableQuery object for table Playlist */  lazy val Playlist = new TableQuery(tag => new Playlist(tag))  /** Entity class storing rows of table Playlisttrack   *  @param playlistid Database column PlaylistId    *  @param trackid Database column TrackId  */  case class PlaylisttrackRow(playlistid: Int, trackid: Int)  /** GetResult implicit for fetching PlaylisttrackRow objects using plain SQL queries */  implicit def GetResultPlaylisttrackRow(implicit e0: GR[Int]): GR[PlaylisttrackRow] = GR{    prs => import prs._    PlaylisttrackRow.tupled((<<[Int], <<[Int]))  }  /** Table description of table PlaylistTrack. Objects of this class serve as prototypes for rows in queries. */  class Playlisttrack(tag: Tag) extends Table[PlaylisttrackRow](tag, "PlaylistTrack") {    def * = (playlistid, trackid) <> (PlaylisttrackRow.tupled, PlaylisttrackRow.unapply)    /** Maps whole row to an option. Useful for outer joins. */    def ? = (playlistid.?, trackid.?).shaped.<>({r=>import r._; _1.map(_=> PlaylisttrackRow.tupled((_1.get, _2.get)))}, (_:Any) =>  throw new Exception("Inserting into ? projection not supported."))    /** Database column PlaylistId  */    val playlistid: Column[Int] = column[Int]("PlaylistId")    /** Database column TrackId  */    val trackid: Column[Int] = column[Int]("TrackId")    /** Primary key of Playlisttrack (database name PlaylistTrack_PK) */    val pk = primaryKey("PlaylistTrack_PK", (playlistid, trackid))    /** Foreign key referencing Playlist (database name FK_PlaylistTrackPlaylistId) */    lazy val playlistFk = foreignKey("FK_PlaylistTrackPlaylistId", playlistid, Playlist)(r => r.playlistid, onUpdate=ForeignKeyAction.NoAction, onDelete=ForeignKeyAction.NoAction)    /** Foreign key referencing Track (database name FK_PlaylistTrackTrackId) */    lazy val trackFk = foreignKey("FK_PlaylistTrackTrackId", trackid, Track)(r => r.trackid, onUpdate=ForeignKeyAction.NoAction, onDelete=ForeignKeyAction.NoAction)  }  /** Collection-like TableQuery object for table Playlisttrack */  lazy val Playlisttrack = new TableQuery(tag => new Playlisttrack(tag))  /** Entity class storing rows of table Track   *  @param trackid Database column TrackId PrimaryKey   *  @param name Database column Name    *  @param albumid Database column AlbumId    *  @param mediatypeid Database column MediaTypeId    *  @param genreid Database column GenreId    *  @param composer Database column Composer    *  @param milliseconds Database column Milliseconds    *  @param bytes Database column Bytes    *  @param unitprice Database column UnitPrice  */  case class TrackRow(trackid: Int, name: String, albumid: Option[Int], mediatypeid: Int, genreid: Option[Int], composer: Option[String], milliseconds: Int, bytes: Option[Int], unitprice: scala.math.BigDecimal)  /** GetResult implicit for fetching TrackRow objects using plain SQL queries */  implicit def GetResultTrackRow(implicit e0: GR[Int], e1: GR[String], e2: GR[Option[Int]], e3: GR[Option[String]], e4: GR1): GR[TrackRow] = GR{    prs => import prs._    TrackRow.tupled((<<[Int], <<[String], <<?[Int], <<[Int], <<?[Int], <<?[String], <<[Int], <<?[Int], <<1))  }  /** Table description of table Track. Objects of this class serve as prototypes for rows in queries. */  class Track(tag: Tag) extends Table[TrackRow](tag, "Track") {    def * = (trackid, name, albumid, mediatypeid, genreid, composer, milliseconds, bytes, unitprice) <> (TrackRow.tupled, TrackRow.unapply)    /** Maps whole row to an option. Useful for outer joins. */    def ? = (trackid.?, name.?, albumid, mediatypeid.?, genreid, composer, milliseconds.?, bytes, unitprice.?).shaped.<>({r=>import r._; _1.map(_=> TrackRow.tupled((_1.get, _2.get, _3, _4.get, _5, _6, _7.get, _8, _9.get)))}, (_:Any) =>  throw new Exception("Inserting into ? projection not supported."))    /** Database column TrackId PrimaryKey */    val trackid: Column[Int] = column[Int]("TrackId", O.PrimaryKey)    /** Database column Name  */    val name: Column[String] = column[String]("Name")    /** Database column AlbumId  */    val albumid: Column[Option[Int]] = column[Option[Int]]("AlbumId")    /** Database column MediaTypeId  */    val mediatypeid: Column[Int] = column[Int]("MediaTypeId")    /** Database column GenreId  */    val genreid: Column[Option[Int]] = column[Option[Int]]("GenreId")    /** Database column Composer  */    val composer: Column[Option[String]] = column[Option[String]]("Composer")    /** Database column Milliseconds  */    val milliseconds: Column[Int] = column[Int]("Milliseconds")    /** Database column Bytes  */    val bytes: Column[Option[Int]] = column[Option[Int]]("Bytes")    /** Database column UnitPrice  */    val unitprice: Column1 = column1("UnitPrice")    /** Foreign key referencing Album (database name FK_TrackAlbumId) */    lazy val albumFk = foreignKey("FK_TrackAlbumId", albumid, Album)(r => r.albumid, onUpdate=ForeignKeyAction.NoAction, onDelete=ForeignKeyAction.NoAction)    /** Foreign key referencing Genre (database name FK_TrackGenreId) */    lazy val genreFk = foreignKey("FK_TrackGenreId", genreid, Genre)(r => r.genreid, onUpdate=ForeignKeyAction.NoAction, onDelete=ForeignKeyAction.NoAction)    /** Foreign key referencing Mediatype (database name FK_TrackMediaTypeId) */    lazy val mediatypeFk = foreignKey("FK_TrackMediaTypeId", mediatypeid, Mediatype)(r => r.mediatypeid, onUpdate=ForeignKeyAction.NoAction, onDelete=ForeignKeyAction.NoAction)    }    /** Collection-like TableQuery object for table Track */    lazy val Track = new TableQuery(tag => new Track(tag))}

基本查询

我们准备好了开发环境，下面就来看看 Slick 的基本查询方法，我们打算查询 Chinook 中的 Album 表，我们先看看之前自动生成的代码中表 Album 的定义： 

/** Entity class storing rows of table Album   *  @param albumid Database column AlbumId PrimaryKey   *  @param title Database column Title   *  @param artistid Database column ArtistId  */case class AlbumRow(albumid: Int, title: String, artistid: Int)    /** 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]))}/** Table description of table Album. Objects of this class serve as prototypes for rows in queries. */class Album(tag: Tag) extends Table[AlbumRow](tag, "Album") {    ...    /** Database column AlbumId PrimaryKey */    val albumid: Column[Int] = column[Int]("AlbumId", O.PrimaryKey)    /** Database column Title  */    val title: Column[String] = column[String]("Title")    /** Database column ArtistId  */    val artistid: Column[Int] = column[Int]("ArtistId")    /** Foreign key referencing Artist (database name FK_AlbumArtistId) */    lazy val artistFk = foreignKey("FK_AlbumArtistId", artistid, Artist)        (r => r.artistid, onUpdate=ForeignKeyAction.NoAction, onDelete=ForeignKeyAction.NoAction)}    /** Collection-like TableQuery object for table Album */    lazy val Album = new TableQuery(tag => new Album(tag))

可以看到对于数据库中每个表，我们定义了一个 case class 代表表的一行，如 AlbumRow，一个 Table 类，比如 Album，还定义了一个 lazy 变量 Album，可以直接使用这个变量以集合类对象的方式来查询数据表。下面我们看看查询的基本用法：

我们在和 Tables.scala 的同一个目录下（本例为 com/guidebee/slick/example）创建一个 Example.scala 文件: 

package com.guidebee.slick.exampleimport scala.slick.driver.MySQLDriver.simple._import com.guidebee.slick.example.Tables._// The main applicationobject Example extends App {    Database.forURL("jdbc:mysql://127.0.0.1/Chinook",        driver = "com.mysql.jdbc.Driver",        user="user",        password="password").withSession {            implicit session =>            // <- write queries here            Album foreach { case AlbumRow(albumId,title,artistId) =>            println(" " + albumId + ":" + title + ":" + artistId)            }        }}

注意：修改正确的用户名和密码。

其中代码 

    Database.forURL("jdbc:mysql://127.0.0.1/Chinook",        driver = "com.mysql.jdbc.Driver",        user="user",        password="password").withSession {          implicit session =>          // <- write queries here           }        }

用来连接数据库，并且创建一个 Session 对象，所有数据库相关查询都可以在这个代码块中实现，这里我们打印出 Album 中所有记录：

图片

Album 为一集合对象，因此我们可以使用 Scala 集合对象支持的方法，来过滤，比较，比如： 

val q1= for (a <- Album;if a.albumid<10)     yield (a.albumid,a.title,a.artistid)q1 foreach println

显示前 9 条记录：(1,For Those About To Rock We Salute You,1)(2,Balls to the Wall,2)(3,Restless and Wild,2)(4,Let There Be Rock,1)(5,Big Ones,3)(6,Jagged Little Pill,4)(7,Facelift,5)(8,Warner 25 Anos,6)(9,Plays Metallica By Four Cellos,7)

val q1= for (a <- Album;if a.albumid<10)    yield a.albumid.asColumnOf[String] ++ LiteralColumn(":") ++ a.titleq1 foreach println

1:For Those About To Rock We Salute You2:Balls to the Wall3:Restless and Wild4:Let There Be Rock5:Big Ones6:Jagged Little Pill7:Facelift8:Warner 25 Anos9:Plays Metallica By Four Cellos

我们再来看看多个表 Join 的情况：先看看直接使用 SQL 语句 

select album.AlbumId,album.Title,artist.Name from albumINNER JOIN artist ON album.ArtistId=artist.ArtistIdWHERE album.AlbumId<10

图片

那么使用 Scala 语句如何实现呢，也就是多个集合对象 Join 的情况： 

val q3 = for {    a <- Album if a.albumid < 10    t <- Artist if a.artistid===t.artistid }  yield (a.albumid,a.title,t.name)q3 foreach println

注意，比较运算符为===，我们也可以直接使用外键来查询，在 Tables.scala，类 Album 中定义了一个外键 artistFk 

val q2 = for {    a <- Album if a.albumid < 10    t <- a.artistFk} yield (a.albumid,a.title,t.name)q2 foreach println

两种方法都输出如下结果： 

(1,For Those About To Rock We Salute You,Some(AC/DC))(2,Balls to the Wall,Some(Accept))(3,Restless and Wild,Some(Accept))(4,Let There Be Rock,Some(AC/DC))(5,Big Ones,Some(Aerosmith))(6,Jagged Little Pill,Some(Alanis Morissette))(7,Facelift,Some(Alice In Chains))(8,Warner 25 Anos,Some(Antônio Carlos Jobim))(9,Plays Metallica By Four Cellos,Some(Apocalyptica))

数据库连接和事务处理

你可以在程序的任何地方使用数据库查询，当执行查询时你需要有一个数据库连接。

你可以通过创建一个 Database 对象来连接一个 JDBC 数据库，有多种方法可以创建一个数据库对象。

使用 JDBC URL

你可以使用 JDBC URL 来创建一个 Database 对象（URL 的格式取决于连接的数据库的类型），比如：

val db = Database.forURL("jdbc:h2:mem:test1;DB_CLOSE_DELAY=-1", driver="org.h2.Driver")

创建一个基于内存的 H2 数据库连接，再比如我们之前使用的 MySQL 数据库，可以使用 

val db = Database.forURL("jdbc:mysql://127.0.0.1/Chinook",    driver = "com.mysql.jdbc.Driver",    user="user",    password="password")

使用 DataSource

你可以使用已有的 datasource 对象，来构建一个 Database 对象，比如你从连接池中取得一个 Datasource 对象，然后连接到 Slick 库中 

val db = Database.forDataSource(dataSource: javax.sql.DataSource)

之后你创建一个 Session 对象，将从连接池中取得一个数据库连接，当关闭 Session 时，连接退回给连接池以作他用。

使用 JNDI 名称

如果你使用 JNDI，你可以提供 JNDI 名称来构建一个 Database 对象： 

val db = Database.forName(jndiName: String)

Session 管理

现在你有了一个数据库对象可以打开一个数据库（Slick 函数库封装了一个 Session 对象）

Database 的 withSession 方法，创建一个 Session 对象，它可以传递给一个函数，函数返回时自动关闭这个 Session 对象，如果你使用连接池，关闭 Session 对象，自动将连接退回连接池。 

val query = for (c <- coffees) yield c.nameval result = db.withSession {    session =>    query.list()( session )}

你可以看到，我们可以在 withSession 之外定义查询，只有在实际执行查询时才需要一个 Session 对象，要注意的是 Session 的缺省模式为自动提交（auto-commit )模式。每个数据库指令（比如 insert )都自动提交给数据库。 如果需要将几个指令作为一个整体，那么就需要使用事务处理（Transaction）上面的例子，我们在执行查询时，明确指明了 session 对象，你可以使用隐含对象来避免这种情况，比如： 

val query = for (c <- coffees) yield c.nameval result = db.withSession {    implicit session =>    query.list // <- takes session implicitly}// query.list // <- would not compile, no implicit value of type Session

手工管理 Session

这不是推荐使用的情况，但如果你需要自己管理 Session 对象，你可以自己管理 Session 的生命周期： 

val query = for (c <- coffees) yield c.nameval session : Session = db.createSessionval result  = query.list()( session )session.close

事务处理

你可以使用 Session 对象的 withTransaction 方法来创建一个事务，传给该方法的语句将作为一个整体事务执行，如果出现异常，Slick 自动回滚事务，你也可以使用 rollback 强制事务回滚，要注意的是 Slick 只会回滚数据库相关操作，而不会取消其它 Scala 语句。 

session.withTransaction {    // your queries go here    if (/* some failure */ false){        session.rollback // signals Slick to rollback later    }} //

如果你没有 Session 对象，也可以直接使用数据库对象的 withTransaction 方法，如： 

db.withTransaction{    implicit session =>    // your queries go here}

数据库 Schema

我们之前 Slick 编程(2): 准备开发环境使用自动代码生成工具生成数据库表的 Slick 定义（使用 Lifted Embedding API )，本篇介绍如何手工来写这些 Schema 定义。

数据库表 Tables

为了能够使用 Slick 的 Lifted Embedding API 定义类型安全的查询，首先我们需要定义数据库表代表表中每行数据的类和对应于数据库表的 Schema 的 TableQuery 值，我们先看看自动生成的 Album 表个相关定义： 

/** Entity class storing rows of table Album   *  @param albumid Database column AlbumId PrimaryKey   *  @param title Database column Title    *  @param artistid Database column ArtistId  */  case class AlbumRow(albumid: Int, title: String, artistid: Int)  /** 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]))  }  /** Table description of table Album. Objects of this class serve as prototypes for rows in queries. */  class Album(tag: Tag) extends Table[AlbumRow](tag, "Album") {    def * = (albumid, title, artistid) <> (AlbumRow.tupled, AlbumRow.unapply)    /** Maps whole row to an option. Useful for outer joins. */    def ? = (albumid.?, title.?, artistid.?).shaped.<>(        {r=>import r._; _1.map(_=> AlbumRow.tupled((_1.get, _2.get, _3.get)))},         (_:Any) =>  throw new Exception("Inserting into ? projection not supported."))    /** Database column AlbumId PrimaryKey */    val albumid: Column[Int] = column[Int]("AlbumId", O.PrimaryKey)    /** Database column Title  */    val title: Column[String] = column[String]("Title")    /** Database column ArtistId  */    val artistid: Column[Int] = column[Int]("ArtistId")    /** Foreign key referencing Artist (database name FK_AlbumArtistId) */    lazy val artistFk = foreignKey("FK_AlbumArtistId", artistid, Artist)        (r => r.artistid, onUpdate=ForeignKeyAction.NoAction, onDelete=ForeignKeyAction.NoAction)  }  /** Collection-like TableQuery object for table Album */  lazy val Album = new TableQuery(tag => new Album(tag))

所有的字段（Column）使用 column 方法来定义，每个字段对应一个 Scala 类型和一个字段名称（对应到数据库表的定义），下面为 Slick 支持的基本数据类型：

  • Numeric types: Byte, Short, Int, Long, BigDecimal, Float, Double
  • LOB types: java.sql.Blob, java.sql.Clob, Array[Byte]
  • Date types: java.sql.Date, java.sql.Time, java.sql.Timestamp
  • Boolean
  • String
  • Unit
  • java.util.UUID

支持 Null 的字段使用 Option[T] 来表示，其中 T 为上述基本数据类型，在字段名称之后，你可以使用一些可选的字段定义，这些可选定义定义在 table 的 O 对象中。下面为常用的定义

PrimaryKey 表明该字段为主键Default[T](defaultValue: T) 该字段缺省值DBType(dbType: String) 非标准字段类型，比如 DBType(“VARCHAR(20)”) 做为 String 类型AutoInc 自动增一的字段NotNull，Nullable 表明该字段是否可以为空

每个表定义都需要一个""方法定义了缺省映射，这定义了执行查询返回表格一行时的数据类型，Slick 的””不要求和数据库表的定义一一映射，你可以添加字段（复合字段）或者省略掉某个字段。

匹配过的表定义

可以使用自定义的数据类型做为”*”的映射，这可以使用双向映射操作符”“来完成。

比如： 

def * = (albumid, title, artistid) <> (AlbumRow.tupled, AlbumRow.unapply)

约束

外键约束可以使用foreignKey来定义 

/** Foreign key referencing Artist (database name FK_AlbumArtistId) */    lazy val artistFk = foreignKey("FK_AlbumArtistId", artistid, Artist)        (r => r.artistid, onUpdate=ForeignKeyAction.NoAction, onDelete=ForeignKeyAction.NoAction)

它的参数为外键约束的名称，本表字段名称，外键所在表名称，和一个函数，这个函数定义了外键约束，以及更新和删除外键时的行为）

主键约束可以使用 primaryKey 来定义，这主要用作定义复合主键的情况 

/** Primary key of Playlisttrack (database name PlaylistTrack_PK) */    val pk = primaryKey("PlaylistTrack_PK", (playlistid, trackid))

其它比如索引的情况和主键约束非常类似，比如： 

class A(tag: Tag) extends Table[(Int, Int)](tag, "a") {    def k1 = column[Int]("k1")    def k2 = column[Int]("k2")    def * = (k1, k2)    def idx = index("idx_a", (k1, k2), unique = true)    // compiles to SQL:    //   create unique index "idx_a" on "a" ("k1","k2")}

数据库定义语言 DDL

数据库定义语句可以使用 TableQuery 的 ddl 方法，多个 DDL 对象可以使用 ++ 连接，比如： 

val ddl = coffees.ddl ++ suppliers.ddldb withDynSession {    ddl.create    //...    ddl.drop}

ddl.create 和 ddl.drop 可以创建表和删除表，如果需要看看对应的 SQL 语句，可以使用 

val ddl = Album.ddlddl.createStatements.foreach(println)ddl.dropStatements.foreach(println)

对应的 MySQL 语句为 

create table `Album` (`AlbumId` INTEGER NOT NULL PRIMARY KEY,`Title` VARCHAR(254) NOT NULL,`ArtistId` INTEGER NOT NULL)alter table `Album` add constraint `FK_AlbumArtistId` foreign key(`ArtistId`) references `Artist`(`ArtistId`) on update NO ACTION on delete NO ACTIONALTER TABLE Album DROP FOREIGN KEY FK_AlbumArtistIddrop table `Album`

查询（一）

本篇介绍 Slick 的基本查询，比如选择，插入，更新，删除记录等。

排序和过滤

Slick 提供了多种方法可以用来排序和过滤，比如： 

val q = Album.filter(_.albumid === 101)//select `AlbumId`, `Title`, `ArtistId` //from `Album` where `AlbumId` = 101val q = Album.drop(10).take(5)//select .`AlbumId` as `AlbumId`, .`Title` as `Title`,// .`ArtistId` as `ArtistId` from `Album`  limit 10,5val q = Album.sortBy(_.title.desc)//select `AlbumId`, `Title`, `ArtistId` //from `Album` order by `Title` desc

Join 和 Zipping

Join 指多表查询，可以有两种不同的方法来实现多表查询，一种是通过明确调用支持多表连接的方法（比如 innerJoin 方法）返回一个多元组，另外一种为隐含连接( implicit join )，它不直接使用这些连接方法（比如 LeftJoin 方法）。

一个隐含的 cross-Join 为 Query 的 flatMap 操作（在 for 表达式中使用多个生成式），例如： 

val q = for{a <- Album            b <- Artist        } yield( a.title, b.name)//select x2.`Title`, x3.`Name` from `Album` x2, `Artist` x3

如果添加一个条件过滤表达式，它就变成隐含的 inner join，例如： 

val q = for{a <- Album            b <- Artist            if a.artistid === b.artistid        } yield( a.title, b.name)//select x2.`Title`, x3.`Name` from `Album` x2, `Artist` x3 //where x2.`ArtistId` = x3.`ArtistId`

明确的多表连接则使用 innerJoin，leftJoin，rightJoin，outerJoin 方法，例如： 

val explicitCrossJoin = = for {             (a,b) <- Album innerJoin Artist               } yield( a.title, b.name)//select x2.x3, x4.x5 from (select x6.`Title` as x3 from `Album` x6) //x2 inner join (select x7.`Name` as x5 from `Artist` x7) x4 on 1=1val explicitInnerJoin  = for {         (a,b) <- Album innerJoin Artist on (_.artistid === _.artistid)         } yield( a.title, b.name)//select x2.x3, x4.x5 from (select x6.`Title` as x3, x6.`ArtistId` as x7 from `Album` x6) x2 //inner join (select x8.`ArtistId` as x9, x8.`Name` as x5 from `Artist` x8) x4 on x2.x7 = x4.x9val explicitLeftOuterJoin   = for {         (a,b) <- Album leftJoin Artist on (_.artistid === _.artistid)         } yield( a.title, b.name.?)//select x2.x3, x4.x5 from (select x6.`Title` as x3, x6.`ArtistId` as x7 from `Album` x6) x2 //left outer join (select x8.`ArtistId` as x9, x8.`Name` as x5 from `Artist` x8) x4 on x2.x7 = x4.x9val explicitRightOuterJoin   = for {         (a,b) <- Album rightJoin Artist on (_.artistid === _.artistid)         } yield( a.title.?, b.name)//select x2.x3, x4.x5 from (select x6.`Title` as x3, x6.`ArtistId` as x7 from `Album` x6) x2 //right outer join (select x8.`ArtistId` as x9, x8.`Name` as x5 from `Artist` x8) x4 on x2.x7 = x4.x9

注意 leftJoin 和 rightJoin 中的 b.name.? 和 a.title.? 的”.?” 这是因为外部查询时会产生额外的 NULL 值，你必须保证返回 Option 类型的值。

除了通常的 InnerJoin，LeftJoin，RightJoin 之外，Scala 还提供了 Zip 方法，它的语法类似于 Scala 的集合类型，比如： 

val zipJoinQuery  = for {       (a,b) <- Album zip Artist     } yield( a.title.?, b.name)

此外，还有一个 zipWithIndex，可以把一个表的行和一个从 0 开始的整数序列 Zip 操作，相当于给行添加序号，比如 

val zipWithIndexJoin  = for {       (a,idx) <- Album.zipWithIndex      } yield( a.title, idx)

查询（二）

Union

两个查询的结果可以通过 ++ （或者 unionAll ) 和 union 操作联合起来：

val q1= Album.filter(_.artistid <10)val q2 = Album.filter(_.artistid > 15)val unionQuery  = q1 union q2val unionAllQuery = q1 ++ q2

union 操作会去掉重复的结果，而 unionAll 只是简单的把两个查询结果连接起来（通常来说比较高效）。

Aggregation

和 SQL 一样，Slick 也有 min，max，sum，avg 等集合操作

val q = Album.map(_.artistid)val q1 = q.maxval q2 = q.min val q3 = q.avg val q4 = q.sum

注意：这里 q.max，min，avg，sum 返回结果类型为 Column[Option[T]]，要得到最好的 scalar 类型的值 T，可以调用 run，得到 Option[T]，然后再调用 Option 的 get 或 getOrDefault，比如： 

val q = Album.map(_.artistid)val q1 = q.max println(q1.run.get)

得到打印的结果：275

其它的 Aggregation 操作还有 length，exists，比如： 

val q1 = Album.lengthval q2 = Album.exists

分组使用 groupBy 操作，类似于 Scala 集合类型的 groupBy 操作： 

val q= (for {     a <- Album     b <- Artist     if a.artistid === b.artistid   } yield (b.artistid,b.name)).groupBy(_._2)val q1 = q.map { case (name, records) =>        (records.map(_._1).avg, name,records.length)}q1 foreach println

这段代码使用两个查询，给出 Album 根据艺术家出的专辑的统计，其中中间查询 q，包含一个嵌套的 Query，目前 Scala 不支持直接查询嵌套的 Query，因此我们需要分两次查询，打印出的部分结果如下： 

(Some(230),Some(Aaron Copland & London Symphony Orchestra),1)(Some(202),Some(Aaron Goldberg),1)(Some(1),Some(AC/DC),2)(Some(214),Some(Academy of St. Martin in the Fields & Sir Neville Marriner),1)(Some(215),Some(Academy of St. Martin in the Fields Chamber Ensemble & Sir Neville Marriner),1)(Some(222),Some(Academy of St. Martin in the Fields, John Birch, Sir Neville Marriner & Sylvia McNair),1)(Some(257),Some(Academy of St. Martin in the Fields, Sir Neville Marriner & Thurston Dart),1)(Some(2),Some(Accept),2)(Some(260),Some(Adrian Leaper & Doreen de Feis),1)(Some(3),Some(Aerosmith),1)(Some(197),Some(Aisha Duo),1)(Some(4),Some(Alanis Morissette),1)(Some(206),Some(Alberto Turco & Nova Schola Gregoriana),1)(Some(5),Some(Alice In Chains),1)(Some(252),Some(Amy Winehouse),2)...

查询（三）

Slick 的查询实际上是执行由 Invoker（无参数时为 UnitInvoker ) Trait 定义的方法，Slick 定义了一个从 Query 隐含的变换,使得你可以直接执行查询操作，最常用的一个情况是把整个查询结果存放到一个 Scala 集合类型中（比如使用 list 方法） 

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

所有的查询方法都定义了一个隐含参数 Session，如果你愿意，你也可以直接传入一个 session 参数： 

val l = q.list()(session)

如果你只需要单个查询结果，你可以使用 first 或 firstOption 方法，而方法 foreach， foldLeft 和 elements 方法可以用来遍历查询结果而不需要先把结果复制到另外一个 Scala 集合对象中。

Deleting

删除数据和查询很类似，你首先写一个选择查询，然后调用它的 delete 方法，同样 Slick 也定义一个从 Query 到 DeleteInvoker 的隐含转换，DeleteInvoker 定义了 delete 方法 

val affectedRowsCount = q.deleteval invoker = q.deleteInvokerval statement = q.deleteStatement

定义用来删除记录的查询时只能使用单个表格。

Inserting

插入操作基于单个表定义的字段映射，当你直接使用某个表来插入数据时，这个操作基于表类型中定义的“*”，如果你省略某些字段，那么插入这些省略的字段会使用缺省值，所有的插入操作方法定义在 InsertInvoker 和 FullInsertInvoker。 

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.insertStatementval invoker = coffees.insertInvoker// compiles to SQL:// INSERT INTO "COFFEES" ("COF_NAME","SUP_ID","PRICE","SALES","TOTAL") VALUES (?,?,?,?,?)

如果你的插入操作定义了自动增一的字段，该字段会自动忽略，由数据库本身来插入该字段的值。缺省情况 += 返回受影响的行数（通常总为 1），而 ++ 操作给出总计的行数（以 Option 类型给出），你可以使用 returning 修改返回的值，比如返回插入的行的主键： 

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

要注意的是很多数据库只支持返回自动增一的作为主键的那个字段，如果想返回其它字段，可能会抛出 SlickException 异常。

除了上面的插入记录的方法，还可以使用服务器端表达式的方发插入数据： 

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.createusers2 insert (users.map { u => (u.id, u.first ++ " " ++ u.last) })users2 insertExpr (users.length + 1, "admin")

Updating

更新记录也是先写查询，然后调用 update 方法，比如： 

val q = for { c <- coffees if c.name === "Espresso" } yield c.priceq.update(10.49)val statement = q.updateStatementval invoker = q.updateInvoker

update 方法定义在 UpdateInvoker Trait 中。

Compiled Queries

数据库查询时，通常需要定义一些查询参数，比如根据 ID 查找对应的记录。你可以定义一个带参数的函数来定义查询对象，但每次调用该函数时都要重新编译这个查询语句，系统消耗有些大，Slick 支持预编译这个带参数的查询函数，例如： 

def userNameByIDRange(min: Column[Int], max: Column[Int]) =  for {    u <- users if u.id >= min && u.id < max  } yield u.firstval userNameByIDRangeCompiled = Compiled(userNameByIDRange _)// The query will be compiled only once:val names1 = userNameByIDRangeCompiled(2, 5).runval names2 = userNameByIDRangeCompiled(1, 3).run

这种方法支持查询，更新和删除数据。

直接使用 SQL 语句

如果你有需要直接使用 SQL 语句，Slick 也支持你直接使用 SQL 语句。

首先你需要引入一些引用包： 

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

其中最重要的一个相关类似 StaticQuery，为简洁起见，我们使用 Q 作为它的别名。连接数据库还是和以前一样 Slick 编程(4): 数据库连接和事务处理

DDL 和 DML 语句

StaticQuery 的方法 updateNA，（NA 代表无参数），它返回 DDL 指令影响的行数，比如使用 H2 数据库

连接数据库： 

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 the tables, including primary and foreign keysQ.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)").executeQ.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

你可以使用字符串和一个 StaticQuery 对象相加（+）构成一个新的 StaticQuery 对象，一个简单的方法是使用 Q.u 和一个字符串相加，Q.u 代表一个相当与 StaticQuery.updateNA(“”)

例如我们在表中插入一些数据： 

// 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

在 SQL 查询语句中使用字面量不是一种推荐的方法，尤其是当用户提供数据时（不十分安全）， 此时你可以使用 +? 操作符为查询语句绑定一个参数，比如： 

def insert(c: Coffee) = (Q.u + "insert into coffees values (" +? c.name +  "," +? c.supID + "," +? c.price + "," +? c.sales + "," +? c.total + ")").execute// Insert some coffeesSeq(  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)

这段代码相对于 insert into coffees values (?,?,?,?,?)

查询语句

和 updateNA 类似， StaticQuery 还有一个 queryNA 方法，它支持一个类型参数（代表表的一行），比如： 

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

这段代码之所以能工作，是因为 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]))}

定义了从 JDBC 类型到 GetResult[T] 的隐含转换，GetResult[T] 为函数 PositionedResult => T 的一个封装。<<[T] 返回指定位置上期望的值。

和 queryNA 对应的带参数的 query 定义了两个类型参数，一个是参数的类型，另外一个是返回的结果的每行的类型，例如： 

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)

返回结果如下： 

1 For Those About To Rock We Salute You 14 Let There Be Rock 12 Balls to the Wall 23 Restless and Wild 25 Big Ones 36 Jagged Little Pill 47 Facelift 58 Warner 25 Anos 634 Chill: Brazil (Disc 2) 69 Plays Metallica By Four Cellos 710 Audioslave 811 Out Of Exile 8271 Revelations 812 BackBeat Soundtrack 9

Q.interpolation 支持字符串插值，比如： 

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

使用 sql 做为前缀的字符串，可以将以 $ 开始的变量替换成该变量的值，此外对于 update/delete 语句，可以使用 sqlu 前缀。