Skip to main content

JPA CriteriaBuilder - How to use “IN” comparison operator

Please note these java.net forums are being decommissioned and use the new and improved forums at https://community.oracle.com/community/java.
No replies
jemrusalem07
Offline
Joined: 2004-11-29

Can you please help me how to convert the following codes to using "in" operator of criteria builder? I need to filter by using list/array of usernames using "in".

I also tried to search using JPA CriteriaBuilder - "in" method but cannot find good result. So I would really appreciate also if you can give me reference URLs for this topic. Thanks.

Here is my codes:

[prettify]<span class="com">//usersList is a list of User that I need to put inside IN operator </span><span class="pln"><br /><br /></span><span class="typ">CriteriaBuilder</span><span class="pln"> builder </span><span class="pun">=</span><span class="pln"> getJpaTemplate</span><span class="pun">().</span><span class="pln">getEntityManagerFactory</span><span class="pun">().</span><span class="pln">getCriteriaBuilder</span><span class="pun">();</span><span class="pln"><br /></span><span class="typ">CriteriaQuery</span><span class="pun"><</span><span class="typ">ScheduleRequest</span><span class="pun">></span><span class="pln"> criteria </span><span class="pun">=</span><span class="pln"> builder</span><span class="pun">.</span><span class="pln">createQuery</span><span class="pun">(</span><span class="typ">ScheduleRequest</span><span class="pun">.</span><span class="kwd">class</span><span class="pun">);</span><span class="pln"><br /><br /></span><span class="typ">Root</span><span class="pun"><</span><span class="typ">ScheduleRequest</span><span class="pun">></span><span class="pln"> scheduleRequest </span><span class="pun">=</span><span class="pln"> criteria</span><span class="pun">.</span><span class="kwd">from</span><span class="pun">(</span><span class="typ">ScheduleRequest</span><span class="pun">.</span><span class="kwd">class</span><span class="pun">);</span><span class="pln"><br />criteria </span><span class="pun">=</span><span class="pln"> criteria</span><span class="pun">.</span><span class="kwd">select</span><span class="pun">(</span><span class="pln">scheduleRequest</span><span class="pun">);</span><span class="pln"><br /><br /></span><span class="typ">List</span><span class="pun"><</span><span class="typ">Predicate</span><span class="pun">></span><span class="pln"> </span><span class="kwd">params</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">ArrayList</span><span class="pun"><</span><span class="typ">Predicate</span><span class="pun">>();</span><span class="pln"><br /><br /></span><span class="typ">List</span><span class="pun"><</span><span class="typ">ParameterExpression</span><span class="pun"><</span><span class="typ">String</span><span class="pun">>></span><span class="pln"> usersIdsParamList </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">ArrayList</span><span class="pun"><</span><span class="typ">ParameterExpression</span><span class="pun"><</span><span class="typ">String</span><span class="pun">>>();</span><span class="pln"><br /><br /></span><span class="kwd">for</span><span class="pln"> </span><span class="pun">(</span><span class="kwd">int</span><span class="pln"> i </span><span class="pun">=</span><span class="pln"> </span><span class="lit">0</span><span class="pun">;</span><span class="pln"> i </span><span class="pun"><</span><span class="pln"> usersList</span><span class="pun">.</span><span class="pln">size</span><span class="pun">();</span><span class="pln"> i</span><span class="pun">++)</span><span class="pln"> </span><span class="pun">{</span><span class="pln"><br /></span><span class="typ">ParameterExpression</span><span class="pun"><</span><span class="typ">String</span><span class="pun">></span><span class="pln"> usersIdsParam </span><span class="pun">=</span><span class="pln"> builder</span><span class="pun">.</span><span class="pln">parameter</span><span class="pun">(</span><span class="typ">String</span><span class="pun">.</span><span class="kwd">class</span><span class="pun">);</span><span class="pln"><br /></span><span class="kwd">params</span><span class="pun">.</span><span class="pln">add</span><span class="pun">(</span><span class="pln">builder</span><span class="pun">.</span><span class="pln">equal</span><span class="pun">(</span><span class="pln">scheduleRequest</span><span class="pun">.</span><span class="kwd">get</span><span class="pun">(</span><span class="str">"createdBy"</span><span class="pun">),</span><span class="pln"> usersIdsParam</span><span class="pun">)</span><span class="pln"> </span><span class="pun">);</span><span class="pln"><br />usersIdsParamList</span><span class="pun">.</span><span class="pln">add</span><span class="pun">(</span><span class="pln">usersIdsParam</span><span class="pun">);</span><span class="pln"><br /></span><span class="pun">}</span><span class="pln"><br /><br />criteria </span><span class="pun">=</span><span class="pln"> criteria</span><span class="pun">.</span><span class="kwd">where</span><span class="pun">(</span><span class="kwd">params</span><span class="pun">.</span><span class="pln">toArray</span><span class="pun">(</span><span class="kwd">new</span><span class="pln"> </span><span class="typ">Predicate</span><span class="pun">[</span><span class="lit">0</span><span class="pun">]));</span><span class="pln"><br /><br /></span><span class="typ">TypedQuery</span><span class="pun"><</span><span class="typ">ScheduleRequest</span><span class="pun">></span><span class="pln"> query </span><span class="pun">=</span><span class="pln"> getJpaTemplate</span><span class="pun">().</span><span class="pln">getEntityManagerFactory</span><span class="pun">().</span><span class="pln">createEntityManager</span><span class="pun">().</span><span class="pln">createQuery</span><span class="pun">(</span><span class="pln">criteria</span><span class="pun">);</span><span class="pln"><br /><br /></span><span class="kwd">for</span><span class="pln"> </span><span class="pun">(</span><span class="kwd">int</span><span class="pln"> i </span><span class="pun">=</span><span class="pln"> </span><span class="lit">0</span><span class="pun">;</span><span class="pln"> i </span><span class="pun"><</span><span class="pln"> usersList</span><span class="pun">.</span><span class="pln">size</span><span class="pun">();</span><span class="pln"> i</span><span class="pun">++)</span><span class="pln"> </span><span class="pun">{</span><span class="pln"><br />query</span><span class="pun">.</span><span class="pln">setParameter</span><span class="pun">(</span><span class="pln">usersIdsParamList</span><span class="pun">.</span><span class="kwd">get</span><span class="pun">(</span><span class="pln">i</span><span class="pun">),</span><span class="pln"> usersList</span><span class="pun">.</span><span class="kwd">get</span><span class="pun">(</span><span class="pln">i</span><span class="pun">).</span><span class="pln">getUsername</span><span class="pun">());</span><span class="pln"><br /></span><span class="pun">}</span><span class="pln"><br /><br /></span><span class="typ">List</span><span class="pun"><</span><span class="typ">ScheduleRequest</span><span class="pun">></span><span class="pln"> scheduleRequestList </span><span class="pun">=</span><span class="pln"> query</span><span class="pun">.</span><span class="pln">getResultList</span><span class="pun">();</span><span class="pln"><br /></span>[/prettify]

The internal Query String is converted to below, so I don't get the records created by the two users, because it is using "AND".

[prettify]<span class="kwd">select</span><span class="pln"> generatedAlias0 </span><span class="kwd">from</span><span class="pln"> </span><span class="typ">ScheduleRequest</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> generatedAlias0 </span><span class="kwd">where</span><span class="pln"> </span><span class="pun">(</span><span class="pln"> generatedAlias0</span><span class="pun">.</span><span class="pln">createdBy</span><span class="pun">=:</span><span class="pln">param0 </span><span class="pun">)</span><span class="pln"> </span><span class="kwd">and</span><span class="pln"> </span><span class="pun">(</span><span class="pln"> generatedAlias0</span><span class="pun">.</span><span class="pln">createdBy</span><span class="pun">=:</span><span class="pln">param1 </span><span class="pun">)</span><span class="pln"> order </span><span class="kwd">by</span><span class="pln"> generatedAlias0</span><span class="pun">.</span><span class="pln">trackingId asc <br /></span>[/prettify]

Please need your advise. Thank you in advance.