How to add sql count to Map?

Question

MySQL

Task: read books by genre and bring them out.
I can't count them correctly. At first I tried to just count the number, but here is the problem.

public List<Integer> task4() {

        Session session = this.sessionFactory.getCurrentSession();

        String sql = "SELECT book.genre, COUNT(book.genre) AS counter FROM book book  GROUP BY genre";
        SQLQuery query = session.createSQLQuery(sql);
        query.addEntity(Book.class);
       // List<Integer> task4List = session.createQuery("SELECT book.genre, COUNT(book.genre) AS counter FROM book  GROUP BY genre")
        //        .list();

        List<Integer>  task4List = query.list();

        //Map<Integer,String> task4List  = (Map<counter,genre >).
        return task4List;

    }

Thought to use Mapu as it is perfect for UTB.
How to correctly count books by genre on request?
Suitable criteria, hql or pure sql.

Answer 1

The key question is - for what purpose are you interested?
There are many options, offhand:

1. Make a separate table with genres, and triggers to serve counters in it (by analogy, tag functions do). Triggers can also be outside the DBMS - write a couple of functions that will add, increment and decrement
   
2. Select everything and count by hand. The result can be cached
   
3. Select one genre at a time and return the size of the result
   

Answer 2

Something like this? It's in jpql

@Query("SELECT COUNT(b) FROM Book b WHERE b.genre = ?1")
Long booksCount(String genre);