Hibernate count on superclass with discriminator

In Hibernate we can use discriminators to subclass from a common class. This is very helpful if we want to map common fields to different tables in the database.

For example, I have an abstract class called Gene. Now all organisms will have Genes, but if we want to differentiate the Gene based on the organism, we subclass Gene to make MouseGene and ZebrafishGene.

In the hibernate O/R mapping file we can have the following:

  <class name="au.edu.apf.phenomebank.strain.Gene" abstract="true" table="genes">
      
          <id name="id" type="java.lang.Long">
            <generator class="increment"/>
        </id>
      
        <!-- DISCRIMINATOR MUST COME IMMEDIATELY AFTER ID ELEMENT. AND SUBCLASS ELEMENTS MUST BE AT THE END !!! -->
        <discriminator column="gene_class" type="string" />      
      
          <property name="geneName" column="affected_gene_name" type="text" />
        <property name="symbol" column="affected_gene_symbol" type="text" />
        <property name="synonyms" type="text" />
        <property name="alleleName" column="allele_name" type="text" />
        <property name="alleleSymbol" column="allele_symbol" type="text" />
        <property name="alleleSynonyms" column="allele_synonyms" type="text" />
        
        <property name="proteinExpression" column="protein_expression" type="au.edu.apf.phenomebank.db.ProteinExpressionUserType" />
        <property name="microsatelliteMarkers" column="microsatellite_markers" type="text" />
        <property name="ensembl" column="ensembl" type="text" />
        <many-to-one name="mutationSequence" column="mutation_sequence_id" cascade="save-update" />
        <property name="geneticAlteration" column="genetic_alteration" type="text" />
        
        <property name="chromosomeLocation" column="chromosome_location" type="text"  />
        <property  name="locationOnChr" column="location_on_chr" type="text" />
        
        <!-- ALL SUBCLASS ELEMENTS MUST BE AFTER PROPERTY ELEMENTS. ORDERING IS VERY IMPORTANT !!! --> 
        <subclass name="au.edu.apf.phenomebank.db.mouse.MouseGene" discriminator-value="mouse">
            <property name="mgiGeneAccessionId" column="MGI_gene_accession_id" type="text" />
            <property name="mgiGeneAccessionIdUrl" column="MGI_gene_accession_id_url" type="text" />
            <property name="mgiAlleleAccessionId" column="MGI_allele_accession_id" type="text" />
            <property name="mgiAlleleAccessionIdUrl" column="MGI_allele_accession_id_url" type="text" />    
        </subclass>         
        
  </class>


  <class name="au.edu.apf.phenomebank.strain.ZebrafishGene" table="zebrafish_genes">
      
          <id name="id" type="java.lang.Long">
            <generator class="increment"/>
        </id>
      
        <!-- DISCRIMINATOR MUST COME IMMEDIATELY AFTER ID ELEMENT. AND SUBCLASS ELEMENTS MUST BE AT THE END !!! -->
        <discriminator column="gene_class" type="string" />      
      
          <property name="geneName" column="affected_gene_name" type="text" />
        <property name="symbol" column="affected_gene_symbol" type="text" />
        <property name="synonyms" type="text" />
        <property name="alleleName" column="allele_name" type="text" />
        <property name="alleleSymbol" column="allele_symbol" type="text" />
        <property name="alleleSynonyms" column="allele_synonyms" type="text" />
        
        <property name="proteinExpression" column="protein_expression" type="au.edu.apf.phenomebank.db.ProteinExpressionUserType" />
        <property name="microsatelliteMarkers" column="microsatellite_markers" type="text" />
        <property name="ensembl" column="ensembl" type="text" />
        <many-to-one name="mutationSequence" column="mutation_sequence_id" cascade="save-update" />
        <property name="geneticAlteration" column="genetic_alteration" type="text" />
        
        <property name="chromosomeLocation" column="chromosome_location" type="text"  />
        <property  name="locationOnChr" column="location_on_chr" type="text" />
        
        <!-- ALL SUBCLASS ELEMENTS MUST BE AFTER PROPERTY ELEMENTS. ORDERING IS VERY IMPORTANT !!! --> 
        <subclass name="au.edu.apf.phenomebank.db.zebrafish.ZebrafishGene" discriminator-value="zebrafish">
            <property name="zebrafishProperty" column="MGI_gene_accession_id" type="text" />
        </subclass>         
        
  </class>    

Now in Hibernate, if you performed a count using the following HQL call:

String sql = "SELECT COUNT(DISTINCT g.geneName) FROM "+Gene.class.getSimpleName()+ " g"; 

List list = getHibernateTemplate().find(sql); 

This will actually return a count of all the distinct gene names from both MouseGene table and Zebrafish Gene table where each column of the returned result represents a count from each table. This is because we are searching based on the abstract class Gene, and Hibernate is smart enough to search all subclasses.

Now if you want to only search on the one table then you have to specify the subclass as follows:

String sql = "SELECT COUNT(DISTINCT g.geneName) FROM "+MouseGene.class.getSimpleName()+ " g";