CODEWITHSUNDEEP
hibernatejpahql
Abhishek Nayak
Abhishek Nayak

Reputation: 3748

JPA count query to count total result rows of a group by query

How to get total result rows below SQL query in JPQL:

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;

I have tried with:

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

But it returns only first rows count, How can get total rows count?

EDIT:

My entity looks like:

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
}

Upvotes: 3

Views: 6642

Answers (1)

David SN
David SN

Reputation: 3519

Your query will return an array of Long. Each Long is the count of UserTransaction for a given MerchantMaster name.

if you have your query in a NamedQuery, you can use (if not, you can use the creatyQuery() method and use your JPQL query String):

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();

You probably will also need the Merchant name, you can add the merchant name to your query: 

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

Now, the query will return an array of objects, the first object is the count, and the second is the name. You also need to modify the code to get the results:

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];
}

Upvotes: 3

Related Questions