我有一個帶有單個表的 Hibernate 數(shù)據庫，如下所示：PURCHASE_ID | PRODUCT_NAME | PURCHASE_DATE | PURCHASER_NAME | PRODUCT_CATEGORY------------------------------------------------------------------------------ 1 Notebook 09-07-2018 Bob Supplies 2 Notebook 09-06-2018 Bob Supplies 3 Pencil 09-06-2018 Bob Supplies 4 Tape 09-10-2018 Bob Supplies 5 Pencil 09-09-2018 Steve Supplies 6 Pencil 09-06-2018 Steve Supplies 7 Pencil 09-08-2018 Allen Supplies我想根據其他一些限制只退回最新購買的商品。例如：List<Purchase> getNewestPurchasesFor(Array<String> productNames, Array<String> purchaserNames) { ... }可以使用以下方法調用：List<Purchase> purchases = getNewestPurchasesFor(["Notebook", "Pencil"], ["Bob", "Steve"]);用英語，“給我最新購買的筆記本或鉛筆，鮑勃或史蒂夫?！辈⑻峁篜URCHASE_ID | PRODUCT_NAME | PURCHASE_DATE | PURCHASER_NAME----------------------------------------------------------- 1 Notebook 09-07-2018 Bob 3 Pencil 09-06-2018 Bob 5 Pencil 09-09-2018 Steve 所以，它就像基于一些一個“獨特”的多列查找，或“限價”后排序合并列唯一鍵，但所有的例子，我發(fā)現(xiàn)顯示使用SELECT DISTINCT(PRODUCT_NAME, PURCHASER_NAME)以獲得只有那些列，而我需要使用格式：from Purchases as entity where ...以便模型類型以完整的關系返回。對于重復購買，這會導致性能下降。我應該使用任何特殊關鍵字來完成此操作嗎？查詢語言和 SQL-fu 不是我的強項。
3 回答
慕田峪7331174
TA貢獻1828條經驗 獲得超13個贊
首先，使用聚合查詢獲取產品+購買者組合的最后購買日期。
使用該查詢作為匹配元組的子選擇：
from Puchases p
where (p.PRODUCT_NAME, p1.PURCHASER_NAME, p1.PURCHASE_DATE) in
(select PRODUCT_NAME, PURCHASER_NAME , max(PURCHASE_DATE)
from Purchases
where
PRODUCT_NAME in :productNames and
PURCHASER_NAME in :purchaserNames
group by PRODUCT_NAME, PURCHASER_NAME)
也應該可以使用標準 API 來實現(xiàn)相同的功能，使用 Subqueries.propertiesIn。
如果您的 PURCHASE_ID 保證“按時間順序遞增”，那么您只需在子選擇中使用 max(PURCHASE_ID) 即可。
慕運維8079593
TA貢獻1876條經驗 獲得超5個贊
在我看來，訣竅是看到“給我最新的”相當于“給沒有新購買的行”。這轉化為這種查詢：
-- This is SQL
-- Note that if two purchases have exactly the same date, this query will
-- return both; you can fine tune the condition inside the exists clause
-- to avoid this
select *
from purchases p1
where
p1.product_name in ('Notebook', 'Pencil') and
p1.purchaser_name in ('Bob', 'Steve') and
not exists (
select p2.purchase_id
from purchases p2
where
p2.product_name = p1.product_name and
p2.purchaser_name = p1.purchaser_name and
p2.purchase_date > p1.purchase_date
)
order by purchase_id;
盡管這是 SQL，但轉換為 HQL 應該非常簡單，這對您來說可能就足夠了。自從我使用 Hibernate Criteria 已經很長時間了（這些天你傾向于使用 JPA API），但它應該是類似的東西：
DetachedCriteria criteria = DetachedCriteria.forClass(Purchase.class, "p1");
// add here your filters to criteria
// criteria.add(purcharserName in (....));
// criteria.add(productName in (....));
// this appends the not exists clause
DetachedCriteria notExistsCriteria = DetachedCriteria.forClass(Purchase.class, "p2");
notExistsCriteria.add(Restrictions.eqProperty("p2.productName", "p1.productName"));
notExistsCriteria.add(Restrictions.eqProperty("p2.purchaserName", "p1.purchaserName"));
notExistsCriteria.add(Restrictions.gtProperty("p2.purchaseDate", "p1.purchaseDate"));
criteria.add(Subqueries.notExists(notExistsCriteria.setProjection(Projections.property("p1.id"))));
List<Purchase> results = // issue Criteria query
更新：
我看到 Hibernate Criteria 支持SQLALL運算符，所以如果你的數(shù)據庫支持它，你也可以這樣寫：
DetachedCriteria criteria = DetachedCriteria.forClass(Purchase.class, "p1");
// add here your filters to criteria
// criteria.add(purcharserName in (....));
// criteria.add(productName in (....));
// this appends the p1.purchaseDate > all (...) filter
DetachedCriteria allCriteria = DetachedCriteria.forClass(Purchase.class, "p2");
allCriteria.add(Restrictions.eqProperty("p2.productName", "p1.productName"));
allCriteria.add(Restrictions.eqProperty("p2.purchaserName", "p1.purchaserName"));
criteria.add(Subqueries.propertyGeAll("p1.purchaseDate", allCriteria.setProjection(Projections.property("p2.purchaseDate"))));
List<Purchase> results = // issue Criteria query
讀起來更清楚一點。
添加回答
舉報
0/150
提交
取消