CODEWITHSUNDEEP
javasqlhibernatespring-data-jpajpql
KiddoV
KiddoV

Reputation: 670

Nested SQL in @Query tag is not working properly

Suppose I have a table which contains all the accounts of user and type. I want to make a Jpa Repository method which returns an array of total number of each type of user (USER, ADMIN, MASTER).

Here is how I did it in JpaRepository:

@Query(value="SELECT   (SELECT COUNT(*) FROM account WHERE account_role='USER'),"
                        + "(SELECT COUNT(*) FROM account WHERE account_role='ADMIN'),"
                        + "(SELECT COUNT(*) FROM account WHERE account_role='MASTER')"
                        + "FROM account LIMIT 1",
            nativeQuery=true)
public List<Integer> getTotalAccountType();

The code executed fine, but the result wasn't what I expected.

Result: [2]

Expected result: [2,10,30]

Any idea how would I use nested SQL with JPQL? Thank you in advance!

Upvotes: 1

Views: 319

Answers (2)

Oleksii Valuiskyi
Oleksii Valuiskyi

Reputation: 2851

If repository method returns List of Integers it means that query result row contains an Integer value. But you expect to get sequence of Integers in one row.

You can get same result different way:

@Query(value="SELECT COUNT(*) FROM account WHERE account_role=?", nativeQuery=true)
public Integer getTotalAccountType(String role);

and then:

Integer userCount = repository.getTotalAccountType("USER");
Integer adminCount = repository.getTotalAccountType("ADMIN");
Integer masterCount = repository.getTotalAccountType("MASTER");

or if you have mapped entity:

  1. create Pair<K,V> class with constructor Pair(K key, V value) or use it from any external library

  2. repository method based on hql query

    @Query(value="select new javafx.util.Pair(a.accountRole, count(a)) from Account a group by a.accountRole") public List<Pair<String, Integer>> getRoleCountList();

  3. convert repository result to a Map<String, Integer> in service

    javafx.util.Pair<String, Integer> result = repository.getRoleCountList(); Map<String, Integer> map = result.stream().collect(Collectors.toMap(r-> r.getKey(), r-> r.getValue()));

Upvotes: 2

Corey
Corey

Reputation: 152

Try returning Object[] rather than a List<Integer>. I think returning List<Integer> would indicate multiple rows of an Integer value are being returned, whereas you're getting back one row with multiple Intger columns.

From the resulting Object[] you would pull out the first value (indicating a row). This should be another Object[], which will have your values in the order returned.

You can also remove that last "FROM account LIMIT 1" line, as it has no bearing on the result.

I would recommend casting all of this to an object though. As seen here - How to return a custom object from a Spring Data JPA GROUP BY query

Upvotes: 0

Related Questions