How to store recursive object in database?

Question

How can I store an object that has references to other objects of the same class?

I have tried a lot of ways to doing it with annotations and XML but all of them have failed.

In the best cases, I get the following error:

WARNING: Insert of object "pmo.domain.entities.Unit@1129829c" using statement "INSERT INTO UNIT ("NAME",PARENT_UNIT_ID,ID) VALUES (?,?,?)" failed : Parameter "#2" is not set; SQL statement: INSERT INTO UNIT ("NAME",PARENT_UNIT_ID,ID) VALUES (?,?,?) [90012-196]

ExampleTest.java:

package pmo.domain.entities;

public class ExampleTest {
    public static void Main(String[] args) {
        Unit unit1 = new Unit(0, "Unit 1");
        Unit unit2 = new Unit(1, "Unit 2", unit1);
        Unit unit3 = new Unit(2, "Unit 3", unit2);

        PersistenceManagerFactory pmf = JDOHelper.getPersistenceManagerFactory("Test");
        PersistanceManager pm = pmf.getPersistenceManager();

        Transaction transaction = pm.currentTransaction();

        try {
            transaction.begin();
            pm.makePersistent(unit1);
            pm.makePersistent(unit2);
            pm.makePersistent(unit3);
            transaction.commit();
        } finally {
            if (transaction.isActive()) {
                transaction.rollback();
            }

            pm.close();
        }
    }
}

Unit.java:

package pmo.domain.entities;

public class Unit {
    private long id;
    private String name;
    private Unit parentUnit;

    public Unit(long id, String name, Unit parentUnit) {
        this.id = id;
        this.name = name;
        this.parentUnit = parentUnit;
    }

    public Unit(long id, String name) {
        this(id, name, null);
    }
}

package.jdo:

<?xml version="1.0" encoding="UTF-8" ?>
<jdo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xmlns="http://xmlns.jcp.org/xml/ns/jdo/jdo"
     xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/jdo/jdo http://xmlns.jcp.org/xml/ns/jdo/jdo_3_1.xsd" version="3.1">
    <package name="pmo.domain.entities">
        <class name="Unit" table="unit">
            <field name="id" primary-key="true">
                <column name="id"/>
            </field>
            <field name="parentUnit">
                <column name="parent_unit_id" target-field="id"/>
                <foreign-key name="unit_id_fk" />
            </field>
        </class>
    </package>
</jdo>

Answer 1

I have solved the issue by inverting the relation. From child to parent. So now, it is parent to child using a collection.

ExampleTest.java:

package pmo.domain.entities;

public class ExampleTest {
    public static void Main(String[] args) {
        Unit unit1 = new Unit(0, "Unit 1");
        Unit unit2 = new Unit(1, "Unit 2");
        Unit unit3 = new Unit(2, "Unit 3");

        unit2.addUnit(unit3);
        unit1.addUnit(unit2);

        PersistenceManagerFactory pmf = JDOHelper.getPersistenceManagerFactory("Test");
        PersistanceManager pm = pmf.getPersistenceManager();

        Transaction transaction = pm.currentTransaction();

        try {
            transaction.begin();
            pm.makePersistent(unit1);
            transaction.commit();
        } finally {
            if (transaction.isActive()) {
                transaction.rollback();
            }

            pm.close();
        }
    }
}

Unit.java:

package pmo.domain.entities;

public class Unit {
    private long id;
    private String name;
    private Collection<Unit> childUnits;

    public Unit(long id, String name, Collection<Unit> childUnits) {
        this.id = id;
        this.name = name;
        this.childUnits = childUnits;
    }

    public Unit(long id, String name) {
        this(id, name, new ArrayList<>());
    }

    public void addUnit(Unit unit) {
        this.childUnits.add(unit);
    }

    public void removeUnit(long id) {
        childUnits.removeIf(unit -> unit.id == id);
    }
}

package.jdo:

<?xml version="1.0" encoding="UTF-8" ?>
<jdo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xmlns="http://xmlns.jcp.org/xml/ns/jdo/jdo"
     xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/jdo/jdo http://xmlns.jcp.org/xml/ns/jdo/jdo_3_1.xsd" version="3.1">
    <package name="es.indra.pmo.pmomanagement.domain.entities">
        <class name="Employee" table="employee">
            <field name="id" primary-key="true"/>
        </class>
        <class name="Unit" table="unit">
            <field name="id" primary-key="true">
                <column name="id"/>
            </field>
            <field name="childUnits">
                <order column="parent_unit_id"/>
            </field>
        </class>
    </package>
</jdo>