[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
Re: [eclipselink-users] JPQL : Not able to use IN clause on only the values of an element collection map
|
Hi Bhakti,
JPQL provides a VALUE keyword that lets you navigate to map values. Have you
tried that keyword?
-Tom
On 29/11/2012 11:56 PM, bhakti b wrote:
I am querying a map using JPQL, but am not able to use the IN clause on only
the values of the map. I do not want to have the key columns when using the
IN clause. Can anyone suggested a way to get this working
The following is the setup
The ProductGroup entity mapping :
<entity class="com.test.ProductGroup"
name="com.test.ProductGroup">
<attributes>
<embedded-id
attribute-type="com.test.ProductGroupKey"
name="productGroupKey">
<attribute-override name="productGroupCode">
<column name="PRODUCT_GROUP_CODE" />
</attribute-override>
</embedded-id>
<element-collection name="groupCreditPolicyLinkage">
<map-key-column name="BUSINESS_UNIT" />
<column name="CREDIT_POLICY_ID" />
<collection-table name="CREDIT_TEMP_LINK">
<join-column name="PRODUCT_GROUP_CODE"
referenced-column-name="PRODUCT_GROUP_CODE" />
</collection-table>
</element-collection>
</attributes>
</entity>
<embeddable class="com.test.ProductGroupKey">
<attributes>
<basic name="productGroupCode" attribute-type="java.lang.String">
<column name="PRODUCT_GROUP_CODE" />
</basic>
</attributes>
</embeddable>
The CreditPolicyTemplate mapping :
<entity
class="com.test.CreditPolicyTemplate"
name="com.test.CreditPolicyTemplate">
<attributes>
<embedded-id
attribute-type="com.test.CreditPolicyTemplateKey"
name="creditPolicyTemplateKey">
<attribute-override name="creditPolicyTemplateId">
<column name="ID" />
</attribute-override>
</embedded-id>
<element-collection attribute-type="java.util.Set"
fetch="EAGER" name="allowedPurposeCodes">
<cascade />
<collection-table name="ALLOWED_PURPOSES">
<join-column name="CREDIT_POLICY_TMPLT_ID"
referenced-column-name="ID" />
</collection-table>
</element-collection>
</attributes>
</entity>
<embeddable
class="com.test.CreditPolicyTemplateKey">
<attributes>
<basic name="creditPolicyTemplateId" attribute-type="java.lang.String">
<column name="ID" />
</basic>
</attributes>
</embeddable>
<embeddable name="AllowedPurposeCodes"
class="com.test.AllowedPurposeCodes"
access="FIELD">
<attributes>
<basic name="allowedPurposeCodes">
<column name="PURPOSE_CODE" />
</basic>
</attributes>
</embeddable>
The Query :
<named-query name="fetchAllowedPurposeCodes">
<query>
select distinct
a.allowedPurposeCodes from com.test.CreditPolicyTemplate
c JOIN FETCH c.allowedPurposeCodes a where
c.creditPolicyTemplateKey.creditPolicyTemplateId IN
( select
lnk
from
com.test.ProductGroup p JOIN
p.groupCreditPolicyLinkage lnk
where
p.productGroupKey.productGroupCode in
(:productGroup)
)
</query>
</named-query>
Basically the requirement in the above query is :
p.groupCreditPolicyLinkage is map of the type Map<String, String> where the
BUSINESS_UNIT column is mapped to the key and the CREDIT_POLICY_ID column is
mapped to the value.
The IN clause above should check the value field (that is CREDIT_POLICY_ID)
and NOT the key field (i.e BUSINESS_UNIT).
Currently the above query when executed, throws : ORA-00913: too many values
Generated Query :
SELECT
DISTINCT t0.PURPOSE_CODE FROM ALLOWED_PURPOSES t0, CREDIT_POLICY t1
WHERE (t1.ID IN
(SELECT DISTINCT *t2.CREDIT_POLICY_ID, t2.BUSINESS_UNIT*
FROM PRODUCT_GRP_ALL t3, CREDIT_TEMP_LINK t2
WHERE ((t3.PRODUCT_GROUP_CODE IN (?))
AND (t2.PRODUCT_GROUP_CODE = t3.PRODUCT_GROUP_CODE)))
AND (t0.CREDIT_POLICY_TMPLT_ID = t1.ID))
*Required Query :*
SELECT
DISTINCT t0.PURPOSE_CODE FROM ALLOWED_PURPOSES t0, CREDIT_POLICY t1
WHERE (t1.ID IN
(SELECT DISTINCT *t2.CREDIT_POLICY_ID*
FROM PRODUCT_GRP_ALL t3, CREDIT_TEMP_LINK t2
WHERE ((t3.PRODUCT_GROUP_CODE IN (?))
AND (t2.PRODUCT_GROUP_CODE = t3.PRODUCT_GROUP_CODE)))
AND (t0.CREDIT_POLICY_TMPLT_ID = t1.ID))
--
View this message in context: http://eclipse.1072660.n5.nabble.com/JPQL-Not-able-to-use-IN-clause-on-only-the-values-of-an-element-collection-map-tp156050.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users