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