hangscer

有可能是最最最简单优美的ORM框架-quill(基于scala)

2017/05/24

Quill provides a Quoted Domain Specific Language (QDSL) to express queries in Scala and execute them in a target language. The library’s core is designed to support multiple target languages, currently featuring specializations for Structured Query Language (SQL) and Cassandra Query Language (CQL).

这篇博客确实有标题党的嫌疑,不过quill真的很优秀。

这是官网对其的介绍。quill提供非常优美舒适的dsl,容易快速入手使用。本博客采用的数据库基于mysql,数据来源于《SQL学习指南》中的数据,数据库脚本在此。至于mybatishibernate本文不再赘述。

写博客也是种学习过程。

Schema

The database schema is represented by case classes. By default, quill uses the class and field names as the database identifiers.
数据库模式被case class代表,case class的类名和字段名最好与数据库中的模式相同。(case classclass区别不再累述)比如:

1
2
3
object Schema{
case class Customer(cust_id:Int,fed_id:String,cust_type_cd:String,address:String,city:String,state:String,postal_code:String)
}

一个简单的查询:

1
2
3
4
val q=quote{
query[Customer].filter(customer=>customer.cust_id<100)
}
ctx.run(q).toList.foreach(println)

当然,还可以自定义字段名与表名,比如这里Depart与数据库实体名称不一致:

1
2
3
4
5
6
7
8
case class Depart(deptId:Int,departName:String)
val departs=quote{
querySchema[Depart]("department_table",_.deptId->"dept_id",_.departName->"name")
}
val q=quote{
departs.filter(_.deptId<100)
}
ctx.run(q).toList.foreach(println)

querySchema的源码片段为:

1
def querySchema[T](entity: String, columns: (T => (Any, String))*): EntityQuery[T] = NonQuotedException()//源码值得学习

If multiple tables require custom identifiers, it is good practice to define a schema object with all table queries to be reused across multiple queries.

Database-generated values

It is possible to make a column that is a generated by the database to be ignored during insertions and returned as a returning value.
某一列的值由数据库产生,在插入时忽略,并由数据库将产生的值返回。
数据库中branch表的branch_id设置为auto_increment,那么如何在插入数据时代码中体现呢:

1
2
3
4
5
6
case class Branch(branch_id:Int,name:String,address:String,city:String,state:String,zip:String)
def insertBranch(branch: Branch)=quote{
query[Branch].insert(liftCaseClass(branch)).returning(_.branch_id)
//query[Branch].insert(lift(branch)).returning(_.branch_id) 把leftCaseClass换成lift也没问题
}
println(ctx.run(insertBranch(Branch(0,"JiangAn8","HuaiAn lianshui1","lianshui1","JS","223400"))))

注:在源码中:type RunActionReturningResult[T] = T

Embeded case classes

Quill supports nested Embedded case classes.
Quill支持类实体的嵌套。

1
2
3
4
5
6
7
8
9
CREATE TABLE person(
id INT AUTO_INCREMENT,
name VARCHAR(20),
homePhone VARCHAR(20),
homeAddress VARCHAR(20),
workPhone VARCHAR(20),
workAddress VARCHAR(20),
PRIMARY KEY person(id)
)
1
2
3
4
5
6
7
8
9
10
11
12
case class Contanct(phone:String,address:String) extends Embedded
case class Person(id:Int,name:String,homeContact:Contanct,workContact:Contanct)
val persons=quote{
querySchema[Person](
"person",
_.homeContact.phone->"homephone",
_.homeContact.address->"homeaddress",
_.workContact.phone->"workphone",
_.workContact.address->"workaddress"
)
}
ctx.run( persons.insert(lift(Person(0,"jianghang1",Contanct("829X4190","HuaiAn"),Contanct("188X8106870","WUXI")))).returning(_.id))

Embedded特质就像Serializtion接口一样,只做标记作用。

Bindings

写在前面,case class的字段命名不要使用驼峰命名法。

1
def lift[T](v: T): T = macro EncodingDslMacro.lift[T]//注意lift的函数签名

Quotations are designed to be self-contained, without references to runtime values outside their scope. There are two mechanisms to explicitly bind runtime values to a quotation execution.
Quotation是被设计为独立的,没有对runtime value的引用。有两种机制显式把runtime value提升为Quotation来执行。

Lifted values

1
2
3
4
def biggerthan(id:Int)=quote{
query[Branch].filter(branch=>branch.branch_id>id)
}
val r=ctx.run(biggerthan(3))

字面上理解没有丝毫问题,但是它报错了:

1
2
3
4
5
6
7
[error] Found the following free variables: id.
[error] Quotations can't reference values outside their scope directly.
[error] In order to bind runtime values to a quotation, please use the method `lift`.
[error] Example: `def byName(n: String) = quote(query[Person].filter(_.name == lift(n)))`
[error]
[error] val r=ctx.run(biggerthan(3))
[error] ^

此时需要用lift函数来提升这个free variables:id:

1
2
3
4
def biggerthan(id:Int)=quote{
query[Branch].filter(branch=>branch.branch_id>lift(id))
}
//SELECT branch_id,name,address,city,state,zip FROM branch WHERE branch_id > ?

Lifted queries

A Traversable instance can be lifted as a Query. There are two main usages for lifted queries.
一个序列实例可以被提升为一个Query

contains

1
2
3
4
5
def find(listOfId:List[Int])=quote{
query[Branch].filter(branch=>liftQueryScalar(listOfId).contains(branch.branch_id))
}
val r=ctx.run(find(List(3,4,5)))
//SELECT branch_id, name, address, city, state, zip FROM branch WHERE branch_id IN (?)

liftQueryScalar的源码片段为:

1
def liftQueryScalar[U[_] <: Traversable[_], T](v: U[T])(implicit e: Encoder[T]): Query[T] = NonQuotedException()

batch action

批量插入数据,而且部分字段是auto increment,并返回插入数据实体的id值。

1
2
3
4
5
6
7
8
9
def batchInsert(list: List[Branch])=quote {
liftQuery(list).foreach(item => query[Branch].insert(item).returning(_.branch_id))
}
val list=List(
Branch(0,"JiangAn5","HuaiAn","LianShui","JS","223400"),
Branch(0,"JiangAn6","HuaiAn","LianShui","JS","223400"),
Branch(0,"JiangAn7","HuaiAn","LianShui","JS","223400"))
val r=ctx.run(batchInsert(list))
println(r) //List(10, 11, 12)
1
def liftQuery[U[_] <: Traversable[_], T](v: U[T]): Query[T] = macro EncodingDslMacro.liftQuery[T]

理解:
Query类,可以类比为SeqList之类的类型构造器,是数据的集合,只不过这类数据可以存放在数据库中,也可以从List实例中构建。

Queries

Scala for-comprehensions provide syntatic sugar to deal with these kind of monadic operations.
quill提供mapflatMap等等高阶函数,所以可以使用for-comprehension推导式语法糖。至于monadmap等函数的关系,可以参考《scala函数式编程》。
以下例子演示了一对多问题。Person可以有多个Contact

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE person(
id INT AUTO_INCREMENT,
name VARCHAR(20),
age INT,
PRIMARY KEY person(id)
);
CREATE TABLE contact(
personId INT,
phone VARCHAR(20),
PRIMARY KEY person(personId,phone),
FOREIGN KEY contact(personId) REFERENCES person(id)
);
1
2
3
4
5
6
7
8
9
case class Contact(personid:Int,phone:String) //并没有使用驼峰命名法
case class Person(id:Int,name:String,age:Int)
val q=quote{
for{
p<-query[Person] if p.id==1
c<-query[Contact] if c.personid==p.id
}yield(p.name,c.phone)
}
val r=ctx.run(q)//List((jiang,1888810687x), (jiang,188881078xx))

Quill normalizes the quotation and translates the monadic joins to applicative joins, generating a database-friendly query that avoids nested queries.
quill避免了嵌套查询,而且产生了数据库友好的查询。

filter

1
2
val r=ctx.run(query[Person].filter(_.age>22))
SELECT x1.id, x1.name, x1.age FROM person x1 WHERE x1.age > 22

字段判空(对引用类型才可以判空):

1
2
val r=ctx.run(query[Person].filter(_.name==null))
SELECT x1.id, x1.name, x1.age FROM person x1 WHERE x1.name IS NULL

map

1
2
3
val r=ctx.run(query[Person].filter(_.age>20).map(_.name))
r.foreach(println)
SELECT x1.name FROM person x1 WHERE x1.age > 20

flatMap(多表join操作)

1
2
3
val r=ctx.run(query[Person].filter(_.age>20).flatMap(p=>query[Contact].filter(c=>c.personid==p.id)))
r.foreach(println)
SELECT c.personid, c.phone FROM person x1, contact c WHERE (x1.age > 20) AND (c.personid = x1.id)

sortBy

1
2
3
val r=ctx.run(query[Person].sortBy(_.age))
r.foreach(println)
SELECT x1.id, x1.name, x1.age FROM person x1 ORDER BY x1.age ASC
1
2
3
val r=ctx.run(query[Person].sortBy(_.age)(Ord.descNullsFirst))
SELECT x1.id, x1.name, x1.age FROM person x1 ORDER BY ISNULL(x1.age) DESC, x1.age DESC
//NULL FIRST
1
2
val r=ctx.run(query[Person].sortBy(_.age)(Ord.descNullsLast))
SELECT x1.id, x1.name, x1.age FROM person x1 ORDER BY x1.age DESC
1
ctx.run(query[Person].sortBy(p=>(p.age,p.name))(Ord(Ord.descNullsLast,Ord.descNullsFirst)))

这是多字段排序.

drop/take

1
2
val r=ctx.run(query[Person].drop(3).take(2))//真方便
SELECT x.id, x.name, x.age FROM person x LIMIT 2 OFFSET 3

groupBy分组

1
2
3
4
5
6
7
val q=quote{
val o=query[Contact].groupBy(_.personid)//Query[(Int,Query[Contact])]
o.map{
case (pid,phoes)=>(pid,phoes.size)
}
}
SELECT x1.personid _1, COUNT(*) _2 FROM contact x1 GROUP BY x1.personid
1
2
3
val r=ctx.run(query[Contact].groupBy(_.personid).map{
case (pid,phones)=>(pid,phones.map(_.phone).max)
})

亦可运用MAXMINAVG等聚合函数。

aggregation聚合函数

1
2
3
4
5
6
7
8
val q=quote{
query[Person].map(_.age)
}
ctx.run(q.max)
ctx.run(q.min)
ctx.run(q.avg)
ctx.run(q.sum)
ctx.run(q.size)

isEmpty/nonEmpty

这个空不空指的是查询的结果空不空,而不是字段空不空。(即存不存在。)

1
2
3
val r=ctx.run(query[Person].filter(_.age>100).isEmpty)
SELECT NOT EXISTS (SELECT x1.* FROM person x1 WHERE x1.age > 100)
1
2
val r=ctx.run(query[Person].filter(p=>query[Contact].filter(c=>c.personid==p.id).isEmpty))
//这个例子是我瞎写的(≧∇≦)

contains

1
2
3
4
5
val q=quote{
query[Person].filter(p=>liftQuery(List(1,2,3)).contains(p.id))
}
SELECT p.id, p.name, p.age FROM person p WHERE p.id IN (?)

distinct

1
ctx.run(query[Person].map(_.age).distinct)

###nested嵌套

1
2
3
val r=ctx.run(query[Person].filter(_.name like "%jiang%").nested.map(_.id))
SELECT x2.id FROM (SELECT x1.id FROM person x1 WHERE x1.name like '%jiang%') x2

joins

1
2
3
4
5
6
val q=quote{
query[Person].join(query[Contact]).on((p,c)=>p.id==c.personid)
}
ctx.run(q)
SELECT p.id, p.name, p.age, c.personid, c.phone FROM person p INNER JOIN contact c ON p.id = c.personid
//默认内连接

以上查询结果的结构类似于:

1
2
3
background log: info: (Person(1,jiang,18),Contact(1,1888810687x))
background log: info: (Person(1,jiang,18),Contact(1,188881068x1))
background log: info: (Person(1,jiang,18),Contact(1,18888106xx1))
1
2
ctx.run(query[Person].leftJoin(query[Contact]).on((p,c)=>p.id==c.personid)).foreach(println)
SELECT p.id, p.name, p.age, c.personid, c.phone FROM person p LEFT JOIN contact c ON p.id = c.personid

以上查询结果的结构类似于:

1
2
3
background log: info: (Person(9,jiang7,25),Some(Contact(9,18888106xx9)))
background log: info: (Person(10,jiang8,26),None)
background log: info: (Person(11,jiang9,27),None)

我们可以清晰的看出内连接外连接的明显区别。

多表连接

1
2
3
4
5
6
7
8
9
10
case class Employer(id:Int,personid:Int,name:String)
val q=quote{
for{
x<-query[Person].
leftJoin(query[Contact])
.on((p,c)=>p.id==c.personid)
.leftJoin(query[Employer])
.on((item,e)=>item._1.id==e.id)
}yield x
}

查询结果为:

1
2
3
background log: info: ((Person(3,jiang1,19),Some(Contact(3,18888106xx3))),Some(Employer(3,2,hang)))
background log: info: ((Person(9,jiang7,25),Some(Contact(9,18888106xx9))),None)
background log: info: ((Person(10,jiang8,26),None),None)

Actions

Database actions are defined using quotations as well. These actions don’t have a collection-like API but rather a custom DSL to express inserts, deletes and updates.
数据库action也是用quotation所定义。这些action不像集合风格的api,而是自定义了DSL来表达插入、删除和更新操作。

insert

1
ctx.run(query[Contact].insert(lift(Contact(3,"188881x688x"))))

It is also possible to insert specific columns.
也可以插入特定说明的列:

1
2
val r=ctx.run(query[Person].insert(_.name->lift("jianghang")).returning(_.id))
INSERT INTO person (name) VALUES (?)

Person模式的id属性是auto_increment的,所以需要指明returning(p=>p.id),以上操作只是插入了nameage字段所以为空。

batch insert

1
val r:List[Int]=ctx.run(liftQuery(list).foreach(p=>query[Person].insert(p).returning(p=>p.id)))

update

可以单独指明更新某些列

1
ctx.run(query[Person].filter(_.name like "hangscer%").update(_.age->lift(100)))

Using columns as part of the update.
也可以使用原先数据作为更新后数据的部分:

1
ctx.run(query[Person].filter(_.id==13).update(p=>p.age->(p.age+1)))

batch update

1
2
3
4
5
6
7
val list=List(
Person(1,"nathan1",118),
Person(2,"nathan2",119),
Person(3,"nathan3",210),
Person(4,"nathan4",211),
Person(5,"nathan5",212))
ctx.run(liftQuery(list).foreach(p=>query[Person].filter(_.id==p.id).update(_.name->p.name,_.age->p.age)))

delete

1
ctx.run(liftQuery(list).foreach(p=>query[Person].filter(_.id==p.id).delete))

SQL-SPECIFIC Operations

Some operations are sql-specific and not provided with the generic quotation mechanism. The sql contexts provide implicit classes for this kind of operation.
一些操作与sql特性有关,通过隐式类来提供这些操作。

like

1
ctx.run(query[Person].filter(_.name like "hangscer%")).foreach(println)

来看一下like的源码:

1
2
3
implicit class Like(s1: String) {
def like(s2: String) = quote(infix"$s1 like $s2".as[Boolean])
}

Transactions

The JdbcContext provides thread-local transaction support.The body of transaction can contain calls to other methods and multiple run calls, since the transaction is propagated through a thread-local.
JdbcContext提供了线程本地的事务支持。事务的主体包含了调用的其它方法和多个run调用。

1
2
3
4
ctx.transaction{
ctx.run(query[Contact].filter(_.personid==9).delete)
ctx.run(query[Person].filter(_.id==10).update(p=>p.age->(p.age+100)))
}

项目配置

sbt依赖为:

1
2
3
4
libraryDependencies ++= Seq(
"mysql" % "mysql-connector-java" % "5.1.38",
"io.getquill" %% "quill-jdbc" % "1.2.1"
)

context definition:

1
lazy val ctx = new MysqlJdbcContext[SnakeCase]("ctx")

application.conf:

1
2
3
4
5
6
7
8
ctx.dataSourceClassName=com.mysql.jdbc.jdbc2.optional.MysqlDataSource
ctx.dataSource.url="jdbc:mysql://host/database"
ctx.dataSource.user=root
ctx.dataSource.password=xxxxxxx
ctx.dataSource.cachePrepStmts=true
ctx.dataSource.prepStmtCacheSize=250
ctx.dataSource.prepStmtCacheSqlLimit=2048
ctx.connectionTimeout=30000