项目介绍

easysql是一个使用Kotlin语言编写的轻量级查询构造库（并提供了一部分orm功能），没有任何第三方库引用。

使用接近于原生sql的dsl构建跨数据库的sql语句，无需任何代码以外的配置，就能构造出复杂的查询，比如：

val select = (db 
        select Post.name 
        from User 
        leftJoin Post on (User.id eq Post.uid) 
        orderBy User.id.asc() 
        limit 10 offset 10)

在使用这种调用方式的时候，强烈建议使用括号将查询表达式包裹起来，这样Kotlin编译器便允许表达式像原生sql一样换行。

支持mysql、postgres sql、oracle、sqlserver、sqlite在内的多种数据库，并额外支持了hive与clickhouse两种OLAP引擎的方言生成，并且封装出了统一的api。（mysql与pgsql为第一优先级支持）

支持Kotlin和Java的调用，并为了适配Java做了大量工作，但为了获得最好的体验，我们推荐使用者至少在数据查询层使用Kotlin。

快速开始

我们首先引入依赖

maven：

<dependency>
  <groupId>com.gitee.wangzhang</groupId>
  <artifactId>easysql</artifactId>
  <version>1.1.10</version>
</dependency>

gradle:

compile	'com.gitee.wangzhang:easysql:1.1.10'

因为本项目使用Kotlin编写，请使用者不要忘记在项目中引入Kotlin 1.5以上版本的依赖。

引入依赖之后，我们创建一个连接池，并把连接池实例放入DBConnection中（此处演示使用druid连接池，您也可以使用其他实现了jdbc中DataSource接口的连接池）：

val druid = DruidDataSource()
druid.apply {
	username = "name"
	password = "password"
	url =
		"jdbc:mysql://localhost:3306/test"
	driverClassName = "com.mysql.jdbc.Driver"
}
val db = DBConnection(druid, DB.MYSQL)

DBConnection类构造的第二个参数为数据库类型，根据实际要生成的方言选择即可。

然后我们编写一个实体类（实体类只支持使用Kotlin编写），在实体类中添加伴生对象，并继承TableSchema类，例如：

data class User(var id: Long? = null, var name: String? = null) {
    companion object : TableSchema("user") {
        val id = column("id")
        val name = column("name")
    }
}

我们在TableSchema的构造里添加表名（如果只用于查询结果接收，可以填写成空字符串）。

给伴生对象的属性赋值成column()类型，在column()函数中添加数据库列名，实体类的属性名需要完整包含伴生对象的属性名。

然后我们就可以编写一个简单的查询：

db.select(User.id).from(User)

上面代码中的User和User.id即是使用我们定义好的伴生对象，伴生对象让我们获得类似原生sql的编写体验，和减少反射的开销。

如果你需要使用Java来调用查询dsl，强烈建议在实体类中进行下面的改造。

首先我们给伴生对象起一个名字叫做Table，并且在伴生对象属性上加上@JvmField注解：

data class User(var id: Long? = null, var name: String? = null, var gender: Int? = null) {
    companion object Table : TableSchema("user") {
        @JvmField
        val id = column("id")
        @JvmField
        val name = column("name")
    }
}

我们看一下改造前后的Java调用对比。

改造前：

db.select(User.Companion.getId())
    .from(User.Companion)
    .where(User.Companion.getName().eq("小黑"));

改造后：

db.select(User.id)
    .from(User.Table)
    .where(User.name.eq("小黑"));

可以看到，在进行实体类改造之后，在Java上的使用体验将会大大提升。

虽然我们介绍了伴生对象的好处，但编写实体类与伴生对象仍是一件令人沮丧的工作，为此内置了小工具来生成相关代码。

如果你使用的是mysql或者pgsql数据库，那么可以自动生成实体类和伴生对象：

db.generateEntity("test", "src/main/kotlin/entity/")

第一个参数填写数据库的名称，第二个参数填写实体类路径。请确保DBConnection中使用的用户有权限访问数据库的系统表。

如果是其他数据库，手动创建好实体类之后，我们这样调用：

convertClassToObject<User>()

即可读取实体类的属性，在控制台打印伴生对象代码，根据实际需要再进行微调即可。

实体增删改查

对于通用的增删改查功能，我们不需要编写前文中的dsl样板代码，直接使用DBConnection类提供的增删改查功能即可。

实体类的设置

在伴生对象中对主键字段填加.primaryKey()调用。

// 此处省略实体类的代码...
companion object : TableSchema("user") {
        val id = column("id").primaryKey()
        val name = column("name")
}

如果是自增主键，再添加.incr()调用（一张表支持多个主键但只支持一个自增主键）：

// 此处省略实体类的代码...
companion object : TableSchema("user") {
        val id = column("id").primaryKey().incr()
        val name = column("name")
}

如果并非是自增主键，我们又需要指定一个生成策略，可以调用.autoGenerateKey()，然后在.generateFunction { }这个高阶函数中设置主键生成策略（此处使用JDK自带的UUID展示，实际使用时可以替换成自己想要的策略）：

// 此处省略实体类的代码...
companion object : TableSchema("user") {
        val id = column("id").primaryKey()
    				.autoGenerateKey().generateFunction { UUID.randomUUID().toString() }
        val name = column("name")
}

设置好之后，对于通用的增删改查操作，我们便可以使用简单的api来操作，不需要使用查询dsl。

下面还有一些注意事项：

实体类必须对每个字段设置默认值，或提供一个无参构造函数。

如果未使用.primaryKey()来标识主键字段，为了避免产生无差别的删除或修改操作，调用时会产生一个异常。

使用.incr()标识的字段，会在插入数据成功时将数据库生成的主键替换到实体对象中，因此这并非线程安全的，需要避免在多线程环境使用同一个实体对象插入数据。

使用.autoGenerateKey()标识的字段，会在插入数据时调用.generateFunction{}中定义的函数，并替换到实体对象中，注意事项同上。

插入

使用insert方法插入数据：

db.insert(entity)

insert也可以做批量写入，传入一个实体的List即可。

新增修改删除操作的返回值都为受影响行数，后续便不再赘述。

更新

使用实体对象，按主键字段修改其他字段的值：

db.update(entity)

主键之外，值为null的字段将在生成sql语句时被忽略掉，如果需要更精细的控制，请参考后文的查询构造器部分。

插入或更新

在实际开发中我们会遇到按主键在数据库中是否存在而决定采用插入或者更新数据的情况，使用save方法即可：

db.save(entity)

save方法会根据DBConnection中设置的数据库类型，生成不同的sql语句，以上文的User为例。

mysql：

INSERT INTO user (id, name) VALUES (1, '小黑') ON DUPLICATE KEY UPDATE name = VALUES(name)

pgsql：

INSERT INTO user (id, name) VALUES (1, '小黑') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name

sqlite：

INSERT OR REPLACE INTO user (id, name) VALUES (1, '小黑')

oracle：

MERGE INTO user t1 USING (SELECT 1 AS id, '小黑' AS name FROM dual) t2
ON (t1.id = t2.id)
WHEN MATCHED THEN UPDATE SET t1.name = t2.name
WHEN NOT MATCHED THEN INSERT (t1.id, t1.name) VALUES (1, '小黑')

sqlserver中生成的sql语句与oracle类似。

删除

根据实体对象中主键字段的值删除数据：

db.delete(entity)

我们也可以不依赖实体对象，通过泛型即可实现同样的操作：

db.delete<Entity>(1)

因为Java类型擦除的原因，携带泛型的功能只支持Kotlin（依赖Kotlin的reified功能）

如果表是联合主键，我们推荐使用第一种方式，但也可以在第二种方式的参数里传入一个Map<String, Any>。

查询

DBConnection类提供了一些通用的查询方法，来应对简单且通用的查询操作（更复杂的查询请阅读后文查询构造器部分）。

此部分api也依赖Kotlin的reified功能，所以不支持Java调用，Java请使用后文的查询构造器。

按主键查询一条数据，可以使用find：

val entity = db.find<Entity>(1)

此方法会返回一个Entity? 可空类型，需要注意空值处理。

查询整表数据使用queryAll：

val entities = db.queryAll<Entity>()

会返回一个实体类型的List，如果没有查询到数据，会返回一个空的List。

查询总数使用fetchCount方法：

val count = db.fetchCount<Entity>()

返回结果为Long类型。

当然DBConnection还提供了分页查询等方法，我们会在查询构造器中做详细介绍。

查询构造器

上文演示了通用的数据库操作方式，但实际的应用开发中，我们不可能只使用到如此简单的功能，所以我们引入查询构造器。

查询构造器作为核心功能，在使用方式简洁的同时，还保留着强大的抽象能力，方便开发者来构建复杂的sql语句。

比如我们有一个比较复杂的查询：

val select = db
    .select(case(User.gender eq 1 then "男") elseIs "女" alias "gender")
    .select(sum(User.id) * 100 alias "sum_id")
    .from(User)
    .groupBy(case(User.gender eq 1 then "男") elseIs "女")

这会生成下面的sql语句：

SELECT 
    CASE WHEN user.gender = 1 THEN '男' ELSE '女' END AS gender, 
    SUM(user.id) * 100 AS sum_id
FROM 
    user 
GROUP BY 
    CASE WHEN user.gender = 1 THEN '男' ELSE '女' END

能实现这些功能的核心，便是库内置的sql抽象语法树，并在此基础上封装的查询dsl和表达式：

在此基础上，我们可以使用代码来构建复杂的sql语句，并可以获得ide的智能提示，和类型安全，动态构建查询等使用sql字符串得不到的好处。

表达式和运算符

我们首先来介绍库提供的表达式和各种运算符。

表达式拥有共同的父类Query，而大多数表达式的参数中也接收Query类型，因此，表达式之间可以互相嵌套，这便是抽象能力的体现。

这部分可能会有些枯燥，但希望使用者能耐心看完。

表达式类型中如果代入进了字符串，会在生成sql时自动转义单引号，以此来防止sql注入。

字段

字段是最基本的表达式，比如上文伴生对象的属性User.id就是一个字段类型，可以代入到其他表达式或查询语句中：

db.select(User.id + 1).from(User)

假如你的项目中，表名字段名是动态的，并不能构建出实体类，那可以使用另一种方式生成字段表达式：

column("c1")

将这种字段代入查询：

db.select(column("c1") + 1).from(User)

如果column()中的字符串包含.，那么.左侧会被当做表名，右侧会被当做字段名；如果包含*，那么会产生一个sql通配符。

在Java中需要使用static import dsl.Clause.*来引入。

表达式别名

表达式类型可以使用中缀函数alias来起别名，相当于数据库的AS，我们在此以字段类型为例，后文的其他表达式类型也支持这个功能：

db.select(User.id alias "c1").from(User)

这里的alias，Java通过.alias()调用。

为了方便使用，对String类也添加了alias扩展函数：

db.select("column_name" alias "column_alias")

这等价于：

db.select(column("column_name") alias "column_alias")

Java中由于不支持扩展函数，可以使用后者。

常量

在某些需求中，可能会将某一种常量来作为查询结果集的一列，比如：

SELECT 1 AS c1

我们可以使用const()来生成常量类型的表达式：

db.select(const(1) alias "c1")

Java调用时，使用value()而并非是const()，并且需要使用static import dsl.Clause.*来引入。

聚合函数

内置了count、countDistinct、sum、avg、max、min这些标准的聚合函数，比如：

db.select(count() alias "col1", sum(User.id) alias "col2").from(User)

在Java中需要使用static import dsl.StandardFunction.*来引入。

如果我们需要扩展其他的聚合函数，可以通过表达式Query的子类QueryAggFunction来构造，QueryAggFunction的定义如下：

data class QueryAggFunction(
    val name: String, // 函数名
    val args: List<Query>, // 参数列表
    val distinct: Boolean = false, // 是否携带DISTINCT
    val attributes: Map<String, Query>? = null, // 其他属性
    val orderBy: List<OrderBy> = listOf(), // 排序列表
    override var alias: String? = null // 别名
) : Query()

比如我们需要构建一个mysql的GROUP_CONCAT函数：

fun groupConcat(
    query: Query,
    separator: String,
    distinct: Boolean = false,
    vararg orderBy: OrderBy
): Query = QueryAggFunction("GROUP_CONCAT", listOf(query), distinct, mapOf("SEPARATOR" to const(separator)), 	orderBy.toList())

然后这个函数便可以在查询dsl中使用了。

逻辑运算符

库内置了eq(=)、ne(!=)、gt(>)、ge(>=)、lt(<)、le(<=)、and(AND)、or(OR)

等逻辑运算符，我们可放入where条件中：

db.select().from(User).where(User.id eq 1)

因为这些运算符只是普通函数，并不能指定结合性，所以在较复杂的条件中，我们需要使用小括号来控制：

db.select().from(User).where((User.name eq "小黑") and ((User.id gt 1) or (User.gender eq 1)))

这会产生下面的查询：

SELECT * 
FROM user 
WHERE user.user_name = '小黑' AND (user.id > 1 OR user.gender = 1)

当然，绝大多数情况我们都是使用and来拼接的条件组，就不需要如此小心翼翼了，直接使用多个where就可以：

db.select()
    .from(User)
    .where(User.name eq "小黑")
    .where(User.id gt 1)

便可以在生成sql语句时自动添加AND。

除了上文的逻辑运算符外，还支持inList(IN)、notInList(NOT IN)、like(LIKE)、notLike(NOT LIKE)、isNull(IS NULL)、isNotNull(IS NOT NULL)、between(BETWEEN)、notBetween(NOT BETWEEN)：

db.select()
    .from(User)
    .where(User.gender inList listOf(1, 2))
    .where(User.id.between(1, 10))
    .where(User.name.isNotNull())
    .where(User.name like "%xxx%")

这些运算符不仅可以代入数值，字符串等常量，Query表达式类型的子类也可以代入其中，比如我们需要做一个需求，查询当前的时间在表的两个字段范围内的数据，我们可以这样写：

db.select().from(User).where(const(Date()).between(User.time1, User.time2))

这已经体现出运算符的抽象性了，但是，通过内置的运算符扩展函数，我们还可以再简洁一些：

db.select().from(User).where(Date().between(User.time1, User.time2))

比如我们还可以通过内置的运算符扩展函数来生成一个1 = 1的恒等条件：

db.select().from(User).where(1 eq 1)

注：因为Java不支持扩展函数，只能使用把常量用value()函数包裹起来的写法。

以上的运算符，左侧和右侧都支持Number，String，Boolean，Date，List以及Query表达式类型与子查询。

数学运算符

得益于Kotlin的运算符重载，我们提供了+(plus)、-(minus)、*(times)、/(div)、%(rem)五个数学运算符（括号中是相对应的方法名称，Java可以使用方法名调用），比如：

db.select(count() + 1).from(User)

如果你使用的是Java，便不推荐使用复杂的数学运算表达式，因为把重载的运算符翻译成对应的方法之后，便会失去编译器对于结合性的控制，这可能会产生不符合直觉的sql语句。

比如下面的表达式：

db.select(User.id.plus(1).times(2)).from(User);

这看起来会生成：

SELECT user.id + 1 * 2 FROM user

但实际上会生成：

SELECT (user.id + 1) * 2 FROM user

如果想生成前面的语句，我们需要写成这样：

db.select(User.id.plus(value(1).times(2))).from(User)

这无疑增加了编写代码的难度，而这并不是能在库层面解决的问题，这与Java操作BigDecimal时遇到的问题如出一辙。

另外还有一些基于扩展函数或者具体化泛型的功能，Java也无法使用，所以在开篇的项目介绍中，我们推荐至少在数据查询层使用Kotlin。

case表达式

使用case()方法和中缀函数then与elseIs来生成一个case表达式：

val case = case(User.gender eq 1 then "男", User.gender eq 2 then "女") elseIs "其他"

db.select(case alias "gender").from(User)

这会产生下面的查询：

SELECT CASE 
		WHEN user.gender = 1 THEN '男'
		WHEN user.gender = 2 THEN '女'
		ELSE '其他'
	END AS gender
FROM user

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

val case = case(User.gender eq 1 then User.gender) elseIs null

val select = db
	.select(count(case) alias "male_count")
	.from(User)

这会产生下面的查询：

SELECT COUNT(CASE 
		WHEN user.gender = 1 THEN user.gender
		ELSE NULL
	END) AS male_count
FROM user

在Java中需要使用static import dsl.Clause.*来引入，并把函数名case替换成caseWhen。

窗口函数

使用聚合函数或rank()、denseRank()、rowNumber()三个窗口专用函数，后面调用.over()，来创建一个窗口函数，然后通过partitionBy()和orderBy()来构建一个窗口：

db.select(rank().over().partitionBy(User.id).orderBy(User.name.asc()) alias "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以下版本不支持窗口函数功能）。

普通函数

除了上文提到的标准聚合函数和窗口函数之外，还支持一些常用的函数，并做了不同数据库的适配。

虽然我们提供了这个功能，但是使用数据库的函数会导致sql的可移植性变差，并且不利于查询优化，所以并不建议使用此功能。

concat和concatWs：

用于字符串拼接。

concat是一个可变长参数的函数，接收的参数为表达式类型。

concatWs的第一个参数为分隔符的字符串，其他同concat。

val select = db
	.select(concat(User.id, const(","), User.name))
	.select(concatWs(",", User.id, User.name))
	.from(User)

在oracle、sqlite中生成以||拼接的二元运算表达式。

ifNull：

用于表达式为空时赋予默认值。

第一个参数表达式类型，为待检测的表达式；

第二个参数为表达式类型或者数值、字符串等类型，代表前面的表达式为空时选择的值。

比如有些时候我们需要检测sum返回的结果是否是空值，可以使用ifNull函数：

val select = db
	.select(ifNull(sum(User.age), 0))
	.from(User)

每种数据库转换出的函数均有不同。

除开内置的函数，我们还可以使用表达式Query的子类QueryExprFunction来构造一个普通函数，QueryExprFunction的定义如下：

data class QueryExprFunction(val name: String, val args: List<Query>, override var alias: String? = null) : Query()

具体的使用方式可以参考聚合函数部分。

cast表达式

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

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

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

比如：

val select = db.select(cast(User.id, "CHAR")).from(User)

这会产生下面的查询：

SELECT CAST(user.id AS CHAR) FROM user

查询语句

在介绍完表达式和运算符之后，我们便可以开始着重来讲sql的核心：select语句的构建。

库内置了一系列方法来支持SELECT语句的编写，比如：

val select = db.select().from(User).where(User.name eq "小黑")

我们甚至可以通过中缀函数来获得类似原生sql的编写体验：

val select = db from User where (User.name eq "小黑")

如果我们需要对表达式起别名或者查询比较复杂的情况，仍推荐使用非中缀的链式调用表示法。

然后我们就可以用query()发送到数据库查询：

val result = select.query<User>()

除了使用DBConnectionn类提供的select或from方法来创建查询之外，我们也可以通过手动创建一个Select实例来创建查询：

val select = Select(DB.MYSQL).select().from(User) // Select的构造中可以传入数据库类型的枚举，默认为mysql

当然，这样我们就失去了数据库的连接信息，因此不能调用query()方法来获得查询结果，但我们可以使用sql()方法来获取生成的sql语句：

val sql = select.sql()

这样我们也可单纯作为查询dsl，配合其他orm框架来使用，以此来把对业务的侵入性保持在最低。

当然，不管是发送到数据库查询还是生成sql语句，我们提供的方法都并非如此单一，后文会详细说明。

select子句

select()方法中支持传入若干个前文介绍的表达式类型，也支持传入若干个字符串（字段名）：

db.select(User.id, User.name)

或：

db.select("user.id", "user.name")

如果select()中不传参数，即是生成SELECT *。

链式调用多个select，会在生成sql时依次拼接进sql语句。

如果使用中缀调用，由于Kotlin限制中缀函数的参数有且只有一个，所以多个列需要使用List包裹：

db select listOf(User.id, User.name)

此处有一个小遗憾，目前Kotlin还未支持元组字面量，所以需要一个listOf函数，暂时没有办法更简洁，如果Kotlin后续支持元组及其字面量，我们会第一时间加入支持。

from子句

from()方法支持传入一个字符串表名，或者前文介绍的继承了TableSchema的伴生对象名：

db.select().from(User)

db.select().from("table")

不支持from多张表，如果有此类需求，请使用join功能。

表别名

alias()方法给表起别名：

db.select().from(User).alias("t1")

如果别名需要加入列名，alias()的表名参数后面继续添加列名即可（这种别名方式对后文介绍的values临时表非常有用）：

db.select().from(User).alias("table", "col1", "col2")

这会产生如下的查询：

SELECT * FROM user t1(col1, col2)

alias()调用之前，必须保证调用了from()，否则运行时会抛出异常。

如果需要对表达式起别名，会和表的alias冲突，此时不推荐使用中缀函数的方式构建查询。

where子句

使用where()配合各种前面介绍的运算符和表达式，生成where条件：

db.select().from(User).where(User.id eq 1).where(User.gender ne 1)

多个where()会使用AND来拼接条件，如果需要使用OR和XOR，请参考前文的运算符部分。

有些时候，我们需要根据一些条件动态拼接where条件，我们可以这样调用：

db.select().from(User).where(!arg.isNullOrEmpty(), User.name eq arg)

where()的第一个参数接收一个Boolean表达式，只有表达式返回的值是true的时候，条件才会被拼接到sql中。

如果判断条件比较复杂，第一个参数也可以传入一个返回Boolean类型的lambda表达式。

不止是select语句，后文的update和delete语句也支持这些where()的调用方式，以后便不再赘述。

order by子句

order by有两种方式：

1. 使用orderBy()方法传入表达式类型的.asc()或者.desc()方法来生成的排序规则：

       db.select().from(User).orderBy(User.id.asc(), User.name.desc())

2. 使用orderByAsc()或orderByDesc()，传入表达式类型或者列名字符串：

       db.select().from(User).orderByAsc(User.id).orderByDesc("name")

两种方式可以组合调用。

group by和having子句

使用groupBy()来聚合数据，having()来做聚合后的筛选：

db.select(User.gender, count()).from(User).groupBy(User.gender).having(count() gt 1)

groupBy()接收若干个表达式类型，having()的使用方式与where()相似。

distinct子句

在调用链中添加distinct()即可，会对查出的列进行去重。

db.select(User.name).distinct().from(User)

limit子句

使用limit(count, offset)来做数据条数筛选（注意此处与mysql的参数顺序不一样），如：

db.select().from(User).limit(1, 100)

limit中第二个参数也可以不填，即为默认值0：

db.select().from(User).limit(1)

使用中缀函数调用时，由于Kotlin限制中缀函数的参数有且只能有一个，所以我们可以使用limit和offset两个函数组合的方式：

db select allColumn() from User limit 1 offset 10

也可以不调用offset函数，即为默认值0。

limit语句并不是sql标准用法，因此每个数据库厂商采用的语法都有差异，生成sql时会根据数据源的数据库类型进行方言适配。

oracle需要版本在12c以上，sqlserver需要版本在2012以上。低于此版本，需要使用者自行处理ROW NUMBER。

join子句

提供：join()、innerJoin()、leftJoin()、rightJoin()、crossJoin()、fullJoin()几种不同的join方法。

上述方法配合on()方法来做表连接：

db.select()
	.from(User)
	.leftJoin(Post)
	.on(User.id eq Post.uid)

对于表的alias()方法，会给最近的一个表起别名，比如：

db.select().from(User)
           .alias("u")
           .leftJoin(Post)
           .alias("p")
           .on(column("u.id") eq column("p.uid"))

会生成如下的sql语句：

SELECT *
FROM user u
	LEFT JOIN post p ON u.id = p.uid

子查询

如果需要使用子查询，我们另外声明一个Select对象传入调用链即可：

db.select().from(Select().from(User)).alias("t")

join中的子查询：

db.select()
	.from("t1")
	.leftJoin(Select().from("t2").limit(10))
	.alias("t2")
	.on(column("t1.id") eq column("t2.id"))

操作符中的子查询：

db.select()
	.from(User)
	.where(User.id inList Select().select(User.id).from(User).limit(10))

如果select的列表中、或者操作符的左侧需要使用子查询，那么需要在select调用链最后调用toExpr()方法，将查询链转换为表达式类型，比如：

val subQuery = db
	.select()
	.from(User)
	.where(Select().select(max(User.id)).from(User).toExpr() gt User.id)

当然，为了方便使用，我们也添加了一系列扩展函数，因此toExpr绝大多数情况可以省略，当遇到编译不通过的情况下或者使用Java调用的情况下，再添加toExpr即可。

支持EXISTS、NOT EXISTS、ANY、ALL、SOME这五个子查询谓词，使用对应的全局函数把查询调用链代入即可：

db.select()
   .from(User)
   .where(exists(Select().select(max(User.id)).from(User)))

当然子查询谓词依然是表达式类型，所以可以使用操作符函数来计算：

val subQuery = db
   .select()
   .from(User)
   .where(User.id lt any(Select().select(max(User.id)).from(User)))

如果需要使用LATERAL子查询，把from()改为fromLateral()即可（join的调用方式类似，需要注意使用的数据库版本是否支持LATERAL关键字）：

val subQuery = db
   .select()
   .fromLateral(Select().from(User)).alias("t")

需要注意的是，如果子查询内部需要使用非标准函数等各种数据库存在较大差异的功能，子查询的Select要在构造里使用枚举类DB传入实际的数据库类型。

查询加锁

使用forUpdate()方法将查询加锁：

db.select().from(User).forUpdate()

不支持OLAP引擎和sqlite；在sqlserver中会在FROM子句后生成WITH (UPDLOCK)；其他数据库会在sql语句末尾生成FOR UPDATE。

获取查询结果或sql

前面通过链式调用构建的sql语句，其实只是构建出了一个查询语句，还并未发送到数据库查询，我们还需要一个真正的链式调用终止操作，来将查询转换为实际的结果。

下面就介绍库提供的查询操作（下文出现的所有带泛型参数的方法，由于类型擦除的原因，都不支持Java调用，Java需要在方法的第一个参数中传入接收类的class信息）：

查询结果集

使用query和queryMap方法查询结果集。

query()方法会返回一个传入泛型类型的List。

queryMap()方法会返回一个Map类型的List。其中Map的key为列名，value为具体的值。

如果查询结果集为空，那么List的size为0。

select.query<User>()

select.queryMap()

泛型中除了传入实体类类型之外，还有几个保留类型：Int, Long, Float, Double, String, Date, BigDecimal来接收单列结果；Pair来接收双列结果；Triple来接收三列结果。

并非所有查询都值得我们为此创建一个接收实体类，但我们又想在使用结果集时获得比queryMap更高的安全性，对于那些不重要的查询，我们可以使用queryAnonymousObjects来映射到匿名对象里：

select.queryAnonymousObjects(object {
   val id = 0L
   val name = ""
})

此功能基于Kotlin的类型推断，而Java并没有在外部操作匿名内部类属性的能力，所以理所当然的，Java并不支持此功能。

查询单条

使用find和findMap方法查询单条结果，返回的类型为可空类型，需要注意空值判断。

select.find<User>()

select.findMap()

获取总数

使用fetchCount方法获取一个Long类型的总数结果。

select.fetchCount()

fetchCount会拷贝一个查询的副本，把limit和order by的信息清空，并把select后面的内容替换成COUNT(*)。

普通分页查询

使用page和pageMap方法来基于页码和每页结果集数量来分页。

第一个参数为一页的数据量；

第二个参数为页数；

第三个参数可选，为是否需要查询总数，默认为true。如果为true，会自动调用前文的fetchCount方法。

众所周知，数据量较大的时候，count操作十分耗时，所以非必要的情况（比如第一页），第三个参数请尽量设置为false。

select.page<User>(10, 1)

select.pageMap(10, 1)

返回的数据结果是一个Page类，类的定义如下：

data class Page<T>(
    val totalPage: Long = 0,
    val totalCount: Long = 0,
    val data: List<T> = listOf()
)

totalPage为总页数；totalCount为符合条件的总条数；data便是当前页的结果集。

第三个参数设置为false时，返回的前两项都为0。

游标分页查询

上文的普通分页方式，除开查询count的开销外，还会有随着偏移量（offset）的增加，数据库的开销会越来越大的问题，如果表的数据量非常大，翻页到最后，可能会导致数据库崩溃。

所以提供了另一种分页方式，依赖一个不重复的排序字段（如果这个字段有重复值，翻页可能会漏数据，并且强烈建议对这个字段添加索引）。

此方式的好处是，对排序字段添加索引后，即使翻到最后一页，消耗的时间和占用的数据库资源也与第一页差距不大，使用方式如下：

首页：

select.firstPage<User>(User.id.asc(), 10)

泛型中传入接收类的类型，第一个参数传入一个字段的排序方式，也就是翻页依赖的不重复字段，第二个参数是一页的数据量。

尾页：

select.lastPage<User>(User.id.asc(), 10)

尾页的参数顺序与首页相同。

下一页：

select.nextPage<User>(User.id.asc(), 10, 10)

与首页尾页不同的是，在排序方式和数据量中间，增加了一个参数，这个参数的使用方式是：

假如我们查询出了一页数据，此时需要向后翻页，那么需要在这个参数中传入之前返回的列表中，最后一条数据的排序列的值。

比如我们使用id字段排序，第一页查出的id分别为1到10，那下一页就需要在第二个参数传入之前的最后一个id，即为10。

上一页：

select.previousPage<User>(User.id.asc(), 10, 10)

与下一页不同的是，第二个参数应该传入之前列表的第一条数据中排序列的值。

获取sql

如果我们不想使用数据库查询，而是只希望返回sql的字符串，配合其他查询库使用，可以使用下面的方式：

select.sql()

select.fetchCountSql()

select.pageSql(10, 1)

其他查询语句

下面会介绍union、values等其他类型的查询语句，这些查询语句在查询结果时只支持使用query或queryMap获取结果集，并不支持其他的查询接收方式，如果需要使用，请将此类查询放入子查询中。

union查询

使用union或unionAll配合新声明的Select对象来构建union查询：

db select User.name from User where (User.id eq 1) union
		(Select() select User.name from User where (User.id eq 2)) unionAll
		(Select() select User.name from User where (User.id eq 3))

此外还有差集方法except和交集方法interSect（mysql数据库目前还未支持此语法）。

with查询

使用with方法创建一个with查询（此功能只支持mysql（8.0以上）、pgsql、oracle和sqlserver）：

db.with().add("query1", Select().select("id", "name").from("user"), "id", "name")
            .select {
                select().from("query1")
            }

add()方法为添加一个查询到with语句中，第一个参数为查询名，第二个参数是一个Select对象或一个union查询，后续参数为可变参数，是with查询的列名。

select()方法参数是一个带接受者的lambda表达式，使用链式调用构建最终需要的查询。

如果是mysql或者pgsql，想使用递归查询，在with()后面调用.recursive()方法即可。

values查询

使用values方法创建一个values查询，来作为一张临时表：

db.values(listOf(1, "小黑"), listOf(2, "小白"))

这会生成下面的sql：

VALUES (1, '小黑'), (2, '小白') --mysql数据库会在每一行数据前面添加ROW

当然values也可以代入子查询或者使用union：

db.select()
   .from(values(listOf(1, "小黑"), listOf(2, "小白")) union Select().select(User.id, User.name).from(User))
   .alias("user", "id", "name")

原生sql查询

查询构造器已经十分强大，但可能还是不完全满足使用者的需求，为此提供了一个使用原生sql查询的方式：

val select = db.nativeSelect("SELECT * FROM user WHERE id = ? AND name = ?", 1, "小黑")

如果需要加查询参数，只需要在sql语句中添加问号，并且在后面的条件中传入需要的参数列表，如果是字符串等类型，会自动生成sql中的单引号。（参数列表合法的类型有Number、String、Date、List、Boolean以及null和Query表达式类型）

原生sql查询与上面的查询类似，只支持query和queryMap获取结果集。

插入语句

前文的实体增删改查部分已经介绍了实体类的新增功能，如果没有实体类，我们可以用下面的方式：

val user = mutableMapOf<String, Any?>("name" to "小黑", "gender" to 1)
val insert = db insert "user" value user
insert.exec()

insert方法传入一个字符串类型的表名。

value方法传入一个MutableMap<String, Any?>（批量写入则把value方法改为values方法，并传递一个List<MutableMap<String, Any?>>）。

value和values中的每一个数据都支持数字、字符串等常量或者是字段、函数等表达式类型。

如果表有自增主键，写入后会在原来的map中添加一个名为“incrKey”的键，其值为数据库生成的自增主键值。

非查询语句都可以使用exec()方法提交到数据库执行，并返回受影响行数，后文便不再赘述。

如果需要使用insert select来复制表数据，可以使用下面的方式：

val insert = db
            .insert("user")
            .columns("name", "gender")
            .select {
                select(User.name, User.gender).from(User)
            }

此方式如果想接受数据库生成的主键，不能使用exec()方法，而是使用execReturnKey()方法，此方法会返回一个Long类型的List，需要手动接收。

更新语句

更新语句的调用方式如下：

val update = db update User set listOf(User.name to "小白", User.gender to 2) where (User.id eq 1)
update.exec()

列修改的值支持数字、字符串等常量或者是字段、函数等表达式类型。

有时我们需要对列进行自增自减，比如增减点赞数，easysql对此场景提供了两个实用的函数incr和decr：

val incr = db update User incr User.id
incr.exec()

生成的sql如下：

UPDATE user
SET id = user.id + 1

也可手动指定增减的大小：

val incr = db.update(User).incr(User.id, 2)
incr.exec()

我们也可以不使用incr方法，而是使用这样的方式：

db update User set (User.id to User.id + 1)

这体现了查询构造器强大的抽象能力。

删除语句

删除语句的调用方式如下：

val delete = db delete User where (User.id eq 1)
delete.exec()

清空表

清空表调用方式如下：

val truncate = db truncate User
truncate.exec()

创建表

使用dsl风格创建表：

val createTable = db createTable "user_copy" columns {
	add(column("id").dataType("INT(10)").primaryKey())
	add(column("user_name").dataType("VARCHAR(50)").default("").notNull())
	add(column("gender").dataType("TINYINT(1)").default(1))
}
createTable.exec()

primaryKey表示主键，notNull表示非空，default设置默认值。

暂不支持自增主键。

创建索引

普通索引：

val createIndex = db createIndex "index_name" on "table_name" column "col"
createIndex.exec()

唯一索引：

val createIndex = db createUniqueIndex "index_name" on "table_name" column "col"
createIndex.exec()

删除表和索引

val dropTable = db dropTable "table_name"

val dropIndex = db dropIndex "index_name" on "table_name"

数据库事务

使用DBConnection的transaction函数创建一个事务：

val transaction = db.transaction(TRANSACTION_READ_UNCOMMITTED) { 
	update(User).set(User.name to "xxx").where(User.id eq 1).exec()

	val update = this update User set (User.name to "xxx") where (User.id eq 2)
	update.exec()

	throw SQLException()
}

1. transaction函数第一个参数为隔离级别，可以传入一个java.sql.Connection中定义的隔离级别，也可以省略，使用数据库默认的级别。
2. 第二个参数为一个lambda表达式，函数体中如果出现异常，则会直接回滚事务，也可以手动throw出异常。
3. 因为第二个参数的函数是一个带接受者的lambda，所以如果不使用中缀函数方式查询，可以省略掉this。事务中只能使用接受者this来构造和调用查询，不要使用DBConnection来调用查询。

与spring boot整合

在与spring boot协同开发时，我们可以使用如下的方式：

// 新建一个配置类，添加@Configuration注解
@Configuration
class Conf { 
	// 使用@Value注解读取spring boot配置
	@Value("此处填写配置文件路径")
	private lateinit var name: String

    @Value("此处填写配置文件路径")
	private lateinit var password: String

    @Value("此处填写配置文件路径")
	private lateinit var url: String
	
	// 使用@Bean注解返回DBConnection对象
	@Bean
	fun getDataSource(): DBConnection {
		val druid = DruidDataSource()
		druid.apply {
			// 填写连接池配置
		}

		return DBConnection(druid, DB.MYSQL)
	}
}

即可在需要调用数据库连接的文件里使用@Autowired注入：

@Autowired
private lateinit var db: DBConnection

额外说明（必读）

针对库的特性做一些额外的说明：

1. 链式调用的好处：

   库的背后是基于sql语法树，所以跟传统的xml字符串构建sql的orm比，链式调用的时候，我们可以实现一些特别的功能。

   比如我们的select列表是通过参数传进来的集合，我们就可以在遍历这个集合时循环调用select()方法：

   val query = db.select().from("table1")
   val list = listOf("col1", "col2", "col3")
   list.foreach {
       query.select(it)
   }

   还有我们可以把一个sql的公用部分用方法封装起来，其他的查询调用这个公共部分，再添加一些新的东西：

   fun getQuery(): Select {
       return db.select().from("table1")
   }
   
   val query1 = getQuery().select("col1")
   
   val query2 = getQuery().select("col2").limit(10)

   假如有一个需求，我们需要拼接类似WHERE col1 = 1 AND (col2 = 1 OR col2 = 2 OR col2 = 3)的where条件，其中col2的OR条件是参数传入的数组，数组的size是动态的，我们 可以这样写：

   val where = column("col1") eq 1
   val argList = listOf(1, 2, 3) // 此处可能是传入的参数
   val orWhere = argList
               .map { column("col2") eq it }
               .reduce { acc, orCondition -> acc or orCondition }
   val select = db.select().from("t1").where(where and orWhere)

   链式调用最大的好处就是我们可以获得类似于宏(macro)的能力，这能帮助我们动态构建sql语句，其他的用法，我们可以发挥想象力。

2. 如果需要接收一个join查询的结果，比如接收帖子和用户的复合实体，我们可以这样定义一个实体类：

   data class UserPost(var id: Long? = null, var name: String? = null, var userName: String? = null) {
      companion object : TableSchema("") {
        val id = Post.uid
        val name = Post.name
        val userName = column("user_name")
      }
   }

   TableSchema中的表名不填，如果查询中列的别名和某个单表的字段名一致，直接引用那个单表伴生对象的属性，如果与别名不一致，直接用column()定义即可。

实验性功能

对于数据库的各种函数和json操作，添加的实验性功能，暂时以mysql、pgsql两个数据库为主。

stringAgg：

用于字符串聚合。

第一个参数为Query的子类型，为需要聚合的表达式；

第二个参数为String，为分隔符；

第三个参数为可选参数，Boolean类型，为是否使用DISTINCT，默认为false；

之后是一个可选的变长参数，为排序规则，使用Query类的asc()或desc()两个方法构建。

例子：

val select = db
	.select(stringAgg(User.name, ",", true, User.id.asc(), User.gender.desc()))
	.from(User)

mysql生成的sql语句：

SELECT GROUP_CONCAT(DISTINCT user.user_name ORDER BY user.id ASC, user.gender DESC SEPARATOR ',')
FROM user

pgsql生成的sql语句：

SELECT STRING_AGG(DISTINCT CAST(user.user_name AS VARCHAR), ',' ORDER BY user.id ASC, user.gender DESC)
FROM user

arrayAgg：

使用方式同上，在pgsql中生成的sql为ARRAY_TO_STRING(ARRAY_AGG())形式。

获取Json：

使用json（数据库的->操作符）和jsonText(数据库的->>操作符)函数来获取Json数据（支持使用Int下标或者String对象名获取）：

val select = db
	.select(User.jsonInfo.json(0).json("obj").jsonText("id"))
	.from(User)

mysql生成的sql语句：

SELECT user.json_info ->> '$[0].obj.id'
FROM user

pgsql生成的sql语句：

SELECT CAST(user.json_info AS JSONB) -> 0 -> 'obj' ->> 'id'
FROM user

mysql最终生成的操作符取决于调用链的最后一次操作。

jsonLength：

作用为获取json的数组长度。

参数为Json调用链或一个Query的子类型。

例子：

val select = db
	.select(jsonLength(User.jsonInfo.json(0).json("objs")))
	.from(User)

mysql生成的sql语句：

SELECT JSON_LENGTH(user.json_info, '$[0].objs')
FROM user

pgsql生成的sql语句：

SELECT JSONB_ARRAY_LENGTH(CAST(user.json_info AS JSONB) -> 0 -> 'objs')
FROM user

findInSet：

用于查询元素是否在某个以","隔开的字符串中。

第一个参数为Query的子类型或者String，为需要查询的表达式；

第二个参数为Query的子类型，为需要查询的集合。

例子：

val select = db.select().from(User).where(findInSet("1", User.ids))

mysql生成的sql语句：

SELECT *
FROM user
WHERE FIND_IN_SET('1', user.ids)

pgsql生成的sql语句：

SELECT *
FROM user
WHERE CAST('1' AS VARCHAR) = ANY(STRING_TO_ARRAY(user.ids, ','))