Nested SQL in @Query tag is not working properly

Question

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!

https://stackoverflow.com/questions/9743011/springdata-is-it-possible-to-have-subqueries-in-the-query-annotation — ShadyBears, Jul 19 '19 at 16:33
Side note - possibly better query: `SELECT account_role, COUNT(*) FROM account WHERE account_role IN ('USER', 'ADMIN', 'MASTER') GROUP BY account_role` (although you'd need to return a different type from your method) — Clockwork-Muse, Jul 19 '19 at 18:28

Oleksii Valuiskyi · Answer 1 · 2019-07-19T20:23:04.103

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:

create Pair<K,V> class with constructor Pair(K key, V value) or use it from any external library
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();
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()));

Corey · Accepted Answer · 2019-07-19T17:45:12.257

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

Nested SQL in @Query tag is not working properly

2 Answers2