On Oct 26, 2005, at 19:00, Thomas Dudziak wrote:
On 10/27/05, Jason A. Lunn <[EMAIL PROTECTED]> wrote:
I have an indirection table foo_bar that happily implements an
m:n relationship between foo and bar. the columns of foo_bar include
foo_id, bar_id, and a unique id for the association itself (mysql
backend, the id column is inserted automatically). This is because it
is relevant to my application to materialize references to the same
object at different indices of the collection upon retrieval.
I started out doing all the insertion into this table by hand. When I
retrieve foo or bar, I successfully get a collection containing all
the expected elements at all the right indices. But now I'm trying to
get more sophisticated, and thus writing an online editor for this
relationship instead of populating the foo_bar table through the
mysql command line. What I'm finding is that OJB is deduping my
collection by ignoring successive references to elements it has
already seen in the current transaction. The result is that I don't
get as many rows inserted into my indirection table as I want.
For instance:
Foo foo1 = new Foo();
Bar bar1 = new Bar();
broker.beginTransaction();
broker.store( bar1 );
broker.store( foo1 );
bar1.getFoos().add( foo1 );
bar1.getFoos().add( foo1 );
broker.store( bar1 );
broker.commitTransaction();
I would expect the above to result in an insert into the foo table,
an insert into the bar table, and 2 insertions into the foo_bar
table. I get the expected behavior in the foo and bar tables, but I
only get one new record into the foo_bar table.
Someone I know suggested that I manually iterate over the collection
using the link() and unlink() methods of BrokerHelper, but I was
convinced that there was a more elegant way to do this, possibly a
configuration file tweak to tell OJB to create multiple links in the
indirection table rather than assuming that I only want one.
Your thoughts appreciated.
How is the indirection table defined in the database ? Is there a
compound primary key defined over the columns used to refer to the two
classes ?
Also, you could configure P6Spy to trace the generated SQL in order to
see whether OJB or the database ignores the second insert. Details on
how to configure P6Spy can be found here:
http://db.apache.org/ojb/docu/faq.html#traceProfileSQL
Tom
CREATE TABLE `foo_bar` (
`id` int(11) NOT NULL auto_increment,
`foo_id` int(11) NOT NULL default '0',
`bar_id` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Here's the output from Pspy:
1130371602114|34|0|statement||select count(*) from foo
1130371602229|3|0|statement||INSERT INTO bar (name) VALUES ('bar1')
1130371602247|4|0|statement||SELECT LAST_INSERT_ID() FROM bar LIMIT 1
1130371602288|2|0|statement||INSERT INTO foo (name) VALUES ('foo1')
1130371602290|1|0|statement||SELECT LAST_INSERT_ID() FROM foo LIMIT 1
1130371602301|2|0|statement||UPDATE bar SET name='bar1' WHERE id = '5'
1130371602323|2|0|statement||SELECT foo_id FROM foo_bar WHERE bar_id='5'
1130371602357|2|0|statement||INSERT INTO foo_bar (bar_id,foo_id)
VALUES ('5','7')
1130371602378|2|0|commit||
- Jason
