[EJB2.1 relation mapping] why JBoss duplicates column names in sql statements?

[vquartara]

Hi everybody, I have a problem with an ejb relation. I'm using JBoss 7.2.0.Final.

Given the following relational table

CREATE TABLE RIGAORDINERICEVUTO

(

   IDORDINERICEVUTO          INTEGER          NOT NULL,

   COUNT_RIGA_ORDINE         SMALLINT         NOT NULL,

   COD_SERVIZIO              VARCHAR(250),

   NUM_ELEMENTI              INTEGER          NOT NULL,

   IMPORTO_TOTALE_NON_IVATO  DECIMAL(10,2),

   IMPORTO_TOTALE_IVA        DECIMAL(10,2),

   FLG_VALIDA                SMALLINT         NOT NULL,

   COD_ERRORE                VARCHAR(250),

   DESCR_ERRORE              VARCHAR(250),

   IDPRENOTAZIONE            BIGINT,

   DATIAGGIUNTIVI            VARCHAR(1500)

);

if I call RigaOrdineRicevutoLocalHome.create(...) JBoss produces the following SQL:

INSERT INTO RIGAORDINERICEVUTO (count_riga_ordine, idOrdineRicevuto, cod_servizio, num_elementi, importo_totale_non_ivato, importo_totale_iva, flg_valida, cod_errore, descr_errore, idPrenotazione, datiAggiuntivi, IDORDINERICEVUTO) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Why column "IDORDINERICEVUTO" is duplicated?

This is the mapping in jbosscmp-jdbc.xml:

<ejb-relation>

            <ejb-relation-name>OrdineRicevuto-RigaOrdineRicevuto</ejb-relation-name>

            <ejb-relationship-role>

                <ejb-relationship-role-name>ordric</ejb-relationship-role-name>

                <key-fields />

            </ejb-relationship-role>

            <ejb-relationship-role>

                <ejb-relationship-role-name>rigordric</ejb-relationship-role-name>

                <key-fields>

                    <key-field>

                        <field-name>idOrdineRicevuto</field-name>

                        <column-name>IDORDINERICEVUTO</column-name>

                    </key-field>

                </key-fields>

            </ejb-relationship-role>

        </ejb-relation>

<ejb-relation>

            <ejb-relation-name>RigaOrdineUtente-RigaOrdineRicevuto</ejb-relation-name>

            <ejb-relationship-role>

                <ejb-relationship-role-name>rigordinric</ejb-relationship-role-name>

                <key-fields>

                    <key-field>

                        <field-name>ordine_idOrdine</field-name>

                        <column-name>idOrdineRicevuto</column-name>

                    </key-field>

                    <key-field>

                        <field-name>count_riga_ordine</field-name>

                        <column-name>count_riga_ordine</column-name>

                    </key-field>

                </key-fields>

            </ejb-relationship-role>

            <ejb-relationship-role>

                <ejb-relationship-role-name>rigordut</ejb-relationship-role-name>

                <key-fields>

                    <key-field>

                        <field-name>ordric_idOrdineRicevuto</field-name>

                        <column-name>ORDINE_IDORDINE </column-name>

                    </key-field>

                    <key-field>

                        <field-name>count_riga_ordine</field-name>

                        <column-name>count_riga_ordine</column-name>

                    </key-field>

                <key-fields>

            </ejb-relationship-role>

        </ejb-relation>

From ejb-jar.xml:

<entity id="RigaOrdineRicevuto">

            <ejb-name>RigaOrdineRicevuto</ejb-name>

            <local-home>it.visura.bke.business.services.ejb.model.RigaOrdineRicevutoLocalHome</local-home>

            <local>it.visura.bke.business.services.ejb.model.RigaOrdineRicevutoLocal</local>

            <ejb-class>it.visura.bke.business.services.ejb.model.RigaOrdineRicevutoBean</ejb-class>

            <persistence-type>Container</persistence-type>

            <prim-key-class>it.visura.bke.business.services.ejb.model.RigaOrdineRicevutoKey</prim-key-class>

            <reentrant>false</reentrant>

            <cmp-version>2.x</cmp-version>

            <abstract-schema-name>RigaOrdineRicevuto</abstract-schema-name>

            <cmp-field>

                <field-name>count_riga_ordine</field-name>

            </cmp-field>

            <cmp-field>

                <field-name>cod_servizio</field-name>

            </cmp-field>

            <cmp-field>

                <field-name>num_elementi</field-name>

            </cmp-field>

            <cmp-field>

                <field-name>importo_totale_non_ivato</field-name>

            </cmp-field>

            <cmp-field>

                <field-name>importo_totale_iva</field-name>

            </cmp-field>

            <cmp-field>

                <field-name>flg_valida</field-name>

            </cmp-field>

            <cmp-field>

                <field-name>cod_errore</field-name>

            </cmp-field>

            <cmp-field>

                <field-name>descr_errore</field-name>

            </cmp-field>

            <cmp-field>

                <description>Generated to support relationships.  Do NOT delete.</description>

                <field-name>ordric_idOrdineRicevuto</field-name>

            </cmp-field>

            <cmp-field>

                <field-name>idPrenotazione</field-name>

            </cmp-field>

            <cmp-field>

                <field-name>datiAggiuntivi</field-name>

            </cmp-field>

            <ejb-local-ref>

                <ejb-ref-name>ejb/OrdineRicevuto</ejb-ref-name>

                <ejb-ref-type>Entity</ejb-ref-type>

                <local-home>it.visura.bke.business.services.ejb.model.OrdineRicevutoLocalHome</local-home>

                <local>it.visura.bke.business.services.ejb.model.OrdineRicevutoLocal</local>

                <ejb-link>OrdineRicevuto</ejb-link>

            </ejb-local-ref>

            <ejb-local-ref>

                <ejb-ref-name>ejb/RigaOrdineUtente</ejb-ref-name>

                <ejb-ref-type>Entity</ejb-ref-type>

                <local-home>it.visura.bke.business.contabilizzazione.ejb.model.RigaOrdineUtenteLocalHome</local-home>

                <local>it.visura.bke.business.contabilizzazione.ejb.model.RigaOrdineUtenteLocal</local>

                <ejb-link>RigaOrdineUtente</ejb-link>

            </ejb-local-ref>

        </entity>

[wdfink]

Not sure.

What kind of relation is it 1-n or n-1 and how the other side look like.

Also did you use XDoclet to generate the XML? Could you show the code of the related entities?

[vquartara]

I noticed that, surprisingly, <column-name> is case sensitive!!

If I change

<key-field>

        <field-name>idOrdineRicevuto</field-name>

        <column-name>IDORDINERICEVUTO</column-name>

</key-field>

to

<key-field>

       <field-name>idOrdineRicevuto</field-name>

       <column-name>idOrdineRicevuto</column-name>

</key-field>

then the insert statement is correct.

[wdfink]

The column names are case sensitive in configuration as some databases are

For the configuration you should always use unique names (case sensitve)