Mount Query with JOINS

Question

I have a query executed on DB2:

SELECT f.* 
       FROM Fat f 
            INNER EFat ef 
                  ON f.id = ef.id AND f.seq = ef.seq 

How do I reverse it in Java? I tried this way, but it’s wrong, all my classes are with their proper relationships:

StringBuilder sb = new StringBuilder();
sb.append("SELECT f FROM ")
.append(getClasseGenerica().getSimpleName() + " f ")
.append(" INNER JOIN f.eFaturas ef ")   

My question is to do, that:

ON f.id = ef.id AND f.seq = ef.seq

With that in JAVA:

.append(" AND f.id = ef.id AND f.seq = ef.seq ")
 Query query = getEntityManager().createQuery(sb.toString()); 

When I put AND or WHERE always gives error in query execution.

Answer 1

In JPQL it is only possible to do the Inner Join the way you are trying when the class is related (Ex. Fat Detro has an Efat type attribute).

Alternatively you can do the querie as follows.

SELECT f 
FROM Fat f,  EFat ef 
WHERE f.id = ef.id 
    AND f.seq = ef.seq 

https://docs.jboss.org/hibernate/orm/3.3/reference/pt-BR/html/queryhql.html#queryhql-joins

Or you can do as below:

SELECT f.* 
FROM Fat f 
    in(f.eFaturas) ef 
WHERE  f.seq = ef.seq