CMR 2.1 and EJB-QL
hello. I have some errors trying to use ejb-ql 2.1 and i dont understand why :(
I am developing a test application to learn how to use j2ee ejbs
Im currently using EJB 2.1, so no 3.0.
what follows is what i tried following sun manuals and oreilly ejb 3rd edition book.
ok, so, let say i have a db with 2 tables: Dipartimento and Account such as
CREATE TABLE DIPARTIMENTO ( IdDipartimento INTEGER NOT NULL PRIMARY KEY, Descrizione VARCHAR(255), unique(Descrizione)); CREATE TABLE ACCOUNT ( Utente VARCHAR(32) NOT NULL PRIMARY KEY, Pwd VARCHAR(32) NOT NULL, Cognome VARCHAR(255) NOT NULL, Nome VARCHAR(255) NOT NULL, Ruolo VARCHAR(255) NOT NULL, Tipo INTEGER NOT NULL, Dipartimento INTEGER NOT NULL, foreign key(Dipartimento) references DIPARTIMENTO(IdDipartimento) on delete cascade on update cascade);
(obviously i dont create these tables with this SQL, since entity beans create for me)
now, i modelled these tables with CMP Entity bean, thus writing the beans (with all the getter and setter methods for the fields, the BeanNameCMP, the BeanNameLocal and BeanNameLocalHome interfaces), and put in the deployment descriptor correct entity blocks for them (i'll paste below).
Now, i would like to be able to perform JOIN queries with ejb-ql.
two sample queries i wanted to perform were:
"SELECT OBJECT(a) FROM Account AS a WHERE a.Dipartimento.Descrizione = 'FOO'"
(select all the accounts with a certain department description) , and the sister query:
"SELECT OBJECT(a) FROM Dipartimento d, IN (d.IdDipartimento) AS a WHERE d.Descrizione = 'FOO'"
this one was the first one, and is a bit strange, because i wanted to try this ejb-ql join, the IN operator.
ok. So afaiunderstood, i have to map these foreign key references as relations. so below is how i did it (i mapped the one to many relation between department and account in both way)
ejb-jar.xml (part)
<entity > <description><![CDATA[Description for Account]]></description> <display-name>Entity Bean relativa agli Account di accesso</display-name> <ejb-name>Account</ejb-name> <local-home>servizioDipartimentale.interfaces.AccountLocalHome</local-home> <local>servizioDipartimentale.interfaces.AccountLocal</local> <ejb-class>servizioDipartimentale.ejb.AccountBean</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>Account</abstract-schema-name> <cmp-field > <description><![CDATA[Getter for CMP Field utente]]></description> <field-name>Utente</field-name> </cmp-field> <cmp-field > <description><![CDATA[Getter for CMP Field pwd]]></description> <field-name>Pwd</field-name> </cmp-field> <cmp-field > <description><![CDATA[Getter for CMP Field cognome]]></description> <field-name>Cognome</field-name> </cmp-field> <cmp-field > <description><![CDATA[Getter for CMP Field nome]]></description> <field-name>Nome</field-name> </cmp-field> <cmp-field > <description><![CDATA[Getter for CMP Field ruolo]]></description> <field-name>Ruolo</field-name> </cmp-field> <cmp-field > <description><![CDATA[Getter for CMP Field tipo]]></description> <field-name>Tipo</field-name> </cmp-field> <cmp-field > <description><![CDATA[Getter for CMP Field dipartimento]]></description> <field-name>Dipartimento</field-name> </cmp-field> <primkey-field>Utente</primkey-field> <query> <query-method> <method-name>findAll</method-name> <method-params> </method-params> </query-method> <ejb-ql><![CDATA[SELECT OBJECT(a) FROM Account AS a]]></ejb-ql> </query> <query> <query-method> <method-name>findAll2</method-name> <method-params> </method-params> </query-method> <ejb-ql><![CDATA[SELECT OBJECT(a) FROM Account AS a ORDER BY a.Tipo DESC, a.Utente ASC]]></ejb-ql> </query> <query> <query-method> <method-name>findAllUsers</method-name> <method-params> </method-params> </query-method> <ejb-ql><![CDATA[SELECT OBJECT(a) FROM Account AS a WHERE a.Tipo = 1]]></ejb-ql> </query> <query> <query-method> <method-name>findAllAdministrators</method-name> <method-params> </method-params> </query-method> <ejb-ql><![CDATA[SELECT OBJECT(a) FROM Account AS a WHERE a.Tipo = 2]]></ejb-ql> </query> <query> <query-method> <method-name>findRelatedAccounts</method-name> <method-params></method-params> </query-method> <!-- ejb-ql><![CDATA[SELECT OBJECT(a) FROM Dipartimento AS d, IN (d.IdDipartimento) as a WHERE d.Descrizione = 'DIPARTIMENTO DI FISICA']]></ejb-ql--> <ejb-ql><![CDATA[SELECT OBJECT(a) FROM Account AS a WHERE a.Dipartimento.Descrizione = 'DIPARTIMENTO DI FISICA']]></ejb-ql> </query> </entity> <entity > <description><![CDATA[Description for Dipartimento]]></description> <display-name>Name for Dipartimento</display-name> <ejb-name>Dipartimento</ejb-name> <local-home>servizioDipartimentale.interfaces.DipartimentoLocalHome</local-home> <local>servizioDipartimentale.interfaces.DipartimentoLocal</local> <ejb-class>servizioDipartimentale.ejb.DipartimentoBean</ejb-class> <persistence-type>Container</persistence-type> <prim-key-class>java.lang.Integer</prim-key-class> <reentrant>False</reentrant> <cmp-version>2.x</cmp-version> <abstract-schema-name>Dipartimento</abstract-schema-name> <cmp-field > <description><![CDATA[Getter for CMP Field idDipartimento]]></description> <field-name>IdDipartimento</field-name> </cmp-field> <cmp-field > <description><![CDATA[Getter for CMP Field Descrizione]]></description> <field-name>Descrizione</field-name> </cmp-field> <primkey-field>IdDipartimento</primkey-field> <query> <query-method> <method-name>findAll</method-name> <method-params> </method-params> </query-method> <ejb-ql><![CDATA[SELECT OBJECT(a) FROM Dipartimento AS a]]></ejb-ql> </query> <!-- Write a file named ejb-finders-DipartimentoBean.xml if you want to define extra finders. --> </entity> <!-- Relationships --> <ejb-relation> <ejb-relation-name>Dipartimento-Account</ejb-relation-name> <ejb-relationship-role> <ejb-relationship-role-name>DipartimentoBean-AccountBean</ejb-relationship-role-name> <multiplicity>One</multiplicity> <relationship-role-source> <ejb-name>Dipartimento</ejb-name> </relationship-role-source> <cmr-field> <cmr-field-name>IdDipartimento</cmr-field-name> <cmr-field-type>java.lang.Integer</cmr-field-type> </cmr-field> </ejb-relationship-role> <ejb-relationship-role> <ejb-relationship-role-name>Account-belongs-to-department</ejb-relationship-role-name> <multiplicity>Many</multiplicity> <cascade-delete/> <relationship-role-source> <ejb-name>Account</ejb-name> </relationship-role-source> <cmr-field> <cmr-field-name>Dipartimento</cmr-field-name> <cmr-field-type>java.lang.Integer</cmr-field-type> </cmr-field> </ejb-relationship-role> </ejb-relation> <ejb-relation> <ejb-relation-name>Account-Dipartimento</ejb-relation-name> <ejb-relationship-role> <ejb-relationship-role-name>AccountBean-DipartimentoBean</ejb-relationship-role-name> <multiplicity>One</multiplicity> <relationship-role-source> <ejb-name>Account</ejb-name> </relationship-role-source> <cmr-field> <cmr-field-name>Dipartimento</cmr-field-name> <cmr-field-type>java.lang.Integer</cmr-field-type> </cmr-field> </ejb-relationship-role> <ejb-relationship-role> <ejb-relationship-role-name>Department-belongs-to-account</ejb-relationship-role-name> <multiplicity>one</multiplicity> <cascade-delete/> <relationship-role-source> <ejb-name>Dipartimento</ejb-name> </relationship-role-source> <cmr-field> <cmr-field-name>IdDipartimento</cmr-field-name> <cmr-field-type>java.lang.Integer</cmr-field-type> </cmr-field> </ejb-relationship-role> </ejb-relation>
ok. Trying to deploy everything with this deployment descriptor results in this error (due to the query):
ERROR [Account#findRelatedAccounts] In path field is not a cmr field: at line 1, column 56. Encountered: "Dipartimento" after: "a."
If instead i try the second query (the one actually comented) i get:
00:17:15,944 ERROR [Account#findRelatedAccounts] Encountered "d.IdDipartimento" at line 1, column 48.
Was expecting:
<COLLECTION_VALUED_PATH> ...
that is more or less what i get if i try the first query using IN keyword, and not accessing directly a.Documento.Descrizione
what am i doing wrong? i mean this should not be something very complex to do, but i cant get it to work:(
thanks for any help!
below the code for one of the 2 beans (the other one is more or less the same):
package servizioDipartimentale.ejb;
import java.rmi.RemoteException;
import javax.ejb.EJBException;
import javax.ejb.EntityBean;
import javax.ejb.EntityContext;
import javax.ejb.RemoveException;
public abstract class DipartimentoBean implements EntityBean {
public DipartimentoBean() {
super();
// TODO Auto-generated constructor stub
}
public void setEntityContext(EntityContext ctx)
throws EJBException,
RemoteException {
// TODO Auto-generated method stub
}
public void unsetEntityContext() throws EJBException, RemoteException {
// TODO Auto-generated method stub
}
public void ejbRemove()
throws RemoveException,
EJBException,
RemoteException {
// TODO Auto-generated method stub
}
public void ejbActivate() throws EJBException, RemoteException {
// TODO Auto-generated method stub
}
public void ejbPassivate() throws EJBException, RemoteException {
// TODO Auto-generated method stub
}
public void ejbLoad() throws EJBException, RemoteException {
// TODO Auto-generated method stub
}
public void ejbStore() throws EJBException, RemoteException {
// TODO Auto-generated method stub
}
public Integer ejbCreate(
Integer idDipartimento,
String descrizione) throws javax.ejb.CreateException {
setIdDipartimento(idDipartimento);
setDescrizione(descrizione);
return null;
}
public void ejbPostCreate(
Integer idDipartimento,
String descrizione) throws javax.ejb.CreateException {
// TODO Auto-generated method stub
}
public abstract java.lang.Integer getIdDipartimento();
public abstract void setIdDipartimento(java.lang.Integer value);
public abstract java.lang.String getDescrizione();
public abstract void setDescrizione(java.lang.String value);
}