aspose file tools*
The moose likes EJB and other Java EE Technologies and the fly likes CMR and EJBQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "CMR and EJBQL" Watch "CMR and EJBQL" New topic
Author

CMR and EJBQL

Alex
Greenhorn

Joined: Dec 19, 2003
Posts: 8
Hi,
Can somebody please give me some advice on how to construct ejbql queries when using cmr fields?
For example: I have two tables table A which has got 4 fields in it a b c and d. fields A nd B are primary key fields in table A. And Table B which has 5 fields in it K, E, F , G and H.
Fields G, and H in table B are foreign key fields to Table A.
Table A has a one to many relationship with Table B. (Table B has a one to one with Table A)
If i search in Table B with integer values for E and F it returns me a collection of rows. Each row is a entity local. The values for G and H in these rows map to the primary key fields in Table A which are A and B.
For every result in the collection I want to search table A with the values in G and H.
In sql i would have to do a join between the two tables. How would I do this using EJB_QL?
i think it can be done using the select method rather than a finder method but am not sure how to implement it.

Thanks in advance
sathish sathish
Greenhorn

Joined: Feb 26, 2004
Posts: 6
Hi,
Assume that the Abstract schema name for Table A and Table B are SchemaA and SchemaB
SELECT Object(a) FROM SchemaA AS a, IN(a.SchemaB) AS b
WHERE b.E = ?1 AND b.F = ?2
I hope that this query will work.
Please let me know, if u need more info.
Sathish
Edy Yu
Ranch Hand

Joined: Nov 21, 2000
Posts: 264
"Table A has a one to many relationship with Table B. (Table B has a one to one with Table A)"
Is this right?


SCJP, SCJD, SCWCD, SCBCD, SCEA, IBM Certified Enterprise Developer, WebSphere Studio V5.0
sathish sathish
Greenhorn

Joined: Feb 26, 2004
Posts: 6
Its correct.
Think of the relation between a Person and Phone numbers.
A person can have many Phone numbers(Office, Residence..)
But a phone number can be associated only with one person.
Alex
Greenhorn

Joined: Dec 19, 2003
Posts: 8
Thanks for the reply!
Would I put this query into the ejb jar descriptor enclosed within the entity for table A or enclosed within the entity tags for table B. I know that the ejbql will be wtihin a query tag.
Alex
Greenhorn

Joined: Dec 19, 2003
Posts: 8
Also would this be a ejb select method or is it just a normal finder method?
Alex
Greenhorn

Joined: Dec 19, 2003
Posts: 8
Hello again,
I tried the query by putting it in as a finder in the ejb jar xml an extract from the ejb jar file is below:
<query>
<query-method>
<method-name>findStoresInStoreGroups</method-name>
<method-params>
<method-param>java.lang.Integer</method-param>
<method-param>java.lang.Integer</method-param>
</method-params>
</query-method>
<ejb-ql>
SELECT OBJECT(locn) FROM LocationEntity AS locn, IN(locn.StoreGroupMemberEntity) AS group WHERE group.locationIdGroup = ?1 AND group.locationTypeIdGroup = ?2
</ejb-ql>
</query>
and i got the following error when running ejbc? any ideas?
[java] [EJBCompiler] : Compliance Checker said bean was compliant
[java] ERROR: Error from ejbc: Error while reading 'META-INF/weblogic-cmp-rdbms-jar.xml'. The error was:
[java]
[java] weblogic.ejb20.cmp.rdbms.finders.IllegalExpressionException:
[java] While trying to process Query
[java] Method Name: findStoresInStoreGroups
[java] Parameter Types: (java.lang.Integer, java.lang.Integer)
[java] EJB Query: SELECT OBJECT(locn) FROM LocationEntity AS locn, IN(locn.StoreGroupMemberEntity) AS group WHERE group.locationIdGroup = ?1 AND group.locationTypeIdGroup = ?2
[java] )
[java] Could not parse EJB QL expression: SELECT OBJECT(locn) FROM LocationEntity AS locn, IN(locn.StoreGroupMemberEntity) AS group WHERE group.locationIdGroup = ?1 AND group.locationTypeIdGroup = ?2
[java] weblogic.ejb20.ejbc.EJBCException: line 1: unexpected token: WHERE
[java] Error in FROM clause, there may be a problem with the FROM Clause.
[java] Check that the Range Variable Declarations and the Collection Member Declarations are correct
[java] and that no EJB QL keywords are being used as:
[java] Range Variable names,
[java] Collection Member names,
[java] Abstract Schema names,
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
Originally posted by Alex:
SELECT OBJECT(locn)
FROM LocationEntity AS locn, IN(locn.StoreGroupMemberEntity) AS group
WHERE group.locationIdGroup = ?1 AND group.locationTypeIdGroup = ?2

Entity bean attributes, following standard JavaBeans naming standards, begin with a lowercase letter. The accessor "getStoreGroupMemberEntity()" results in a bean attribute named "storeGroupMemberEntity."
 
Don't get me started about those stupid light bulbs.
 
subject: CMR and EJBQL