@Where with @SecondaryTable does not work with Hib…

There is a one-to-many relationship in my model, where the child entity is stored in two tables.

@Entity
@Table(name = "child1")
@SecondaryTable(name = "child2", pkJoinColumns = {
        @PrimaryKeyJoinColumn(name = "id1", referencedColumnName = "id1"),
        @PrimaryKeyJoinColumn(name = "id2", referencedColumnName = "id2")})
@Where(clause = "col1 is not null and col2 is not null")
@Data
@Immutable
public class Child implements Serializable {...}

Child entity is fetched eagerly together with Parent entity. Problem lies within @Where clause, which should reference columns from two tables: col1 is in table child1 and col2 is in child2. This throws the following error:

ERROR 12333 --- [nio-8183-exec-7] o.h.engine.jdbc.spi.SqlExceptionHelper   : Column (col2) not found in any table in the query (or SLV is undefined).
java.sql.SQLException: null
...

Using only: @Where(clause = "col1 is not null") gives propper mapping and results in no error.
Using @Where(clause = "child1.col1 is not null and child2.col2 is not null") gives the following error:
```
Column (child1) not found in any table in the query (or SLV is undefined).
```

How can I make @Where work with two tables or is there any workaround?

There are some requirements though:

I’m using informix as an underlying database and have read-only access.
I know, that it can be solved by native SQL or even JPQL / criteria API and so on, but doing so would make me rewrite a lot of core. I want to avoid it.

Answer

This is due to the HHH-4246 issue.

A workaround would be to replace the @SecondaryTable with a @OneToOne association using @MapsId.

This way, the child2 table becomes the Child2 entity for which you can use @Where or @Filter.

@Where with @SecondaryTable does not work with Hibernate

Advertisement

Answer