Bidirectional support with Associate Tables(many to many)

405 views
Skip to first unread message

daniel

unread,
Jul 19, 2010, 12:31:44 AM7/19/10
to mybatis-user
Before I begin, I would like to say being quite new to mybatis or
other ORM's for that matter, what a great framework the developers
have put together. I love the fact that the details are not that
obscure that you rely on magic although i assume a little bit does
occur:) Many thanks to Clinton, Brendon, Larry and all the other
contributors.

For my question on bidirectional support, I am more than happy to add
details to the project in a FAQ section or send pass through the
details i gather on to add to the mybatis manual/document.

The background -
I am trying to find details about how i can manage bidirectional many
to many mappings. (these tables aren't accurate, ie - not tested or
implemented)

create table User(
uid INTEGER,
name VARCHAR,
CONSTRAINT user_pkey PRIMARY KEY(uid)
);

create table UserRole(
uid INTEGER,
rid INTEGER,
CONSTRAINT user_role_pkey PRIMARY KEY (uid, rid),
CONSTRAINT userRole_rid_fkey FOREIGN KEY (rid) REFERENCES
"role" (rid),
CONSTRAINT userRole_uid_fkey FOREIGN KEY (uid) REFERENCES ssguser
(uid)
);

create table Role(
rid INTEGER,
name VARCHAR,
CONSTRAINT role_pkey PRIMARY KEY(rid)
);

Within mybatis my thoughts were
a) to either build a domain object for each table(user, userRole and
role) or
b) to simply have collections of each other as follows (which seems
reasonable and the path i chose) -

User {
int uid;
String name;
List<Role> roles;

//getters and setters
}

Role {
int rid;
String name;
List<User> users;

//getters and setters
}

Issues I encounter are how to insert new roles or users while
maintaining bidirectional connectivity. (side note: Varargs would be a
nice addition to Session methods using sqlmaps such as
select***,insert,update ....).

In my DAO I have a method that accepts a User and a Role and within
the DAO pass a Hashmap with the variables(rid and uid) to the sqlmap
insert statement.

public int addUserToRole(Role role, User user);
public int addRoleToUser(User user, Role role);

The basic idea is below

1. Call a sql map insert to add to the UserRole table (not cached);
2. Complete the join with the two objects -
user.addRole(role);
role.addUser(user);

Now to the sqlmaps
Here is where i have been scratching my head a little, i have googled
this and seem to get little in terms of concrete understanding of how
this should be carried out. My search has pointed to using caching and
lazy loading which is turned on by default according to the
documentation.

So say i have the following

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
"http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<mapper namespace="User">

<resultMap id="userResultMap" type="User" >
<id property="uid" column="uid"/>
<result property="name" column="uname"/>
<collection property="roles" ofType="Role"
resultMap="Role.roleResultMap"/>
</resultMap>

<select id="selectByUserId" parameterType="int"
resultMap="userResultMap">
SELECT
U.uid as uid,
U.name as uname,
R.rid as rid,
R.name as rname
FROM user U
left outer join userRole UR on U.uid = UR.uid
left outer join role R on UR.rid = R.rid
WHERE U.uid = #{id}
</select>

<insert id="insertRole" parameterType="Map">
INSERT into userRole
(uid, rid)
VALUES
(#{uid}, #{rid})
</insert>
</mapper>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
"http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<mapper namespace="Role">

<resultMap id="roleResultMap" type="Role" >
<id property="rid" column="rid"/>
<result property="name" column="rname"/>
<collection property="users" ofType="User"
resultMap="User.userResultMap"/>
</resultMap>

<select id="selectByRoleId" parameterType="int"
resultMap="roleResultMap">
SELECT
R.rid as rid,
R.name as rname,
U.uid as uid,
U.name as uname
FROM Role R
left outer join userRole UR on UR.uid = R.rid
left outer join user U on UR.uid = U.uid
WHERE R.rid = #{id}
</select>

<!-- a little redundant but makes user inserts clear-->
<insert id="insertUser" parameterType="Map">
INSERT into userRole
(uid, rid)
VALUES
(#{uid}, #{rid})
</insert>
</mapper>

Although when i try and achieve this i get

Exception in thread "main" java.lang.StackOverflowError
at sun.nio.cs.UTF_8.updatePositions(UTF_8.java:58)
at sun.nio.cs.UTF_8$Decoder.xflow(UTF_8.java:165)
at sun.nio.cs.UTF_8$Decoder.decodeArrayLoop(UTF_8.java:241)
at sun.nio.cs.UTF_8$Decoder.decodeLoop(UTF_8.java:305)
at java.nio.charset.CharsetDecoder.decode(CharsetDecoder.java:544)
at java.lang.StringCoding$StringDecoder.decode(StringCoding.java:140)
at java.lang.StringCoding.decode(StringCoding.java:173)
at java.lang.String.<init>(String.java:443)
at org.postgresql.core.Encoding.decode(Encoding.java:193)
at org.postgresql.core.Encoding.decode(Encoding.java:205)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getString(AbstractJdbc2ResultSet.java:
1879)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getString(AbstractJdbc2ResultSet.java:
2405)
at
org.apache.ibatis.type.StringTypeHandler.getNullableResult(StringTypeHandler.java:
17)
at
org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:
29)
at
org.apache.ibatis.executor.resultset.FastResultSetHandler.getPropertyMappingValue(FastResultSetHandler.java:
238)
at
org.apache.ibatis.executor.resultset.FastResultSetHandler.applyPropertyMappings(FastResultSetHandler.java:
221)
at
org.apache.ibatis.executor.resultset.NestedResultSetHandler.getRowValue(NestedResultSetHandler.java:
96)
at
org.apache.ibatis.executor.resultset.NestedResultSetHandler.applyNestedResultMappings(NestedResultSetHandler.java:
126)
at
org.apache.ibatis.executor.resultset.NestedResultSetHandler.getRowValue(NestedResultSetHandler.java:
97)
at
org.apache.ibatis.executor.resultset.NestedResultSetHandler.applyNestedResultMappings(NestedResultSetHandler.java:
126)
at
org.apache.ibatis.executor.resultset.NestedResultSetHandler.getRowValue(NestedResultSetHandler.java:
97)
at
org.apache.ibatis.executor.resultset.NestedResultSetHandler.applyNestedResultMappings(NestedResultSetHandler.java:
126)
at
org.apache.ibatis.executor.resultset.NestedResultSetHandler.getRowValue(NestedResultSetHandler.java:
97)
at
org.apache.ibatis.executor.resultset.NestedResultSetHandler.applyNestedResultMappings(NestedResultSetHandler.java:
126)
at
org.apache.ibatis.executor.resultset.NestedResultSetHandler.getRowValue(NestedResultSetHandler.java:
97)
at
org.apache.ibatis.executor.resultset.NestedResultSetHandler.applyNestedResultMappings(NestedResultSetHandler.java:
126)
at
org.apache.ibatis.executor.resultset.NestedResultSetHandler.getRowValue(NestedResultSetHandler.java:
97)
at
org.apache.ibatis.executor.resultset.NestedResultSetHandler.applyNestedResultMappings(NestedResultSetHandler.java:
126)
at
org.apache.ibatis.executor.resultset.NestedResultSetHandler.getRowValue(NestedResultSetHandler.java:
97)
.....

Which I imagine is due to a circular reference of methods but i am not
sure how this is typically dealt with.

So to conclude, i accept that the way i have tried to carry out the bi-
directional dependencies for this many to many is incorrect. I hope
someone could put some clarity over where i am going wrong.

In addition i am keen to add clarity to this area and possibly use
this example as discussed to contribute to the documentation.

Daniel

daniel

unread,
Jul 20, 2010, 7:06:35 PM7/20/10
to mybatis-user
Is this a stupid question to ask, or am i attacking the problem the
wrong way?

I know this can be achieved in other frameworks but i would really
like to get this right - any hints would be greatly appreciated.

Daniel

Poitras Christian

unread,
Jul 21, 2010, 9:09:54 AM7/21/10
to mybati...@googlegroups.com
The problem you are seeing is because MyBatis cannot load populate 2 collections in the way you desire. This is because MyBatis does not resolve circular dependecies.
To correct your problem, you need to change either resultMap userResultMap's

<collection property="roles" ofType="Role"
resultMap="Role.roleResultMap"/>
Or you can change roleResultMap's

<collection property="users" ofType="User"
resultMap="User.userResultMap"/>

In either case you will probably want to use lazy loading.
<collection property="roles" ofType="Role" column="rid" select="Role.lazyloadRoleFromUserId"/>
And then simply add a select statement to Role mapper.

I know it doesn't seem optimal because you need to fetch the rows n+1 times, but It will work.


If you absolutely want to do only one select, your best option is to use a ResultHandler. It's easy to implement.
I don't have an example for MyBatis, but here is an example for the ResultHandler equivalent in iBATIS 2 - RowHandler. The idea is essentially the same.

class ReplicateHandler implements RowHandler {
private Map<Sequence, List<Spot>> found = new HashMap<Sequence, List<Spot>>();

public void handleRow(Object obj) {
@SuppressWarnings("unchecked")
Map<String, Object> map = (Map<String, Object>) obj;
Sequence sequence = (Sequence) map.get("sequence");
Spot spot = (Spot) map.get("spot");
if (!found.containsKey(sequence)) {
found.put(sequence, new ArrayList<Spot>());
}
List<Spot> spots = found.get(sequence);
spots.add(spot);
}
}

// Get replicates mapped by sequence.
ReplicateHandler replicateHandler = new ReplicateHandler();
this.queryWithRowHandler("kindchip.replicates", id, replicateHandler);


Christian

Poitras Christian

unread,
Jul 21, 2010, 9:20:38 AM7/21/10
to mybati...@googlegroups.com
I forgot to mention that the n+1 select problem occurs only if you access every role instance inside a user.
If you don't plan to access all instances of role inside a user, you can simply use lazy loading. If you plan on accessing all instances of role, I would suggest a ResultHandler to limit database load.

daniel

unread,
Jul 26, 2010, 11:30:10 PM7/26/10
to mybatis-user
Hi Christian,

Thankyou so much for the feedback, this certainly helped with
resolving the errors of circular references. I am still having issues
with getting

user.getRoles().get(0).getUsers().get(0);

Looking through the object model, I can see that i get a user with one
or more roles and that has a list of users which unfortunately isnt
being populated correctly by lazy loading.

I will keep playing with this until i get some further momentum.

The idea of using the RowHandler seems like it could be useful
considering the overhead, I suppose that you use a synchronised
instance within your DAO? Anyway i dont see why t the i cant get the
initial suggestion working perfectly. I will post again when i get
that right.

Daniel
Reply all
Reply to author
Forward
0 new messages