Using Castor JDO for SQL Mapping
Many-to-many Relationship
In order to properly represent many-to-many relationships in a database,
an intersection table is required. An intersection table consists of
two foreign key columns, one for each table in the many-to-many relation.
Each column may have the same key value repeated in the table rows, although
identical foreign key pairings should not appear in more than one row of the
table.
Let's say that each PortOfCall keeps track of the Ships that visit it, in
addition to each Ship maintaining its own PortOfCall list.
Each PortOfCall sees many Ships, each Ship enters many PortsOfCall. This is our many-to-many relation.
I might only have two Java classes, but I will have three database tables
to store these two classes. First, an outline of the Java classes is in
order:
public class Ship
{
String name;
// String registry; // commented out
ArrayList portsOfCall = new ArrayList();
public Ship() { ... }
public void addPortOfCall( PortOfCall poc )
{
portsOfCall.add( poc );
poc.addShip( this );
}
//other methods...
}
public class PortOfCall
{
String name;
ArrayList ships = new ArrayList;
public PortOfCall() { ... }
public void addShip( Ship ship )
{
portsOfCall.add( ship );
}
// other methods...
}
Note: Since they're not pertininent to the present discussion, I haven't shown how a ship's arrival and departure times figure into these two classes. Assume they are somehow associated with the Ship's PortOfCall instances.
Due to a limitation in the current version of Castor, I've had to rely on name alone to identify a Ship. The mapping files for both Ship and PortOfCall are as follows:
<class name="Ship" identity="name" depends="ShippingLine">
<map-to table="ship"/>
<field name="registry" type="string" required="true">
<sql name="registry" type="varchar"/>
</field>
<field name="name" type="string" required="true">
<sql name="name" type="varchar"/>
</field>
<field name="shippingLine" type="ShippingLine" required="true">
<sql name="id_shipping_line"/>
</field>
<field name="portOfCall" type="PortOfCall" collection="arraylist">
<sql name="poc_name" many-table="poc_ship" many-key="ship_name"/>
</field>
</class>
<class name="com.example.shipping.PortOfCall" identity="name">
<map-to table="port_of_call"/>
<field name="name" type="string">
<sql name="name" type="varchar"/>
</field>
<field name="ship" type="Ship" collection="arraylist">
<sql name="ship_name" many-table="poc_ship" many-key="poc_name"/>
</field>
</class>
Nothing need be changed in the ship table, and the port_of_call table is
simple enough:
CREATE TABLE port_of_call (
name VARCHAR(80)
);
I now have to add a third join table, poc_ship, that links many
port_of_call rows to many ship rows through their respective foreign
keys (as described in the mapping file):
CREATE TABLE poc_ship (
poc_name VARCHAR(80), -- port_of_call fkey
ship_name VARCHAR(80) -- ship fkey
);
It's now possible to construct a test case, part of which is shown
below:
...
ship[0].setName("Seven Seas");
ship[1].setName("Sea Six");
_shippingLine.addShip( ship[0] );
_shippingLine.addShip( ship[1] );
PortOfCall pocs[] = new PortOfCall[]
{
new PortOfCall("Pago Pago"),
new PortOfCall("Walla Walla")
};
ship[0].addPortOfCall( _poc[0] );
ship[0].addPortOfCall( _poc[1] );
ship[1].addPortOfCall( _poc[0] );
...
PersistenceFactory.getInstance().store(pocs[0], true);
// true means create, not update
PersistenceFactory.getInstance().store(pocs[1], true);
//stores all Ships instances as well
PersistenceFactory.getInstance().store(_shippingLine, true);
Now, if all went according to plan, I should see entries in the poc_ship table when I execute a SELECT * from poc_ship; SQL statement from the DBMS's command line:
poc_name | ship_name
----------------------------
Pago Pago | Sea Six
Walla Walla | Sea Six
Pago Pago | Seven Seas
If I see these entries, then I know I've successfully recorded a
many-to-many join in the database using Castor JDO.
Making the Connection
In order to be able to access the database, we need to establish a
connection to it. Castor maintains connection information in a separate file
from the mapping file. This file is typically named database.xml.
The database.xml file contains information about the database being used and its location. It also indicates the JDBC driver to be used (JDBC stands for
Java Database Connectivity, an vendor-neutral API for communicating with
relational database management systems).
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE databases PUBLIC "-//EXOLAB/Castor JDO Configuration DTD
Version 1.0//EN" "http://castor.exolab.org/jdo-conf.dtd">
<database name="shippingDB" engine="postgresql">
<driver class-name="org.postgresql.Driver" url="jdbc:postgresql:shippingDB">
<param name="user" value="Administrator"/>
<param name="password" value=""/>
</driver>
<mapping href="d:/shipping/mapping.xml" />
</database>
The database to connect to is indicated by the value of the name attribute
in the database element. The engine attribute of that element tells Castor
that it's a Postgres database (formerly known as PostgreSQL, hence the name
of the Castor database engine).
The driver element has a class-name attribute that indicates the class in
the Java classpath that functions as the JDBC driver. The url attribute is
the connection string that the JDBC driver uses to make a connection to the
Postgres database. A JDBC driver is often supplied by the database vendor or
distributor. For Postgres, an up-to-date JDBC driver can be found at http://jdbc.postgresql.org.
The param elements have user and password
information that the driver needs to log in to the database. Other JDBC
drivers may have more (or possibly fewer) parameters, so additional param
elements can be provided as children of the driver element.
Finally, the location of the JDO mapping file is given in href format.
Once the JDBC connection is established, Castor reads in the mapping.xml file
and is ready to start storing and retrieving data.