[hibernate-dev] Facing Problem while Implementing Nested Selects (Count(*)) with CriteriaBuilder

[sudipta deb]

Hi Guys,

I am facing an issue in implementing the below query with Hibernate
Criteria Query. Can you please help me in implementing the same?

The Query is:

SELECT outCountry.COUNTRY_ID, outCountry.COUNTRY_DESCRIPTION,
outCountry.COUNTRY_TENANT,
  (SELECT COUNT(*) FROM POPULATION population, LOCALITY locality,
STATE state, COUNTRY inCountry
    WHERE population.LOCALITY_ID = locality.LOCALITY_ID
    AND locality.STATE_ID = state.STATE_ID
    AND state.COUNTRY_ID = inCountry.COUNTRY_ID
    AND inCountry.COUNTRY_ID = outCountry.COUNTRY_ID
    AND population.POPULATION_GENDER = "MALE") as MALE_COUNT,
  (SELECT COUNT(*) FROM POPULATION population, LOCALITY locality,
STATE state, COUNTRY inCountry
    WHERE population.LOCALITY_ID = locality.LOCALITY_ID
    AND locality.STATE_ID = state.STATE_ID
    AND state.COUNTRY_ID = inCountry.COUNTRY_ID
    AND inCountry.COUNTRY_ID = outCountry.COUNTRY_ID
    AND population.POPULATION_GENDER = "FEMALE") as FEMALE_COUNT
FROM COUNTRY outCountry;

The DDL Script is attached along with this email.

I am able to implement this with Native Query, but my requirement is to do
this with CriteriaBuilder.

I was checking in Google and found out in few pages that sub select/nested
select with count is not possible with Criteria. But there was no concrete
reason why this is not possible. Could you please help me in this regard?

Any help is highly appreciated.
With regards
Sudipta Deb.
-------------- next part --------------
CREATE TABLE COUNTRY(
  COUNTRY_ID INT NOT NULL AUTO_INCREMENT,
  COUNTRY_DESCRIPTION VARCHAR(40) NOT NULL,
  COUNTRY_TENANT VARCHAR(4) NOT NULL,
  PRIMARY KEY ( COUNTRY_ID )
);

CREATE TABLE STATE(
  STATE_ID INT NOT NULL AUTO_INCREMENT,
  STATE_NAME VARCHAR(40) NOT NULL,
  COUNTRY_ID INT NOT NULL,
  STATE_TENANT VARCHAR(4) NOT NULL,
  PRIMARY KEY(STATE_ID),
  FOREIGN KEY(COUNTRY_ID)
    REFERENCES COUNTRY(COUNTRY_ID)
);

CREATE TABLE LOCALITY(
  LOCALITY_ID INT NOT NULL AUTO_INCREMENT,
  LOCALITY_NAME VARCHAR(40) NOT NULL,
  STATE_ID INT NOT NULL,
  PRIMARY KEY(LOCALITY_ID),
  FOREIGN KEY(STATE_ID)
    REFERENCES STATE(STATE_ID)
);

CREATE TABLE POPULATION(
  POPULATION_ID INT NOT NULL AUTO_INCREMENT,
  POPULATION_NAME VARCHAR(40) NOT NULL,
  POPULATION_GENDER VARCHAR(4) NOT NULL,
  LOCALITY_ID INT NOT NULL,
  PRIMARY KEY(POPULATION_ID),
  FOREIGN KEY(LOCALITY_ID)
    REFERENCES LOCALITY(LOCALITY_ID)
);