Jpa sets where clause in query for fields over 2 mapjoins

Question

I am trying to build a complex query. My objects look 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 the correct request for the properties field. I cannot create a query to get all the configurations where component A has property Prop1 = 1 and component B has property Prop2 = 2. I tried the following without success.

 Root<Configuration> conf = cq.from(Configuration.class); MapJoin<Configuration, String, Component> compJoin = conf.join(Configuration_.components, JoinType.LEFT); MapJoin<Component, String, 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"), cb.equal(propJoin.key(), "Prop2"), cb.equal(propJoin.value(), "2")); Predicate[] pArray = new Predicate[]{p1, p2}; cq.where(pArray); cq.select(conf).distinct(true); 

EDIT: The query displayed by the registrar 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 assume that he is trying to find a configuration where, possibly, all conditions apply to the SAME component? It works if I apply restrictions for only one component, but I get an empty list of results when applying 2 or more, although there are records in the database that satisfy the conditions.

UPDATE

After executing the Pimgd sentence, I got a query that looks like this:

 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 works, two do not give results.

Any help is much appreciated!

Answer 1

I'm going to say that mapJoin.key() should be compJoin.key() .

Other than that, I see nothing wrong.

If my proposed solution does not work, here are some bonus questions for you:

• What are the results you get?
• Is it possible to show generated queries?

An interesting bit I noticed: 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 = ?))

What if I stand it ... just for clarification. ((t1.COMP_TYPE = ?) AND (t0.PROPERTIES_KEY = ?))

WHERE (((SNIP AND t0.PROPERTIES LIKE ?) AND (SNIP AND t0.PROPERTIES LIKE ?)) AND (t2.DTYPE = ?))

So yes ... Now, how would you decide to fix your request? Unfortunately, I am not an expert. But I know that if you made a query with the transition WHERE t2.CONF_ID IN (Subselect for criteria A) AND t2.CONF_ID IN (Subselect for criteria B) .

My suggestion was to find subqueries and insult them. JPA 2.0, Criteria API, Subqueries, In Expressions explains this and seems relevant enough.