How to chain joins with CriteriaBuilder API

Question

I got this SQL query and it works (tested on DB and results are as expected)

select n.name, s.id, pn.name
from structure s
     join node n on n.id = s.node_id                      
     left join structure ps on ps.id = s.parent_id        
     left join node pn on pn.id = ps.node_id

How can I re-create this query with the criteria builder API? I cannot figure it out.. Classes are:

class node {
  String id;
  String name;
}

class Structure {
  String id;
  Node node;
  String parentId; // Structure id
}

I can make the Join from Structure to node like

Root<Structure> structureRoot = criteriaQuery.from(Structure.class);
Join<Structure, Node> firstJoin = structureRoot.join("node");

This takes me from Structure to a Node, how can I use this first join to join in back to parent structure that is on the Structure as a String parentId, not a relation?

edit

When making an relation from structure to structure Classes are updated :

class node {
  String id;
  String name;
}

class Structure {
  String id;
  @ManyToOne
  @JoinColumn(name = "node_id")
  Node node;
  @ManyToOne
  @JoinColumn(name = "parent_id")
  Structure parent;
}

i got this error:

o.h.engine.jdbc.spi.SqlExceptionHelper   : Column "STRUCTURE2_.ID" not 
found; SQL statement:
select structure0_.id as col_0_0_, node3_.name as col_1_0_, 
structure0_.id as id1_2_, structure0_.node_id as node_id3_2_, 
structure0_.parent_id as parent_i4_2_, structure0_.source_id as 
source_i2_2_ from structure structure0_ inner join node node4_ on 
structure0_.node_id=node4_.id and (node1_.id=structure0_.node_id) left 
outer join structure structure5_ on 
structure0_.parent_id=structure5_.id and 
(structure2_.id=structure0_.parent_id) left outer join node node6_ on 
structure0_.node_id=node6_.id and (node3_.id=structure2_.node_id) 
cross 
join node node1_ cross join structure structure2_ cross join node 
node3_ where lower(node1_.name) like ? order by node1_.name desc limit 
? [42122-197]
2018-09-20 12:13:49.447 ERROR 4599 --- [           main] c . 
.t.assets.service.SortToOrderMapper     : 
org.hibernate.exception.SQLGrammarException: could not prepare 
statement

if i run this query:

CriteriaQuery<Object[]> criteriaQuery = 
criteriaBuilder.createQuery(Object[].class);
Root<Structure> s = criteriaQuery.from(Structure.class);
Root<Node> n = criteriaQuery.from(Node.class);
Root<Structure> ps = criteriaQuery.from(Structure.class);
Root<Node> pn = criteriaQuery.from(Node.class);

Join one = s.join("node");
one.on(criteriaBuilder.equal(n.get("id"), s.get("node")));
Join two = ps.join("parent", JoinType.LEFT);
two.on(criteriaBuilder.equal(s.get("parent"),ps.get("id")));
Join three = s.join("node",JoinType.LEFT);
three.on(criteriaBuilder.equal(pn.get("id"),ps.get("node")));

return criteriaQuery.select(criteriaBuilder.array(s,three.get("name"))).where(
            criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])));

@techtabu I have seen that post and tried it but it did not work. I am doing something wrong and i cannot see it — ApexOne, Sep 20 '18 at 05:33
@K.Nicholas I have seen that post and tried it but it did not work. I am doing something wrong and i cannot see it — ApexOne, Sep 20 '18 at 05:33
Maybe `three` should be defined as follows: `Join three = **ps**.join("node",JoinType.LEFT);` — Priyesh, Sep 20 '18 at 12:05

score 1 · Accepted Answer · answered Sep 20 '18 at 12:41

1

Since i added the

@ManyToOne
@JoinColumn(name = "parent_id")
private Structure parent;

I do not need to make the joins.. i was doing to much and was doing it at the same time. Cleaned my select clause and removed the joins and it works!

answered Sep 20 '18 at 12:41

ApexOne

97
9

score 0 · Answer 2 · answered Sep 20 '18 at 09:40

0

Instead of a String parentId, you need a Structure field in your Structure class that represents the parent structure.

class Structure {
    String id;
    Node node;
    @ManyToOne
    @JoinColumn(name = "parent_id", nullable = true)
    Structure parentStructure;
}

Then, you can write your criteria query like so:

Root<Structure> structureRoot = criteriaQuery.from(Structure.class);
Join<Structure, Node> firstJoin = structureRoot.join("node");
Join<Structure, Structure> parentJoin = structureRoot.join("parentStructure");
Join<Structure, Node> secondJoin = parentJoin.join("node");

answered Sep 20 '18 at 09:40

Priyesh

2,041
1
17
25

thx for the input but this did not work, any other ideas? i updated the question – ApexOne Sep 20 '18 at 10:23
Do you get the error in the question when you use my criteria code or the query added by you in the question? – Priyesh Sep 20 '18 at 10:32
Try with `@ManyToOne(fetch = FetchType.LAZY)` on parent. – Priyesh Sep 20 '18 at 10:37
if i just make the joins the (your) query returns 8 results where it should return 2, this is because there are no 'om' clauses. If i add the 'on' clauses i get the error that 'STRUCTURE2_.ID' not found. If i inspect the generated query i can see that 'STRUCTURE2_' allias is created after it uses the ''STRUCTURE2_.ID' – ApexOne Sep 20 '18 at 10:43
I think you don't need to do the 'on' explicitly. Could you maybe add some sample data to show what the above query returns and what is expected? – Priyesh Sep 20 '18 at 11:59

How to chain joins with CriteriaBuilder API

2 Answers2