2 Replies Latest reply on Jun 13, 2005 12:21 PM by schmidts

EJB QL that uses an in clause does not work.

robr Jun 12, 2005 2:01 PM

I am trying to use a method in an EJB that uses a select statement that uses the in clause.

An sql example would be select * from quote q where q.id in ('sw','se')

My code looks like the following.

priceMatrixEntityHome.java

 Collection findRowsById(String sIds) throws RemoteException, FinderException;

ejb-jar.xml definition for bean - query part at the end

 <entity>
 <description>PriceMatrix Entity Bean</description>
 <display-name>PriceMatrix Entity Bean</display-name>
 <ejb-name>priceMatrixEntityEJB</ejb-name>
 <home>com.dynix.webquotes.ejb.entity.admin.priceMatrixEntityHome</home>
 <remote>com.dynix.webquotes.ejb.entity.admin.priceMatrixEntity</remote>
 <ejb-class>com.dynix.webquotes.ejb.entity.admin.priceMatrixEntityBean</ejb-class>
 <persistence-type>Container</persistence-type>
 <prim-key-class>java.lang.String</prim-key-class>
 <reentrant>False</reentrant>
 <cmp-version>2.x</cmp-version>
 <abstract-schema-name>priceMatrixEntityEJB</abstract-schema-name>
 <cmp-field>
 <description>ID</description>
 <field-name>id</field-name>
 </cmp-field>
 <cmp-field>
 <description>Description</description>
 <field-name>descr</field-name>
 </cmp-field>
 <cmp-field>
 <description>Type</description>
 <field-name>type</field-name>
 </cmp-field>
 <cmp-field>
 <description>Base</description>
 <field-name>base</field-name>
 </cmp-field>
 <cmp-field>
 <description>Staff</description>
 <field-name>staff</field-name>
 </cmp-field>
 <cmp-field>
 <description>Bib</description>
 <field-name>bib</field-name>
 </cmp-field>
 <cmp-field>
 <description>Item</description>
 <field-name>item</field-name>
 </cmp-field>
 <cmp-field>
 <description>Location</description>
 <field-name>location</field-name>
 </cmp-field>
 <primkey-field>id</primkey-field>
 <query>
 <description>
 Return Entity beans for that match id list passed in.
 </description>
 <query-method>
 <method-name>findRowsById</method-name>
 <method-params>
 <method-param>java.lang.String</method-param>
 </method-params>
 </query-method>
 <ejb-ql>
 <![CDATA[select object(u) FROM priceMatrixEntityEJB u where u.id in(?1)]]>
 </ejb-ql>
 </query>
 </entity>

Java code I am using to call the findRowsbyID method

 cSelPMRows = pricMatrixEntityHome.findRowsById("'hacad'");

The above code doesn't return any rows. If I change the code to the following I get a row back.

 cSelPMRows = pricMatrixEntityHome.findRowsById("hacad");

My problem I need this to be dynamic. My in clause may need to contain one or more elements.

I tried the following code and it did not work.

 cSelPMRows = pricMatrixEntityHome.findRowsById("hacad,hpublic");

I try the following sql statement and it gets two rows back.

select * from pricematrix where id in ('hacad','hpublic')

I am sure that my problem is that I do not know how to format the sIds parameter that is being sent in the findRowsById function.

 Collection findRowsById(String sIds) throws RemoteException, FinderException;

I assumed I should have created a string like "'ida','idb','idc'" and pass it in. My test showed that it did not like the single quotes because the "'hacad'" parameter did not work whereas the "hacad" parameter does work.

Maybe I need to pass in a String[] with an element for each ID i am interested in. Anybody know how to do this correctly. I will keep playing and post a solution when I figure it out. Hope someone beats me to the answer.

Thanks.

1. Re: EJB QL that uses an in clause does not work.

robr Jun 13, 2005 10:11 AM (in response to robr)
After much effort I have come to the conclusion that using the in clause in an ejb-ql will only work in cases where you know exactly how many elements will be in the in clause. This does not work in my case since I need my in clause to be dynamic.

The in clause is defined like this.

in_expression ::= cmp_path_expression [NOT ] IN ( {literal | input_parameter} [, { literal | input_parameter} ]*)

If I understand the above BNF correctly you have to either pass in a literal or a variable for each element of the IN list. In my case I needed to have a dynamic number of elements in my IN clause. I don't want to use a hard coded IN clause with a set number of elements.

My solution was to do the following. I had a list of primary keys for the table I was interested in.

I created a loop that iterated through each primary key. Then for each primary key I did the following.

for (int i = 0; i < sRows.length; ++i) { pricMatrixEntity = pricMatrixEntityHome.findByPrimaryKey((String) sRows); if (pricMatrixEntity != null) { adjustEntity(pricMatrixEntity, bInc, fPercent, aCols); } }

I think it ridiculous that the ejb-ql code can't handle a dynamic IN clause. Maybe I misunderstand the code but my solution worked. Anybody have any experience with this?
Actions
2. Re: EJB QL that uses an in clause does not work.

schmidts Jun 13, 2005 12:21 PM (in response to robr)

Your solution is correct and works. EJB-QL is very limited.

But for performance reasons you might want to issue a single query to database and let the database do the work in one round-trip.

A JBoss-specific approach can be seen in http://docs.jboss.org/jbossas/jboss4guide/r3/html/ch11.chapter.html#ch11.dynamicql.sect
Actions

Go to original post