CODEWITHSUNDEEP
javamysqlhibernatejpaspring-boot
Mike
Mike

Reputation: 1097

How to get SELECT values and COUNT value with JPA GROUP BY

I can not see how can I can use a JPA Query like this...

@Query(value = "select statusupdateid, count(*) from comments group by statusupdateid", nativeQuery = true) public

It should give a StatusUpdate id and the count...

I do not know whether to use a HashMap or what.... but the object solution from How to get SELECT values and COUNT value with JPA GROUP BY? does not look like an elegant solution and my HashMap does not work

public HashMap<StatusUpdate, Long> topComments();

Thanks

Upvotes: 0

Views: 3938

Answers (3)

Prafulla Kumar Sahu
Prafulla Kumar Sahu

Reputation: 9693

Quite old post but one solution will be using BIN_TO_UUID

@Query(value = "select BIN_TO_UUID(statusupdateid), count(*) from comments group by statusupdateid", nativeQuery = true) 
public List<String> findStatusupdateidAndCount();

Upvotes: 0

吳建成
吳建成

Reputation: 1

@Query(value = "select statusupdateid as statusupdateid , count(*) as count from comments group by statusupdateid")
public List<Map<String,Object>> find();

or

@Query(value = "select statusupdateid as statusupdateid , count(*) as count from comments group by statusupdateid")
public Page<Map<String,Object>> find(Pageable pageable);

Upvotes: 0

Bhushan Uniyal
Bhushan Uniyal

Reputation: 5703

You can use it like, you need a class with hold statusupdateid,statusupdateid

with parametrized constructor :

    class Data {
    //please modify datatype as per your structure
     long statusupdateid;
     long count
    public(long statusupdateid, long count)
    this.statusupdateid=statusupdateid;
    this.count =count;
    }

Now your query will:

@Query(value = "select statusupdateid, count(*) from comments group by statusupdateid", nativeQuery = true) 
public List<Data> findStatusupdateidAndCount();

Now it will return the list of Data which have statusupdateid and it count

Upvotes: 3

Related Questions