1

I have table A that has an attribute that is an entity in table B that in turn is connected to a table C.

The (working) SQL query looks like this:

SELECT a.* from A a
LEFT JOIN B b ON a.b_id=b.id
LEFT JOIN C c ON b.c_id=c.id where c.attribute=VALUE;

Basically the VALUE is what Im filtering on. There is a one-to-one relationship from A->B and a one-to-one relationship from B->C.

There are other parameters Im also filtering on so I have a Specification class that generates a Predicate for each parameter that is passed in to build a list of predicates which is then ANDed together at the end. The Specification is being called from my Repository using something like findAll(MySpecificationClass.search(params)).

Im having a hard time understanding how to write this SQL query using CriteriaBuilder inside my Specification class.

NOTES:

  1. This is in a Spring Boot application written in Kotlin.
  2. My specification class is a singleton so I can't autowire EntityManager for example.
  3. I tried to use a chain of get()s to navigate from A, through B to C but that doesn't work - it seems to return all records regardless.
  4. My project is using spring-data-jpa 2.2.6
Eno
  • 10,730
  • 18
  • 53
  • 86
  • [is this question helpful?](https://stackoverflow.com/questions/3424696) – Blake Oct 29 '20 at 05:31
  • Not sure what you mean - Im trying to solve a problem and have not found any useful answers in any documentation, examples or blog posts. Are only certain kinds of questions "allowed" ? – Eno Oct 29 '20 at 05:38
  • It's a link to another SO question. I think your question is a good one. – Blake Oct 29 '20 at 05:39
  • Oh right, sorry I misunderstood :-) I read that earlier today and read some of the pages linked there. Im not using Metamodel API which gets mentioned there and in linked docs. I also can't use EntityManager which is used in many examples. It seems to me Criteria API is just not well documented and hard to grok. – Eno Oct 29 '20 at 05:52

1 Answers1

0

Through the help of a colleague I was able to build this (also should have been INNER JOINS).

The specific predicate returned from my Specification class is:

val joinToB: Join<A, B> = root.join("B", JoinType.INNER)
val joinToC: Join<B, C> = joinToB.join("C", JoinType.INNER)
cb.equal(joinToC.get<Long>("attribute"), VALUE)
Eno
  • 10,730
  • 18
  • 53
  • 86