Referencing Another SQLContainer

When developing a database-connected application, there is usually a need to retrieve data related to one table from one or more other tables. In most cases, this relation is achieved with a foreign key reference, where a column of the first table contains a primary key or candidate key of a row in another table.

SQLContainer offers limited support for this kind of referencing relation, although all referencing is currently done on the Java side so no constraints need to be made in the database. A new reference can be created by calling the following method:

public void addReference(SQLContainer refdCont,
                         String refingCol, String refdCol);

This method should be called on the source container of the reference. The target container should be given as the first parameter. The refingCol is the name of the 'foreign key' column in the source container, and the refdCol is the name of the referenced key column in the target container.

Note: For any SQLContainer, all the referenced target containers must be different. You can not reference the same container from the same source twice.

Handling the referenced item can be done through the three provided set/get methods, and the reference can be completely removed with the removeReference() method. Signatures of these methods are listed below:

public boolean setReferencedItem(Object itemId,
        Object refdItemId, SQLContainer refdCont)
public Object getReferencedItemId(Object itemId,
                                  SQLContainer refdCont)
public Item getReferencedItem(Object itemId,
                              SQLContainer refdCont)
public boolean removeReference(SQLContainer refdCont)

The setter method should be given three parameters: itemId is the ID of the referencing item (from the source container), refdItemId is the referenced itemID (from the target container) and refdCont is a reference to the target container that identifies the reference. This method returns true if the setting of the referenced item was successful. After setting the referenced item you must normally call commit() on the source container to persist the changes to the database.

The getReferencedItemId() method will return the item ID of the referenced item. As parameters this method needs the item ID of the referencing item and a reference to the target container as an identifier. SQLContainer also provides a convenience method getReferencedItem(), which directly returns the referenced item from the target container.

Finally, the referencing can be removed from the source container by calling the removeReference() method with the target container as parameter. Note that this does not actually change anything in the database; it merely removes the logical relation that exists only on the Java-side.