9 Replies Latest reply on Nov 15, 2010 8:09 AM by Ori Kremer

    Query using entity type

    Ori Kremer Newbie
      I have the following data model:

      @Entity
      @Inheritance(strategy = InheritanceType.JOINED)
      @DiscriminatorColumn(name = "type", discriminatorType = DiscriminatorType.STRING)
      public abstract class Paymeans {
        ...
      }

      @Entity
      @PrimaryKeyJoinColumn(name = "paymeans_id")
      @Table(name = "CREDIT_CARD")
      @DiscriminatorValue("CreditCard")
      public class CreditCard extends Paymeans {
        ...
      }

      @Entity
      @Table(name = "BANK_ACCOUNT")
      @PrimaryKeyJoinColumn(name = "paymeans_id")
      @DiscriminatorValue("BankAccount")
      public class BankAccount extends Paymeans {
        ...
      }

      @Entity
      public class Person {
        ..
        private Set<Paymeans> paymeans = new HashSet<Paymeans>();
        @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
        @JoinTable(name = "PERSON_PAYMEANS", joinColumns = @JoinColumn(name = "PERSON_ID"), inverseJoinColumns = @JoinColumn(name = "PAYMEANS_ID"))  
        public Set<Paymeans> getPaymeans() {
              return paymeans;
        }
        ..
      }

      I'm trying to query for a specific paymeans type for a person using the discriminator value, something like:
      "select p.paymeans from Person p where p.id=? and p.paymeans.type=BankAccount", but with no luck.

      I'm using Seam 2.2 with JBoss 5.1.

      Any help will be appreciated.

      Thanks,
      Ori

        • 1. Re: Query using entity type
          Leo van den berg Master

          Hi,


          When you use an InheritanceType joined, there is no need for a dicriminator value, because every subclass has its own additional Table.
          You should change you query so that you retrieve the BankAccount for that specfic person. I assume you have a bidirectional mappingm, so something like the following could work (just guessing, b'cause I can't see the rest of your code)




          select b from BankAccount b, Person p where p.paymeans = b





          Leo

          • 2. Re: Query using entity type
            Ori Kremer Newbie

            I actually have a unidirectional mapping, so the suggested query doesn't work.


            Any suggestions?


            • 3. Re: Query using entity type
              Ori Kremer Newbie
              I am not sure this has anything to do with uni/bidirectional mapping...

              A few more details:

              When I try the query above,
              "select c from CreditCard c, Person p where p.paymeans = c"

              The following exception is thrown:
              20:46:49,930 ERROR [STDERR] javax.persistence.PersistenceException: org.hibernate.TypeMismatchException: left and right hand sides of a binary logic operator were   
              incompatibile [CreditCard : java.util.Set(Person.paymeans)]

              When I change it a bit to:
              "select c from CreditCard c, Person p where c in (p.paymeans)"

              The following SQL is constructed:

              20:15:49,260 INFO  [STDOUT] Hibernate:
                  select
                      creditcard0_.paymeans_id as id8_,
                      creditcard0_1_.status as status8_,
                      creditcard0_1_.version as version8_,
                      creditcard0_.billing_address_id as billing9_25_,
                      creditcard0_.card_number as card1_25_,
                      creditcard0_.first_name as first5_25_,
                      creditcard0_.issuer_telephone as issuer6_25_,
                      creditcard0_.last_name as last7_25_
                  from
                      CREDIT_CARD creditcard0_
                  inner join
                      Paymeans creditcard0_1_
                          on creditcard0_.paymeans_id=creditcard0_1_.id,
                      Person Person1_
                  inner join
                      MEMBER Person1_1_
                          on Person1_.member_id=Person1_1_.id,
                      Person_PAYMEANS paymeans2_,
                      Paymeans paymeans3_
                  where
                      Person1_.member_id=paymeans2_.Person_ID
                      and paymeans2_.PAYMEANS_ID=paymeans3_.id
                      and Person1_.member_id=5
                      and (
                          creditcard0_.paymeans_id in (
                              .
                          )
                      )

              and the following exception:
              20:51:31,774 WARN  [JDBCExceptionReporter] SQL Error: 0, SQLState: 42601
              20:51:31,774 ERROR [JDBCExceptionReporter] ERROR: syntax error at or near "."

              I would like the query to add a "paymeans3_.id=creditcard0.paymeans_id" to it somehow.

              How can I do that ?

              Thanks,
              Ori



              • 4. Re: Query using entity type
                Martin Frey Newbie

                Hi,
                your person contains a Set of Paymeans, so this will not work:


                select b from BankAccount b, Person p where p.paymeans = b 



                Try something like :


                select b from BankAccount b, Person p where b in elements(p.paymeans)



                Leo. JOINED inheritance strategy means that there is one table for the complete class tree. However you should almost never need to query explicitly query for the discriminator, Hibernate solves this for you if you query for the specific class. Only if you need bankaccounts and creditcards in the result you could use it. However normaly i solve this by querying paymeans and filter on the java side. The overhead was never that big in my cases.



                I'm quite sure that this question is faster solved in the hibernate forums ;)

                • 5. Re: Query using entity type
                  Martin Frey Newbie

                  Narf...
                  Sorry looks like i missed your last post..


                  • 6. Re: Query using entity type
                    Leo van den berg Master

                    Hi,


                    Although this is really more a subject for the Hibernate forum, a small correction concerning Martin's remark has to be made for all other thread readers.


                    There are different inheritance strategies for Hibernate:



                    (1) Table per concrete class, using the MappedSuperClass 
                    (2) Table per concrete class with UNIONS with the TABLE_PER_CLASS
                    (3) Table per Class Hierarchy annotated with SINGLE_TABLE
                    (4) Table per subclass, which is annotaed JOINED
                    
                    


                    There are also some tricks possible for mixing strategies, but i've never used them.


                    The latter is using a table for - let's say - the superclass and all additional sub-classes are stored in an addtional Table. The JOINED inheritance type has no need for a discriminator, because its class hierarchy is directly related to the Table structure. 


                    My personal addition: If you need a very fast access to your data: Use the third solution, where from the OO-view everything is nicely separated, but from the DB point of view  everything resides in the same table (hence the very fast access).



                    Leo


                    • 7. Re: Query using entity type
                      Martin Frey Newbie

                      Hi Leo


                      Yes you are correct!


                      Looks like i should not post that early anymore or at least have some more coffees before doing so.

                      • 8. Re: Query using entity type
                        Leo van den berg Master

                        Hi,


                        The same for me, because my suggested HQL, was well... :-(


                        Leo

                        • 9. Re: Query using entity type
                          Ori Kremer Newbie
                          "select b from BankAccount b, Person p where b in elements(p.paymeans)" query worked perfectly ! :)

                          Thanks,
                          Ori