Spring Data JPA custom count query not showing all records

Question

The query is not returning all the records, i.e all the records whose count is same out of them only of is being returned. Where as the same code of MYSQL workbench works like a charm

JPA Custom Query

public interface BookingRepository extends JpaRepository<Booking, Long> {

    @Query("select count(v.source), concat(v.source,'-', v.destination) as bus_route from Booking v group by v.source, v.destination")
    public List<Object[]> groupByBus();
}

Query in MYSQL

SELECT count(source), concat(source," - ", destination) as bus_route
FROM booking
GROUP BY source, destination;

As you can see there are two records with count of one, but only one is being returned by Spring data jpa

Just change the return type from List<Object[]> to List<Map<String, Object>> — GnanaJeyam
– GnanaJeyam, Commented Nov 18, 2020 at 19:05

J.F. · Accepted Answer · 2020-11-21 00:18:29Z

1

Your query return a List<Object[]> but the object array could be almost everything.

Actually, Object[] contains, for each position, another Object[] with two values: count and bus_route.

You can iterate over every value in this way (I've tested and I've needed BigInteger to cast the object value):

Map<BigInteger,String> map = new HashMap<BigInteger,String>();
for(Object object[] : objectList) map.put((BigInteger)object[0], (String)object[1]);

And you will get the map you want.

Also, if there could be repeated values, only create a new list instead of Map.

answered Nov 21, 2020 at 0:18

J.F.

15.3k10 gold badges41 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Spring Data JPA custom count query not showing all records

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related