I'm sending this message again as I was not a subscriber to the list when I sent it earlier today. Since it hasn't shown up in the archives yet, I'm assuming it was ignored the first time around. In any case, sorry for the duplicate message if you received one earlier.
I have two classes, user and team, which are m:n associative. There is no need for an associative class between the two, so I'm attempting to use transparent mapping in OJB as desribed here: http://jakarta.apache.org/ojb/tutorial3.html#mapping%20m:n%20associations. Here are the respective entries in repository_user.xml: <class-descriptor class="com.blah.User" table="users" > <field-descriptor id="1" name="id" column="id" jdbc-type="INTEGER" primarykey="true" autoincrement="true" /> <field-descriptor id="2" name="username" column="username" jdbc-type="VARCHAR" /> <field-descriptor id="3" name="passwordDigest" column="password" jdbc-type="VARCHAR" /> <field-descriptor id="4" name="emailAddress" column="email_address" jdbc-type="VARCHAR" /> <collection-descriptor name="teams" element-class-ref="com.blah.Team" auto-retrieve="true" auto-update="true" indirection-table="user_team" > <fk-pointing-to-this-class column="user_id"/> <fk-pointing-to-element-class column="team_id"/> </collection-descriptor> </class-descriptor> <class-descriptor class="com.blah.Team" table="teams" > <field-descriptor id="1" name="id" column="id" jdbc-type="INTEGER" primarykey="true" autoincrement="true" /> <field-descriptor id="2" name="name" column="name" jdbc-type="VARCHAR" /> <collection-descriptor name="users" element-class-ref="com.blah.User" auto-retrieve="true" auto-update="true" indirection-table="user_team" > <fk-pointing-to-this-class column="team_id"/> <fk-pointing-to-element-class column="user_id"/> </collection-descriptor> </class-descriptor> Here are the table definitions: CREATE TABLE "users" ( "username" character varying(32) NOT NULL, "id" integer NOT NULL, "password" character(28) NOT NULL, "email_address" character varying(256) NOT NULL, Constraint "users_pkey" Primary Key ("id") ) WITHOUT OIDS; CREATE TABLE "teams" ( "name" character varying(128) NOT NULL, "id" integer NOT NULL, Constraint "teams_pkey" Primary Key ("id") ) WITHOUT OIDS; CREATE TABLE "user_team" ( "user_id" integer NOT NULL, "team_id" integer NOT NULL, Constraint "user_team_pkey" Primary Key ("user_id", "team_id") ) WITHOUT OIDS; In my junit test case I create 3 users and 2 teams and organize them like so: team1.getUsers().add(user1); team1.getUsers().add(user2); team1.getUsers().add(user3); team2.getUsers().add(user1); I then store the teams, so team1 has 3 users, team2 has 1 user, and user1 belongs to 2 teams. When I clear the broker cache and reload the teams, the following two asserts should pass: assertEquals(1, reloadedTeam2.getUsers().size()); assertEquals(3, reloadedTeam1.getUsers().size()); In my case, the first one passes, the second one does not. When I looked in the user_team association table in the database, there were only 3 rows, not 4. So I set the debug flag on the OJB SqlGenerator to DEBUG, reran the test, and it generated the following output: DEBUG: SQL: INSERT INTO users (id,username,password,email_address) VALUES ( ?, ?, ?, ? ) DEBUG: SQL: DELETE FROM user_team WHERE (user_id = 11) DEBUG: SQL: DELETE FROM user_team WHERE (user_id = 12) DEBUG: SQL: DELETE FROM user_team WHERE (user_id = 13) yDefaultImpl] INFO: Already created persistence broker instances: 5 DEBUG: SQL: SELECT TABLENAME,FIELDNAME,GRAB_SIZE,MAX_KEY FROM OJB_HL_SEQ WHERE (OJB_HL_SEQ.TABLENAME = ? ) AND (OJB_HL_SEQ.FIELDNAME = ? ) DEBUG: SQL: INSERT INTO OJB_HL_SEQ (TABLENAME,FIELDNAME,MAX_KEY,GRAB_SIZE) VALUES ( ?, ?, ?, ? ) DEBUG: SQL: SELECT name,id FROM teams WHERE (teams.id = ? ) DEBUG: SQL: INSERT INTO teams (id,name) VALUES ( ?, ? ) DEBUG: SQL: DELETE FROM user_team WHERE (team_id = 1) DEBUG: SQL: DELETE FROM user_team WHERE (team_id = 2) DEBUG: SQL: SELECT name,id FROM teams WHERE (teams.id = ? ) DEBUG: SQL: UPDATE teams SET name=? WHERE (teams.id = ? ) DEBUG: SQL: DELETE FROM user_team WHERE (team_id = 1) DEBUG: SQL: UPDATE users SET username=?, password=?, email_address=? WHERE (users.id = ? ) DEBUG: SQL: DELETE FROM user_team WHERE (user_id = 11) DEBUG: SQL: INSERT INTO user_team (team_id,user_id) VALUES ( 1,11 ) DEBUG: SQL: DELETE FROM user_team WHERE (user_id = 12) DEBUG: SQL: INSERT INTO user_team (team_id,user_id) VALUES ( 1,12 ) DEBUG: SQL: DELETE FROM user_team WHERE (user_id = 13) DEBUG: SQL: INSERT INTO user_team (team_id,user_id) VALUES ( 1,13 ) DEBUG: SQL: DELETE FROM user_team WHERE (team_id = 2) DEBUG: SQL: DELETE FROM user_team WHERE (user_id = 11) DEBUG: SQL: INSERT INTO user_team (team_id,user_id) VALUES ( 2,11 ) DEBUG: SQL: SELECT A0.name,A0.id FROM teams A0 WHERE A0.id = ? DEBUG: SQL: SELECT A0.password,A0.id,A0.username,A0.email_address FROM users A0,user_team WHERE (user_team.team_id = ? ) AND user_team.user_id = A0.id DEBUG: SQL: SELECT A0.name,A0.id FROM teams A0,user_team WHERE (user_team.user_id = ? ) AND user_team.team_id = A0.id DEBUG: SQL: SELECT A0.name,A0.id FROM teams A0,user_team WHERE (user_team.user_id = ? ) AND user_team.team_id = A0.id Interpreting the SQL, it looks like when saving a collection the initial DELETEs that OJB generates to clear the collection are too broad. So team1 is saved with all three users in it, and then when team2 is saved, the "DELETE FROM user_team WHERE (user_id = 11)" wipes out user1's entry for team1. In fact, that query is completely unnecessary in this instance so I'm confused as to why it exists. I tried changing the test to add the teams to the user's collections prior to calling store, but I got a duplicate key insertion exception upon storing them. So I assume the test code was correct the first time. So the question is, where does the problem lie? Is it in my repository definitions or is this a OJB SQL generation bug? I registered for the OJB bug database, but registering unfortunately doesn't give you access to browse. I also scoured the archives of the mailing list and saw a number of problems that people encountered using m:n associations, but nothing that detailed the problem I encountered. I am using OJB 0.9.5 on Win2K with JSDK 1.4.0_01 running against Postgresql 7.2.1-2 on Debian and using the latest JDBC driver. Any help would be greatly appreciated. Thanks! -Brian
