Criteria and joined subclass question

alankstewart · **Joined:** Sat Mar 04, 2006 1:07 am **Posts:** 27

I have an employee search form which can take any combination of a number of employee attributes. One of them is the cost centre an employee belongs to.

My Employee class has an abstract CostCentre object. The CostCentre has 2 concrete subclasses, InternalCostCentre and ExternalCostCentre. This is mapped as a table-per-subclass like this:

Code:

<class name="au.com.woolworths.hrportal.costcentre.CostCentre" table="PYRL_COST_CNTR">
...

      <joined-subclass name="au.com.woolworths.hrportal.costcentre.InternalCostCentre" table="INTL_COST_CNTR">
         <key column="PYRL_COST_CNTR_ID" />
         <property name="costCentreNumber" column="COST_CNTR_NO" access="field" />
         <property name="name" column="COST_CNTR_NM" access="field" />
      </joined-subclass>
      <joined-subclass name="au.com.woolworths.hrportal.costcentre.ExternalCostCentre" table="EXTL_COST_CNTR">
         <key column="PYRL_COST_CNTR_ID" />
         <property name="costCentreNumber" column="EXTL_COST_CNTR_NO" access="field" />
         <property name="name" column="EXTL_COST_CNTR_NM" access="field" />
      </joined-subclass>

Employee has a mapping like this:

Code:

<class name="au.com.woolworths.hrportal.employee.Employee" table="EMP">
...
   <many-to-one name="costCentre" class="au.com.woolworths.hrportal.costcentre.CostCentre" column="PYRL_COST_CNTR_ID" access="field" />
...

The costCentreNumber is an attribute of the parent class and I want to be able to search on costCentreNumber, but the search needs to cater for the fact that the type of cost centre (whether internal or external) is unknown. I tried this

Code:

Criteria criteria = getSession().createCriteria(Employee.class);
Criteria costCentreCriteria = criteria.createCriteria("costCentre");
DetachedCriteria internal = DetachedCriteria.forClass(InternalCostCentre.class);
internal.add(Restrictions.eq(("costCentreNumber"), employee.getCostCentre().getCostCentreNumber()));
DetachedCriteria external = DetachedCriteria.forClass(ExternalCostCentre.class);
external.add(Restrictions.eq(("costCentreNumber"), employee.getCostCentre().getCostCentreNumber()));
costCentreCriteria.add(Subqueries.exists(internal)).add(Subqueries.exists(external));   

I get a ClassCastException. I'm probably completely off the mark here, but have tried a number of different things, without success.
Any suggestions?
Thanks
Alan

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Check out table 9.1, "Features of inheritance mappings", in the ref docs. The intersection of "table per subclass" and "Polymorphic queries" shows that to do a polymorphic query, you query on the common superclass. And seeing as your mapping from Employee to costCentre already specifies the common superclass, you don't have to do anything.

I think you're trying to find all employees in a given cost centre, determined by cost centre number (you don't say in your question). So you want something like this:

Code:

Criteria crit = getSession().createCriteria(Employee.class);
crit.createAlias("costCentre", "cc");
crit.add(Restrictions.eq("cc.costCentreNumber", employee.getCostCentre().getCostCentreNumber()));
return crit.list();

alankstewart · **Joined:** Sat Mar 04, 2006 1:07 am **Posts:** 27

Thanks tenwit for your response. Doing exactly as you say, I get an

Code:

org.hibernate.exception.SQLGrammarException: could not execute query

with the folloiwng cause:

Code:

Caused by: java.sql.SQLException: ORA-00904: "CC1_1_"."EXTL_COST_CNTR_NO": invalid identifier

EXTL_COST_CNTR_NO stores the costCentreNumber in the external cost centre table, whereas COST_CNTR_NO is the name of the column in the internal cost centre table. Neither of these columns exist in the parent table.
Only common fields exist in the parent table like, state, town etc.
Some more insight from you would be much appreciated!
Thanks again
Alan

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Ah yes, didn't notice that before.

This is a known disadvantage to table per concrete class mappings. The way around it is to put a cost centre number mapping in the CostCentre mapping, using the <any> tag. If you have HIA, it's described in some detail in section 6.4.3. The refdocs have a brief explanation in section 5.1.22.

I've never used this myself, so I won't risk explaining this from what I've read. I've done that before and wasted so much of other people's time :)

alankstewart · **Joined:** Sat Mar 04, 2006 1:07 am **Posts:** 27

I'm using table per subclass, as per section 3.6.3 in HIA - not table per concrete class The common attributes of the CostCentre are stored in the PYRL_COST_CNTR table and the costCentreNumber is stored in each of the subclass tables, so I don't think <any> applies here. There is a foreign-key from each of the 2 child tables pointing to the PK of the parent table, that's why I use a joined-subclass element to indicate a table-per-subclass mapping

Alan

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

I think that the <any> mapping is appropriate. It's used to say that some other mapping will supply the actual classes, all we know at this point is the type and name. Isn't that exactly what you need? If the CostCentre mapping used <any> to require that all <joined-subclass> elements will provide a CostCentreNumber mapping, queries based on CostCentre, like the one you want to write, will be able to refer to CostCentreNumber. This means that hibernate can parse the associationPath in your Criteria (using the <any>), and can create the SQL from the Criteria (using the <joined-subclass> mappings).

Then the Criteria query that I wrote earlier will work.

alankstewart · **Joined:** Sat Mar 04, 2006 1:07 am **Posts:** 27

It seems like I'm close, but am mssing something.

Here's the full mapping of CostCentre. I'm not sure what to put in the any element. I do already have a field called PYRL_COST_CNTR_TYPE_IND in the parent that is used as a discriminator (though not as a Hibernate <discriminator> element), which has the values of P or E. Also, the internal cost centre class is called PrecedaCostCentre

Code:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" 
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping auto-import="true" default-lazy="false">
   <class name="au.com.woolworths.hrportal.costcentre.CostCentre" table="PYRL_COST_CNTR">
      <id name="id" column="PYRL_COST_CNTR_ID" access="field">
         <generator class="sequence">
            <param name="sequence">PYRL_COST_CNTR_SEQ</param>
         </generator>
      </id>
      <version name="version" column="ROW_VER_NO" access="field" />
      <!-- property name="payrollType" column="PYRL_COST_CNTR_TYPE_IND" type="au.com.woolworths.hrportal.common.component.PayrollEnumType" access="field" / -->
      <property name="town" column="TOWN_NM" />
      <property name="state" column="ST_ABRV_CD" type="au.com.woolworths.hrportal.common.component.StateEnumType" />
      <property name="contactName" column="CNTCT_NM" />
      <property name="systemSourceCode" column="LKUP_SYS_SRC_ID" type="au.com.woolworths.hrportal.common.component.SystemSourceCodeEnumType" access="field" />
      <any name="costCentreNumber" id-type="string" cascade="save-update" access="field">
         <meta-value value="P" class="au.com.woolworths.hrportal.costcentre.PrecedaCostCentre" />
         <meta-value value="E" class="au.com.woolworths.hrportal.costcentre.ExternalCostCentre" />
         <column name="PYRL_COST_CNTR_TYPE_IND" />
         <column name="COST_CNTR_NO" />
      </any>
      <component name="auditDetails" access="field">
         <property name="dateCreate" column="CRE_DTETM" update="false" access="field" />
         <property name="dateChange" column="UPD_DTETM" access="field" />
         <property name="userIdCreate" column="CRE_USR_ID" update="false" access="field" />
         <property name="userIdChange" column="UPD_USR_ID" access="field" />
      </component>
      <joined-subclass name="au.com.woolworths.hrportal.costcentre.PrecedaCostCentre" table="PRECEDA_COST_CNTR">
         <key column="PYRL_COST_CNTR_ID" />
         <property name="costCentreNumber" column="COST_CNTR_NO" access="field" />
         <property name="name" column="COST_CNTR_NM" access="field" />
      </joined-subclass>
      <joined-subclass name="au.com.woolworths.hrportal.costcentre.ExternalCostCentre" table="EXTL_COST_CNTR">
         <key column="PYRL_COST_CNTR_ID" />
         <property name="costCentreNumber" column="EXTL_COST_CNTR_NO" access="field" />
         <property name="name" column="EXTL_COST_CNTR_NM" access="field" />
      </joined-subclass>
   </class>
   <query name="findPrecedaCostCentre">
      <![CDATA[from PrecedaCostCentre where costCentreNumber = ?]]>
   </query>
   <query name="findExternalCostCentre">
      <![CDATA[from ExternalCostCentre where costCentreNumber = ?]]>
   </query>
</hibernate-mapping>

I'm getting now:

Code:

org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not execute query; bad SQL grammar [select precedacos0_.pyrl_cost_cntr_id as pyrl1_0_, precedacos0_1_.row_ver_no as row2_0_, precedacos0_1_.town_nm as town3_0_, precedacos0_1_.st_abrv_cd as st4_0_, precedacos0_1_.cntct_nm as cntct5_0_, precedacos0_1_.lkup_sys_src_id as lkup6_0_, precedacos0_1_.pyrl_cost_cntr_type_ind as pyrl7_0_, precedacos0_1_.cost_cntr_no as cost8_0_, precedacos0_1_.cre_dtetm as cre9_0_, precedacos0_1_.upd_dtetm as upd10_0_, precedacos0_1_.cre_usr_id as cre11_0_, precedacos0_1_.upd_usr_id as upd12_0_, precedacos0_.cost_cntr_no as cost2_1_, precedacos0_.cost_cntr_nm as cost3_1_ from preceda_cost_cntr precedacos0_, pyrl_cost_cntr precedacos0_1_ where precedacos0_.pyrl_cost_cntr_id=precedacos0_1_.pyrl_cost_cntr_id and precedacos0_1_.cost_cntr_no=?]; nested exception is java.sql.SQLException: ORA-00904: "PRECEDACOS0_1_"."COST_CNTR_NO": invalid identifier

java.sql.SQLException: ORA-00904: "PRECEDACOS0_1_"."COST_CNTR_NO": invalid identifier

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

In the <any> mapping, all columns after the first represent the identifier of the row, not the column with the relevant value. So I think that's PYRL_COST_CNTR_ID in your case.

alankstewart · **Joined:** Sat Mar 04, 2006 1:07 am **Posts:** 27

According to P238 and 239 of HIA, you are correct, however, now I get a Repeated column in mapping error as the PYRL_COST_CNTR_ID is also mapped as the <id> element (it is the PK) . I made need an additional column for the <any> id like in Figure 6.14 on P239. I can't however add another column now. There's got to be something else or another way
Alan

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

In cases like that, I always experiment with <formula>. Usually you can accomplish the same thing, though I don't know if <any> accepts <formula> in place of <column>.

The alternative in this case is to make use of your discriminator, now that you have one. Use <subclass> with a nested <join> tag: see section 9.1.3 of the ref docs.

alankstewart · **Joined:** Sat Mar 04, 2006 1:07 am **Posts:** 27

Tried with the discriminator and the <subclass> <join table .../> but get the same error as before:

Code:

2006-06-08 16:00:52,930 [3625    ] [main] ERROR org.hibernate.util.JDBCExceptionReporter (logExceptions) - ORA-00904: "CC1_1_"."EXTL_COST_CNTR_NO": invalid identifier

I can do the select with native sql like this:

Code:

SELECT em.* FROM ww_sdc.EMP em  JOIN ww_sdc.PRECEDA_COST_CNTR p ON em.PYRL_COST_CNTR_ID = p.PYRL_COST_CNTR_ID  AND p.COST_CNTR_NO = '1' UNION SELECT em.* FROM EMP em  JOIN  ww_sdc.EXTL_COST_CNTR e  ON em.PYRL_COST_CNTR_ID = e.PYRL_COST_CNTR_ID  AND e.EXTL_COST_CNTR_NO = '1' 

which works fine, but I was hoping to use the Critreia API, as I have other fields, like employee name etc that I can add to the criteria to restrict the search further.
I've wasted almost a day on this!!
Alan

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

If the hibernate-generated SQL is very different but the resulting error is the same, could it be that the error is actually coming from somewhere else? Perhaps a reference from another entity to this entity? Some other reference to this entity may have a mistake, like transposed <key> and <column> values in a collection mapping, or something...

Hibernate will occasionally eat a whole day of your time, there's no way around that (unless you're paying for support, I guess..). On the plus side, after you've had about a month eaten up this way, you'll be able to earn oodles of credits on this forum :)

alankstewart · **Joined:** Sat Mar 04, 2006 1:07 am **Posts:** 27

Quote:

If the hibernate-generated SQL is very different but the resulting error is the same, could it be that the error is actually coming from somewhere else? Perhaps a reference from another entity to this entity? Some other reference to this entity may have a mistake, like transposed <key> and <column> values in a collection mapping, or something...

I think you are right. It's a new day here in Sydney and I'm still working on it.
The native sql works fine in Oracle, but I even now I'm having problems transposing it into something acceptable for the sqlRestriction. If I simply prefix it with "exists (select ... )" it doesn't give any errors but it also doesn't filter anything.

According to table 9.1 as you ponited out, it should work with the criteria code you provided earlier.

Alan

alankstewart · **Joined:** Sat Mar 04, 2006 1:07 am **Posts:** 27

tenwit - I gave you another credit for the work you have done on this so far

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

We need to be able to trade credits. What am I going to do with 235 credits? Can I trade them in for a better icon under my name? Maybe an acknowledgement in the next edition of HIA.

I wonder if there's a charity out there that accepts forum credits..

;-)