Using Castor JDO for SQL Mapping
Pages: 1, 2, 3, 4, 5, 6, 7

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.

Pages: 1, 2, 3, 4, 5, 6, 7

Next Page