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!

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
– Clockwork-Muse, Commented Jul 19, 2019 at 18:28

Oleksii Valuiskyi · Accepted Answer · 2019-07-19 20:23:04Z

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-19 17:45:12Z

0

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

edited Jul 19, 2019 at 17:45

answered Jul 19, 2019 at 17:33

Corey

1521 gold badge3 silver badges11 bronze badges

Collectives™ on Stack Overflow

Nested SQL in @Query tag is not working properly

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related