简介
在这篇文章中，我将向你展示如何使用Hibernate 6来编写使用SQL窗口函数的JPQL查询。
在Hibernate 6中增加了这个功能，它提供了一个新的查询分析器，称为SQM（Semantic Query Model），它比以前的Hibernate HQL查询功能更强大。
SQL窗口功能
正如我在这篇文章中所解释的，窗口函数是非常强大的，因为它允许我们在给定的记录集上应用聚合函数，而不必像GROUP BY 子句那样，将结果集减少到每个分区的单行。
例如，让我们假设我们有以下的数据库表。
account 是父表，account_transaction 是子表，因为它有一个account_id 外键列，引用account 表的id 主键。
account 表有两条与Alice和Bob相关的记录。
| id | iban | owner |
|----|-----------------|-------------|
| 1 | 123-456-789-010 | Alice Smith |
| 2 | 123-456-789-101 | Bob Johnson |
而account_transaction 包含属于Alice和Bob的交易。
| id | amount | created_on | account_id |
|----|--------|---------------------|------------|
| 1 | 2560 | 2019-10-13 12:23:00 | 1 |
| 2 | -200 | 2019-10-14 13:23:00 | 1 |
| 3 | 500 | 2019-10-14 15:45:00 | 1 |
| 4 | -1850 | 2019-10-15 10:15:00 | 1 |
| 5 | 2560 | 2019-10-13 15:23:00 | 2 |
| 6 | 300 | 2019-10-14 11:23:00 | 2 |
| 7 | -500 | 2019-10-14 14:45:00 | 2 |
| 8 | -150 | 2019-10-15 10:15:00 | 2 |
我们现在对一个包含以下列的报表感兴趣。
- 与每个账户相关的每个报表条目的编号
- 交易标识符
- 账户标识符
- 交易时间戳
- 交易金额
- 交易发生时的账户余额
为了得到这份报告，我们需要执行以下SQL查询。
SELECT
ROW_NUMBER() OVER(
PARTITION BY account_id
ORDER BY created_on, id
) AS nr,
id,
account_id,
created_on,
amount,
SUM(amount) OVER(
PARTITION BY account_id
ORDER BY created_on, id
) AS balance
FROM account_transaction
ORDER BY id
这个SQL查询使用了两个窗口函数。
ROW_NUMBER 函数的工作原理如下。
首先，它将通过account_id ，对查询结果集进行分区，因此将结果集分为两组，因为我们的交易只属于两个个人账户。
ROW_NUMBER() OVER(
PARTITION BY account_id
ORDER BY created_on, id
) AS nr,
第二，它将按时间顺序对每个分区进行排序。
ROW_NUMBER() OVER(
PARTITION BY account_id
ORDER BY created_on, id
) AS nr,
我们在排序时同时使用created_on 和id 列的原因是为了防止两个交易在同一时刻被注册的情况发生。通过使用id ，它也是单调递增的，我们确保当前框架从第一个分区记录跨越到当前处理行。
一旦记录被分区和排序，ROW_NUMBER 为每条记录分配连续的编号。注意，当切换到一个新的分区时，编号会被重置。
SUM 函数使用相同的分区和排序逻辑。
SUM(amount) OVER(
PARTITION BY account_id
ORDER BY created_on, id
) AS nr,
正如已经解释过的，应用窗口函数的默认框架从当前分区的第一条记录一直到当前处理元素。由于这个原因，SUM 函数要产生一个运行的总数。
因此，产生我们感兴趣的报告的SQL查询看起来像这样。
SELECT
ROW_NUMBER() OVER(
PARTITION BY account_id
ORDER BY created_on, id
) AS nr,
id,
account_id,
created_on,
amount,
SUM(amount) OVER(
PARTITION BY account_id
ORDER BY created_on, id
) AS balance
FROM account_transaction
ORDER BY id
而当执行这个查询时，我们会得到以下结果。
| nr | id | account_id | created_on | amount | balance |
|----|----|------------|----------------------------|--------|---------|
| 1 | 1 | 1 | 2019-10-13 12:23:00.000000 | 2560 | 2560 |
| 2 | 2 | 1 | 2019-10-14 13:23:00.000000 | -200 | 2360 |
| 3 | 3 | 1 | 2019-10-14 15:45:00.000000 | 500 | 2860 |
| 4 | 4 | 1 | 2019-10-15 10:15:00.000000 | -1850 | 1010 |
| 1 | 5 | 2 | 2019-10-13 15:23:00.000000 | 2560 | 2560 |
| 2 | 6 | 2 | 2019-10-14 11:23:00.000000 | 300 | 2860 |
| 3 | 7 | 2 | 2019-10-14 14:45:00.000000 | -500 | 2360 |
| 4 | 8 | 2 | 2019-10-15 10:15:00.000000 | -150 | 2210 |
带有窗口函数的Hibernate JPQL
在Hibernate 6之前，使用Windows函数进行实体查询的唯一方法是通过Blaze Persistence。由于Hibernate 6提供了一个新的语义查询模型，所以实体查询语言比以前强大多了。
出于这个原因，使用Hibernate 6，你现在可以执行下面的JPQL查询。
List<StatementRecord> records = entityManager.createQuery("""
SELECT
ROW_NUMBER() OVER(
PARTITION BY at.account.id
ORDER BY at.createdOn
) AS nr,
at,
SUM(at.amount) OVER(
PARTITION BY at.account.id
ORDER BY at.createdOn
) AS balance
FROM AccountTransaction at
ORDER BY at.id
""", StatementRecord.class)
.unwrap(Query.class)
.setTupleTransformer((Object[] tuple, String[] aliases) ->
new StatementRecord(
longValue(tuple[0]),
(AccountTransaction) tuple[1],
longValue(tuple[2])
)
)
.getResultList();
assertEquals(8, records.size());
StatementRecord record1 = records.get(0);
assertEquals(
1L,
record1.nr().longValue()
);
assertEquals(
1L,
record1.transaction().getId().longValue()
);
assertEquals(
1L,
record1.transaction().getAccount().getId().longValue()
);
assertEquals(
2560L, record1.balance().longValue()
);
因为这是一个投影查询，我们正在使用新的TupleTransformer ，以返回一个具有以下结构的StatementRecord 对象的列表。
public record StatementRecord(
Long nr,
AccountTransaction transaction,
Long balance
) {}
当运行上述JPQL查询时，Hibernate 6会执行以下SQL查询。
SELECT
ROW_NUMBER() OVER(
PARTITION BY a1_0.account_id
ORDER BY a1_0.created_on
),
a1_0.id,
a1_0.account_id,
a1_0.amount,
a1_0.created_on,
SUM(a1_0.amount) OVER(
PARTITION BY a1_0.account_id
ORDER BY a1_0.created_on
)
FROM
account_transaction a1_0
ORDER BY
a1_0.id
请注意，与Hibernate 5、4或3不同的是，生成的SQL查询没有对投影使用额外的别名，因为JDBCResultSet 是按列索引读取的，而不是按别名名称，这也提供了更好的性能。
真棒，对吗？
总结
Hibernate 6提供了很多好处，有了对Window Functions的支持，我们就可以编写结合实体和聚合数据的投影。
这个新的Hibernate版本是相当具有革命性的，由于所有JPQL和Criteria实体查询所使用的新的语义查询模型，还有许多查询功能将很快被实现。
