3 Replies Latest reply on Jun 7, 2007 11:04 AM by i o

    CMR 2.1 and EJB-QL

    i o Newbie

      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);
      
      }
      
      


        • 1. Re: CMR 2.1 and EJB-QL
          i o Newbie

          I also have another question: i somehow understood that i have to create a new "field" in the entity beans for container managed relationships.

          But i was wondering: ok i can tell the container through relationships which entities are related. but how can i specify - for example - that the pk in Dipartimento does not reference pk in Account, but some other field (in this case Documento field)?

          this is the ambiguous part i cant understand in CMP 2.1 entity beans..

          • 2. Re: CMR 2.1 and EJB-QL
            i o Newbie

            ok i finally found that for this i need to configure jbosscmp-jdbc.xml.. now i try to do it. can sbd point me a reference for this file?

            • 3. Re: CMR 2.1 and EJB-QL
              i o Newbie

              ok it should be the .dtd in the docs folder :)