Search code examples
javajpaeclipselink

EclipseLink - Subqueries with Criteria Query (JPA)

take the following two tables as given:

  1. Objects with columns objId, objTaxonId
  2. Taxa with columns taxId, taxValidSynonymId, taxName (Note Taxa is plural for Taxon)

if a Taxon is valid, its id and validSynonymId are identical, otherwise they are different. To find all synonyms of a Taxon you 'only' need to find all Taxa where the taxValidSynonymId is filled with the taxId of the valid Taxon

how can i gain all Objects where the Taxon has a given Name (including their Synonyms?) in SQL this is done in a few lines (and Minutes)

SELECT * 
FROM Objects
WHERE objTaxonId IN (
    SELECT taxId
    FROM Taxa
    WHERE taxName LIKE 'Test Taxon 1'
        OR taxSynIdTaxon IN(
        SELECT taxId 
        FROM Taxa 
        WHERE taxName LIKE 'Test Taxon 1'
    )
)

i was able to work out the inside part, where i gain the list of Taxa and its Synonyms. Now i need to transform this Query to a Subquery...

String NAME_LIKE = "Test Taxon 1";
EntityManager em = EntityManagerProvider.getEntityManager("TestDB"); // get the EntityManager
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<TaxonImpl> cqObject = cb.createQuery(TaxonImpl.class);//
Root<TaxonImpl> taxonRoot = cqObject.from(TaxonImpl.class);//
Expression<String> taxon_name = taxonRoot.<String> get("taxName");
Predicate where = cb.equal(taxon_name, NAME_LIKE);
// subquery
Subquery<Integer> subQuery = cqObject.subquery(Integer.class);
Root<TaxonImpl> subRoot = subQuery.from(clsImpl);
subQuery.select(subRoot.<Integer> get("taxId"));
subQuery.where(cb.equal(subRoot.<String> get("taxName"), NAME_LIKE));
where = cb.or(where, taxonRoot.get("taxValidSynonymId").in(subQuery));
cqObject.where(where);
Query query = em.createQuery(cqObject);
List<TaxonImpl> result = query.getResultList();

NOTE: the Taxon is Mapped as an many to One relation (target-entity is TaxonImpl)

in my actual application the code (from the subquery) will be dynamically, so a Native Query does not help me.

Solution

  • i figured out how to "transform" the subquery into a query but Eclipselink threw me two errors

    the first was forbidden access via field, when i tried the in on a result of TaxonImpl (i tried that first, as in my mapping files the Taxon is mapped as Entity.

    so after this i tried to form the SQL 1:1 to JPA. but Eclipselink generated something weird:

    SELECT t0.objIdObject, t0.objAdminCreated, t0.objAdminCreator, t0.objAdminEdited, t0.objAdminEditor, t0.objAdminImport1, t0.objAdminImport2, t0.objAddBool1, t0.objAddBool2, t0.objAddBool3, t0.objAddBool4, t0.objAddBool5, t0.objAddDateTime1, t0.objAddDateTime2, t0.objCommonComments, t0.objCommonDescription, t0.objCommonKeywords, t0.objCommonName, t0.objCommonPublished, t0.objCommonPublishedAs, t0.objCommonStatus, t0.objCommonType, t0.objCommonTypustype, t0.objDetAccuracy, t0.objDetCf, t0.objDetComments, t0.objDetDate, t0.objDetMethod, t0.objDetResult, t0.objAddFloat1, t0.objAddFloat2, t0.objAddFloat3, t0.objAddFloat4, t0.objAddFloat5, t0.objEventAbundance, t0.objEventCollectionMethod, t0.objEventComments, t0.objEventMoreContacts, t0.objEventDateDay1, t0.objEventDate1, t0.objEventDateMonth1, t0.objEventDate2, t0.objEventDateUncertain, t0.objEventDateYear1, t0.objEventEcosystem, t0.objEventHabitat, t0.objEventNumber, t0.objEventPermission, t0.objEventSubstratum, t0.objEventTime1, t0.objEventTime2, t0.objEventWeekNumber, t0.objFlora, t0.objGuidObject, t0.objIOComments, t0.objIODeAccessed, t0.objAddInt1, t0.objAddInt2, t0.objAddInt3, t0.objAddInt4, t0.objAddInt5, t0.objStorageForeignNumber, t0.objStorageNumber, t0.objStorageNumberInCollection, t0.objStorageNumberOld, t0.objStorageNumberPrefix, t0.objAddLkp1, t0.objAddLkp10, t0.objAddLkp2, t0.objAddLkp3, t0.objAddLkp4, t0.objAddLkp5, t0.objAddLkp6, t0.objAddLkp7, t0.objAddLkp8, t0.objAddLkp9, t0.objAddLkpCs1, t0.objAddLkpCs10, t0.objAddLkpCs11, t0.objAddLkpCs12, t0.objAddLkpCs13, t0.objAddLkpCs14, t0.objAddLkpCs15, t0.objAddLkpCs2, t0.objAddLkpCs3, t0.objAddLkpCs4, t0.objAddLkpCs5, t0.objAddLkpCs6, t0.objAddLkpCs7, t0.objAddLkpCs8, t0.objAddLkpCs9, t0.objOriginAccessionDate, t0.objOriginAccessionNumber, t0.objOriginComments, t0.objOriginMoreContacts, t0.objOriginSource, t0.objOriginType, t0.objPreparationComments, t0.objPreparationDate, t0.objPreparationType, t0.objPropAdults, t0.objPropAge, t0.objPropAgeUnit, t0.objPropEggs, t0.objPropFemale, t0.objPropHeight, t0.objPropHeightUnit, t0.objPropJuveniles, t0.objPropLarvae, t0.objPropLength, t0.objPropLengthUnit, t0.objPropMale, t0.objPropObservation, t0.objPropObservationComments, t0.objPropPupae, t0.objPropSex, t0.objPropStadium, t0.objPropWeight, t0.objPropWeightUnit, t0.objPropWidth, t0.objPropWidthUnit, t0.objSiteComments, t0.objStorageComments, t0.objStorageContainerNumber, t0.objStorageContainerPieces, t0.objStorageContainerType, t0.objStorageLevel1, t0.objStorageLevel2, t0.objStorageLevel3, t0.objStorageLevel4, t0.objStorageLevel5, t0.objStorageNumberInContainer, t0.objstoragePieces, t0.objStorageValue, t0.objStorageValueUnit, t0.objAddText1, t0.objAddText10, t0.objAddText2, t0.objAddText3, t0.objAddText4, t0.objAddText5, t0.objAddText6, t0.objAddText7, t0.objAddText8, t0.objAddText9, t0.objIdCollection, t0.objCommonIdReference, t0.objDetIdContact, t0.objDetIdReference, t0.objEventIdContact, t0.objIdExcursion, t0.objOriginIdContact, t0.objPreparationIdContact, t0.objIdProject, t0.objSiteIdSite, t0.objdetIdTaxon 
FROM tObjects t0 
WHERE t0.objdetIdTaxon IN (
    SELECT t1.taxIdTaxon.t1.taxIdTaxon 
    FROM tTaxa t1 
    WHERE (t1.taxTaxonDisplay LIKE 'Test Taxon 1' 
        OR t1.taxSynIdTaxon IN (
        SELECT t2.taxSynIdTaxon 
        FROM tTaxa t2 
        WHERE t2.taxTaxonDisplay LIKE 'Test Taxon 1')))

    to take out the error:

    SELECT t1.taxIdTaxon.t1.taxIdTaxon

    which is complete crap. you cannot execute a function on an type of int!

    resolving this error (BUG?) introduces a new construct (which still returns the same results)

    SELECT t1.objIdObject, t1.objAdminCreated, t1.objAdminCreator, t1.objAdminEdited, t1.objAdminEditor, t1.objAdminImport1, t1.objAdminImport2, t1.objAddBool1, t1.objAddBool2, t1.objAddBool3, t1.objAddBool4, t1.objAddBool5, t1.objAddDateTime1, t1.objAddDateTime2, t1.objCommonComments, t1.objCommonDescription, t1.objCommonKeywords, t1.objCommonName, t1.objCommonPublished, t1.objCommonPublishedAs, t1.objCommonStatus, t1.objCommonType, t1.objCommonTypustype, t1.objDetAccuracy, t1.objDetCf, t1.objDetComments, t1.objDetDate, t1.objDetMethod, t1.objDetResult, t1.objAddFloat1, t1.objAddFloat2, t1.objAddFloat3, t1.objAddFloat4, t1.objAddFloat5, t1.objEventAbundance, t1.objEventCollectionMethod, t1.objEventComments, t1.objEventMoreContacts, t1.objEventDateDay1, t1.objEventDate1, t1.objEventDateMonth1, t1.objEventDate2, t1.objEventDateUncertain, t1.objEventDateYear1, t1.objEventEcosystem, t1.objEventHabitat, t1.objEventNumber, t1.objEventPermission, t1.objEventSubstratum, t1.objEventTime1, t1.objEventTime2, t1.objEventWeekNumber, t1.objFlora, t1.objGuidObject, t1.objIOComments, t1.objIODeAccessed, t1.objAddInt1, t1.objAddInt2, t1.objAddInt3, t1.objAddInt4, t1.objAddInt5, t1.objStorageForeignNumber, t1.objStorageNumber, t1.objStorageNumberInCollection, t1.objStorageNumberOld, t1.objStorageNumberPrefix, t1.objAddLkp1, t1.objAddLkp10, t1.objAddLkp2, t1.objAddLkp3, t1.objAddLkp4, t1.objAddLkp5, t1.objAddLkp6, t1.objAddLkp7, t1.objAddLkp8, t1.objAddLkp9, t1.objAddLkpCs1, t1.objAddLkpCs10, t1.objAddLkpCs11, t1.objAddLkpCs12, t1.objAddLkpCs13, t1.objAddLkpCs14, t1.objAddLkpCs15, t1.objAddLkpCs2, t1.objAddLkpCs3, t1.objAddLkpCs4, t1.objAddLkpCs5, t1.objAddLkpCs6, t1.objAddLkpCs7, t1.objAddLkpCs8, t1.objAddLkpCs9, t1.objOriginAccessionDate, t1.objOriginAccessionNumber, t1.objOriginComments, t1.objOriginMoreContacts, t1.objOriginSource, t1.objOriginType, t1.objPreparationComments, t1.objPreparationDate, t1.objPreparationType, t1.objPropAdults, t1.objPropAge, t1.objPropAgeUnit, t1.objPropEggs, t1.objPropFemale, t1.objPropHeight, t1.objPropHeightUnit, t1.objPropJuveniles, t1.objPropLarvae, t1.objPropLength, t1.objPropLengthUnit, t1.objPropMale, t1.objPropObservation, t1.objPropObservationComments, t1.objPropPupae, t1.objPropSex, t1.objPropStadium, t1.objPropWeight, t1.objPropWeightUnit, t1.objPropWidth, t1.objPropWidthUnit, t1.objSiteComments, t1.objStorageComments, t1.objStorageContainerNumber, t1.objStorageContainerPieces, t1.objStorageContainerType, t1.objStorageLevel1, t1.objStorageLevel2, t1.objStorageLevel3, t1.objStorageLevel4, t1.objStorageLevel5, t1.objStorageNumberInContainer, t1.objstoragePieces, t1.objStorageValue, t1.objStorageValueUnit, t1.objAddText1, t1.objAddText10, t1.objAddText2, t1.objAddText3, t1.objAddText4, t1.objAddText5, t1.objAddText6, t1.objAddText7, t1.objAddText8, t1.objAddText9, t1.objIdCollection, t1.objCommonIdReference, t1.objDetIdContact, t1.objDetIdReference, t1.objEventIdContact, t1.objIdExcursion, t1.objOriginIdContact, t1.objPreparationIdContact, t1.objIdProject, t1.objSiteIdSite, t1.objdetIdTaxon 
FROM tTaxa t0, tObjects t1 
WHERE (
    t0.taxIdTaxon IN (
        SELECT t2.taxIdTaxon 
        FROM tTaxa t2 
        WHERE (t2.taxTaxonDisplay LIKE 'Test Taxon 1'
            OR t2.taxSynIdTaxon IN (
            SELECT t3.taxSynIdTaxon 
            FROM tTaxa t3 
            WHERE t3.taxTaxonDisplay LIKE 'Test Taxon 1'
            )
        )
    ) AND (t0.taxIdTaxon = t1.objdetIdTaxon)
)

    this seems strange to me, but it is working - and it is faster than my alternative query, which includes a inner join

    NOTE: Eclipselink does ignore the JoinType. regardless what you pass it takes an left outer join. (documentation says something else!)

    finally i provide the two examples for join and joinless

    private static Predicate addSynonymsWithJoins(Root<BioObjectImpl> r, CriteriaBuilder b, CriteriaQuery cq,
        Attribute attr, Path path, Object value) {
    Join taxJoin = r.join(BioObjectEnum.taxon.name(), JoinType.INNER);

    Path<Object> taxValidSynonymId = taxJoin.get(TaxonEnum.validSynonymId.name());
    Subquery<TaxonImpl> innerSubquery = cq.subquery(TaxonImpl.class);
    Root fromSubTax = innerSubquery.from(TaxonImpl.class);
    innerSubquery.select(fromSubTax.<Integer> get(TaxonEnum.id.name()));
    Predicate dynamic1 = cb.like(fromSubTax.get(TaxonEnum.name.name()),
     NAME_LIKE);
    innerSubquery.where(dynamic1);
    Predicate dynamic2 = resolveComparator(b, attr, taxJoin.get(attr.getPropertyName()), attr.getValue());//
    Predicate p = b.or(taxValidSynonymId.in(innerSubquery), dynamic2);

    return p;
}

private static Predicate addSynonymsWithoutJoins(Root<BioObjectImpl> r, CriteriaBuilder b, CriteriaQuery cq,
        Attribute attr, Path path, Object value) {
    cq.select(r);

    Path<Integer> objTaxonId = r.<Integer> get(BioObjectEnum.taxon.name()).get(TaxonEnum.id.name());

    Subquery<Integer> t2 = cq.subquery(Integer.class);
    Root<TaxonImpl> t2fromTaxon = t2.from(TaxonImpl.class);
    Path<Integer> t2taxId = t2fromTaxon.<Integer> get(TaxonEnum.validSynonymId.name());
    t2.select(t2taxId);
    Predicate t2dynamicWhere = resolveComparator(b, attr, t2fromTaxon.get(attr.getPropertyName()), attr.getValue());
    t2.where(t2dynamicWhere);

    Subquery<Integer> t1 = cq.subquery(Integer.class);
    Root<TaxonImpl> t1fromTaxon = t1.from(TaxonImpl.class);
    Predicate t1dynamicWhere = b.like(fromSubTax.get(TaxonEnum.name.name()),
     NAME_LIKE);
    Path<Integer> t1Select = t1fromTaxon.<Integer> get(TaxonEnum.id.name());

    t1.select(t1Select);
    Path<Integer> t1TaxSynonymId = t1fromTaxon.<Integer> get(TaxonEnum.validSynonymId.name());
    t1dynamicWhere = b.or(t1dynamicWhere, t1TaxSynonymId.in(t2));
    t1.where(t1dynamicWhere);

    Predicate where = objTaxonId.in(t1);
    return where;
}