Re: Relationship between tables

From: Sami Mohammed (SMOHAMME..ndependenthealth.com)
Date: Fri Mar 11 2005 - 16:14:24 EST

  • Next message: Alex Gunawan Wibowo: "Re: Strange NullPointerException"

    Hi

    I have 2 tabel emp and dept(Master).
    emp has foregin key (deptno).

    see tables below

    Dept Table :
    -------------------------------
    deptno number primary key
    dname varchar2
    loc varchar2
    --------------------------------

    emp table:
    ------------------------------
    empno number primary key
    ename varchar,
    salary number,
    job varchar,
    deptNo --> Foreign key reffering to dept tabel.
    ------------------------------------------------------
    when i renginering cayenne developed the follwing code. is this correct or i have to do manually for the reletionship in the cayenne modeler.
    pls let me know.

    <db-entity name="EMP" schema="MySchema">
                    <db-attribute name="DEPTNO" type="INTEGER" length="22"/>
                    <db-attribute name="EMPNO" type="INTEGER" isPrimaryKey="true" isMandatory="true" length="22"/>
                    <db-attribute name="ENAME" type="VARCHAR" length="50"/>
                    <db-attribute name="JOB" type="VARCHAR" length="50"/>
                    <db-attribute name="SALARY" type="INTEGER" length="22"/>
            </db-entity>

    <db-entity name="DEPT" schema="MySchema">
                    <db-attribute name="DEPTNO" type="INTEGER" isPrimaryKey="true" isMandatory="true" length="22"/>
                    <db-attribute name="DNAME" type="VARCHAR" length="50"/>
                    <db-attribute name="LOC" type="VARCHAR" length="50"/>
    </db-entity>

    <db-relationship name="empArray" source="DEPT" target="EMP" toMany="true">
                    <db-attribute-pair source="DEPTNO" target="DEPTNO"/>
            </db-relationship>
            <db-relationship name="toDept" source="EMP" target="DEPT" toMany="false">
                    <db-attribute-pair source="DEPTNO" target="DEPTNO"/>
            </db-relationship>
    <obj-relationship name="empArray" source="Dept" target="Emp" deleteRule="Cascade" db-relationship-path="empArray"/>

    Thanks
    sami

    CONFIDENTIALITY NOTICE. This e-mail and attachments, if any, may contain confidential information which is privileged and protected from disclosure by Federal and State
    confidentiality laws rules and regulations. This e-mail and attachments, if any, are intended for the designated addressee only. If you are not the designated addressee, you
    are hereby notified that any disclosure, copying, or distribution of this e-mail and its attachments, if any, may be unlawful and may subject you to legal consequences. If you
    have received this e-mail and attachments in error, please contact Independent Health immediately at (716) 631-3001 and delete the e-mail and its attachments from your
    computer. Thank you for your attention

    >>> SMOHAMME..ndependenthealth.com 03/10/05 07:23PM >>>
    Hi Lazslo,

    I have query about master and detail tables.'
    suppose
    Dept is a master table with a primary key deptno, and emp is a
    detail table with forgien key is deptno refering to dept table.

    1.how can i create the relation in the cayenne modeler and which is target and source?
    2.if i have to select ename, salary,deptno,loc from emp e, dept d
    where deptno.e=deptno.d

    how can cayenne create this query.

    Thanks in advance.

    sami

    CONFIDENTIALITY NOTICE. This e-mail and attachments, if any, may contain confidential information which is privileged and protected from disclosure by Federal and State
    confidentiality laws rules and regulations. This e-mail and attachments, if any, are intended for the designated addressee only. If you are not the designated addressee, you
    are hereby notified that any disclosure, copying, or distribution of this e-mail and its attachments, if any, may be unlawful and may subject you to legal consequences. If you
    have received this e-mail and attachments in error, please contact Independent Health immediately at (716) 631-3001 and delete the e-mail and its attachments from your
    computer. Thank you for your attention

    >>> lspoor_cayenn..otmail.com 03/10/05 08:06AM >>>
    Hi Sami,

    You don't need any code to retrieve the next value of your index. The
    Adapter will take care of that. You only need to create an object like as in
    the examples
    (http://www.objectstyle.org/cayenne/userguide/dataobjects/dataobjects-example.html).
    Just don't set the primary key of your your business Object (e.g.
    myInstance.setId(.....))), you only need to specify it as I pointed out
    earlier. Cayenne will do the rest for you.

    Regards, laszlo

    >From: "Sami Mohammed" <SMOHAMME..ndependenthealth.com>
    >Reply-To: cayenne-use..bjectstyle.org
    >To: <cayenne-use..bjectstyle.org>
    >Subject: RE: Primary key and sequence
    >Date: Wed, 09 Mar 2005 17:00:28 -0500
    >
    >Hi laszlo,
    >
    >Thank you very much. i did like this
    >
    > // PK_WEB_CW_TABLE_LIST is sequence name created
    >in oracle DB
    > Map queryParams = new HashMap();
    > queryParams.put("CW_SEQ_NAME", "PK_WEB_CW_TABLE_LIST");
    >
    > // We have create a Query (CW_PK_SEQUENCE_QUERY)in DataMap_ABC.map.xml
    >file
    >
    > // see below how i worte
    >query in DataMap_ABC.map.xml
    > /*<query
    >name="CW_PK_SEQUENCE_QUERY"
    >factory="org.objectstyle.cayenne.map.SQLTemplateBuilder" root="data-map"
    > root- name="DataMap_ABCs">
    >
    > <property name="cayenne.GenericSelectQuery.fetchingDataRows"
    >value="true"/>
    >
    > <sql
    >adapter-class="org.objectstyle.cayenne.dba.oracle.OracleAdapter"><![CDATA[SELECT
    >MYSEACHMA. $CW_SEQ_NAME .NEXTVAL AS NEXTVAL
    > FROM DUAL]]></sql>
    > </query>
    >
    > */ // Perform the sequence query to get the next PK number
    > List results = context.performQuery("CW_PK_SEQUENCE_QUERY", queryParams,
    >true);
    > Map sequenceMapRow = (Map) results.get(0);
    > BigDecimal pkNextVal =(BigDecimal)
    >sequenceMapRow..get(Constants.CW_SEQ_QUERY_NEXTVAL);
    >
    > int pkNextValInt =
    >pkNextVal.intValue();
    > //set primary key in the
    >table using sequence
    >
    >newCwTable.setCwTableKey(pkNextValInt);
    >
    >it is working fine.
    >--------------------------------------
    >
    >BUT FROM YOUR SOLUTION(Cayenne.xml using modeler), JUST I HAVE TO CHECK
    >THE CHECKBOX (CUSTOMIZE PRIMARY KEY GENERATION) AND THEN
    >
    >DbEntity Name : MyTable
    >Sechma name: MySecham
    >DATABASE OBJECT NAME:(what i have to give here my database name or what)
    >and how to call or set in the program......
    >
    >what to set in this code ...... newCwTable.setCwTableKey(PrimaryKey);
    >context.commitChanges();
    >
    >thanks
    >sami
    >
    >
    >CONFIDENTIALITY NOTICE. This e-mail and attachments, if any, may contain
    >confidential information which is privileged and protected from disclosure
    >by Federal and State
    >confidentiality laws rules and regulations. This e-mail and attachments,
    >if any, are intended for the designated addressee only. If you are not the
    >designated addressee, you
    >are hereby notified that any disclosure, copying, or distribution of this
    >e-mail and its attachments, if any, may be unlawful and may subject you to
    >legal consequences. If you
    >have received this e-mail and attachments in error, please contact
    >Independent Health immediately at (716) 631-3001 and delete the e-mail and
    >its attachments from your
    >computer. Thank you for your attention
    >
    > >>> lspoor_cayenn..otmail.com 03/09/05 04:02PM >>>
    >Hi Sami,
    >
    >If you use Oracle, you can use sequences. However, you will have to tell
    >your cayenne.xml using the modeler. Select your DataMap --> Select a
    >database entity --> Select the Entity tab --> check 'Customize primary key
    >generation' and point to your sequence here.
    >
    >Hope this helps, laszlo
    >
    > >From: "Sami Mohammed" <SMOHAMME..ndependenthealth.com>
    > >Reply-To: cayenne-use..bjectstyle.org
    > >To: <cayenne-use..bjectstyle.org>
    > >Subject: Primary key and sequence
    > >Date: Wed, 09 Mar 2005 15:22:26 -0500
    > >
    > >
    > >Hi
    > >
    > >I am try to save data to the table , i have created sequenc DB, but how
    >to
    > >attached the or set sequence .
    > >see the code below . when i am submit i am getting error--> sequence does
    > >not exist at oracle.jdbc.dbaccess.DBError.throwSqlException
    > >
    > >
    > > CwAddForm addCwTableForm =
    > >(CwAddForm) form;
    > >
    > > // get session DataContext to perform database transactions
    > > DataContext context =
    > >BasicServletConfiguration.getDefaultContext(request.getSession());
    > >
    > > try
    > > {
    > > // create new ABC table object and register it with DataContext
    > > WebCwTableList newCwTable =
    > > (WebCwTableList)
    > >context.createAndRegisterNewObject(WebCwTableList.class);
    > >
    > > // Set the attributes of theABC table object
    > > newCwTable.setCwTableName(addCwTableForm.getTableName());
    > > newCwTable.setCwTableDesc(addCwTableForm.getTableDesc());
    > > newCwTable.setCwObjectName(addCwTableForm.getObjectName());
    > > newCwTable.setLastChngDate(new Date());
    > > newCwTable.setLastUsrKey(user.getUserKey());
    > >
    > > // commit all the changes to the database
    > > context.commitChanges();
    > >
    > > }
    > > catch (Exception e)
    > > {
    > > context.rollbackChanges();
    > > errors = CwUtils.processException(errors, e);
    > > }
    > >
    > >
    > >Thanks
    > >sami
    > >
    > >CONFIDENTIALITY NOTICE. This e-mail and attachments, if any, may contain
    > >confidential information which is privileged and protected from
    >disclosure
    > >by Federal and State
    > >confidentiality laws rules and regulations. This e-mail and attachments,
    > >if any, are intended for the designated addressee only. If you are not
    >the
    > >designated addressee, you
    > >are hereby notified that any disclosure, copying, or distribution of this
    > >e-mail and its attachments, if any, may be unlawful and may subject you
    >to
    > >legal consequences. If you
    > >have received this e-mail and attachments in error, please contact
    > >Independent Health immediately at (716) 631-3001 and delete the e-mail
    >and
    > >its attachments from your
    > >computer. Thank you for your attention
    > >
    >
    >_________________________________________________________________
    >FREE pop-up blocking with the new MSN Toolbar - get it now!
    >http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/
    >

    _________________________________________________________________
    Play online games with your friends with MSN Messenger
    http://messenger.msn.nl/



    This archive was generated by hypermail 2.0.0 : Fri Mar 11 2005 - 16:14:56 EST