Castor is working wonderfully at loading some fairly complex relationships, however, I've come across something that I don't think Castor can handle without me changing the database structure substantially.
I have a Product table (structure is found below) whose Key is productid. This Product table (and object) have several objects such as DataSources that are pulled in a collection based upon that productid using the many-key element. This works fine.
However, I have a situation where there is a referencing table for product headers that uses the Product.categoryid key instead of Product.productid.
In the SQL statement right here you'll see that using a normal "many-key" approach, it assumes that Product.productid will always be used (LEFT OUTER JOIN productheader ON product.productid=productheader.categoryid)
SELECT product.description,product.mfgpartno,product.categoryid,product.manufac turerid,productdatasources.productdatasourcesid,productheader.producthea derid FROM product LEFT OUTER JOIN productdatasources ON product.productid=productdatasources.productid LEFT OUTER JOIN productheader ON product.productid=productheader.categoryid WHERE product.productid=?
What I need it to do is let me change the many-key slightly so the sql becomes:
LEFT OUTER JOIN productheader ON product.categoryid=productheader.categoryid
I have not yet determined a way to do this with Castor -- since Castor always seems to do the join with the Object key defined by 'identity' in the class element -- and the many-key element doesn't seem to have any arguments to change that.
<sql many-key="categoryid">
I would like to do:
<sql many-key="categoryid" name="categoryid"> to override what is the default <sql many-key="categoryid" name="productid">
though I'd call the name element something different in this case, such as many-key-id.
The reason this is done in the database is because if I were to use the ProductID for the ProductHeader table, instead of CategoryID, the Header table would be exponentially bigger than it needs to be, having duplicate entries for thousand of products, rather that just the several hundred categories that those products fall under. This is a simple scenario using JDBC SQL -- could you shine some light please on how to implement this with Castor.
I appreciate your advice in advance.
Ben
********* Mapping File for Product, DataSource, and Header objects ************
Note: I have not included mappings for object that are not important to the question at hand ... such as Category, Manufacturer, AccessoryProducts etc.
<class name="com.company.beans.content.ProductDTO" identity="id" key-generator="MAX" read-only="true">
<cache-type type="count-limited" capacity="500"/>
<map-to table="product" />
<field name="id" type="integer">
<sql name="productid" type="integer" />
</field> <field name="description" type="string">
<sql name="description" type="char" />
</field> <field name="mfgPartNumber" type="string">
<sql name="mfgpartno" type="char" />
</field><field name="category" type="com.company.beans.content.CategoryDTO">
<sql name="categoryid"/>
</field>
<field name="manufacturer" type="com.company.beans.content.ManufacturerDTO">
<sql name="manufacturerid"/>
</field>
<!-- Load all dataSource to go with it -->
<field name="dataSource" type="com.company.beans.content.ProductDataSourceDTO" required="true" collection="arraylist" set-method="setDataSources" get-method="getDataSources">
<sql many-key="productid" />
</field>
<!-- Load all headers to go with it -->
<field name="headers" type="com.company.beans.content.ProductHeaderDTO" required="true" collection="arraylist" set-method="setHeaders" get-method="getHeaders">
<sql many-key="categoryid" />
</field>
<!-- Load all accessoryProducts to go with it
<field name="accessoryProduct" type="com.company.beans.content.AccessoryProductDTO" required="true" collection="arraylist" set-method="setAccessories" get-method="getAccessories">
<sql many-key="productid" />
</field>
-->
</class>
<class name="com.company.beans.content.ProductDataSourceDTO" identity="id" key-generator="MAX">
<cache-type type="unlimited"/>
<map-to table="productdatasources" />
<field name="id" type="integer">
<sql name="productdatasourcesid" type="integer" />
</field> <field name="productid" type="integer">
<sql name="productid" type="integer" />
</field> <field name="code" type="string">
<sql name="datasourcesku" type="char" />
</field> <field name="name" type="string">
<sql name="datasource" type="char" />
</field></class>
<class name="com.company.beans.content.ProductHeaderDTO" identity="id" key-generator="MAX">
<cache-type type="unlimited"/>
<map-to table="productheader" />
<field name="id" type="integer">
<sql name="productheaderid" type="integer" />
</field> <field name="name" type="string">
<sql name="name" type="char" />
</field> <field name="categoryID" type="integer">
<sql name="categoryid" type="integer" />
</field><!-- Load all accessoryProducts to go with it -->
<field name="attributes" type="com.company.beans.content.ProductAttributeDTO" required="true" collection="arraylist" set-method="setAttributes" get-method="getAttributes">
<sql many-key="templateheaderid" />
</field>
</class>
****** Table Structure for product, productdatasources, and productheader ***********
CREATE TABLE product ( productid int(10) unsigned NOT NULL default '0', manufacturerid int(10) unsigned NOT NULL default '0', isactive tinyint(1) NOT NULL default '0', mfgpartno varchar(25) NOT NULL default '', description text NOT NULL, productstatusid tinyint(4) NOT NULL default '0', categoryid int(10) unsigned NOT NULL default '0', PRIMARY KEY (productid), INDEX (manufacturerid), INDEX (categoryid) ) TYPE=InnoDB;
CREATE TABLE productdatasources ( productdatasourcesid int(10) unsigned NOT NULL default '0', productid int(10) unsigned NOT NULL default '0', datasource varchar(60) binary default NULL, datasourcesku varchar(60) binary default NULL, PRIMARY KEY (productdatasourcesid), INDEX (productid) ) TYPE=InnoDB;
CREATE TABLE productheader ( productheaderid int(10) unsigned NOT NULL default '0', templateheaderid int(10) unsigned NOT NULL default '0', name varchar(100) binary default NULL, categoryid int(10) unsigned NOT NULL default '0', PRIMARY KEY (productheaderid), INDEX (templateheaderid), INDEX (categoryid) ) TYPE=InnoDB;
--------------------------------------------- Ben Christensen
----------------------------------------------------------- If you wish to unsubscribe from this mailing, send mail to
[EMAIL PROTECTED] with a subject of:
unsubscribe castor-dev
