0 Replies Latest reply on Nov 15, 2008 11:55 AM by Binesh Gummadi

    SQL question about table per subclass inheritence strategy

    Binesh Gummadi Novice

      jboss-seam-2.1.0.SP1
      Hibernate version:3.3.2


      Project is generated with seam-gen. Following setup works fine but I am confused with the generated SQL.



      I have the following tables.
      -------------------------------
      User
      Patient
      Doctor
      Supervisor





      Respective hibernate objects
      ---------------------------------
      @Entity
      @Table(name = "user")
      @Inheritance(strategy = InheritanceType.JOINED)
      User
      
      @Entity
      @Table(name = "producer")
      Patient extends User
      // id is not defined here.
      
      @Entity
      @Table(name = "producer")
      Doctor extends User
      // id not defined
      
      @Entity
      @Table(name = "producer")
      Supervisor extends User
      // id not defined





      When I load Patient list, I see a join between a patient class and user class which totally makes sense. But when I view User list, I see the following SQL.



          select
              user0_.id as id0_,
              user0_.email as email0_,
              user0_.passwd as passwd0_,
              user0_.first_name as first4_0_,
              user0_.last_name as last5_0_,
              user0_.gender as gender0_,
              user0_.dob as dob0_,
              user0_.phone as phone0_,
              user0_.fax as fax0_,
              user0_1_.contact_name as contact3_1_,
              user0_1_.contact_phone as contact4_1_,
              user0_1_.contact_email as contact5_1_,
              user0_1_.contact_fax as contact6_1_,
              user0_1_.display_order as display11_1_,
              user0_2_.contact_name as contact3_2_,
              user0_2_.contact_phone as contact4_2_,
              user0_2_.contact_email as contact5_2_,
              user0_2_.contact_fax as contact6_2_,
              user0_2_.status as status2_,
              user0_2_.display_order as display11_2_,
              user0_3_.status as status6_,
              user0_3_.contact_name as contact4_6_,
              user0_3_.contact_phone as contact5_6_,
              user0_3_.contact_email as contact6_6_,
              user0_3_.contact_fax as contact7_6_,
              case 
                  when user0_1_.id is not null then 1 
                  when user0_2_.id is not null then 2 
                  when user0_3_.id is not null then 3 
                  when user0_.id is not null then 0 
              end as clazz_ 
          from
              user user0_ 
          left outer join
              doctor user0_1_ 
                  on user0_.id=user0_1_.id 
          left outer join
              patient user0_2_ 
                  on user0_.id=user0_2_.id 
          left outer join
              supervisor user0_3_ 
                  on user0_.id=user0_3_.id limit ?



      Why is it doing a left outer join with doctor, patient and supervisor entities when I load user list? Is this the normal behavior? If it is, what should I do to prevent the joins and only load user list? I guess this makes sense for other inheritance strategies.