JPA sets the where clause when querying the fields of two mapjoins
I'm trying to build a complex query My entity looks like this:
@Entity public class Configuration{ @Id @Column(name="CONF_ID") protected Long configurationId; @ManyToMany @MapKey(name="componentType") @JoinTable(name="CONF_COMP",joinColumns={@JoinColumn(name="CONF_ID",referencedColumnName="CONF_ID")},inverseJoinColumns={@JoinColumn(name="COMP_ID",referencedColumnName="componentId")}) protected Map<String,Component> components; }
and
@Entity public class Component { @Id protected long componentId; @ElementCollection protected Map<String,String> properties; @ManyToMany(mappedBy="components") private List<Configuration> configurations; @Column(name="COMP_TYPE") protected String componentType; }
My problem is to query attribute fields correctly I can't seem to create a query to get all the configurations, where component A has attribute PROP1 = 1 and component B has attribute prop2 = 2 I tried the following but failed
Root<Configuration> conf = cq.from(Configuration.class); MapJoin<Configuration,String,Component> compJoin = conf.join(Configuration_.components,JoinType.LEFT); MapJoin<Component,String> propJoin = compJoin.join(Component_.properties,JoinType.LEFT); Predicate p1 = cb.and( cb.equal(mapJoin.key(),"A"),cb.equal(propJoin.key(),"Prop1"),cb.equal(propJoin.value(),"1")); Predicate p2 = cb.and( cb.equal(mapJoin.key(),"B"),"Prop2"),"2")); Predicate[] pArray = new Predicate[]{p1,p2}; cq.where(pArray); cq.select(conf).distinct(true);
Edit: the query output by the recorder is as follows:
SELECT DISTINCT t2.CONF_ID,t2.DTYPE,t2.TOTALPRICE,t2.NAME FROM CONfigURATION t2 LEFT OUTER JOIN (CONF_COMP t3 JOIN COMPONENT t1 ON (t1.COMPONENTID = t3.COMP_ID)) ON (t3.CONF_ID = t2.CONF_ID) LEFT OUTER JOIN Component_PROPERTIES t0 ON (t0.Component_COMPONENTID = t1.COMPONENTID) WHERE (((((t1.COMP_TYPE = ?) AND (t0.PROPERTIES_KEY = ?)) AND t0.PROPERTIES LIKE ?) AND (((t1.COMP_TYPE = ?) AND (t0.PROPERTIES_KEY = ?)) AND t0.PROPERTIES LIKE ?)) AND (t2.DTYPE = ?)) bind => [7 parameters bound]
I guess it's trying to find a configuration where all the conditions apply to the same component? If I apply restrictions to only one component, it will work, but when I apply 2 or more, I get an empty result list, even though the entries in the DB meet the conditions
UPDATE
According to pimgd's suggestion, I finally got a query as follows:
SELECT DISTINCT t1.CONF_ID,t1.DTYPE,t1.TOTALPRICE,t1.NAME FROM CONfigURATION t1 LEFT OUTER JOIN (CONF_COMP t2 JOIN COMPONENT t0 ON (t0.COMPONENTID = t2.COMP_ID)) ON (t2.CONF_ID = t1.CONF_ID) WHERE ((( t0.COMPONENTID IN (SELECT t3.COMPONENTID FROM COMPONENT t3 LEFT OUTER JOIN Component_PROPERTIES t4 ON (t4.Component_COMPONENTID = t3.COMPONENTID) WHERE ((t4.PROPERTIES_KEY = Brand) AND (t4.PROPERTIES = Intel)))) AND t0.COMPONENTID IN (SELECT t6.COMPONENTID FROM COMPONENT t6 LEFT OUTER JOIN Component_PROPERTIES t7 ON (t7.Component_COMPONENTID = t6.COMPONENTID) WHERE ((t7.PROPERTIES_KEY = Capacity) AND t7.PROPERTIES LIKE 4GB%))))
One criterion is valid and two have no results
Any help, thank you very much!
Solution
I'll say that
Besides, I didn't see any mistakes
If the solution I offer doesn't work, here are some reward questions:
>What did you get? > Can the generated query be displayed?
I found an interesting point: where ((T1. Comp_type =?) AND(t0.PROPERTIES_KEY =?)) AND t0. PROPERTIES LIKE?) AND(((t1.COMP_TYPE =?) AND(t0.PROPERTIES_KEY =?)) AND t0. Properties (similar?) And (T2. Dtype =?)
If, I bring this out... Just to clarify ((t1.COMP_TYPE =?) AND(t0.PROPERTIES_KEY =?))
Where do you like (SNIP and t0. Properties?) And (SNIP and t0. Properties like?) And (T2. Dtype =?)
Yes... Now how will you fix your query? Sadly, I'm not an expert But all I know is, if you make a query, where is where T2 CONF_ ID in (sub selection of standard a) and T2 CONF_ ID in (sub selection of standard B)
My advice is to look for sub - choices and abuse them JPA 2.0, criteria API, subqueries, in expressions explains it and seems relevant enough