Hibernate: ManyToOne / multiple join columns with one column nullable=true

Viewed 269

I hope you can help me on that.

Table A has a multi-column join to Table B where one of the JoinColumns can be nullable...

@Entity
@Table(name = "TABLE_A")
public class TableA {

@ManyToOne(fetch = FetchType.EAGER, optional = false)
@JoinColumns({ 
        @JoinColumn(name = "KEY1_TABLE_A", referencedColumnName = "KEY1_TABLE_B"),
        @JoinColumn(name = "KEY2_TABLE_A", referencedColumnName = "KEY2_TABLE_B"),
        @JoinColumn(name = "GROUP_TABLE_A", referencedColumnName = "GROUP_TABLE_B", nullable = true)})
private TableB typeB;

}

In TableB object the columns

  • TABLE_B#KEY1_TABLE_B (not null)
  • TABLE_B#KEY2_TABLE_B (not null)
  • TABLE_B#GROUP_TABLE_B (nullable)

are mapped as Strings. The touple KEY1_TABLE_B /KEY2_TABLE_B /GROUP_TABLE_B is a unique key.

The generated SQL is as follows (shortened)

SELECT
 *
FROM
     table_a this_
     INNER JOIN table_b b_ ON 
        this_.KEY1_TABLE_A = b_.KEY1_TABLE_B AND 
        this_.KEY2_TABLE_A = b_.KEY2_TABLE_B AND 
        this_.GROUP_TABLE_A = b_.GROUP_TABLE_B  <-- here is the issue: works only with "is not null" on Oracle
 WHERE
     this.XYZ=<some-conditions-here>;

if i would write the SQL directly it should be something like

on ... AND (
            (this_.GROUP_TABLE_A = b_.GROUP_TABLE_B) 
         OR (this_.GROUP_TABLE_A is null and b_.GROUP_TABLE_B is null)
           )

Thanks for your thoughts and ideas!

0 Answers
Related