联表

在上一节中，我们介绍了查询的 SQL DSL，这足以应付许多的场景。不过前面的查询都只限于单表，在大部分情况下，我们的业务都需要多个表来完成。连接查询的支持，对于一个 ORM 框架而言必不可少。

连接函数

Ktorm 使用扩展函数对连接查询提供支持，内置的标准连接类型有四种：

连接类型	扩展函数名	对应的 SQL 关键字
交叉连接	crossJoin	cross join
内连接	innerJoin	inner join
左外连接	leftJoin	left join
右外连接	rightJoin	right join
全外连接	fullJoin	full join

以上函数都是 QuerySource 的扩展函数，最简单的使用方式如下：

1	val querySource = database.from(Employees).crossJoin(Departments)

我们知道，from 函数的功能是把一个表对象包装成 QuerySource 对象，而 crossJoin 则把它的结果与另一个表进行交叉连接，返回一个新的 QuerySource。然而，大部分时候，我们持有一个 QuerySource 并没有任何用处，我们需要将它变成一个 Query 对象，以便进行多表查询，并取得查询的结果。

还记得怎样使用 QuerySource 创建一个查询吗？是的，只需要调用 select 函数：

1	val query = database.from(Employees).crossJoin(Departments).select()

上面的查询把员工表和部门表进行交叉连接，并返回所有记录（笛卡尔积），生成的 SQL 如下：

1
2
3

select * 
from t_employee 
cross join t_department

上面的查询比较简单，在实际使用中，如此简单的联表查询通常都用处有限。接下来是一个比较实际的例子，这个查询获取所有薪水大于 100 的员工的名字和他所属的部门的名字。在这里，我们指定了 leftJoin 函数的第二个参数，它就是连接条件：

val query = database
    .from(Employees)
    .leftJoin(Departments, on = Employees.departmentId eq Departments.id)
    .select(Employees.name, Departments.name)
    .where { Employees.salary gt 100L }

生成的 SQL 如下：

select t_employee.name as t_employee_name, t_department.name as t_department_name 
from t_employee 
left join t_department on t_employee.department_id = t_department.id 
where t_employee.salary > ?

自连接查询与表别名

自连接是连接查询的一种特殊用法，它支持把一个表与它自身进行连接，比如下面这句 SQL 就使用了自连接，它查询每个员工的名字、他直属上司的名字以及他所属部门的名称：

select emp.name as emp_name, mgr.name as mgr_name, dept.name as dept_name 
from t_employee emp 
left join t_employee mgr on emp.manager_id = mgr.id 
left join t_department dept on emp.department_id = dept.id 
order by emp.id

可以看到，在这句 SQL 中，t_employee 表出现了两次，但是它们拥有不同的别名，分别是 emp 和 mgr，正是这两个别名区分开了连接查询中的两个相同的表。那么在 Ktorm 中，我们如何实现这样的查询呢？

如果你有心的话，可能已经发现，Table 类中正好提供了一个 aliased 函数，它返回一个新的表对象，该对象复制自当前对象，具有完全相同的数据和结构，但是赋予了新的 alias 属性，这个函数正是在现在这个场景中使用的。使用 aliased 函数，尝试完成上面的自连接查询，你可能会写出这样的代码：

data class Names(val name: String?, val managerName: String?, val departmentName: String?)

val emp = Employees.aliased("emp") // 第三行，对 Employees 表对象赋予别名
val mgr = Employees.aliased("mgr") // 第四行，对 Employees 表对象赋予另一个不同的别名
val dept = Departments.aliased("dept")

val results = database
    .from(emp)
    .leftJoin(mgr, on = emp.managerId eq mgr.id) // 第八行，连接两个不同的 Employees 表
    .leftJoin(dept, on = emp.departmentId eq dept.id)
    .select(emp.name, mgr.name, dept.name)
    .orderBy(emp.id.asc())
    .map { row -> 
        Names(
            name = row[emp.name],
            managerName = row[mgr.name],
            departmentName = row[dept.name]
        )
    }

上面的代码很符合直觉，也正是 Ktorm 的 SQL DSL 所推荐的书写风格，但遗憾的是，它很有可能无法通过编译。为了帮助我们分析这个错误，在这里先贴出 Employees 表对象的定义，这个定义复制自定义表结构 - 表对象一节：

object Employees : Table<Nothing>("t_employee") {
    val id = int("id").primaryKey()
    val name = varchar("name")
    val job = varchar("job")
    val managerId = int("manager_id")
    val hireDate = date("hire_date")
    val salary = long("salary")
    val departmentId = int("department_id")
}

而父类 Table 中 aliased 方法的签名则是这样的：

1	open fun aliased(alias: String): Table<E> { ... }

很显然，根据 aliased 方法的签名，上面第三行中的 Employees.aliased("emp") 得到的返回值的类型应该是 Table<E>，第四行中的 mgr 变量的类型也是如此。那么，第八行中的 emp.managerId eq mrg.id 明显就是错误的了，因为 id 和 managerId 两个属性只在 Employees 对象中存在，而这里的两个具有别名的表对象的类型都是 Table<E>，而不是 Employees。

受限于 Kotlin 语言的限制，Table.aliased 函数虽然能够完成复制表结构并赋予别名的功能，但它的返回值只能是 Table<E>，而无法与它的调用者具有完全相同的类型。例如在这里我们使用 object 关键字将 Employees 定义为单例的表对象，由于 Kotlin 的单例限制，aliased 方法创建的新的表对象不可能也是 Employees。

为了正常实现自连接查询，我们推荐，如果需要使用到表别名功能，请勿将表对象定义为 object，而应该使用 class 代替，并重写 aliased 方法使其返回完全相同的类型：

class Employees(alias: String?) : Table<Nothing>("t_employee", alias) {
    override fun aliased(alias: String) = Employees(alias)
    // 此处省略无关的列定义
}

但是，单纯把 object 改成 class 也会遇到问题，比如无法再使用 Employees.name 的写法快速获取一个列，而必须要先调用构造方法创建一个表对象。因此，我们还推荐在将表定义为 class 的同时，提供一个伴随对象，作为未赋予别名的默认表对象，这样既支持了原来的写法，又能使用表别名的功能。最终的 Employees 定义如下：

open class Employees(alias: String?) : Table<Nothing>("t_employee", alias) {
    companion object : Employees(null)
    override fun aliased(alias: String) = Employees(alias)

    val id = int("id").primaryKey()
    val name = varchar("name")
    val job = varchar("job")
    val managerId = int("manager_id")
    val hireDate = date("hire_date")
    val salary = long("salary")
    val departmentId = int("department_id")
}

以上就是 Ktorm 提供的表别名的支持。现在你可以再尝试执行一下前面的自连接查询，如无意外，它现在应该已经可以完美生成 SQL，返回结果了。

扩展连接类型

Ktorm 的核心模块只提供了四种标准的连接类型（见连接函数一节），一般来说，这四种连接类型已经足够应付我们的业务，但是，如果我们想使用某些数据库特有的连接类型，该如何做呢？下面我们以 MySQL 中的自然连接（natural join）为例对此问题进行探讨。

查看源码，我们可以知道，JoinExpression 继承于 QuerySourceExpression，这是一个抽象类。我们也可以创建一个 NaturalJoinExpression 类继承于 QuerySourceExpression：

data class NaturalJoinExpression(
    val left: QuerySourceExpression,
    val right: QuerySourceExpression,
    override val isLeafNode: Boolean = false
) : QuerySourceExpression()

有了定制的表达式类型以后，我们需要添加一个扩展函数，就像上面的 crossJoin、leftJoin 等扩展函数一样，用于将 QuerySource 对象中的 expression 替换为 NaturalJoinExpression。

1
2
3

fun QuerySource.naturalJoin(right: BaseTable<*>): QuerySource {
    return this.copy(expression = NaturalJoinExpression(left = expression, right = right.asExpression()))
}

Ktorm 默认情况下无法识别我们自己创建的表达式类型 NaturalJoinExpression，因此无法生成支持 natural join 的 SQL 语句。这时，我们可以扩展 SqlFormatter 类，重写它的 visitUnknown 方法，在里面检测我们的自定义表达式，为其生成正确的 SQL：

class CustomSqlFormatter(database: Database, beautifySql: Boolean, indentSize: Int)
    : MySqlFormatter(database, beautifySql, indentSize) {

    override fun visitUnknown(expr: SqlExpression): SqlExpression {
        if (expr is NaturalJoinExpression) {
            visitQuerySource(expr.left)
            newLine(Indentation.SAME)
            write("natural join ")
            visitQuerySource(expr.right)
            return expr
        } else {
            return super.visitUnknown(expr)
        }
    }
}

最后，使用方言（Dialect）将这个自定义的 SqlFormatter 注册到 Database 对象中。更多关于方言的细节，可参考后面的章节。

val database = Database.connect(
    url = "jdbc:mysql://localhost:3306/ktorm", user = "root", password = "***",
    dialect = object : SqlDialect {
        override fun createSqlFormatter(database: Database, beautifySql: Boolean, indentSize: Int): SqlFormatter {
            return CustomSqlFormatter(database, beautifySql, indentSize)
        }
    }
)

大功告成，naturalJoin 的使用方式如下：

1	val query = database.from(Employees).naturalJoin(Departments).select()

这样，Ktorm 就能够无缝支持自然连接，事实上，这正是 ktorm-support-mysql 模块的功能之一，如果你真的需要使用 MySQL 的自然连接，请直接在项目中添加依赖，不必再写一遍上面的代码，这里仅作示范。

Maven 依赖：

<dependency>
    <groupId>org.ktorm</groupId>
    <artifactId>ktorm-support-mysql</artifactId>
    <version>${ktorm.version}</version>
</dependency>

或者 gradle：

1	compile "org.ktorm:ktorm-support-mysql:${ktorm.version}"