Win a copy of Five Lines of Code this week in the OO, Patterns, UML and Refactoring forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

CMR and EJBQL

 
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 264
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"Table A has a one to many relationship with Table B. (Table B has a one to one with Table A)"
Is this right?
 
sathish sathish
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Also would this be a ejb select method or is it just a normal finder method?
 
Alex
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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,
 
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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."
 
I was born with webbed fish toes. This tiny ad is my only friend:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
    Bookmark Topic Watch Topic
  • New Topic