Multiple Nested Select Query in Hibernate

Question

I am kind of new to Hibernate. I have to implement the below query with Hibernate. Could you please help me how to solve that? I was searching in Google, but not able to find the solution. May be I was looking into wrong section.

SELECT s.name state_name,
       (SELECT count (*)
          FROM male m
         WHERE     m.id = s.id male,
       (SELECT count (*)
          FROM female f
         WHERE    f.id = s.id female
  FROM state s;

I am able to find the count separately, but the problem is that I am not able to add them all to the single query.

The query should find me the below result:

state_name    male    female
ABCD          10      5
MNOP          7       15

Any help is highly appreciated. Thanks.

Below is my approach:

public void populateCountForStateByGender() {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<PopulationCount> query = criteriaBuilder.createQuery(PopulationCount.class);

        query = getQueryWithSummaryColumns(query, criteriaBuilder);

        TypedQuery<PopulationCount> typedQuery = entityManager.createQuery(query);
        List<PopulationCount> rows = typedQuery.getResultList();

    }

private <T> CriteriaQuery<T> getQueryWithSummaryColumns(CriteriaQuery<T> query, CriteriaBuilder cb) {
    Root<State> root = query.from(State.class);

    Root<Population> populationOccurrence = query.from(Population.class);


    Expression<Short> statusOfOccurrence = errorOccurrence.get(Population_.gender);

    LinkedList<Selection<? extends Object>> columns = new LinkedList<Selection<? extends Object>>();

    Predicate correlatePredicate = cb.equal(
        populationOccurrence.get(Population_.stateId), root);

    columns.add(root.get(State_.id));
    columns.add(cb.count((cb.selectCase().when(
        cb.equal(statusOfOccurrence, GenderStatus.MALE.getCodeValue()), 1L))));
    columns.add(cb.count((cb.selectCase().when(
        cb.equal(statusOfOccurrence, GenderStatus.FEMALE.getCodeValue()), 1L))));

    query.where(correlatePredicate);
    query.multiselect(columns);

    return query;
}

Now TypedQuery<PopulationCount> typedQuery = entityManager.createQuery(query); is giving me NullPointerException.

Just for Information: Population & State are the Entity Classes And PopulationCount is a POJO with Getters and Setters to store the result

Answer 1

One approach is to use createSQLQuery:

String sql = //.. your sql as per above
List result = session.createSQLQuery(sql).addEntity(StateSummary.class).list();

This is assuming you have a StateSummary class to map the generated resultset, something like this:

@Entity
public class StateSummary {
  private String state_name;
  private int male;
  private int female;
  // getters & setters..
}