How to set a 2nd discriminator for subclasses within a subcl

wilko.vehreke · **Joined:** Mon May 09, 2005 7:31 pm **Posts:** 6

Hibernate version: 3.0

Name and version of the database you are using: MySQL 4

Hi!
I do have a table containing 2 discriminator columns. I have to achieve following class structure

Code:

           A
            |
|-----------------|
B                 C 
               |--------|
              D         E

Question: How can I set the 2nd discriminator for the “inner” subclass
Rgds - Wilko

steve · **Posted:** Mon May 09, 2005 9:17 pm

Use a discriminator formula

wilko.vehreke · **Joined:** Mon May 09, 2005 7:31 pm **Posts:** 6

steve wrote:

Use a discriminator formula

Thanks for your fast reply. For sure it is a solution. But in this case I add additional queries. Unless hibernates dtd provides subclasses within subclasses I am wondering how to use the inner subclasses
rgds wilko

steve · **Posted:** Tue May 10, 2005 3:19 pm

I don't understand.

From the Hibernate testsuite (yes there is a wealth of information in there):

Code:

<class name="Person" discriminator-value="0">

    <discriminator type="int"
        formula="CASE WHEN company is null THEN 0 WHEN company = 'JBoss' THEN 1 ELSE 2 END"/>


    <subclass name="Employee" discriminator-value="1">
    </subclass>

    <subclass name="Customer" discriminator-value="2">
    </subclass>

</class>

And before you ask: yes there is only one column referenced in my particular formula case statement; but its a case statement, it can be whatever you want it to be.

wilko.vehreke · **Joined:** Mon May 09, 2005 7:31 pm **Posts:** 6

Hi Steve,
perhaps it is because of my bad english that I can not spot my question to a clear point. So i try it again

I checked the test stuff and hibernate in action. both could not provide an unswer to this:

Hibernates 3.0.x Mapping DTD enables me to create a Mapping-File like this

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
  <class name="spacelords.domain.Foo" table="foo" catalog="bar">
    <id name="id" type="java.lang.Integer">
      <column name="ID" scale="10" precision="0" not-null="true" unique="true" sql-type="int unsigned" />
      <generator class="native" />
    </id>

    <discriminator column="CATEGORY" type="string"/>

    <subclass name="spacelords.domain.Foofoo" discriminator-value="foofoo">
       <subclass name="spacelords.domain.Foofoofoo" discriminator-value="foofoo">
          <property name="xxx">
             ...
          </property>
       </subclass>
    </subclass>
  </class>
</hibernate-mapping>

So my question are-
- can I set an additional discriminator for the inner subclass. If this is the case how
- if I can't and i have to use queries how is the inner subclass influencing my class structure.

Thx and rgds

Wilko

duffymo · **Joined:** Wed Dec 17, 2003 10:13 am **Posts:** 33

I've been reading "Hibernate In Action", so I completely missed the new <formula> child for the <discriminator> tag. I've been banging my head against the wall trying to do multilevel inheritance without it. When I read this reply today it seemed like a godsend.

However, I just tried using this feature in a test case that I've made up and it failed. The exception I'm getting is "Bad SQL grammar":

Code:

org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [] in task 'Hibernate operation'; nested exception is java.sql.SQLException: Syntax error or access violation,  message from server: "You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'when coverage0_.coverage_type = 'std' and coverage0_.coverage_s"
java.sql.SQLException: Syntax error or access violation,  message from server: "You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'when coverage0_.coverage_type = 'std' and coverage0_.coverage_s"

I'm using MySQL 4.0.17 for my database. I'm running Sun's Java 1.4.2 for my JVM. My app uses Hibernate 3.0.2 and Spring 1.2-RC2.

I'm trying to model a two-level hierarchy with a Coverage interface at the top. There are two abstract classes LTDCoverage and STDCoverage under that, and Standard and NonStandard flavors under each of those.

The COVERAGE table has two VARCHAR(20) columns named COVERAGE_TYPE and COVERAGE_SUBTYPE that are my discriminator.

The entry in the Coverage.hbm.xml looks like this:

Code:

      <discriminator type="string">
         <formula>
            case
                when coverage_type = 'std' and coverage_subtype = 'standard' then 'STANDARD-STD'
                when coverage_type = 'ltd' and coverage_subtype = 'standard' then 'STANDARD-LTD'
                when coverage_type = 'std' and coverage_subtype = 'nonstandard' then 'NON-STANDARD-STD'
                else 'NON-STANDARD-LTD'
            end
         </formula>
      </discriminator>

I've managed to make single inheritance work fine without <formula>.

When I look in the MySQL docs, I notice that there is no case keyword. Is that my problem? I'm not that confident about my SQL here. Hibernate certainly doesn't like it much.

Thanks - %

steve · **Posted:** Tue May 10, 2005 7:28 pm

Well, yeah the database needs to support case statements ;)

I nothing next to nothing about MySQL, so could not say if it does or does not.

duffymo · **Joined:** Wed Dec 17, 2003 10:13 am **Posts:** 33

steve wrote:

Well, yeah the database needs to support case statements ;)

I nothing next to nothing about MySQL, so could not say if it does or does not.

Egg on my face. 8) I mindlessly ran off following an example before checking to see if MySQL supported "case".

I don't believe Oracle does, either, but it does use DECODE. Gotta dig some more. Thanks - %

steve · **Posted:** Wed May 11, 2005 12:36 pm

Oracle has CASE since 9i

duffymo · **Joined:** Wed Dec 17, 2003 10:13 am **Posts:** 33

steve wrote:

Oracle has CASE since 9i

Thanks, Steve.

I just dug a little more and found CASE in MySQL, too. So I have a syntax problem, just like the exception says.

Here's my question: what does <formula> buy me over and above what I can already do with <discriminator>? Sorry for the ignorant question. I know I need to think about this some more, but the little I've done isn't pointing me in the right direction.

Also, will there be a new 3.0 edition of the Manning book? Will the 2.x copy that I have go in the trash? 8(

%

steve · **Posted:** Wed May 11, 2005 1:25 pm

Imagine you have a particular subclass that maps to three different dirscriminator values; you cannot do <subclass discriminator-value="1, 2, 3"/>

Or imagine, like the original question here, if you have a subclass hierarchy where the discrimination is not based on a simple "equality check" from a single column. Look at the Person hierarchy I listed in a previous post. Imagine I wanted to further distinguish Customers into Partners and Customers seperately. Both Partners and Customer have a company (non-null) other than 'JBoss'. The distinction between customer and partner, though, is actually based on another column named CONTRACT_STATUS.

Make sense?

duffymo · **Joined:** Wed Dec 17, 2003 10:13 am **Posts:** 33

steve wrote:

Imagine you have a particular subclass that maps to three different dirscriminator values; you cannot do <subclass discriminator-value="1, 2, 3"/>

Or imagine, like the original question here, if you have a subclass hierarchy where the discrimination is not based on a simple "equality check" from a single column. Look at the Person hierarchy I listed in a previous post. Imagine I wanted to further distinguish Customers into Partners and Customers seperately. Both Partners and Customer have a company (non-null) other than 'JBoss'. The distinction between customer and partner, though, is actually based on another column named CONTRACT_STATUS.

Make sense?

Thanks for your patience, Steve. I'll go through your example more carefully. - %

duffymo · **Joined:** Wed Dec 17, 2003 10:13 am **Posts:** 33

I did some more digging into my <discriminator> problem.

I have the following table in MySQL:

Code:

create table if not exists coverage
(
   coverage_id int unsigned not null auto_increment,
   coverage_type varchar(20) not null,
   coverage_subtype varchar(20) not null;
   plan_id int unsigned not null,
   primary key(coverage_id)
)
type=innodb
min_rows=0
max_rows=1000
pack_keys=default
row_format=default;

I populate it with the following data:

Code:

insert into coverage(coverage_type, coverage_subtype, plan_id) select 'std', 'standard', plan_id from plan where plan_type = 'employee';

insert into coverage(coverage_type, coverage_subtype, plan_id) select 'std', 'nonstandard', plan_id from plan where plan_type = 'dependent';

insert into coverage(coverage_type, coverage_subtype, plan_id) select 'ltd', 'standard', plan_id from plan where plan_type = 'employee';

insert into coverage(coverage_type, coverage_subtype, plan_id) select 'ltd', 'nonstandard', plan_id from plan where plan_type = 'dependent';

I can run the following query in the MySQL query tool and get back good results:

Code:

select 
   case
      when coverage_type = "std" and coverage_subtype = "standard" then "STANDARD-STD"
      when coverage_type = "ltd" and coverage_subtype = "standard" then "STANDARD-LTD"
      when coverage_type = "std" and coverage_subtype = "nonstandard" then "NON-STANDARD-STD"
      when coverage_type = "ltd" and coverage_subtype = "nonstandard" then "NON-STANDARD-LTD"
   end
from 
   coverage
where 
   coverage_id = 1;      

But when I put this <discriminator> into my Hibernate properties:

Code:

   <class   name="Coverage"
            table="COVERAGE"
            polymorphism="implicit"
            discriminator-value="abstract">

      <id name="id" column="COVERAGE_ID" type="long">
         <generator class="native"/>
      </id>

      <discriminator type="string">
         <formula>
            case
                when coverage_type = "std" and coverage_subtype = "standard" then "STANDARD-STD"
                when coverage_type = "ltd" and coverage_subtype = "standard" then "STANDARD-LTD"
                when coverage_type = "std" and coverage_subtype = "nonstandard" then "NON-STANDARD-STD"
                when coverage_type = "ltd" and coverage_subtype = "nonstandard" then "NON-STANDARD-LTD"
            end
         </formula>
      </discriminator>

<!--
      <set name="rates" outer-join="true" inverse="true" cascade="all-delete-orphan">
         <key column="coverage_id"/>
         <one-to-many class="Rate"/>
      </set>

      <one-to-one name="plan" class="Plan" cascade="save-update"/>
-->
      <subclass name="StandardSTDCoverage" discriminator-value="STANDARD-STD"/>
      <subclass name="StandardLTDCoverage" discriminator-value="STANDARD-LTD"/>
      <subclass name="NonStandardSTDCoverage" discriminator-value="NON-STANDARD-STD"/>
      <subclass name="NonStandardLTDCoverage" discriminator-value="NON-STANDARD-LTD"/>

   </class>

I get a SQL syntax exception from Hibernate:

Code:

Hibernate: select coverage0_.COVERAGE_ID as COVERAGE1_3_, 
            coverage0_.case
                when coverage0_.coverage_type = 'std' and coverage0_.coverage_subtype = 'standard' then 'STANDARD-STD'
                when coverage0_.coverage_type = 'ltd' and coverage0_.coverage_subtype = 'standard' then 'STANDARD-LTD'
                when coverage0_.coverage_type = 'std' and coverage0_.coverage_subtype = 'nonstandard' then 'NON-STANDARD-STD'
                else 'NON-STANDARD-LTD'
            coverage0_.end
          as clazz_3_, rates1_.coverage_id as coverage4___, rates1_.RATE_ID as RATE1___, rates1_.RATE_ID as RATE1_0_, rates1_.PERCENTAGE as PERCENTAGE2_0_, rates1_.COVERAGE_ID as COVERAGE3_2_0_, coverage2_.COVERAGE_ID as COVERAGE1_1_, 
            coverage2_.case
                when coverage2_.coverage_type = 'std' and coverage2_.coverage_subtype = 'standard' then 'STANDARD-STD'
                when coverage2_.coverage_type = 'ltd' and coverage2_.coverage_subtype = 'standard' then 'STANDARD-LTD'
                when coverage2_.coverage_type = 'std' and coverage2_.coverage_subtype = 'nonstandard' then 'NON-STANDARD-STD'
                else 'NON-STANDARD-LTD'
            coverage2_.end
          as clazz_1_, plan3_.PLAN_ID as PLAN1_2_, plan3_.PLAN_TYPE as PLAN2_2_ from COVERAGE coverage0_ left outer join RATE rates1_ on coverage0_.COVERAGE_ID=rates1_.coverage_id left outer join COVERAGE coverage2_ on rates1_.COVERAGE_ID=coverage2_.COVERAGE_ID left outer join PLAN plan3_ on coverage0_.COVERAGE_ID=plan3_.PLAN_ID where coverage0_.COVERAGE_ID=?
May 12, 2005 10:57:48 AM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 1064, SQLState: 42000
May 12, 2005 10:57:48 AM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: Syntax error or access violation,  message from server: "You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'when coverage0_.coverage_type = 'std' and coverage0_.coverage_s"
May 12, 2005 10:57:48 AM org.hibernate.event.def.DefaultLoadEventListener onLoad
INFO: Error performing load command
org.hibernate.exception.SQLGrammarException: could not load an entity: [twodeep.model.Coverage#1]

I turned on "show_sql" so I can see the generated SQL. I thought I'd turned off the 1:m relationship with Rate.

I'd be grateful to anyone who can see what I've done wrong with the <formula> SQL. Thanks - %