Query using entity type

[orik]

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

[lvdberg]

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

[orik]

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

Any suggestions?

[orik]

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

[antibrumm.mfrey0.bluewin.ch]

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 ;)

[antibrumm.mfrey0.bluewin.ch]

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

[lvdberg]

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

[antibrumm.mfrey0.bluewin.ch]

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.

[lvdberg]

Hi,

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

Leo

[orik]

"select b from BankAccount b, Person p where b in elements(p.paymeans)" query worked perfectly ! :)

Thanks,
Ori