罗里吧嗦
示例：用户user和角色role 一个用户可以有多个角色 一个角色可以赋予多个用户 步骤： 1、建立两张表：用户表，角色表 让用户表和角色表具有多对多的关系。 需要使用中间表，中间表中包含各自的主键，在中间表中是外键。 多对多关系其实我们看成是双向的一对多关系。 2、建立两个实体类：用户实体类和角色实体类 让用户和角色的实体类能体现出来多对多的关系 各自包含对方一个集合引用 3、建立两个配置文件 用户的配置文件 角色的配置文件 4、实现配置： 当我们查询用户时，可以同时得到用户所包含的角色信息 当我们查询角色时，可以同时得到角色的所赋予的用户信息
mysql准备
CREATE TABLE `user` ( `uid` int(11) NOT NULL auto_increment, `name` varchar(32) NOT NULL COMMENT ‘用户名称‘, `sex` char(1) default NULL COMMENT ‘性别‘, `address` varchar(256) default NULL COMMENT ‘地址‘, PRIMARY KEY (`uid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `role` ( `ID` int(11) NOT NULL COMMENT ‘编号‘, `ROLE_NAME` varchar(30) default NULL COMMENT ‘角色名称‘, `ROLE_DESC` varchar(60) default NULL COMMENT ‘角色描述‘, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `user_role` ( `UID` int(11) NOT NULL COMMENT ‘用户编号‘, `RID` int(11) NOT NULL COMMENT ‘角色编号‘, PRIMARY KEY (`UID`,`RID`), KEY `FK_Reference_10` (`RID`), CONSTRAINT `FK_Reference_10` FOREIGN KEY (`RID`) REFERENCES `role` (`ID`), CONSTRAINT `FK_Reference_9` FOREIGN KEY (`UID`) REFERENCES `user` (`uid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
从 Role 出发，我们也可以发现一个角色可以由多个人扮演，这样角色到用户的关系也还是一对多关系。
这样我们就可以认为 User 与 Role 的多对多关系，可以被拆解成两个一对多关系来实现
那么，从Role出发，怎么才能查询到所有role，并且扮演这些role的user呢？
前面说了，多表查询，需要用到一个中间表，所以可以分为两个步骤
1. role表左外连中间表user_role
2. 根据上面的左外连的结果，再左外连user表
也就是
<!-- 查询所有role，并且查询到扮演这些role的user-->
<select id="findAll" resultMap="roleUserMap">
select * from role r
left outer join user_role ur on r.id = ur.rid
left outer join user u on u.uid = ur.uid
</select>
然后从user表到role表也是类似的过程
1.创建maven项目，导入依赖
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.cong</groupId> <artifactId>mybatis_many2many</artifactId> <version>1.0-SNAPSHOT</version> <packaging>jar</packaging> <dependencies> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.4.5</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.6</version> </dependency> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency> </dependencies> </project>
2.两个类
package com.cong.pojo; import java.util.List; public class User { private int uid; private String name; private char sex; private String addr; //查询所有user,并且包含这个user扮演的role private List<Role> roles; public List<Role> getRoles() { return roles; } public void setRoles(List<Role> roles) { this.roles = roles; } @Override public String toString() { return "User{" + "uid=" + uid + ", name=‘" + name + ‘\‘‘ + ", sex=" + sex + ", addr=‘" + addr + ‘\‘‘+ ‘}‘; } public int getUid() { return uid; } public void setUid(int uid) { this.uid = uid; } public String getName() { return name; } public void setName(String name) { this.name = name; } public char getSex() { return sex; } public void setSex(char sex) { this.sex = sex; } public String getAddr() { return addr; } public void setAddr(String addr) { this.addr = addr; } } package com.cong.pojo; import java.util.List; public class Role { private int rid; private String roleName; private String roleDesc; //一个角色可以由多个用户扮演 private List<User> users; public List<User> getUsers() { return users; } public void setUsers(List<User> users) { this.users = users; } @Override public String toString() { return "Role{" + "rid=" + rid + ", roleRname=‘" + roleName + ‘\‘‘ + ", roleDesc=‘" + roleDesc + ‘\‘‘ + ‘}‘; } public int getRid() { return rid; } public void setRid(int rid) { this.rid = rid; } public String getRoleName() { return roleName; } public void setRoleName(String roleName) { this.roleName = roleName; } public String getRoleDesc() { return roleDesc; } public void setRoleDesc(String roleDesc) { this.roleDesc = roleDesc; } }
3.两个代理接口
package com.cong.mapper; import com.cong.pojo.Role; import java.util.List; public interface RoleMapper { List<Role> findAll();//查询所有role，同时获取扮演这个role的所有user } package com.cong.mapper; import com.cong.pojo.User; import java.util.List; public interface UserMapper { List<User> findAll();//查询所有user，同时获取user含有的role信息 User findById(int id); }
4.SqlMapConfig.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <typeAliases> <package name="com.cong.pojo"></package> </typeAliases> <environments default="mysql"> <environment id="mysql"> <transactionManager type="JDBC"></transactionManager> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"></property> <property name="url" value="jdbc:mysql://localhost:3306/rainbow?characterEncoding=UTF-8"></property> <property name="username" value="root"></property> <property name="password" value="123456"></property> </dataSource> </environment> </environments> <mappers> <!--<mapper class="com.cong.mapper.UserMapper"></mapper>--> <package name="com.cong.mapper"></package> </mappers> </configuration>
5.UserMapper.xml
<?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="com.cong.mapper.UserMapper"> <!-- 定义User的resultMap--> <resultMap id="userRoleMap" type="user"> <id property="uid" column="uid"></id> <result property="name" column="name"></result> <result property="addr" column="addr"></result> <result property="sex" column="sex"></result> <!--配置角色集合的映射--> <collection property="roles" ofType="role"> <id property="rid" column="id"></id> <result property="roleName" column="role_name"></result> <result property="roleDesc" column="role_desc"></result> </collection> </resultMap> <!-- 查询所有user，以及它扮演的role --> <select id="findAll" resultMap="userRoleMap"> select * from user u left outer join user_role ur on u.uid = ur.uid left outer join role r ON r.ID = ur.RID </select> <select id="findById" resultType="user" parameterType="int"> select * from user where uid = #{uid}; </select> </mapper>
6.RoleMapper.xml
<?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="com.cong.mapper.RoleMapper"> <!--定义role表的ResultMap--> <!-- property是pojo对象的属性，column是表的字段 --> <resultMap id="roleUserMap" type="role"> <id property="rid" column="id"></id> <result property="roleName" column="role_name"></result> <result property="roleDesc" column="role_desc"></result> <!--用户集合的映射--> <collection property="users" ofType="user"> <id property="uid" column="uid"></id> <result property="name" column="name"></result> <result property="addr" column="addr"></result> <result property="sex" column="sex"></result> </collection> </resultMap> <!-- 查询所有role，并且查询到扮演这些role的user--> <select id="findAll" resultMap="roleUserMap"> select * from role r left outer join user_role ur on r.id = ur.rid left outer join user u on u.uid = ur.uid </select> </mapper>
7.测试类
import com.cong.mapper.RoleMapper; import com.cong.mapper.UserMapper; import com.cong.pojo.Role; import com.cong.pojo.User; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.After; import org.junit.Before; import org.junit.Test; import java.io.InputStream; import java.util.List; public class TestTables { private InputStream inputStream; private SqlSession sqlSession; private UserMapper userMapper; RoleMapper roleMapper; @Test //查询所有user，同时获取user含有的role信息 public void findAllUser() { List<User> users = userMapper.findAll(); for (User user : users) { System.out.println(user.toString()); if(! user.getRoles().isEmpty()){ for (Role role : user.getRoles()) { System.out.println("\t" + role.toString()); } } } } @Test //查询所有role，同时获取扮演这个role的所有user public void findAllRole() { List<Role> roles = roleMapper.findAll(); for (Role role : roles) { System.out.println(role.toString()); if(!role.getUsers().isEmpty()){ for (User user : role.getUsers()) { System.out.println("\t" + user.toString()); } } } } @Before public void init() throws Exception{ inputStream = Resources.getResourceAsStream("SqlMapConfig.xml"); SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream); sqlSession = factory.openSession(); roleMapper = sqlSession.getMapper(RoleMapper.class); userMapper = sqlSession.getMapper(UserMapper.class); } @After public void destroy() throws Exception{ sqlSession.commit(); sqlSession.close(); inputStream.close(); } }
8.目录结构，一个测试结果
原文：https://www.cnblogs.com/ccoonngg/p/11349592.html