Hibernate @Where annotation not working with inheritance

Question

I am using Hibernate 5.1.2

I have run into an unexpected problem that I can't seem to work around. Here's the summary of my data model:

dfip_project_version is my superclass table, and dfip_appln_proj_version is my subclass table. dfip_application contains a list of dfip_appln_proj_versions.

I have mapped this as follows:

@Table(name = "DFIP_PROJECT_VERSION")
@Entity
@Inheritance(strategy = InheritanceType.JOINED)
public abstract class AbstractProjectVersion {
    @Id @GeneratedValue
    @Column(name = "PROJECT_VERSION_OID")
    Long oid;

    @Column(name = "PROJ_VSN_EFF_FROM_DTM")
    Timestamp effFromDtm;

    @Column(name = "PROJ_VSN_EFF_TO_DTM")
    Timestamp effToDtm;

    @Column(name = "PROJECT_VERSION_TYPE")
    @Type(type = "project_version_type")
    ProjectVersionType projectVersionType;
}


@Table(name = "DFIP_APPLN_PROJ_VERSION")
@Entity
class ApplicationProjectVersion extends AbstractProjectVersion {

    @OneToOne
    @JoinColumn(name = "APPLICATION_OID", nullable = false)
    Application application;

    public ApplicationProjectVersion() {
        projectVersionType = ProjectVersionType.APPLICATION;
    }
}

@Table(name = "DFIP_APPLICATION")
@Entity
class Application {

    @Id @GeneratedValue
    @Column(name = "APPLICATION_OID")
    Long oid;

    @OneToMany(mappedBy="application", orphanRemoval = true, fetch = FetchType.EAGER)
    @Fetch(FetchMode.SELECT)
    @Where(clause = "PROJ_VSN_EFF_TO_DTM is null")
    List<ApplicationProjectVersion> applicationVersions = [];
}

I am using the @Where annotation so that only the current ApplicationProjectVersion is retrieved with the Application.

The problem with this is that Hibernate assumes that the column I am referencing is in the dfip_appl_proj_version table, when it's actually on the super-class table (dfip_project_version).

Here's what I tried so far to work around this limitation:

Attempt 1

I tried putting the @Where annotation onto the AbstractProjectVersion super-class, like so:

@Table(name = "DFIP_PROJECT_VERSION")
@Entity
@Inheritance(strategy = InheritanceType.JOINED)
@Where(clause = "PROJ_VSN_EFF_TO_DTM is null")
public abstract class AbstractProjectVersion {
    ...etc...
}

This did nothing, as the WHERE clause does not seem to be noticed when retrieving the Application.

---

Attempt 2

I made the applicationVersions list on Application LAZY, and tried to map latestVersion manually like this:

@Table(name = "DFIP_APPLICATION")
@Entity
class Application {

    @Id @GeneratedValue
    @Column(name = "APPLICATION_OID")
    Long oid;

    @OneToMany(mappedBy="application", orphanRemoval = true, fetch = FetchType.LAZY)
    @Fetch(FetchMode.SELECT)
    List<ApplicationProjectVersion> applicationVersions = [];

    @ManyToOne
    @JoinColumnsOrFormulas([
        @JoinColumnOrFormula(formula = @JoinFormula(value = "(APPLICATION_OID)", referencedColumnName="APPLICATION_OID")),
        @JoinColumnOrFormula(formula = @JoinFormula(value = "(select apv.PROJECT_VERSION_OID from DFIP_PROJECT_VERSION pv, DFIP_APPLN_PROJ_VERSION apv where apv.PROJECT_VERSION_OID = pv.PROJECT_VERSION_OID and apv.APPLICATION_OID = APPLICATION_OID and pv.PROJ_VSN_EFF_TO_DTM is null)", referencedColumnName="PROJECT_VERSION_OID")),
    ])
    ApplicationProjectVersion latestVersion;
}

This caused Hibernate to generate the following SQL (snippet):

from DFIP_APPLICATION this_ 
left outer join DFIP_APPLN_PROJ_VERSION applicatio2_ 
    on (this_.APPLICATION_OID)=applicatio2_.APPLICATION_OID and 
       (select apv.PROJECT_VERSION_OID from DFIP_PROJECT_VERSION pv, DFIP_APPLN_PROJ_VERSION apv 
        where apv.PROJECT_VERSION_OID = pv.PROJECT_VERSION_OID and apv.APPLICATION_OID = this_.APPLICATION_OID 
        and pv.PROJ_VSN_EFF_TO_DTM is null)=applicatio2_.PROJECT_VERSION_OID 

which resulted in ORA-01799: a column may not be outer-joined to a subquery.

If I can't specify a sub-query in my join formula, then I cannot join to the super-class manually...

---

Attempt 3

I noticed that usage of @JoinFormula makes Hibernate notice my @Where annotation on the super-class. So I tried the following:

@Table(name = "DFIP_PROJECT_VERSION")
@Entity
@Inheritance(strategy = InheritanceType.JOINED)
@Where(clause = "PROJ_VSN_EFF_TO_DTM is null")
public abstract class AbstractProjectVersion {
    ...etc...
}

@Table(name = "DFIP_APPLICATION")
@Entity
class Application {

    @Id @GeneratedValue
    @Column(name = "APPLICATION_OID")
    Long oid;

    @OneToMany(mappedBy="application", orphanRemoval = true, fetch = FetchType.LAZY)
    @Fetch(FetchMode.SELECT)
    List<ApplicationProjectVersion> applicationVersions = [];

    @ManyToOne
    @JoinFormula(value = "(APPLICATION_OID)", referencedColumnName="APPLICATION_OID")
    ApplicationProjectVersion latestVersion;
}

This generated the following SQL (snippet):

from DFIP_APPLICATION this_ 
left outer join DFIP_APPLN_PROJ_VERSION applicatio2_ 
    on (this_.APPLICATION_OID)=applicatio2_.APPLICATION_OID and ( applicatio2_1_.PROJ_VSN_EFF_TO_DTM is null) 
left outer join DFIP_PROJECT_VERSION applicatio2_1_ on applicatio2_.PROJECT_VERSION_OID=applicatio2_1_.PROJECT_VERSION_OID 

This is almost correct! Unfortunately it is not valid SQL, since applicatio2_1_ is used before it is declared on the next line :(.

---

Now I am out of ideas, so any help would be appreciated. Is there a way to specify a WHERE clause that will bring in only the current ProjectVersion, without getting rid of my inheritance structure?

Related Hibernate issue ticket

Answer 1

I have a solution to this problem. I must admit, it ended up being a little more cumbersome than what I hoped for, but it does work quite well. I waited a couple of months before posting, to make sure that there are no issues and so far, I have not experienced any problems.

My entities are still mapped exactly as described in the question, but instead of using the problematic @Where annotation, I had to use @Filter annotation instead:

public class Application {

    @OneToMany(mappedBy="application", orphanRemoval = true, fetch = FetchType.EAGER)
    @Cascade([SAVE_UPDATE, DELETE, MERGE])
    @Fetch(FetchMode.SELECT)

    // Normally we'd just use the @Where(clause = "PROJ_VSN_EFF_TO_DTM is null"), but that doesn't work with collections of
    // entities that use inheritance, as we have here.
    //
    // Hibernate thinks that PROJ_VSN_EFF_TO_DTM is a column on DFIP_APPLN_PROJ_VERSION table, but it is actually on the "superclass"
    // table (DFIP_PROJECT_VERSION). 
    //
    // B/c of this, we have to do the same thing with a Filter, which is defined on AbstractProjectVersion.
    // NOTE: This filter must be explicitly enabled, which is currently achieved by HibernateForceFiltersAspect 
    //
    @Filter(name="currentProjectVersionOnly", 
        condition = "{pvAlias}.PROJ_VSN_EFF_TO_DTM is null", 
        deduceAliasInjectionPoints=false, 
        aliases=[ @SqlFragmentAlias(alias = "pvAlias", table = "DFIP_PROJECT_VERSION") ]
    )
    List<ApplicationProjectVersion> projectVersions = [];

}

Since we are using a Filter, we must also define it:

// NOTE: This filter needs to be explicitly turned on with session.enableFilter("currentProjectVersionOnly");
// This is currently achieved with HibernateForceFiltersAspect
@FilterDef(name="currentProjectVersionOnly")

@Table(name = "DFIP_PROJECT_VERSION")
@Inheritance(strategy = InheritanceType.JOINED)
public abstract class AbstractProjectVersion  {

}

And of course, we must enable it, since Hibernate does not have a setting to automatically turn on all filters.

To do this I created a system-wide Aspect, whose job is to enable specified filters before every call to any DAO:

/**
 * Enables provided Hibernate filters every time a Hibernate session is openned.
 * 
 * Must be enabled and configured explicitly from Spring XML config (i.e. no auto-scan here)
 *
 * @author Val Blant
 */
@Aspect
public class HibernateForceFiltersAspect {

    List<String> filtersToEnable = [];

    @PostConstruct
    public void checkConfig() throws Exception {
        if ( filtersToEnable.isEmpty() ) {
            throw new IllegalArgumentException("Missing required property 'filtersToEnable'");
        }
    }

    /**
     * This advice gets executed before all method calls into DAOs that extend from <code>HibernateDao</code>
     * 
     * @param jp
     */
    @Before("@target(org.springframework.stereotype.Repository) && execution(* ca.gc.agr.common.dao.hibernate.HibernateDao+.*(..))")
    public void enableAllFilters(JoinPoint jp) {
        Session session = ((HibernateDao)jp?.getTarget())?.getSession();

        if ( session != null ) {
            filtersToEnable.each { session.enableFilter(it) } // Enable all specified Hibernate filters
        }
    }

}

And the corresponding Spring configuration:

<!-- This aspect is used to force-enable specified Hibernate filters for all method calls on DAOs that extend HibernateDao -->  
<bean class="ca.gc.agr.common.dao.hibernate.HibernateForceFiltersAspect">
    <property name="filtersToEnable">
        <list>
            <value>currentProjectVersionOnly</value>            <!-- Defined in AbstractProjectVersion -->
        </list>
    </property>
</bean>

And there you have it - polymorphic @Where clause :).