Define JPA relation with condition on another relation

Hibernate ORM
1

I have the following (legacy) DB schema that I want to model in JPA/Hibernate:

TABLE A {
  id integer
  b_foo string
  c_bar string
}
TABLE B {
  id integer
  foo string
}
TABLE C {
  id integer
  bar string
  b_id number
}

The relations are:

  • A ManyToOne B
  • A ManyToOne C
  • C ManyToOne B

In SQL, I could do the following:

SELECT *
FROM A
JOIN B
  ON B.foo = A.b_foo
JOIN C
  ON C.bar = A.c_bar
  AND C.b_id = B.id

The issue is that I need b.id in the JOIN to get the C relation, and @JoinColumn’s and @JoinFormula’s referenceColumnName only allow “simple” column names.

This is what I have tried so far:

@Entity
class A {
  @Id
  private Long id;
  
  @ManyToOne
  @JoinColumn(name = "b_foo", referencedColumnName = "foo")
  private B b;

  @ManyToOne
  @JoinColumnsOrFormulas({
    @JoinColumnOrFormula(column = @JoinColumn(name = "c_bar", referencedColumnName = "bar")),
    @JoinColumnOrFormula(formula = @JoinFormula(value = "(SELECT B.id FROM B WHERE b.foo = b_foo)", referencedColumnName = "b_id"))
  })
  private C c;
}

@Entity
class B {
  @Id
  private Long id;
  @Column(name = "foo")
  private String foo;
}

@Entity
class C {
  @Id
  private Long id;
  @Column(name = "bar")
  private String bar;
}

While this works, it’s considerably less efficient than the raw SQL join.

I could also do:

@ManyToOne
@JoinColumn(name = "c_bar", referencedColumn = "bar")
private C c;

and then just add WHERE c.b_id = b.id in my queries. That would perform significantly better, but I couldn’t find a way to do that in one general/global place so I don’t have to remember to add it to every query. The @Where annotation doesn’t allow querying relations AFAIK.

I am currency using hibernate 6.2.7.

Is there any other way to implement these relations in an efficient and global manner?

2

This is not a proper association i.e. no foreign key from A to C can guarantee the target element exists. What you want to model is an ad-hoc relation that you can only model with a query language. Use HQL and write the same join you wrote in SQL.

3

All right, thanks for the reply. Do you happen to have any tips for extracting the JOIN clause in a way that I don’t have to repeat it in case I have multiple queries that need that JOIN?

4

If you’re using JPA Criteria then you can create a static utility method for creating the join. For HQL it’s probably easiest if you just write the join in the query and not try to start with HQL fragments in static variables.