Mybatis多对多
第一次写博客,请大家多多关照
数据库表的关系
数据库表我简单的画了下,3张主表 2张关系映射表
原则:
不同的角色用户登录 显示不同的权限列表
一个用户可以多个角色 一个角色有多个权限
实体类
用户 :
// An highlighted block
public class User {
private Integer uid;
private String uname;
private List<Role> roleList;
public List<Role> getRoleList() {
return roleList;
}
public void setRoleList(List<Role> roleList) {
this.roleList = roleList;
}
public Integer getUid() {
return uid;
}
public void setUid(Integer uid) {
this.uid = uid;
}
public String getUname() {
return uname;
}
public void setUname(String uname) {
this.uname = uname;
}
}
用户角色 :
// An highlighted block
public class Role {
private Integer rid;
private String rname;
private List<Qx> qxList;
public List<Qx> getQxList() {
return qxList;
}
public void setQxList(List<Qx> qxList) {
this.qxList = qxList;
}
public Integer getRid() {
return rid;
}
public void setRid(Integer rid) {
this.rid = rid;
}
public String getRname() {
return rname;
}
public void setRname(String rname) {
this.rname = rname;
}
}
用户权限 :
// An highlighted block
public class Qx {
private Integer qid;
private String qname;
public Integer getQid() {
return qid;
}
public void setQid(Integer qid) {
this.qid = qid;
}
public String getQname() {
return qname;
}
public void setQname(String qname) {
this.qname = qname;
}
}
mapper接口 :
// An highlighted block
public interface UserMapper {
public User getUserRoleQxList(int uid);
}
UserMapper.xml :
// An highlighted block
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mapper.UserMapper">
<select id="getUserRoleQxList" parameterType="int" resultMap="user_map">
SELECT
u.uid,
u.uname,
r.rid,
r.rname,
q.qid,
q.qname
FROM
USER u
LEFT JOIN u_r ON u_r.uid = u.uid
LEFT JOIN role r ON r.rid = u_r.rid
LEFT JOIN r_q ON r_q.rid = r.rid
LEFT JOIN qx q ON q.qid = r_q.qid
WHERE
u.uid = #{uid}
</select>
<resultMap type="user" id="user_map">
<id column="uid" property="uid"/>
<result column="uname" property="uname"/>
<collection property="roleList" ofType="role">
<id column="rid" property="rid"/>
<result column="rname" property="rname"/>
<collection property="qxList" ofType="qx">
<id column="qid" property="qid"/>
<result column="qname" property="qname"/>
</collection>
</collection>
</resultMap>
</mapper>
稍微把公共的语句封装了下:
// An highlighted block
public class MyBatisUtil {
private static SqlSessionFactory sqlSessionFactory ;
static {
try {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 获取Sqlsession
* @return
*/
public static SqlSession getSqlSession() {
if(sqlSessionFactory!=null) {
return sqlSessionFactory.openSession();
}else {
try {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
return sqlSessionFactory.openSession();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return sqlSessionFactory.openSession();
}
}
/**
* 关闭Sqlsession
* @param sqlSession
*/
public static void CloseSqlsession(SqlSession sqlSession) {
if(sqlSession!=null) {
sqlSession.close();
}
}
}
测试类
// An highlighted block
public class Test {
@org.junit.Test
public void test01() {
SqlSession sqlSession = MyBatisUtil.getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//根据用户ID进行查询
User user = userMapper.getUserRoleQxList(1);
System.out.println("用户名:"+user.getUname());
List<Role> roleList = user.getRoleList();
for (Role role : roleList) {
System.out.println("用户角色:"+role.getRname());
List<Qx> qxList = role.getQxList();
for (Qx qx : qxList) {
System.out.println("用户权限:"+qx.getQname());
}
System.out.println("---------------------");
}
MyBatisUtil.CloseSqlsession(sqlSession);
}
}
结果: