easysql

# 表达式

在开始编写查询之前，我们最好对sql的表达式有一些了解。

easysql中封装了sql的语法树，sql的表达式拥有共同的父类Expr，而大部分表达式，它们也接收Expr类型的参数，来组合成新的表达式。

因此，easysql拥有强大的抽象能力，通过表达式的组合，我们几乎可以写出任何sql中合法的语句，而无需使用容易出错的原生sql。

# 字段

字段是最基本的表达式，上文中，我们使用asTable创建的元数据对象，它的属性就是字段类型的表达式。

有了字段表达式，我们就可以写这样的查询：

import easysql.dsl.*

val s = select (user.id) from user

select传入使用asTable产生的对象的话，可以在查询中自动展开全部字段：

import easysql.dsl.*

val s = select (user) from user

如果你喜欢，也可以在asTable生成的对象后面添加*，使其更接近原生sql，这并不会改变查询的意义：

import easysql.dsl.*

val s = select (user.*) from user

有些应用里，表是运行期动态创建的，没法对数据进行预先建模，这时候可以使用col方法来创建一个动态字段；

import easysql.dsl.*

val s = select (col("t.id")) from table("t")

col可以携带类型参数，我们可以精确指定字段类型：

import easysql.dsl.*

val idCol = col[Int]("t.id")

val s = select (idCol) from table("t") where idCol > 1

在非常动态的查询中（比如编译期我们对数据的模型一无所知的情况下），也可以不给col添加类型参数，而是导入import easysql.dsl.unsafeOperator。这样就会放弃类型安全，但是会让动态查询更方便，不过在大多数可以配置元数据的场景中，不推荐这样做。

库内置了一个名为*的方法，用来产生一个字段通配符：

import easysql.dsl.*
import easysql.dsl.AllColumn.`*`

val s = select (*) from table("t")

同样主要用于非常动态的查询

# 别名

Expr表达式可以使用as方法，用来起别名：

import easysql.dsl.*

val s = select (user.id as "c1", user.name as "c2") from user

as方法可以推广到后续介绍的任何sql表达式类型，后文便不再赘述。

值得一提的是：as方法使用了一种名为refinement type的手段达到类型安全，as的参数如果为空字符串，则不能通过编译：

import easysql.dsl.*

// 编译错误
val s = select (user.id as "") from user

如果别名需要运行期动态确定，可以使用unsafeAs方法。

# 值

有一些需求中，可能需要把值作为查询结果集的一列，比如：

SELECT 1 AS "col1"

为了实现这个需求，我们需要import easysql.dsl.Expr.given：

import easysql.dsl.*
import easysql.dsl.Expr.given

val s = select (1 as "col1", "a" as "col2")

如果有一些特殊的需求，比如下文会介绍的自定义sql函数，我们也可以使用value()将值包装起来，生成一个值表达式：

import easysql.dsl.*

val expr: Expr[Int] = value(1)

# 逻辑运算与关系运算

除开字段和常量之外，最常见的表达式就是逻辑运算与关系运算构成的表达式。它常被用于sql的where条件中。

支持的符号运算符如下：

运算符名称 对应sql运算符
=== =
<> <>
> >
>= >=
< <
<= <=
&& AND
|| OR
^ XOR

使用这些运算符就如同语言内置的一样：

import easysql.dsl.*

val id = 1
val name = "x"
val s = select (user) from user where user.id > id && user.name === name

二元运算的右侧，不仅可以是值，也可以是其他表达式，比如做join语句的连接条件：

import easysql.dsl.*

val s = select (user, post) from user join post on user.id === post.userId

当然，运算符的左侧也可以是普通的值，easysql使用typeclass（类型类）对运算符进行扩展，这种情况我们需要import easysql.dsl.Expr.given：

import easysql.dsl.*
import easysql.dsl.Expr.given

val s = select (user) from user where 1 < user.id

===与<>在与None比较时，生成的sql为IS NULL与IS NOT NULL。

字符串也可以和Date类型的表达式进行比较。

除开这些符号组成的运算，还支持下面这些非符号运算符。由于这些运算符没有编译器控制的优先级，所以更推荐使用.加上方法名的方式调用，而非中缀的方式：

运算符名称 对应sql运算符
in IN
notIn NOT IN
between BETWEEN
notBetween NOT BETWEEN
like LIKE
notLike NOT LIKE
import easysql.dsl.*

val s = select (user) from user where user.id.between(1, 5) || user.name.in("x", "y")

val s1 = select (user) from user where user.name.like("x%")

这些运算符的抽象能力也一样强大：

import easysql.dsl.*
import easysql.dsl.Expr.given

val s = select (user) from user where user.id.in(1, 2, user.id)

val s1 = select (user) from user where 1.in(user.id)

in运算符为了避免生成的查询语法错误，如果in后面的参数是空列表，那么会把此部分的条件改写为FALSE，notIn空列表时会把此部分改写为TRUE。

此外，还支持一元逻辑运算!：

val s = select (user) from user where !(user.id === 1)

生成的sql为NOT()。

# 数学运算

除开上面的逻辑运算外，还支持+、-、*、/、%五个数学运算：

import easysql.dsl.*

val s = select (user.id * 100) from user where user.id + 1 > 5

普通的值也可以写在表达式左侧。

import easysql.dsl.*

val s = select (100 * user.id) from user where 1 + user.id > 5

# 聚合函数

库内置了几个常用的标准聚合函数：count、countDistinct、max、min、sum、avg：

import easysql.dsl.*

val s = select (count() as "col1", sum(user.id) as "col2") from user

聚合函数也可以成为其他表达式的一部分：

import easysql.dsl.*

val s = select (count() + avg(user.id) * (100 + sum(user.id + 1)) as "col1") from user

有趣的是，上面介绍的用于创建字段通配符的*，也是Expr的子类，所以我们可以像真正的sql一样写：

import easysql.dsl.*
import easysql.dsl.AllColumn.`*`

val s = select (count(*)) from user

您也可以从此处看出easysql表达式组合的威力。

# 普通函数

由于各种数据库的函数差异极大，easysql没有内置普通的函数，但是我们可以通过Expr的子类FuncExpr来定义需要的sql函数。

FuncExpr的定义如下：

case class FuncExpr[T <: SqlDataType](name: String, args: List[Expr[_]]) extends Expr[T]()

其中，类型参数T是sql函数的返回值类型，name是函数名称，args是参数列表。

以mysql的LEFT函数为例，LEFT接收两个参数，第一个参数是一个String类型的表达式，第二个参数是一个Int值，我们可以这样来封装：

import easysql.dsl.*

def left(expr: Expr[String], n: Int) = FuncExpr[String]("LEFT", List(expr, value(n)))

然后这个函数就可以带入进查询了：

import easysql.dsl.*

val s = select (left(user.name, 1) as "left") from user

当然，与其他表达式一样，函数也可以嵌套调用：

import easysql.dsl.*

val s = select (left(left(user.name, 2), 1) as "left") from user

对于聚合函数，也提供了类似的封装结构：

case class AggExpr[T <: SqlDataType](
    name: String,
    args: List[Expr[_]],
    distinct: Boolean,
    attributes: Map[String, Expr[_]],
    orderBy: List[OrderBy]
) extends Expr[T]()

有需要的话，也可以用来封装聚合函数来带入到查询里。

# 窗口函数

内置了三个标准的窗口函数使用的聚合函数：rank, denseRank, rowNumber。

在聚合函数后面调用.over，来创建一个窗口函数，然后通过partitionBy和orderBy来构建一个窗口：

select (rank().over partitionBy user.id orderBy user.name.asc as "over") from user

这会产生如下的查询：

SELECT RANK() OVER (PARTITION BY user.id ORDER BY user.user_name ASC) AS over FROM user

partitionBy()接收若干个表达式类型

orderBy()接收若干个排序列，在表达式类型之后调用.asc或.desc来生成排序规则。

使用窗口函数时需要注意数据库本身是否支持（比如mysql8.0以下版本不支持窗口函数功能）。

我们也可以在窗口函数中使用rowsBetween或rangeBetween筛选行，比如：

select (rank().over partitionBy user.id orderBy user.name.asc rowsBetween(10.preceding, currentRow) as "over") from user

rowsBetween或rangeBetween中的参数可以是unboundedPreceding、unboundedFollowing、currentRow以及Int的扩展方法preceding、following中的任意两项。

# case when

使用caseWhen()方法创建一个条件表达式，由于此表达式用到的关键字case、then、else等都是Scala3的关键字，所以只能使用中缀函数thenIs与elseIs来生成一个条件表达式：

import easysql.dsl.*

val c = caseWhen(user.state === 1 thenIs "正常", user.gender === 0 thenIs "删除") elseIs "其他"

val s = select (c as "state") from user

这会产生下面的查询：

SELECT CASE 
		WHEN user.state = 1 THEN '正常'
		WHEN user.state = 0 THEN '删除'
		ELSE '其他'
	END AS state
FROM user

case when表达式也可以传入聚合函数中：

import easysql.dsl.*

val c = caseWhen(user.state === 1 thenIs user.state) elseIs None

val select = select (count(c) as "count") from user

# cast类型转换

使用cast方法生成一个cast表达式用于数据库类型转换。

第一个参数为待转换的表达式；

第二个参数为String，为想转换的数据类型。

比如：

import easysql.dsl.*

val select = select (cast[String](user.id, "CHAR")) from user

这会产生下面的查询：

SELECT CAST(user.id AS CHAR) FROM user

# json操作

支持->和->>两个json操作符，语义与原生sql一致。

由于->与标准库构建二元组的函数名相同，为了避免编译错误，我们需要导入easysql.dsl.Expr.given。

import easysql.dsl.*
import easysql.dsl.Expr.given

// mysql
val s1 = select (user.id, user.jsonCol ->> "$.x.y") from user where user.jsonCol ->> "$.x.y" === "a"

// pgsql
val s2 = select (user.id, user.jsonCol -> "x" ->> "y") from user where user.jsonCol -> "x" ->> "y" === "a"

如需使用json操作函数的话，可以参考上文sql函数部分的说明进行封装。

json操作会在数据库查询后被映射到String类型。

# 动态表达式

有些需要动态查询的场景中（比如报表平台），查询的表达式可能是由用户动态填写的，如果想对填写的内容进行解析，可能需要开发者有一定的编译原理功底，不过，easysql支持将普通的sql字符串当做表达式的一部分，即使开发者没有这种功底，也可以方便地解析用户创建的动态表达式。

解析一个动态的表达式，可以使用上文字段部分介绍的col方法，col不仅可以处理简单的字段名，也可以处理更复杂的表达式，并将其代入查询中：

import easysql.dsl.*

val dynExpr1 = col[String]("case when user.id = 1 then 'a' when user.id = 2 then 'b' else 'c'")
val dynExpr2 = col[Boolan]("user.id in (1, 2, 3) or user.name is not null")


val s = select (user.id, user.name, dynExpr1 as "col3") from user where dynExpr2 && user.createTime > "2023-01-01"

在使用col创建动态表达式时，会对传入的sql表达式进行解析，如果其中有语法错误，则会抛出easysql.parser.ParseException异常，并说明错误位置。

动态表达式支持子查询谓词和窗口函数在内的大多数sql表达式。

# 日期间隔表达式

我们可以使用interval方法生成日期间隔表达式（目前仅支持生成MySQL和PostgreSQL方言）：

import easysql.dsl.*

// mysql
val s1 = select ("2023-01-01 00:00:00" - interval("1", day))

// pgsql
val s2 = select ("2023-01-01 00:00:00" - interval("1 days"))

← 查询 →