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>
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 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
