Search code examples
javamysqljpajpqlopenjpa

How to construct a JPQL query getting data from several tables overcoming lazy initialization using JPA 1.0?

I need to get data from the several tables using a JPQL query based on OpenJPA JPA 1.0. Constructing and executing query with natural join is working but I do not get the related objects in the result because of lazy initialization.

query=" SELECT ledger from SdiOrderSkuLedger ledger, SdiOrderSku sOrderSku, 
OrderSkuImpl orderSku, ProductSkuImpl productSku 
WHERE ledger.sdiOrderSku.uidPk=sOrderSku.uidPk 
AND sOrderSku.orderSku.uidPk=orderSku.uidPk 
AND orderSku.productSkuInternal.uidPk=productSku.uidPk
AND ledger.createdDate between :startDate AND :endDate
AND productSku.uidPk IN (:ids)

I can construct the query using join fetch (join fetch worked for me in other cases in this project): 

query=" SELECT ledger from SdiOrderSkuLedger ledger
join fetch ledger.sdiOrderSku sOrderSku
join fetch sOrderSku.orderSku orderSku join fetch orderSku.productSkuInternal productSku 
WHERE ledger.sdiOrderSku.uidPk=sOrderSku.uidPk 
AND sOrderSku.orderSku.uidPk=orderSku.uidPk 
AND orderSku.productSkuInternal.uidPk=productSku.uidPk
AND ledger.createdDate between :startDate AND :endDate
AND productSku.uidPk IN (:ids)

Then I am getting the error:

org.apache.renamed.openjpa.persistence.ArgumentException: Encountered "sOrderSku" at character 76, but expected: [",", ".", "GROUP", "HAVING", "INNER", "JOIN", "LEFT", "ORDER", "WHERE", ]. at org.apache.renamed.openjpa.kernel.jpql.JPQL.generateParseException(JPQL.java:9501)

Solution

  • It appeared that once I added "JOIN FETCH" I do not need the INNER JOIN conditions anymore. So, the resulted working JPQL query is as follows:

    query=" SELECT ledger from SdiOrderSkuLedger ledger
                        join fetch ledger.sdiOrderSku
                        join fetch ledger.sdiOrderSku.orderSku
                        join fetch ledger.sdiOrderSku.orderSku.productSkuInternal
                        join fetch ledger.sdiPriceListAssignment
        WHERE ledger.createdDate between :startDate AND :endDate
        AND ledger.sdiOrderSku.orderSku.productSkuInternal.uidPk IN (:ids) "