CUBA Platform. Displaying Charts And Maps
1.4. Example of Working with Charts

One of the methods of the service will return a list of non-persistent entities, therefore let us start by creating and registering a class for this entity.

Create the com.sample.library.entity class in the global BooksByGenre package of the global module:

package com.sample.library.entity;

import com.haulmont.chile.core.annotations.MetaClass;
import com.haulmont.chile.core.annotations.MetaProperty;
import com.haulmont.cuba.core.entity.AbstractNotPersistentEntity;

@MetaClass(name = "library$BooksByGenre")
public class BooksByGenre extends AbstractNotPersistentEntity {

    @MetaProperty
    private String genre;

    @MetaProperty
    private Long countOfBooks;

    public String getGenre() {
        return genre;
    }

    public void setGenre(String genre) {
        this.genre = genre;
    }

    public Long getCountOfBooks() {
        return countOfBooks;
    }

    public void setCountOfBooks(Long countOfBooks) {
        this.countOfBooks = countOfBooks;
    }
}

An instance of this class contains the number of books of a certain genre. Then, we should register the entity class in the metadata.xml file of the global module:

<metadata-model root-package="com.sample.library">
        <class>com.sample.library.entity.BooksByGenre</class>
    </metadata-model>

An interface and a service class can be created using CUBA Studio. For this, open the Services tab of the navigation panel and click New. In a window that opens, fill in the Interface field with the following value: com.sample.library.service.StatisticsService. Bean and Service name fields will be updated with suitable values of com.sample.library.service.StatisticsServiceBean and library_StatisticsService respectively. Save changes.

Open the interface of the StatisticsService service in IDE and declare the methods retrieving data from the database:

package com.sample.library.service;

import com.sample.library.entity.BooksByGenre;
import java.util.List;
import java.util.Map;

public interface StatisticsService {
    String NAME = "library_StatisticsService";

    public List<BooksByGenre> getCountOfBooksByGenre();

    public List<String> getTopPublishers(int count);

    public Map<Integer, Map<String, Long>> getCountOfBooksByPublisherAndYear();
}

Then, open the StatisticsServiceBean service class and replace its content with the following code:

package com.sample.library.service;

import com.google.common.collect.Lists;
import com.haulmont.cuba.core.EntityManager;
import com.haulmont.cuba.core.Persistence;
import com.haulmont.cuba.core.Transaction;
import com.sample.library.entity.BooksByGenre;
import org.springframework.stereotype.Service;

import javax.inject.Inject;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

@Service(StatisticsService.NAME)
public class StatisticsServiceBean implements StatisticsService {

    @Inject
    private Persistence persistence;


    @Override
    public List<BooksByGenre> getCountOfBooksByGenre() {
        List<BooksByGenre> result = new ArrayList<>();
        String query = "select ltype.name, count(book) " +
                       "from library$Book book join book.literatureType ltype " +
                       "group by ltype.name order by ltype.name";
        Transaction transaction = persistence.createTransaction();
        try {
            EntityManager em = persistence.getEntityManager();
            List<Object[]> resultList = em.createQuery(query, Object[].class).getResultList();

            for (Object[] row : resultList) {
                BooksByGenre entity = new BooksByGenre();
                entity.setGenre((String) row[0]);
                entity.setCountOfBooks((Long) row[1]);
                result.add(entity);
            }
        } finally {
            transaction.end();
        }

        return result;
    }

    @Override
    public List<String> getTopPublishers(final int count) {
        List<String> result = Lists.newArrayList();
        String query = "select instance.bookPublication.publisher.name, count(instance) " +
                "from library$BookInstance instance " +
                "group by instance.bookPublication.publisher.name order by count(instance) desc";
        Transaction transaction = persistence.createTransaction();
        try {
            EntityManager em = persistence.getEntityManager();
            List resultList = em.createQuery(query).getResultList();

            for (int i = 0; i < resultList.size(); i++) {
                if (i == count) {
                    break;
                }
                Object[] data = (Object[]) resultList.get(i);
                result.add((String) data[0]);
            }
        } finally {
            transaction.end();
        }

        return result;
    }

    @Override
    public Map<Integer, Map<String, Long>> getCountOfBooksByPublisherAndYear() {
        Map<Integer, Map<String, Long>> result = new LinkedHashMap<>();
        String query = "select instance.bookPublication.publisher.name, instance.bookPublication.year, count(instance) " +
                "from library$BookInstance instance " +
                "group by instance.bookPublication.year, instance.bookPublication.publisher.name " +
                "order by instance.bookPublication.year, instance.bookPublication.publisher.name";
        Transaction transaction = persistence.createTransaction();
        try {
            EntityManager em = persistence.getEntityManager();
            List resultList = em.createQuery(query).getResultList();

            for (Object row : resultList) {
                Object[] data = (Object[]) row;

                String publisher = (String) data[0];
                Integer year = (Integer) data[1];
                Long quantity = (Long) data[2];

                if (result.get(year) == null) {
                    result.put(year, new LinkedHashMap<String, Long>());
                }
                result.get(year).put(publisher, quantity);
            }
        } finally {
            transaction.end();
        }

        return result;
    }
}

The getCountOfBooksByGenre() method returns the number of books of every genre as a list of BooksByGenre entities. The getTopPublishers(int count) method sorts publishers by the number of issued books available in the library in descending order, and returns the first count of publishers. The getCountOfBooksByPublisherAndYear() method returns the number of books issued by publishers in a given year.