• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

CMP ejb-ql query

 
my deen
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have added these things into my ejb-jar.xml for retrieving data for particular IN condition.But its throwing error..Is it any other way to write the query with IN condition...
<query>
<query-method>
<method-name>findByDevice</method-name>
<method-params>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>

<ejb-ql>
SELECT OBJECT(a) FROM tableName a WHERE a.fieldName IN (?1)
</ejb-ql>
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Riyaz,
Welcome to JavaRanch!

That query says you plan to put exactly one element in the "in clause". I assume you want to put a list instead. This can't be done using a statement prepared in advance. Similarly, it can't be done with binding variables using the underlying JDBC either. You need to build the query at runtime which JPA (EJB 3) allows.
 
my deen
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Jeanne,
I added like this also but i got same error..I am using ejb2.0.Will it support this kind of query.Also i tried with Between

<query>
<query-method>
<method-name>findByDevice</method-name>
<method-params>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>

<ejb-ql>
SELECT OBJECT(a) FROM tableName a WHERE a.fieldName IN ('23443','234234')
</ejb-ql>

--------------

<query>
<query-method>
<method-name>findByRange</method-name>
<method-params>
<method-param>java.sql.Timestamp</method-param>
<method-param>java.sql.Timestamp</method-param>
</method-params>
</query-method>

<ejb-ql>
SELECT OBJECT(a) FROM Events a WHERE a.timeStamp BETWEEN ?1 AND ?2

</ejb-ql>
</query>

But i got following error

2008-03-07 16:44:55,695 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.Events#findByRange] EJB-QL: SELECT OBJECT(a) FROM Events a WHERE a.timeStamp BETWEEN ?1 AND ?2


2008-03-07 16:44:55,703 ERROR [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.Events#findByRange] Encountered "BETWEEN" at line 1, column 53.
Was expecting one of:
">" ...
"<" ...
"=" ...
"<=" ...
">=" ...
"<>" ...

org.jboss.ejb.plugins.cmp.ejbql.ParseException: Encountered "BETWEEN" at line 1, column 53.
Was expecting one of:
">" ...
"<" ...
"=" ...
"<=" ...
">=" ...
"<>" ...

at org.jboss.ejb.plugins.cmp.ejbql.EJBQLParser.generateParseException(EJBQLParser.java:4360)
at org.jboss.ejb.plugins.cmp.ejbql.EJBQLParser.jj_consume_token(EJBQLParser.java:4237)
at org.jboss.ejb.plugins.cmp.ejbql.EJBQLParser.ComparisonExpression(EJBQLParser.java:1449)
at org.jboss.ejb.plugins.cmp.ejbql.EJBQLParser.SimpleCondExpression(EJBQLParser.java:924)
at org.jboss.ejb.plugins.cmp.ejbql.EJBQLParser.ConditionalPrimary(EJBQLParser.java:881)
at org.jboss.ejb.plugins.cmp.ejbql.EJBQLParser.ConditionalTest(EJBQLParser.java:849)
at org.jboss.ejb.plugins.cmp.ejbql.EJBQLParser.ConditionalFactor(EJBQLParser.java:826)
at org.jboss.ejb.plugins.cmp.ejbql.EJBQLParser.WhereConditionalTerm(EJBQLParser.java:673)
[ March 08, 2008: Message edited by: Riyaz deen ]
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Riyaz,
Both of those should work. Since the number or items in the "in clause" is hard coded, the binding problem is gone. What error did you get? You referred to the "same error", but I don't see an error in the first post. Maybe I am missing it?

I don't remember when "between" was added to EJBQL. I recommend just rewriting that query to "a.timeStamp >= ?1 and a.timeStamp <= ?2". It's logically equivalent and JBoss' error message hints that it would be happier with this.
 
my deen
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Jeanne,

I have replaced Between with >= and <=,its working fine..
But still,I got this error while doing with In condition..you said some binding problem..how to solve this issue...i am jboss4.0.5 and ejb2.0

<query>
<query-method>
<method-name>findByDevice</method-name>
<method-params>
<method-param>java.lang.String</method-param>

</method-params>
</query-method>

<ejb-ql>
SELECT OBJECT(a) FROM NCMEvents a WHERE a.deviceId IN (?1)

</ejb-ql>
</query>

org.jboss.deployment.DeploymentException: Error compiling EJB-QL statement for EJB 'NCMEvents': SELECT OBJECT(a) FROM NCMEvents a WHERE a.deviceId IN (?1); - nested throwable: (org.jboss.ejb.plugins.cmp.ejbql.ParseException: Encountered "1" at line 1, column 56.
Was expecting one of:
"ABS" ...
"LENGTH" ...
"LOCATE" ...
"SQRT" ...
"MOD" ...
"(" ...
<INTEGER_LITERAL> ...
<FLOATING_POINT_LITERAL> ...
<NUMERIC_VALUED_PARAMETER> ...
<NUMERIC_VALUED_PATH> ...
)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.<init>(JDBCEJBQLQuery.java:66)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCCommandFactory.createEJBQLQuery(JDBCCommandFactory.java:75)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCQueryManager.start(JDBCQueryManager.java:286)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.startStoreManager(JDBCStoreManager.java:505)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.start(JDBCStoreManager.java:396)
at org.jboss.ejb.plugins.CMPPersistenceManager.start(CMPPersistenceManager.java:172)
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Riyaz,
If you know you will always have one value:


If you know you will always have two values:


If there are varying numbers of values, you cannot write the EJB-QL and stick it in the deployment descriptor. You will need to build it at runtime.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic