Hibernate polymorphic query

Question

I have two classes, Person and Company, derived from another class Contact. They are represented as polymorphically in two tables (Person and Company). The simplified classes look like this:

public abstract class Contact {

  Integer id;

  public abstract String getDisplayName();

}

public class Person extends Contact {

  String firstName;
  String lastName;

  public String getDisplayName() {
    return firstName + " " + lastName;
  }

}

public class Company extends Contact {

  String name;

  public String getDisplayName() {
    return name;
  }

}

The problem is that I need to make a query finding all contacts with displayName containing a certain string. I can't make the query using displayName because it is not part of either table. Any ideas on how to do this query?

Answer 1

You could create a new column in the Contact table containing the respective displayName, which you could fill via a Hibernate Interceptor so it would always contain the right string automatically.

The alternative would be having two queries, one for the Person and one for the Company table, each containing the respective search logic. You may have to use native queries to achieve looking for a concatenated string via a LIKE query (I'm not a HQL expert, though, it may well be possible).

If you have large tables, you should alternatively think about full-text indexing, as LIKE '%...%' queries require a full table scan unless your database supports full text indexes.

Answer 2

If you change displayName to be a mapped property (set to the name column in Company and to a formula like first||' '||last in Person), then can query for Contract and Hibernate will run two queries both of which now have a displayName. You will get back a List of two Lists, one containing Companies and one containing Persons so you'll have to merge them back together. I think you need to query by the full package name of Contract or set up a typedef to tell Hibernate about it.

Answer 3

Because you do the concatenation in the Java class, there is no way that Hibernate can really help you with this one, sorry. It can simply not see what you are doing in this method, since it is in fact not related to persistence at all.

The solution depends on how you mapped the inheritance of these classes:

If it is table-per-hierarchy you can use this approach: Write a SQL where clause for a criteria query, and then use a case statement:

s.createCriteria(Contact.class)
 .add(Restrictions.sqlRestriction("? = case when type='Person' then firstName || ' '|| lastName else name end"))
 .list();

If it is table per-concrete-subclass, then you are better of writing two queries (since that is what Hibernate will do anyway).