0

I have a table with an enumerated column named "status". I am implementing an endpoint to get statistics about active and inactive entries. It will return a response like this

{ "activeCount" : 10, "inactiveCount" : 10 }

There are 4 possible status for each entry (active, inactive, awaitingApproval, suspicious). activeCount = amount of entries with active status. inactiveCount = amount of entries with inactive/awaitingApproval/suspicious status.

I am using controller-service-repository pattern and H2 in-memory database. I need this to be as fast as possible. Also assume that this table will hold massive amount of data in the future so getting all entries into memory and calculating the status statistics is not possible.

What are your best practice suggestions?

Thanks for help in advance.

Ahmet
  • 41
  • 1
  • 6
  • 1
    You could create a case statement with a count for each case. https://stackoverflow.com/questions/5295626/hibernate-how-to-count-with-condition – Simon Martinelli Aug 09 '21 at 08:05
  • @SimonMartinelli I understood that SQL solution. Thank you. I'd also like to ask: can I accomplish this with a no-parameter method with custom query in a JPARepository? – Ahmet Aug 09 '21 at 08:48
  • It's not only SQL! It also works with JPQL (as in the example). So yes how does your Entity look like? – Simon Martinelli Aug 09 '21 at 08:57

1 Answers1

1

Just use a query like select e.status, count(*) from Entity e group by e.status. If this is not fast enough for you, you will have to maintain a current count per group somehow in a dedicated table and just query that. That obviously requires you to change the count respectively for every status change or insert/delete. Usually, this can be done by using triggers.

Christian Beikov
  • 15,141
  • 2
  • 32
  • 58