CODEWITHSUNDEEP
javahibernatespring-bootjpaspring-data-jpa
Raikyn
Raikyn

Reputation: 163

Count and GROUP BY in a JPA nativeQuery

I have the following native query in my repository :

@Repository
public interface CardSearchRepository extends JpaRepository<Card, Long>{

@Query(value = "SELECT count(1) as numberInCollection, ca.* FROM CARD ca, PROFIL_CARD pfca " + 
" WHERE ca.id_en = pfca.id_card_en " +
" AND pfca.id = 9999 " +
" GROUP BY ca.multiverse_id_en",
nativeQuery = true)
public Page<Card> searchInCollection(Pageable pageable);

So basically, I want the cards in the collection with the number of copies in the collection (hence the count/group by).

The code works but I don't have the "numberInCollection" value since I put the result in a "Card" collection.

So my question is: how can I get the numberInCollection value?

Upvotes: 0

Views: 1655

Answers (1)

Maciej Kowalski
Maciej Kowalski

Reputation: 26572

Try to create a Result Interface:

public static interface CardCountResult{

     Integer getNumberInCollection();   

     Long getId();

     ... // rest of the card fields
  }

that would get implicitly instantiated and populated by Spring Data Jpa.

Then just change the return type:

public Page<CardCountResult> searchInCollection(Pageable pageable);

Make sure aliases match the names in the interface.

Upvotes: 1

Related Questions