Facebook Twitter LinkedIn E-mail
magnify
Home Posts tagged "Slick"

Slick 编程(9): 直接使用SQL语句

如果你有需要直接使用SQL语句,Slick也支持你直接使用SQL语句。
首先你需要引入一些引用包:

import scala.slick.jdbc.{GetResult, StaticQuery => Q}
import scala.slick.jdbc.JdbcBackend.Database
import 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 keys
Q.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)").execute
Q.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 coffees
Seq(
  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定义了两个类型参数,一个是参数的类型,另外一个是返回的结果的每行的类型,例如: [sourcecode lang="scala"] 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) [/sourcecode] 返回结果如下: [sourcecode lang="scala"] 1 For Those About To Rock We Salute You 1 4 Let There Be Rock 1 2 Balls to the Wall 2 3 Restless and Wild 2 5 Big Ones 3 6 Jagged Little Pill 4 7 Facelift 5 8 Warner 25 Anos 6 34 Chill: Brazil (Disc 2) 6 9 Plays Metallica By Four Cellos 7 10 Audioslave 8 11 Out Of Exile 8 271 Revelations 8 12 BackBeat Soundtrack 9 [/sourcecode] Q.interpolation 支持字符串插值,比如: [sourcecode lang="scala"] def albumByTitle(title: String) = sql"select * from Album where title = $title".as[AlbumRow] println("Album: " + albumByTitle("Let There Be Rock").firstOption) [/sourcecode] 使用sql 做为前缀的字符串,可以将以$开始的变量替换成该变量的值,此外对于update/delete语句,可以使用sqlu前缀

 

Slick 编程(8): 查询(三)

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

val l = q.list
val v = q.buildColl[Vector]
val invoker = q.invoker
val 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.delete
val invoker = q.deleteInvoker
val 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.insertStatement
val 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.create

users2 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.price
q.update(10.49)

val statement = q.updateStatement
val 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.first

val userNameByIDRangeCompiled = Compiled(userNameByIDRange _)

// The query will be compiled only once:
val names1 = userNameByIDRangeCompiled(2, 5).run
val names2 = userNameByIDRangeCompiled(1, 3).run

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

 

Slick 编程(7): 查询(二)

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

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

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

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

val q = Album.map(_.artistid)
val q1 = q.max
val 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.length
val 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 编程(6): 查询(一)

本篇介绍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指多表查询,可以有两种不同的方法来实现多表查询,一种是通过明确调用支持多表连接的方法(比如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=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.? 的”.?” 这是因为外部查询时会产生额外的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)
 

Slick 编程(5): 数据库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.ddl
db withDynSession {
  ddl.create
  //...
  ddl.drop
}

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

val ddl = Album.ddl
ddl.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 ACTION
ALTER TABLE Album DROP FOREIGN KEY FK_AlbumArtistId
drop table `Album`
 

Slick 编程(4): 数据库连接和事务处理

你可以在程序的任何地方使用数据库查询,当执行查询时你需要有一个数据库连接。
你可以通过创建一个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.name
val result = db.withSession {
  session =>
  query.list()( session )
}

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

val query = for (c <- coffees) yield c.name
val 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.name
val session : Session = db.createSession
val 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
}