easysql

# 查询

库内置了一系列中缀方法，来构建查询，比如：

import easysql.dsl.*

val s = select (user) from user where user.name === "x" orderBy user.id.asc

然后我们可以使用sql方法，传入数据库方言的枚举，来生成sql语句：

val sql: String = s.sql(DB.MYSQL)

如果不需要使用多种数据库，每次生成sql都要传入一个数据库类型，十分不方便，这个时候，我们可以创建一个given，然后使用toSql方法来生成sql。

import easysql.dsl.*
import easysql.database.*

given DB = DB.MYSQL

val s1 = select (user.id) from user
val sql1 = s1.toSql

val s2 = select (user) from user where user.name === "x"
val sql2 = s2.toSql

# select

使用select方法，传入若干表达式或使用asTable创建的表，来生成select子句：

import easysql.dsl.*

// 后续的select方法调用，会把字段追加在后面
val s = select(user.id).select(user.name)

表结构和表达式都可以传入select中，生成sql时会进行自动展开：

import easysql.dsl.*

val s = select (user, count())

select列表中的字段类型会在子查询等时机保证类型安全，并且在与数据库交互时自动推断结果类型，但要求你的查询字段是可以在编译期确定的。如果查询字段是运行期动态传入的，需要使用dynamicSelect：

import easysql.dsl.*

// 假设此处查询的列是用户传入参数
val list = List("x", "y", "z")

// 把字符串列表转换为字段列表，并传入dynamicSelect中
val s = dynamicSelect(list.map(col)*)

# from

使用中缀方法from，并传入表结构：

import easysql.dsl.*

val s = select (user) from user

如果你只需要构建简单的单表查询，select子句可以省略：

import easysql.dsl.*

val s = from(user)

如果你的表名也是运行期动态确定的，可以使用table方法：

import easysql.dsl.*

val s = select (col("c1")) from table("t1")

from方法也可以传入一个子查询，会在后续子查询部分进行说明。

表可以起别名，我们可以用别名表来处理自连接：

import easysql.dsl.*

val t1 = user as "t1"
val t2 = user as "t2"

val s = select (t1, t2) from t1 join t2 on t1.id === t2.id

如果别名是运行期确定，要使用unsafeAs方法。

# where

使用中缀方法where配合表达式来生成查询条件：

import easysql.dsl.*

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

如果你的查询条件都是使用AND来连接，那么也可以选择使用多个where：

import easysql.dsl.*

val s = select (user) from user

val sWhere = s.where(user.id.in(1, 2, 3)).where(user.name === "x")

某些需要视情况而动态拼接的查询条件，可以在where中传入一个Boolean值或者返回Boolean的函数：

import easysql.dsl.*

val name: Option[String] = ???

val s = select (user) from user
val sWhere = s.where(name.nonEmpty, user.name === name)

# order by

表达式类型中，有asc、desc两个方法，用来配合orderBy创建排序规则：

import easysql.dsl.*

val s = select (user) from user orderBy (user.id.asc, user.name.desc)

# group by与having

使用groupBy做数据分组，having做分组后的筛选：

import easysql.dsl.*

val s = select (user.name, count()) from user groupBy user.name having count() > 1

除了普通的group by之外，还支持几个特殊的group by形式：

  1. rollup：
    select (user.id, user.name, count()) from user groupBy rollup(user.id, user.name)
  2. cube：
    select (user.id, user.name, count()) from user groupBy cube(user.id, user.name)
  3. groupingSets（参数类型支持表达式、表达式元组以及Unit）:
    select (user.id, user.name, count()) from user groupBy groupingSets((user.id, user.name), user.name, ())

上面的各种group by形式可以组合调用。

# distinct

使用distinct做数据去重：

import easysql.dsl.*

val s = select(user.name).from(user).distinct

# limit

使用limit和offset两个方法，来限制结果集：

import easysql.dsl.*

val s = select (user) from user limit 10 offset 100

当只调用其中一个方法的时候，limit的缺省值为1，offset的缺省值为0。

生成sql时会根据方言的不同生成不同的语句

# join

内置了join、leftJoin、rightJoin、innerJoin、fullJoin、crossJoin方法，配合on方法来连接表：

import easysql.dsl.*

val s = select (a, b) from a join b on a.x === b.y

可以像真正的sql一样，使用小括号来限制表连接的顺序：

import easysql.dsl.*

val s = select (a, b, c) from a join (b leftJoin c on b.y === c.z) on a.x === b.y

# 子查询

  1. from中的子查询：

    使用as方法对子查询起别名，并需要对非字段类型表达式起别名。然后使用字段名或者别名作为属性来调用子查询的字段，子查询的字段类型会自动推断。

    import easysql.dsl.*

val sub = select (user.id, count() + sum(user.id) as "c1") from user as "sub"

val s = select (sub.id, sub.c1) from sub

    join中的子查询与from的类似，此处不再赘述。

    可以使用fromLateral、joinLateral等来调用lateral子查询。

  2. 表达式中的子查询：

    只返回一列的子查询可以被用在表达式里，比如：

    import easysql.dsl.*

val sub = select (max(user.id)) from user

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

    支持some、any、exists、notExists、all等子查询谓词：

    import easysql.dsl.*

val sub = select (max(user.id)) from user

val s = select (user) from user where exists(sub)

  3. 标量子查询

    只返回一列一行的查询可以被放入select列表中，这被称作标量子查询，我们需要使用toExpr把子查询转换为Expr：

    import easysql.dsl.*

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

    子查询写在表达式的左侧时，也可以使用toExpr方法进行转换

# for update

使用forUpdate方法来给查询加锁：

import easysql.dsl.*

val s = select(user).from(user).forUpdate

在sqlserver中会在FROM子句后生成WITH (UPDLOCK)。

# 生成sql

上文的链式调用，只是构建出了sql语法树，并未真正生成sql语句，我们需要一个终止操作来生成sql：

import easysql.dsl.*

given DB = DB.MYSQL

val s = select (user) from user orderBy user.id.asc limit 10

val sql: String = s.toSql
val countSql: String = s.toCountSql
val pageSql: String = s.toPageSql(10, 1)

toSql会一比一的生成sql语句。

为了避免性能浪费，toCountSql会拷贝语法树副本，把limit信息和order by信息去掉，并把select列表替换为COUNT(*)，这适用于查询中没有group by的情况：

SELECT COUNT(*) AS `count`
FROM `user`

toPageSql，顾名思义，就是生成分页的查询，第一个参数为每页条数，第二个参数为页码，会依据这两个参数替换语法树副本的信息。

# union查询

支持union、unionAll、except、exceptAll、intersect、intersectAll来将两个查询拼接在一起：

import easysql.dsl.*

val s1 = select (user.name) from user where user.id === 1
val s2 = select (user.name) from user where user.id === 2

val union = s1 union s2

用于拼接的查询，select中的字段数目与字段类型必须一致，否则无法通过编译。

# with查询

可以使用cte函数来创建一个with查询：

import easysql.dsl.*

val q = cte {
    val q1 = select (user.name as "n1") from user where user.id > 1 as "q1"
    val q2 = select (user.name as "n2") from user where user.id > 2 as "q2"

    commonTable(q1, q2).query {
        select (q1.n1, q2.n2) from q1 join q2
    }
}

在cte函数中创建公共查询，并使用commonTable将公共查询引入并创建一个with查询表达式，使用query创建最终的查询即可（mysql和pgsql使用递归查询在调用链添加.recursive）。

cte是一个上下文函数，cte中定义的查询在互相引用时，最终生成的sql会把查询别名当做一个表名称使用，而不是嵌套查询本身。

# values临时表

values临时表最大的用处是被放入union中，我们可以使用Tuple或者List[Tuple]来调用：

import easysql.dsl.*

val union = select (user.id, user.name) from user union (1, "x") union List((2, "y"), (3, "z"))

# 获取生成的语法树

上述的查询类型，均支持使用getAst方法来获取查询生成的语法树，方便后续的自定义处理，处理获取到的语法树需要有一些编译原理相关知识，感兴趣的用户可以自行查看easysql.ast包中的语法树定义。

import easysql.dsl.*

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

val ast: SqlQuery = s.getAst

在处理一些特殊的需求时，可能需要对生成的查询进行深度定制，这种场景下，则可以在上面生成的语法树基础上进行二次处理。

← 集合函数风格查询 →