gpt4 book ai didi

hibernate - JPA 计数查询以按查询计算组的总结果行

转载 作者:行者123 更新时间:2023-12-03 08:18:55 25 4
gpt4 key购买 nike

如何在 JPQL 中获取 SQL 查询下面的总结果行:

select
count(usertransa0_.id) as col_0_0_
from
user_transaction usertransa0_ cross
join
merchant_master merchantma1_
where
usertransa0_.merchant_master=merchantma1_.id
and usertransa0_.status=0
and (
usertransa0_.transaction_date between '2015-02-19' and '2015-02-20'
)
group by
merchantma1_.full_name;

我试过:

SELECT COUNT(ut)
FROM UserTransaction ut
WHERE ut.status = :stus AND ut.transactionDate between :frm AND :toDt"
GROUP BY ut.merchantMaster.fullName

但它只返回第一行数,如何获得总行数?

编辑:

我的实体看起来像:

public class UserTransaction extends BaseEntity {

@OneToOne(fetch = FetchType.LAZY)
private UserInfo info;

@OneToOne(fetch = FetchType.LAZY)
private MerchantMaster merchantMaster;

@OneToOne(fetch = FetchType.LAZY)
private PaymentMaster paymentMaster;

@Column(name = "AMOUNT")
private BigDecimal amount;

@Temporal(TemporalType.TIMESTAMP)
@Column(name = "TRANSACTION_DATE")
private Date transactionDate;

@Column(name = "STATUS")
private Integer status;

private String userTransactionID;

@OneToOne(cascade = { CascadeType.ALL }, fetch = FetchType.LAZY)
private UserTransactionRequest trRequest;

@OneToOne(cascade = { CascadeType.ALL }, fetch = FetchType.LAZY)
private UserTransactionResponse trResponse;

//getters & setters
}

最佳答案

您的查询将返回一个 Long 数组。每个 Long 是给定 MerchantMaster 名称的 UserTransaction 的计数。

如果您在 NamedQuery 中有您的查询,您可以使用(如果没有,您可以使用 creatyQuery() 方法并使用您的 JPQL 查询字符串):

TypedQuery<Long> query = em.createNamedQuery("UserTransaction.MyQuery", Long.class);
/* The list have the count of User Transaction grouped by Merchant name */
List<Long> userTransactionsPerMerchantName = query.getResultList();

您可能还需要商家名称,您可以将商家名称添加到您的查询中:

SELECT COUNT(ut), ut.merchantMaster.fullName
FROM UserTransaction ut
WHERE ut.status = :stus AND ut.transactionDate between :frm AND :toDt"
GROUP BY ut.merchantMaster.fullName

现在,查询将返回一个对象数组,第一个对象是计数,第二个是名称。您还需要修改代码以获取结果:

TypedQuery<Object[]> query = em.createNamedQuery("UserTransaction.MyQuery", Object[].class);
List<Object []> results = query.getResultList();
for (Object [] row : results) {
Long count = (Long) row[0];
String name = (String) row[1];
}

关于hibernate - JPA 计数查询以按查询计算组的总结果行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/28604517/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com