我正在嘗試將以下查詢轉換為 JPA 規范，以便更靈活地查詢我的規則物體，但我沒有找到任何將以下 SQL 查詢轉換為規范的方法。我一直在尋找使用規格查詢“disctint on”的可能性，但我找不到任何東西。
SELECT DISTINCT ON (name, key) * FROM (SELECT * FROM rules WHERE activated_at < NOW() AND name IN (?1) AND key IN (?2) ORDER BY activated_at DESC) AS tmp;
上面的查詢為每個 name key 組合提供了 1 條規則，每個組合都有最新的 activate_at 時間戳。
一些背景：
- 特定規則用名稱 鍵標識
- 可以有多個具有相同名稱 鍵的記錄，其中當前活動的規則是具有最大激活時間時間戳的規則，但不是未來值。
IN 子句使用如下謂詞直截了當，但我找不到查詢最近的 activate_at 時間戳的方法。
return (root, query, criteriaBuilder) -> root.get(key).in(keys);
這可以通過 JPA 規范實作嗎？
有沒有人對如何實作它有任何指導或指點？
uj5u.com熱心網友回復：
使用@Toru 建議的查詢
SELECT * FROM rules r
WHERE
name IN (?1)
AND key IN (?2)
AND activated_at = (
SELECT max(avtivated_at) from rule r2
where r2.name = r.name and r2.key = r.key
and activated_at < NOW()
)
在規范中
public class Specs {
public static Specification<Rules> getMaxActivatedRules(String name, String key) {
return (root, query, builder) -> {
// SubQuery portion start
Subquery<Date> subQuery = query.subquery(Date.class);
Root<Rules> subRoot = subQuery.from(Rules.class);
Expression<Date> maxActivatedDateExpr = builder.max(subRoot.get(Rules_.activatedAt));
Predicate subqueryNameEqual = builder.equal(subRoot.get(Rules_.name), name);
Predicate subqueryKeyEqual = builder.equal(subRoot.get(Rules_.key), key);
Predicate subqueryActivatedAtLessThenNow = builder.lt(root.get(Rules_.activatedAt), builder.literal("NOW()"));
subQuery.select(maxActivatedDateExpr).where(subqueryNameEqual, subqueryKeyEqual, subqueryActivatedAtLessThenNow);
// Subquery portion end
Predicate subQueryEqual = builder.equal(root.get(Rules_.activatedAt), subQuery);
Predicate nameEqual = builder.equal(root.get(Rules_.name), name);
Predicate keyEqual = builder.equal(root.get(Rules_.key), key);
return builder.and(subQueryEqual, nameEqual, keyEqual );
};
}
}
uj5u.com熱心網友回復：
通過對@Ratul的回答進行一些修改，下面的實作就是我想要的。不同之處在于我不想將子查詢名稱和鍵基于輸入引數，而是讓子查詢基于當前根物件的值。
private Specification<Rule> isActiveRule() {
return (root, query, builder) -> {
Subquery<Instant> subquery = query.subquery(Instant.class);
Root<Rule> subRoot = subquery.from(Rule.class);
Expression<Instant> maxActivatedAt = builder.greatest(subRoot.get(Rule_.activatedAt));
Predicate subqueryNameEqual = builder.equal(subRoot.get(Rule_.name), root.get(Rule_.name));
Predicate subqueryKeyEqual = builder.equal(subRoot.get(Rule_.key), root.get(Rule_.key));
Predicate subQueryActivatedAtBeforeNow = builder.lessThan(subRoot.get(Rule_.activatedAt), Instant.now());
subquery.select(maxActivatedAt).where(subqueryNameEqual, subqueryKeyEqual, subQueryActivatedAtBeforeNow);
Predicate subQueryEqual = builder.equal(root.get(Rule_.activatedAt), subquery);
return builder.and(subQueryEqual);
};
}
這個實作的巧妙部分是允許我選擇哪些謂詞包含在我的 SpecificationBuilder 類的規范中。因此，如果我不想提供名稱或產品密鑰，而只使用isActiveRule()謂詞，我將獲得所有有效的規則。
例如
var spec = RuleSpecificationBuilder.builder()
.nameIn(names)
.keyIn(keys)
.isActiveRule()
.build();
// OR
var spec = RuleSpecificationBuilder.builder()
.isActiveRule()
.build();
// OR
RuleSpecificationBuilder.builder()
.nameIn(names)
.isActiveRule()
.build();
// etc..
謂詞 nameIn(names)、keyIn(keys) 和 isActiveRule() 轉換為@Toru在評論中指定的這個查詢：
SELECT * FROM rules r WHERE name IN (?1) AND key IN (?2) and activated_at = (SELECT max(activated_at) from rule r2 where r2.name = r.name and r2.key = r.key and activated_at < NOW())
...isActiveRule()謂詞轉化為activated_at = (SELECT max(activated_at) from rule r2 where r2.name = r.name and r2.key = r.key and activated_at < NOW())
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/462562.html
上一篇：如何處理建構式中的可為空引數？