JPA CriteriaQuery join three tables not directly navigable

Question

i need to translate this sql query to jpa criteria:

SELECT tbl1.id_t1, tbl2.name, tbl3.name, tbl4.symbol, tbl1.limit, tbl1.value, tbl1.uncertainty 
FROM table_1 AS tbl1
JOIN table_2 AS tbl2 ON tbl2.id_t2=tbl1.id_t2
JOIN table_3 AS tbl3 ON tbl3.id_t3=tbl1.id_t3
JOIN table_4 AS tbl4 ON tbl4.id_t4=tbl1.id_t4
WHERE (tbl2.id_l=1 AND tbl3.id_l=1) AND tbl1.id_s=1;

my mapping between pojo and database table are as follows:

Table_1

@Entity
@Table("table_1")
public class Table1 {
 @Id
 @Column(name="id_t1")
 private Long idRowT1
 @ManyToOne
 @JoinColumn(name="id_t2")
 private Table2 tbl2;
 @ManyToOne
 @JoinColumn(name="id_t3")
 private Table3 tbl3;
 @ManyToOne
 @JoinColumn(name="id_t4")
 private Table4 tbl4;
 @Column(name="limit")
 private String limit;
 @Column(name="value")
 private String value;
 @Column(name="uncertainty")
 private String uncertainty;

 // getter and setter
}

Table_2

@Entity
@Table("table_2")
public class Table2 {
 @Id
 @Column(name="id_t2")
 private Long idT2;

 // getter and setter
}

Table_2_lang

@Entity
@Table("table_2_lang")
@IdClass(Table2LangPK.class)
public class Table2Lang {
 @Id
 @Column(name="id_t2")
 private Long idT2;
 @Id
 @Column(name="id_l")
 private Lang l;
 @Column(name="name")
 private String name;

 // getter and setter
}

Table_3

@Entity
@Table("table_3")
public class Table3 {
 @Id
 @Column(name="id_t3")
 private Long idT3;

 // getter and setter
}

Table_3_lang

@Entity
@Table("table_3_lang")
@IdClass(Table3LangPK.class)
public class Table3Lang {
 @Id
 @Column(name="id_t3")
 private Long idT3;
 @Id
 @Column(name="id_l")
 private Lang l;
 @Column(name="name")
 private String name;

 // getter and setter
}

Table_4

@Entity
@Table("table_4")
public class Table4 {
 @Id
 @Column(name="id_t4")
 private Long idT4;
 @Column(name="name")
 private String name;

 // getter and setter
}

To send data from business layer to front-end i'm using value objects defined as follows:

Simple entity

public class SimpleEntityVO {
 private Long entityId;
 private String name;

 // getter and setter
}

Complex Entity

public class SimpleEntityVO {
 private Long entityId;
 private SimpleEntityVO tbl2VO;
 private SimpleEntityVO tbl3VO;
 private SimpleEntityVO tbl4VO;
 // ... other field of table_1

 // getter and setter
}

In my EJB i need to implement a method that return a list of ComplexEntityVO starting from Table_1

...

private CriteriaBuilder cB = eM.getCriteriaBuilder();

public List<ComplexEntityVO> findAll(Long id_s, Long id_l) {
 CriteriaQuery<ComplexEntityVO> cQ = cB.createQuery(ComplexEntityVO.class)
 Root<Table1> tbl1Root = cQ.from(Table1.class);

 // UPDATE BEGIN
 Root<Table2Lang> tbl2Root = cQ.from(Table2Lang.class);
 ...

 Selection<SimpleEntityVO> sESTbl2 = cB.construct(SimpleEntityVO.class, tbl2Root.get(Table2Lang_.id_t2), tbl2Root.get(Table2Lang_.name));
  // The selection for table_3_lang and table_4 are the same
 // UPDATE END

 TypedQuery<ComplexEntityVO> tQ = eM.createQuery(cQ);
}

...

To achieve the results i've tried with join betwen Table1 and Table2Lang, tried with selection like the one exposed below

`Selection<SimpleEntityVO> sES = cB.construct(SimpleEntityVO.class, ...);`

using Root for lang table, tried with solution exposed here

https://community.oracle.com/message/10795956#10795956

but when i try to execute this statement

`cQ.select(cB.construct(ComplexEntityVO.class, id_t1, SimpleEntityVO)`

or this

`cQ.multiselect(...)`

i get the: IllegalArgumentException

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: , near line 1, column 64
[select new com.example.vo.ComplexEntityVO(generatedAlias0.id_t1,
 new com.example.labims.vo.SimpleEntityVO(generatedAlias1.table2.id_t2, generatedAlias1.name),
 new com.example.vo.SimpleEntityVO(generatedAlias2.table_3.id_t3, generatedAlias2.name),
 new com.example.vo.SimpleEntityVO(generatedAlias3.id_t4, generatedAlias3.name),
 generatedAlias0.limit, generatedAlias0.value, generatedAlias0.uncertainty)
 from com.example.Table1 as generatedAlias0, 
 com.example.model.Table2Lang as generatedAlias1, 
 com.example.model.Table3Lang as generatedAlias2,
 com.example.model.Table4 as generatedAlias3
 where ( generatedAlias0.id_s=:param0 ) and ( ( generatedAlias1.lang.id_l=:param1 ) and ( generatedAlias2.lang.id_l=:param1 ) )]

From the cause of execption understanded that i can't instanciate new object inside select or multiselect statement, but i don't find a way to achieve the original SQL query using criteria API.

UPDATE i've added an excerpt of what i've tried to achieve the result between //UPDATE BEGIN and //UPDATE END

which line gives you the error? – Abdelhak Feb 05 '16 at 21:46 — Abdelhak, Feb 05 '16 at 21:46

score 0 · Answer 1 · answered Feb 05 '16 at 22:05

0

I think make hibernate show sql == true and take query by console,test showed query your databases and find error hbernate not generate query correct

answered Feb 05 '16 at 22:05

kakashi hatake

1,175
2
10
18

The query generated by hibernate is the one inside the excerpt of exception – PatrickT80 Feb 05 '16 at 22:55

score 0 · Answer 2 · edited May 23 '17 at 11:45

There are two approaches to solve this problem.

Add a constructor method to ComplexEntityVO like this:

public ComplexEntityVO(Long id, Long simpleId2, String simpleName2 /*     etc ... */) {
    this.simpleEntityVo = new SimpleEntityVO(simpleId2, simpleName2);
    // etc for other associations
}

add a ProjectionList to your query, return a List<Object[]> instead of a List<ComplexEntityVO> and then iterate over the results like so

for(Object[] o: results) {
    ComplexEntityVO cvo = new ComplexEntityVO((Long)o[0]);
    new SimpleEntityVO vo2 = new SimpleEntityVO((Long) o[1], (String) o[2]);
    cvo.setTbl2VO(vo2);
    // ... etc for other associations
}

Although the second is uglier I would prefer it, since it is more flexible, and allows more opportunities for debugging, logging etc.

See AliasToBeanResultTransformer(MyDTO.class) fails to instantiate MyDTO

Thank's a lot @carbontax i'm trying to solve the problemi using specialized constructor, but with no success. Trying to use projectionlist — PatrickT80, Feb 06 '16 at 17:36
many thank's to your suggestion @carbontax, but i prefer to not use specific class of persistence provider. I've reviewed the solution proposed by oracle community, and it's working well. — PatrickT80, Feb 24 '16 at 17:50

JPA CriteriaQuery join three tables not directly navigable

2 Answers2