Skip to content

Java|Kotlin 语言集成查询

Jump to bottom
qiuwenchen edited this page Mar 7, 2024 · 1 revision

语言集成查询（WCDB Integrated Language Query，简称 WINQ），是 WCDB 的一项基础特性。它使得开发者能够直接使用 Java/Kotlin 编写 SQL 语句。

//Java
List<Sample> samples = table.getAllObjects(DBSample.id.gt(1));

//Kotlin
val samples = table.getAllObjects(DBSample.id.gt(1))

其中传入getAllObjects的参数 DBSample.id.gt(1) 就是语言集成查询的其中一个写法，它是 Expression对象，表示SQL语句中 id > 1 这个表达式。该 Expression 作为 SQL 的 where 参数，用于数据库查询。

语言集成查询基于 SQLite 的 SQL 语法实现。只要是 SQL 支持的语句，都能使用语言集成查询完成。也因此，语言集成查询具有和 SQL 语法一样的复杂性，本文不可能将所有涉及的接口和类都介绍一遍。而是结合 WCDB，介绍常用的类型，并在最后说明如何将已有 SQL 转换为语言集成查询的写法。

Column

Column 代表数据库内的一个字段，如 Insert 语句中的 column-name，指定了需要插入的数据所属的字段。它可通过字段名创建。

//Java
Column idColumn = new Column("id");
StatementInsert statementInsert = new StatementInsert().insertInto("sampleTable")
        .columns(idColumn)
        .value(1);
System.out.print(statementInsert);// 输出 "INSERT INTO sampleTable(id) VALUES(1)"
//Kotlin
val idColumn = Column("id")
val statementInsert = StatementInsert().insertInto("sampleTable")
    .columns(idColumn)
    .value(1)
print(statementInsert)// 输出 "INSERT INTO sampleTable(id) VALUES(1)"

ResultColumn

ResultColumn 通常代表数据库查询中的结果，如 Select 语句中的 result-column，指定了期望查询的结果。

//Java
Column idColumn = new Column("id");
ResultColumn idResultColumn = new ResultColumn(idColumn);
StatementSelect statementSelect = new StatementSelect().select(idResultColumn).from("sampleTable");
System.out.print(statementSelect);// 输出 "SELECT id FROM sampleTable"
//Kotlin
val idColumn = Column("id")
val idResultColumn = ResultColumn(idColumn)
val statementSelect = StatementSelect().select(idResultColumn).from("sampleTable")
print(statementSelect)// 输出 "SELECT id FROM sampleTable"

Convertible

细心观察语法中的描述可以发现，许多节点的参数可以不止一种。以刚才提到的 ResultColumn 为例。

WINQ-Column-Result

可以看到，Expression 也可以转换为 ResultColumn。

我们再回到 StatementSelect 语句的 select 函数，倘若它只接受 ResultColumn 类作为参数，那么每次调用时，都需要将 Expression 转换为 ResultColumn。

// 以下为示例代码，并非 WCDB 真正的实现
public class StatementSelect {
    public StatementSelect select(ResultColumn resultColumn) { /* ... */ }
}

Column idColumn = new Column("id");
Expression idExpression = new Expression(idColumn);
ResultColumn idResultColumn = new ResultColumn(idExpression);
StatementSelect statementSelect = new StatementSelect().select(idResultColumn).from("sampleTable");
System.out.print(statementSelect); // 输出 "SELECT identifier FROM sampleTable"

可以看到，需要 3 重转换，才能将 Column 转换为我们需要的 ResultColumn。

为了解决这些类型转换问题，WCDB 定义了一系列 Convertible 协议，用于语法中可互相转换的类型。

// StatementSelect.java
public StatementSelect select(ResultColumnConvertible... resultColumns)

基于 Convertible 协议，select 接口的参数为 ResultColumnConvertible，即所有可转换为 ResultColumn 的类型，都能作为 select 函数的参数。

在 SQL 语法中，Expression 是能转换为 ResultColumn 的类型；而 Column 是能转换为 Expression 的类型，因此其也同时是能转换为 ResultColumn 的类型。

// WCDB Java 内部的代码示例
public interface ResultColumnConvertible { /* ... */ }
public interface ExpressionConvertible extends ResultColumnConvertible { /* ... */ }

public class Column extends ExpressionConvertible { /* ... */ }
public class Expression extends ResultColumnConvertible { /* ... */ }

因此，原来的 select 语句可以直接简写为：

//Java
Column idColumn = new Column("id");
StatementSelect statementSelect = new StatementSelect().select(idColumn).from("sampleTable");
System.out.print(statementSelect);// 输出 "SELECT id FROM sampleTable"
//Kotlin
val idColumn = Column("id")
val statementSelect = StatementSelect().select(idColumn).from("sampleTable")
print(statementSelect)// 输出 "SELECT id FROM sampleTable"

WCDB 内的 Convertible 接口协议较多，这里不一一赘述。开发者也无需逐一了解，在使用时再查阅接口即可。

Field

Field 严格来说不属于语言集成查询的一部分，它们是语言集成查询和模型绑定结合的产物。

当需要通过对象来操作数据库时，如"getObject"或者"update with object"等，WCDB 不仅需要知道查找数据的哪个字段（即 Column 所完成的事情），还需要知道这个字段对应模型绑定中的哪个变量。

//Java
Field<Sample> field = DBSample.id;
List<Sample> samples = database.getAllObjects(new Field[]{field}, "sampleTable");
//Kotlin
val field = DBSample.id
val samples: List<Sample> = database.getAllObjects(arrayOf(field), "sampleTable")

而 Field 就是存储了数据库字段和模型绑定字段的映射关系。

基于模型绑定，开发者可以完全摆脱通过字符串创建 Column，更便捷地操作数据库。Field 是继承了Column来实现的，WINQ中所有使用Column的地方都可以使用Field 。

//Java
StatementSelect statementSelect = new StatementSelect().select(DBSample.id).from("sampleTable");
System.out.print(statementSelect);// 输出 "SELECT identifier FROM sampleTable"
//Kotlin
val statementSelect = StatementSelect().select(DBSample.id).from("sampleTable")
print(statementSelect)// 输出 "SELECT identifier FROM sampleTable"

Expression

Expression 可以算是 SQL 里最复杂的一个了。它在许多语句中都出现，比如查询语句的 where、groupBy、having、limit、offset，更新语句的 value、where、limit、offset等等。同时，它的完整定义也很长，甚至还包含了递归。

从单个 Expression 的语法角度来看，它支持从数字、字符串、字段创建。而 WCDB 将其扩展为支持所有字段映射类型，也即是内建的类型。

//Java
Expression expressionInt = new Expression(new LiteralValue(1));
Expression expressionDouble = new Expression(new LiteralValue(2.0f));
Expression expressionString = new Expression(new LiteralValue("3"));
Expression expressionColumn = new Expression(new Column("id"));
//Kotlin
val expressionInt = Expression(LiteralValue(1))
val expressionDouble = Expression(LiteralValue(2.0f))
val expressionString = Expression(LiteralValue("3"))
val expressionColumn = Expression(Column("id"))

除此之外，还有一个内建的绑定参数 BindParameter 也可以用来创建 Expression 类型，甚至StatementSelect也能转成 Expression 类型，因为有些场景子查询的结果也能作为判断条件。

在SQLite中，多个 Expression可以通过运算符和函数来操作。而在WCDB中，因为Java和Kotlin对运算符重载的支持都不好，所以运算符操作和函数操作都是通过 Expression对象的方法调用来实现。

运算符

SQLite 比较运算符对应的方法有：

运算符 方法名 示例
==, = eq expressionColumn.eq(expressionInt); // id == 1
!=, <> notEq expressionColumn.notEq(expressionInt); // id != 1
> gt expressionColumn.gt(expressionInt); // id > 1
< lt expressionColumn.lt(expressionInt); // id < 1
>= ge expressionColumn.ge(expressionInt); // id >= 1
<= le expressionColumn.le(expressionInt); // id <= 1

SQLite 算术运算符对应的方法有：

运算符 方法名 示例
+ add expressionInt.add(expressionDouble); // 1 + 2.0
- minus expressionInt.minus(expressionDouble); // 1 - 2.0
* multiply expressionInt.multiply(expressionDouble); // 1 * 2.0
/ divide expressionInt.devide(expressionDouble); // 1 / 2.0
% mod expressionColumn.mod(expressionDouble); // id % 2.0

SQLite 位运算符对应的方法有：

运算符 方法名 示例
& bitAnd expressionColumn.bitAnd(expressionInt); // id & 1
| bitOr expressionColumn.bitOr(expressionInt); // id | 1
<< leftShift expressionColumn.leftShift(expressionInt); // id << 1
>> rightShift expressionColumn.rightShift(expressionInt); // id >> 1

SQLite的逻辑运算符的方法名跟SQLite中的基本一致：

运算符 方法名 示例
AND and // id > 1 AND id != 2
expressionColumn.gt(1).and(expressionColumn.notEq(2));
OR or // id == 1 OR id > 2
expressionColumn.eq(1).or(expressionColumn.gt(2));
BETWEEN between expressionColumn.between(1, 2); // id BETWEEN 1 AND 2
IN in expressionColumn.in(1, 2); // id IN(1, 2)
LIKE like expressionColumn.like("%abc%"); // id LIKE '%abc%'
GLOB glob expressionColumn.glob("%abc%"); // id GLOB '%abc%'
IS NULL isNull expressionColumn.isNull(); // id IS NULL
IS is expressionColumn.is(1); // id IS 1
MATCH Match expressionColumn.match("fts"); // id MATCH 'fts'
EXISTS Expression.exists // EXISTS SELECT id FROM sampleTable
Expression.exists(new StatementSelect()
.select(expressionColumn).from("sampleTable"));
NOT 跟不同的操作符分别结合成：
notIn、notNull、notBetween、
notLike、notGlob、isNot、
notMatch、Expression.notExists		 expressionColumn.notNull(); // id NOTNULL
|| concat expressionColumn.concat("abc"); // id || 'abc'

从上面的示例中可以看到，这些方法的传入参数可以是Expression，也可以是基础类型。实际上，这些支持传参的方法都重载了多个版本来支持传入所有可以转成Expression的数据类型，以eq方法为例，它有以下这些重载版本：

public Expression eq(boolean operand)
public Expression eq(byte operand)
public Expression eq(short operand)
public Expression eq(int operand)
public Expression eq(long operand)
public Expression eq(float operand)
public Expression eq(double operand)
public Expression eq(String operand)
public Expression eq(ExpressionConvertible operand)

除了运算符，Expression的方法还包括了以下多种常用的SQLite内置函数：

  • Core Function中提到的SQLite常用内置函数，包括abs、hex、length等。
  • Aggregate Function中提到的SQLite常用内置聚合函数，包括avg、count、max、min等。
  • FTS函数，包括highlight、matchinfo等。
  • Collation中提到的字符串比对函数，可以使用collate方法。
expressionColumn.length(); // length(id)
expressionColumn.count(); // count(id)

ExpressionOperable

显然，每个类都要转换成 Expression 来进行这些操作，虽然也可以，但这就太麻烦了。

//Java
Expression expression = new Expression(DBSample.id).gt(1);
//Kotlin
val expression = Expression(DBSample.id).gt(1)

因此，WCDB 定义了 ExpressionOperable 基类。继承该基类的类都可以与 Expression 的类似，使用上述提到的方法来进行语言集成查询，包括 Column、Expression、Field等。也因此，基于模型绑定，开发者可以完全摆脱拼装 Expression，更便捷地操作数据库。

//Java
StatementSelect statementSelect = new StatementSelect().select(DBSample.id)
        .from("sampleTable")
        .where(DBSample.id.lt(-1).or(DBSample.id.ge(3.0f)));\
// 输出 "SELECT identifier FROM sampleTable WHERE ((identifier < -1) OR (identifier >= 3.0))"
System.out.print(statementSelect);
//Kotlin
val statementSelect = StatementSelect().select(DBSample.id)
    .from("sampleTable")
    .where(DBSample.id.lt(-1).or(DBSample.id.ge(3.0f)))
// 输出 "SELECT identifier FROM sampleTable WHERE ((identifier < -1) OR (identifier >= 3.0))"
print(statementSelect)

Statement

Statement 在前文已经接触到不少了，如查询 StatementSelect、插入 StatementInsert 等。它是一个最基本的完整可被执行的 SQL 语句。

SQLite 共包含 27 种 Statement，WCDB 都支持了。根据语法规则创建的 Statement，可以通过Database、Handle的 execute(Statement) 方法直接执行，也可以使用Database、Handle和Table下面的这些接口获取执行Statement的结果，

  • getValueFromStatement执行Statement并获取一个结果。
  • getOneRowFromStatement执行Statement并获取一行结果，结果为一维数组。
  • getOneColumnFromStatement系列接口执行Statement并获取一列结果，结果为一维数组，支持返回多种基础类型的数组。
  • getAllRowsFromStatement执行Statement并获取多行结果，以二维数组的方式返回。

试考虑，表中的数据可以想象为一个矩阵的存在，假设其数据如下：

identifier content
1 "sample1"
2 "sample1"
3 "sample2"
4 "sample2"
5 "sample2"

以下是使用Statement进行查询操作的示例代码：

// Java
// 获取所有内容
List<Value[]> allRows = database.getAllRowsFromStatement(
        new StatementSelect().select(DBSample.allFields()).from("sampleTable"));
System.out.print(allRows.get(2)[0].getInt()); //输出3

// 获取第二行
Value[] secondRow = database.getOneRowFromStatement(
        new StatementSelect().select(DBSample.allFields()).from("sampleTable").offset(1));
System.out.print(secondRow[0].getInt()); //输出2

// 获取第二行 content 列的值
List<String> contentColumn = database.getOneColumnStringFromStatement(
        new StatementSelect().select(DBSample.content).from("sampleTable"));
System.out.print(contentColumn.get(3)); //输出 "sample2"

// 获取 id 的最大值
Value maxId = database.getValueFromStatement(
        new StatementSelect().select(DBSample.id.max()).from("sampleTable"));
System.out.print(maxId.getInt()); //输出5

// 获取不同的 content 数
Value distinctContentCount = database.getValueFromStatement(
        new StatementSelect().select(DBSample.content.count().distinct()).from("sampleTable"));
System.out.print(distinctContentCount.getInt());//输出2
// Kotlin
// 获取所有内容
val allRows = database.getAllRowsFromStatement(
    StatementSelect().select(*DBSample.allFields()).from("sampleTable"))
print(allRows[2][0].int) //输出3

// 获取第二行
val secondRow = database.getOneRowFromStatement(
    StatementSelect().select(*DBSample.allFields()).from("sampleTable").offset(1))
print(secondRow[0].int) //输出2
        
// 获取第二行 content 列的值
val contentColumn = database.getOneColumnStringFromStatement(
    StatementSelect().select(DBSample.content).from("sampleTable"))
print(contentColumn[3]) //输出 "sample2"

// 获取 id 的最大值
val maxId = database.getValueFromStatement(
    StatementSelect().select(DBSample.id.max()).from("sampleTable"))
print(maxId.int) //输出5

// 获取不同的 content 数
val distinctContentCount = database.getValueFromStatement(
    StatementSelect().select(DBSample.content.count().distinct()).from("sampleTable"))
print(distinctContentCount.int) //输出2

Statement 还可以通过 getOrCreatePreparedStatement(Statement) 创建 PreparedStatement 对象做进一步的操作。我们会在高级接口的Handle一节详细介绍。

SQL 到语言集成查询

如前文所说，SQL 的复杂性决定了不可能介绍每一个类型及语句。因此，这里将介绍如何将一个已有的 SQL，转写为语言集成查询。开发者可以以此为例子，触类旁通。

对于已有的 SQL：

  1. 在语法中确定其所属的 Statement。
  2. 对照对应 Statement 的语法，根据关键字对已有的 SQL 进行断句。
  3. 逐个通过语言集成查询的方法调用进行实现。

以如下 SQL 为例：

SELECT min(id) FROM sampleTable WHERE (id > 0 || id / 2 == 0 ) && content NOT NULL ORDER BY content ASC LIMIT 1, 100

归类

根据 Statement 的列表，显然这个 SQL 属于 StatementSelect。

// Java
StatementSelect statementSelect = new StatementSelect();
// Kotlin
val statementSelect = StatementSelect()

断句

根据 StatementSelect 的语法规则，按关键词（即语法中的大写字母）进行断句：

SELECT min(id) 
FROM sampleTable 
WHERE (id > 0 || id / 2 == 0 ) && content NOT NULL 
ORDER BY id ASC 
LIMIT 1, 100

调用语言集成查询

根据每个关键词，找到语言集成查询中对应的方法，并完成其参数。

如 SELECT 关键词对应 StatementSelect 的 select() 函数：

statementSelect.select(DBSample.id.min());
statementSelect.from("sampleTable");

WHERE 的参数虽然较复杂，但也都能找到对应的函数：

statementSelect.where((DBSample.id.gt(0).or(DBSample.id.divide(2).eq(0)))
                      .and(DBSample.content.notNull()));

其他语句也同理：

statementSelect.orderBy(DBSample.id.order(Order.Asc));
statementSelect.limit(1, 100);

根据 SQL 语法结构图拼装 WINQ

SQLite的官网给出了它支持的全部SQL语法的结构图，还是以最复杂的select语句为例：

WINQ的整体设计思路是把整个select语句包装成一个StatementSelect对象，把图中的圆角方框的连接点包装成StatementSelect对象的方法，如：with、select、from、where、groupby、having、orderBy、limit等等这些方法。

图中直角方框的内容则是设计成StatementSelect对象的方法的入参。一些简单的入参则是支持使用基础类型，比如from方法可以传入一个字符串表示表名，一些复杂的入参则是继续包装成一个对象，比如前面提到的ResultColumn和Expression，其他的还有CommonTableExpression、Join、WindowDef等等这些对象。

所以在编写复杂SQL语句时，可以先找到对应的Statement对象，然后找到需要调用的方法，最后再根据方法需要的入参传入具体值。按照这个思路，可以根据SQL的语法结构图编写出所有SQLite支持的SQL语句。

首页

C++

Java/Kotlin

Swift

Objective-C

其他

Clone this wiki locally