|
|
|
<select id="selectGroupUser" parameterType="java.lang.Integer" resultType="sy.model.GroupUser"> SELECT group_user.id, group_user.userid, group_user.groupid FROM group_user where group_user.userid=#{id,jdbcType=INTEGER} </select> <resultMap type="sy.model.User" id="userResultMap" extends="BaseResultMap"> <collection property="groupUsers" column="id" javaType="list" select="selectGroupUser" /> </resultMap> <select id="getAll" resultMap="userResultMap"> select id, name, loginname, pwd, email, mobile, createtime from user </select>
|
|
|
|
|
|
|
昨天搞到现在，出现一些小bug 现在OK了，总结一下： 
JUNT测试数据输出如下： 
|
|
|
|
|
|
|
刚刚通过user 查出对应group_user表的group_id 下面继续查对应group表的name名称： 同时用了sql的N+1查询，不推荐，要进行优化：
[sy.dao.UserMapper.getAll]==> Preparing: select id, name, loginname, pwd, email, mobile, createtime from user [sy.dao.UserMapper.getAll]==> Parameters: [sy.dao.UserMapper.selectGroupUser]====> Preparing: SELECT group_user.id, group_user.userid, group_user.groupid FROM group_user where group_user.userid=? [sy.dao.UserMapper.selectGroupUser]====> Parameters: 1(Integer) [sy.dao.UserMapper.selectGroupUser]<==== Total: 1 [sy.dao.UserMapper.selectGroupUser]====> Preparing: SELECT group_user.id, group_user.userid, group_user.groupid FROM group_user where group_user.userid=? [sy.dao.UserMapper.selectGroupUser]====> Parameters: 3(Integer) [sy.dao.UserMapper.selectGroupUser]<==== Total: 1
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
返回 /mybatis/src/main/java/sy/model/User.java 这里不要动 package sy.model; import java.util.List;
public class User {
//1、user里面加入中间表角色分组用户表模块 private List<GroupUser> groupUsers; //2、Alt+Shift+s自动生成GET SET方法 public List<GroupUser> getGroupUsers() { return groupUsers; }
public void setGroupUsers(List<GroupUser> groupUsers) { this.groupUsers = groupUsers; }
|
|
|
|
|
|
|
回到/mybatis/src/main/java/sy/mapping/UserMapper.xml 
|
|
|
|
|
|
|
|
|
|
|
|
刚只是优化找到了groupid,最后找出user 对应 group name: 
|
|
|
|
|
|
|
最后语法为： SELECT `user`.id, `user`.`name`, `user`.loginname, `user`.pwd, `user`.sex, `user`.mobile, group_user.id, group_user.userid, group_user.groupid, `group`.id, `group`.groupname, `group`.createtime FROM `user` INNER JOIN group_user ON `user`.id = group_user.userid INNER JOIN `group` ON group_user.groupid = `group`.id
|
|
|
|
