JDO 1-N/N-1 Relationships with Maps

You have a 1-N (one to many) or N-1 (many to one) when you have one object of a class that has a Map of objects of another class. There are two general ways in which you can represent this in a datastore. Join Table (where a join table is used to provide the relationship mapping between the objects), and Foreign-Key (where a foreign key is placed in the table of the object contained in the Map.

The various possible relationships are described below.

This page is aimed at Map fields and so applies to fields of Java type java.util.HashMap, java.util.Hashtable, java.util.LinkedHashMap, java.util.Map, java.util.SortedMap, java.util.TreeMap, java.util.Properties



1-N Map using Join Table

We have a class Account that contains a Map. With a Map we store values using keys. As a result we have 3 main combinations of key and value, bearing in mind whether the key or value is PersistenceCapable .

Map[PC, PC]

Here both the keys and the values are PersistenceCapable . Like this

If you define the Meta-Data for these classes as follows 

<package name="com.mydomain">
    <class name="Account" identity-type="datastore">
        ...
        <field name="addresses" persistence-modifier="persistent">
            <map key-type="com.mydomain.Name" value-type="com.mydomain.Address"/>
            <join/>
        </field>
    </class>

    <class name="Address" identity-type="datastore">
        ...
    </class>

    <class name="Name" identity-type="datastore">
    </class>
</package>

This will create 4 tables in the datastore, one for Account , one for Address , one for Name and a join table containing foreign keys to the key/value tables.





If you want to configure the names of the columns in the "join" table you would use the <key> and <value> subelements of <field>, something like this 

        <field name="addresses" persistence-modifier="persistent" table="ACCOUNT_ADDRESS">
            <map key-type="com.mydomain.Name" value-type="com.mydomain.Address"/>
            <join>
                <column name="ACCOUNT_ID"/>
            </join>
            <key>
                <column name="NAME_ID"/>
            </key>
            <value>
                <column name="ADDRESS_ID"/>
            </value>
        </field>

If you wish to fully define the schema table and column names etc, follow these tips

  • To specify the name of the table where a class is stored, specify the table attribute on the class element
  • To specify the names of the columns where the fields of a class are stored, specify the column attribute on the field element.
  • To specify the name of the join table, specify the table attribute on the field element.
  • To specify the names of the columns of the join table, specify the column attribute on the join , key , and value elements.
  • To specify the foreign-key between container table and join table, specify <foreign-key> below the <join> element.
  • To specify the foreign-key between join table and key table, specify <foreign-key> below the <key> element.
  • To specify the foreign-key between join table and value table, specify <foreign-key> below the <value> element.

Which changes the names of the join table to ACCOUNT_ADDRESS from ACCOUNT_ADDRESSES and the names of the columns in the join table from ACCOUNT_ID_OID to ACCOUNT_ID, from NAME_ID_KID to NAME_ID, and from ADDRESS_ID_VID to ADDRESS_ID.



Map[Simple, PC]

Here our key is a simple type (in this case a String) and the values are PersistenceCapable . Like this

If you define the Meta-Data for these classes as follows 

<package name="com.mydomain">
    <class name="Account" identity-type="datastore">
        ...
        <field name="addresses" persistence-modifier="persistent">
            <map key-type="java.lang.String" value-type="com.mydomain.Address"/>
            <join/>
        </field>
    </class>

    <class name="Address" identity-type="datastore">
        ...
    </class>
</package>


This will create 3 tables in the datastore, one for Account , one for Address and a join table also containing the key.



If you want to configure the names of the columns in the "join" table you would use the <key> and <value> subelements of <field> as shown above.

Please note that the column ADPT_PK_IDX is added by DataNucleus when the column type of the key is not valid to be part of a primary key (with the RDBMS being used). If the column type of your key is acceptable for use as part of a primary key then you will not have this "ADPT_PK_IDX" column.



Map[PC, Simple]

This operates exactly the same as "Map[Simple, PC]" except that the additional table is for the key instead of the value.

Map[Simple, Simple]

Here our keys and values are of simple types (in this case a String). Like this

If you define the Meta-Data for these classes as follows 

<package name="com.mydomain">
    <class name="Account" identity-type="datastore">
        ...
        <field name="addresses" persistence-modifier="persistent">
            <map key-type="java.lang.String" value-type="java.lang.String"/>
            <join/>
        </field>
    </class>
</package>

This results in just 2 tables. The "join" table contains both the key AND the value.



If you want to configure the names of the columns in the "join" table you would use the <key> and <value> subelements of <field> as shown above.

Please note that the column ADPT_PK_IDX is added by DataNucleus when the column type of the key is not valid to be part of a primary key (with the RDBMS being used). If the column type of your key is acceptable for use as part of a primary key then you will not have this "ADPT_PK_IDX" column.



Embedded

The above relationship types assume that all PersistenceCapable classes in the 1-N relation will have their own table. A variation on this is where you have a join table but you embed the keys, the values, or the keys and the values of the map into this join table. This is described in Embedded Maps.



1-N Map using Foreign-Key
1-N Foreign-Key Bidirectional (key stored in value)

In this case we have an object with a Map of objects and we're associating the objects using a foreign-key in the table of the value.

With these classes we want to store a foreign-key in the value table (ADDRESS), and we want to use the "alias" field in the Address class as the key to the map. If you define the Meta-Data for these classes as follows 

<package name="com.mydomain">
    <class name="Account" identity-type="datastore">
        ...
        <field name="addresses" persistence-modifier="persistent" mapped-by="account">
            <map key-type="java.lang.String" value-type="com.mydomain.Address"/>
            <key mapped-by="alias"/>
        </field>
    </class>

    <class name="Address" identity-type="datastore">
        ...
        <field name="account" persistence-modifier="persistent">
        </field>
        <field name="alias" null-value="exception">
            <column name="KEY" length="20" jdbc-type="VARCHAR"/>
        </field>
    </class>
</package>

This will create 2 tables in the datastore. One for Account , and one for Address . The table for Address will contain the key field as well as an index to the Account record (notated by the mapped-by tag).





1-N Foreign-Key Unidirectional (key stored in value)

In this case we have an object with a Map of objects and we're associating the objects using a foreign-key in the table of the value. As in the case of the bidirectional relation above we're using a field ( alias ) in the Address class as the key of the map.

In this relationship, the Account class has a Map of Address objects, yet the Address knows nothing about the Account . In this case we don't have a field in the Address to link back to the Account and so DataNucleus has to use columns in the datastore representation of the Address class. So we define the MetaData like this 

<package name="com.mydomain">
    <class name="Account" identity-type="datastore">
        ...
        <field name="addresses" persistence-modifier="persistent">
            <map key-type="java.lang.String" value-type="com.mydomain.Address"/>
            <key mapped-by="alias"/>
            <value column="ACCOUNT_ID_OID"/>
        </field>
    </class>

    <class name="Address" identity-type="datastore">
        ...
        <field name="alias" null-value="exception">
            <column name="KEY" length="20" jdbc-type="VARCHAR"/>
        </field>
    </class>
</package>

Again there will be 2 tables, one for Address , and one for Account . Note that we have no "mapped-by" attribute specified on the "field" element, and also no "join" element. If you wish to specify the names of the columns used in the schema for the foreign key in the Address table you should use the value element within the field of the map.





In terms of operation within your classes of assigning the objects in the relationship. You have to take your Account object and add the Address to the Account map field since the Address knows nothing about the Account . Also be aware that each Address object can have only one owner, since it has a single foreign key to the Account . If you wish to have an Address assigned to multiple Accounts then you should use the "Join Table" relationship above.



1-N Foreign-Key Unidirectional (value stored in key)

In this case we have an object with a Map of objects and we're associating the objects using a foreign-key in the table of the key. We're using a field ( businessAddress ) in the Address class as the value of the map.

In this relationship, the Account class has a Map of Address objects, yet the Address knows nothing about the Account . We don't have a field in the Address to link back to the Account and so DataNucleus has to use columns in the datastore representation of the Address class. So we define the MetaData like this 

<package name="com.mydomain">
    <class name="Account" identity-type="datastore">
        ...
        <field name="phoneNumbers" persistence-modifier="persistent">
            <map key-type="com.mydomain.Address" value-type="java.lang.String"/>
            <key column="ACCOUNT_ID_OID"/>
            <value mapped-by="businessPhoneNumber"/>
        </field>
    </class>

    <class name="Address" identity-type="datastore">
        ...
        <field name="businessPhoneNumber" null-value="exception">
            <column name="BUS_PHONE" length="20" jdbc-type="VARCHAR"/>
        </field>
    </class>
</package>

There will be 2 tables, one for Address , and one for Account . The key thing here is that we have specified a "mapped-by" on the "value" element. Note that we have no "mapped-by" attribute specified on the "field" element, and also no "join" element. If you wish to specify the names of the columns used in the schema for the foreign key in the Address table you should use the key element within the field of the map.





In terms of operation within your classes of assigning the objects in the relationship. You have to take your Account object and add the Address to the Account map field since the Address knows nothing about the Account . Also be aware that each Address object can have only one owner, since it has a single foreign key to the Account . If you wish to have an Address assigned to multiple Accounts then you should use the "Join Table" relationship above.