@JoinColumns should still join rows when one column is null


#1

I have 2 tables:

First one (which is actually a view) has id, column1, column2.

Second one has id, column1, column2, column3. column1 and column2 are foreign keys for 2 other tables.

Respective entity for the first table (view):

@Entity
public class FirstEntity {
    @Id
    private Integer id;
    
    @ManyToOne
    @JoinColumn(referencedColumnName = "ID", name = "column1")
    private Column1Entity;

    @OneToOne
    @JoinColumn(referencedColumnName = "ID", name = "column2")
    private Column2Entity;

    @OneToMany
    @JoinColumns({
        @JoinColumn(name = "column1", referencedColumnName = "column1"),
        @JoinColumn(name = "column2", referencedColumnName = "column2")
    })
    private List<SecondEntity> secondEntities;

}

So join works fine with one exception: column2 may be null in both tables. And in that case rows with same column1 values where column2 is null should be joined as well (this combination is unique for FirstEntity as well as any other).

With SQL simple join on 2 columns works fine, while with the code above the list is empty when column2 is null.

What can be done in order to get secondEntity field set for null column as well?

I also tried @Inheritance with separation of this 2 cases into abstract parent one (with id, Column1Entity and Column2Entity) and 2 different sub-entities (with secondEntities list in each of them). One of them contained mapping from above, while the second one was with

@OneToMany
@JoinColumn(name = "column1", referencedColumnName = "column1")
@Where(clause = "column2 IS NULL")

but got “referencedColumnNames… not mapped to a single property” for both subclasses then.


#2

What do you mean that a join between two columns with both null should work? One should be not null anyway.


#3

@vlad I didn’t write this. column1 is not null. column2 can be null both for FirstEntity and for SecondEntity. And the topic name says “when one column is null”.


#4

Try to replicate it with this test case template and send it as a Pull Request so we can better understand your mapping.