Slick 查詢(一)

查詢（一）

本篇介紹 Slick 的基本查詢，比如選擇，插入，更新，刪除記錄等。

排序和過濾

Slick 提供了多種方法可以用來排序和過濾，比如：

val q = Album.filter(_.albumid === 101)

//select `AlbumId`, `Title`, `ArtistId` 
//from `Album` where `AlbumId` = 101

val q = Album.drop(10).take(5)
//select .`AlbumId` as `AlbumId`, .`Title` as `Title`,
// .`ArtistId` as `ArtistId` from `Album`  limit 10,5

val q = Album.sortBy(_.title.desc)
//select `AlbumId`, `Title`, `ArtistId` 
//from `Album` order by `Title` desc

Join 和 Zipping

Join 指多表查詢，可以有兩種不同的方法來實(shí)現(xiàn)多表查詢，一種是通過明確調(diào)用支持多表連接的方法（比如 innerJoin 方法）返回一個(gè)多元組，另外一種為隱含連接( implicit join )，它不直接使用這些連接方法（比如 LeftJoin 方法）。

一個(gè)隱含的 cross-Join 為 Query 的 flatMap 操作（在 for 表達(dá)式中使用多個(gè)生成式），例如：

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

//select x2.`Title`, x3.`Name` from `Album` x2, `Artist` x3

如果添加一個(gè)條件過濾表達(dá)式，它就變成隱含的 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=1

val 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.x9

val 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.x9

val 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.? 的”.?” 這是因?yàn)橥獠坎樵儠r(shí)會產(chǎn)生額外的 NULL 值，你必須保證返回 Option 類型的值。

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

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

此外，還有一個(gè) zipWithIndex，可以把一個(gè)表的行和一個(gè)從 0 開始的整數(shù)序列 Zip 操作，相當(dāng)于給行添加序號，比如

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