2

We have a Country table, which references StateRegion table:

@Entity
@Table(name="MDM_COUNTRY")
public class Country extends AuditedEntity {

private Set<StateRegion> stateRegions;

    @OneToMany(fetch = FetchType.EAGER)
    @LazyCollection(LazyCollectionOption.FALSE)
    @BatchSize(size=100)
    @JoinColumn(name = "COUNTRY_CODE", referencedColumnName="COUNTRY_CODE")
    public Set<StateRegion> getStateRegions() {
        return stateRegions;
    }
}

As you can see it is using non-primary key as a join column. When just loading countries it works fine, but when Country is referenced in some other entity, for example Department, then if Country without any StateRegions is selected ClassCastException is thrown, because Hibernate tries to evaluate getCountryCode() on a country id (Long) for some reason.

Some investigations showed, that using non-primary key as join column is not very good idea, so in order to fix this, I've changed StateRegions list mapping to use @Formula:

@Formula("(SELECT region FROM StateRegion region WHERE region.countryCode = countryCode)")
@ElementCollection(targetClass = StateRegion.class)
public Set<StateRegion> getStateRegions() {
    return stateRegions;
}

Which works fine with Hibernate 4.3.7 (since we are also in process of migrating from Jboss 7.1 to Wildfly 8.2.1 - I have already Wildfly (Hibernate 4.3.7), but other environments are still running with JBoss (where Hibernate 4.0.1 is in use)).

For some reason, Hibernate 4.0.1 generates following SQL:

select <columns_were_cut> from MDM_COUNTRY_MDM_STATE_REGION stateregio0_ inner join MDM_STATE_REGION stateregio1_ on stateregio0_.stateRegions_id=stateregio1_.id where stateregio0_.Country_id=?

which is kind of not, what is written in @Formula description and which, apparently fails, with 

ERROR: relation "mdm_country_mdm_state_region" does not exist

because there is really no such table.

Any idea how to make my @Formula definition compatible with Hibernate 4.0.1?

Alex K.
  • 3,294
  • 4
  • 29
  • 41
  • Did you ever find a solution to this problem? – Casey Feb 22 '17 at 13:28
  • @Casey, I am not in the project anymore, but if I remember correctly then I fixed it by introducing proper primary key (id) on country table and made state_region referencing country via country_id (FK), instead of country code, then fixed mapping. But if you can use newer version of Hibernate then it should also work with a Formula. Hope that helps. – Alex K. Feb 22 '17 at 20:15
  • I faced the same problem with @Formula with the latest version of Hibernate org.hibernate:hibernate-core:5.6.1.Final and 5.5.3.Final. So is there way to fix @Formula? – RoutesMaps.com Nov 16 '21 at 07:32

0 Answers0