文章目录
Mybatis 多表查询2 - 多对多查询操作
1. 分析mybatis多对多的步骤并搭建环境变量
1.1 分析
- 示例:用户和角色
- 一个用户可以有多个角色
- 一个角色可以赋予多个用户
- 所以用户和角色之间是多对多
- 步骤
- 建立两张表:用户表、角色表
- 让用户表和角色表具有多对多关系。需要使用中间表,中间表中包含各自的主键,中间表包各自的主键,在中间表中时外键。
- 建立两个实体类:用户实体类和角色实体类
- 让用户和角色的实体类能体现出来多对多的关系
- 给包含对方一个集合引用
- 建立两个配置文件
- 用户的配置文件
- 角色配置文件
- 实现配置:
- 查询用户,可以同时获得用户所包含的角色信息
- 查询角色,可以同时获得角色所赋予的用户信息
- 建立两张表:用户表、角色表
1.2 环境搭建
-
打开window小黑窗或者数据库连接软件,创建以下几个表:
DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(11) NOT NULL auto_increment, `username` varchar(32) NOT NULL COMMENT '用户名称', `birthday` datetime default NULL COMMENT '生日', `sex` char(1) default NULL COMMENT '性别', `address` varchar(256) default NULL COMMENT '地址', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into `user`(`id`,`username`,`birthday`,`sex`,`address`) values (41,'老王','2018-02-27 17:47:08','男','北京'),(42,'小二王','2018-03-02 15:09:37','女','北京金燕龙'),(43,'小二王','2018-03-04 11:34:34','女','北京金燕龙'),(45,'传智播客','2018-03-04 12:04:06','男','北京金燕龙'),(46,'老王','2018-03-07 17:37:26','男','北京'),(48,'小马宝莉','2018-03-08 11:44:00','女','北京修正'); DROP TABLE IF EXISTS `role`; 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; insert into `role`(`ID`,`ROLE_NAME`,`ROLE_DESC`) values (1,'院长','管理整个学院'),(2,'总裁','管理整个公司'),(3,'校长','管理整个学校'); DROP TABLE IF EXISTS `user_role`; 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` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into `user_role`(`UID`,`RID`) values (41,1),(45,1),(41,2);
-
创建一个 maven 项目,并配置 pom.xml 文件:
<?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.mybatis</groupId> <artifactId>maven_mybaits_dynamicSQL</artifactId> <version>1.0-SNAPSHOT</version> <dependencies> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.2</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> <scope>test</scope> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.17</version> </dependency> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency> </dependencies> </project>
-
创建包,并在包下面存放 User 实体类:
package com.itheima.domain; import java.io.Serializable; import java.util.Date; public class User implements Serializable { private Integer id; private String username; private String address; private String sex; private Date birthday; @Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + ", address='" + address + '\'' + ", sex='" + sex + '\'' + ", birthday=" + birthday + '}'; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } }
-
在该包下,创建 Role 实体类:
package com.itheima.domain; public class Role { private Integer roleId; private String roleName; private String roleDesc; public Integer getRoleId() { return roleId; } public void setRoleId(Integer roleId) { this.roleId = roleId; } 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; } @Override public String toString() { return "Role{" + "roleId=" + roleId + ", roleName='" + roleName + '\'' + ", roleDesc='" + roleDesc + '\'' + '}'; } }
-
创建 Dao 类,并写入以下方法:
package com.itheima.dao; import com.itheima.domain.User; import java.util.List; /** * * 用户的持久层接口 */ public interface IUserDao { /** * 查询所有用户 * @return */ List<User> findAll(); /** * 根据id查询用户信息 * @param userId * @return */ User findById(Integer userId); }
-
在该包中创建另一个 dao 接口:
package com.itheima.dao; import com.itheima.domain.Role; import java.util.List; public interface IRoleDao { /** * 查新所有角色 * @return */ List<Role> findAll(); }
-
由于使用了 log4,在 resources 资源文件夹下需要配置 log4j 的属性(文件名 log4j.properties):
# Set root category priority to INFO and its only appender to CONSOLE. #log4j.rootCategory=INFO, CONSOLE debug info warn error fatal log4j.rootCategory=debug, CONSOLE, LOGFILE # Set the enterprise logger category to FATAL and its only appender to CONSOLE. log4j.logger.org.apache.axis.enterprise=FATAL, CONSOLE # CONSOLE is set to be a ConsoleAppender using a PatternLayout. log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout log4j.appender.CONSOLE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n # LOGFILE is set to be a File appender using a PatternLayout. log4j.appender.LOGFILE=org.apache.log4j.FileAppender log4j.appender.LOGFILE.File=d:\axis.log log4j.appender.LOGFILE.Append=true log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout log4j.appender.LOGFILE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n
-
在资源文件中配置连接数据库的属性(文件名称随意):
jdbc.driver=com.mysql.cj.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/day23?serverTimezone=Asia/Shanghai jdbc.username=root jdbc.password=root
-
配置 Mybatis 主配置文件(名称随意):
<?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> <!-- 配置properties--> <properties resource="jdbcConfig.properties"></properties> <!--使用typeAliases配置别名,它只能配置domain中类的别名 --> <typeAliases> <package name="com.itheima.domain"></package> </typeAliases> <!--配置环境--> <environments default="mysql"> <!-- 配置mysql的环境--> <environment id="mysql"> <!-- 配置事务 --> <transactionManager type="JDBC"></transactionManager> <!--配置连接池--> <dataSource type="POOLED"> <property name="driver" value="${jdbc.driver}"></property> <property name="url" value="${jdbc.url}"></property> <property name="username" value="${jdbc.username}"></property> <property name="password" value="${jdbc.password}"></property> </dataSource> </environment> </environments> <!-- 配置映射文件的位置 --> <mappers> <package name="com.itheima.dao"></package> </mappers> </configuration>
-
创建和 Dao 接口类所在包结构相同的文件夹组,并在下面创建映射配置文件:
-
IUserDaox.xml (名称随意,修改时请同时修改主配置文件中的 mappers 标签下的 package 标签中 name 属性值)
<?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.itheima.dao.IUserDao"> <!-- 查询所有 --> <select id="findAll" resultType="user"> select * from user; </select> <!-- 根据id查询用户 --> <select id="findById" parameterType="INT" resultType="user"> select * from user where id = #{uid} </select> </mapper>
-
IRoleDao(名称随意,修改时请同时修改主配置文件中的 mappers 标签下的 package 标签中 name 属性值)
<?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.itheima.dao.IRoleDao"> <!-- 定义 role 表的resultMap --> <resultMap id="roleMap" type="role"> <id property="roleId" column="id"></id> <result property="roleName" column="role_name"></result> <result property="roleDesc" column="role_desc"></result> </resultMap> <!-- 查询所有 --> <select id="findAll" resultType="roleMap"> select * from role; </select> </mapper>
-
在 test 测试类文件夹下创建测试类以供测试:
package com.itheima.test; import com.itheima.dao.IUserDao; import com.itheima.domain.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.ArrayList; import java.util.List; /** * @author 黑马程序员 * @Company http://www.ithiema.com * * 测试mybatis的crud操作 */ public class MybatisTest { private InputStream in; private SqlSession sqlSession; private IUserDao userDao; @Before//用于在测试方法执行之前执行 public void init()throws Exception{ //1.读取配置文件,生成字节输入流 in = Resources.getResourceAsStream("SqlMapConfig.xml"); //2.获取SqlSessionFactory SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in); //3.获取SqlSession对象 sqlSession = factory.openSession(true); //4.获取dao的代理对象 userDao = sqlSession.getMapper(IUserDao.class); } @After//用于在测试方法执行之后执行 public void destroy()throws Exception{ //6.释放资源 sqlSession.close(); in.close(); } /** * 测试查询所有 */ @Test public void testFindAll(){ //5.执行查询所有方法 List<User> users = userDao.findAll(); for(User user : users){ System.out.println(user); } } @Test public void testFindOne(){ //5.执行查询一个方法 User user = userDao.findById(50); System.out.println(user); } }
-
在该测试包下创建另外一个测试类,用于测试是否能够访问数据库并取回数据:
package com.itheima.test; import com.itheima.dao.IRoleDao; import com.itheima.domain.Role; 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.IOException; import java.io.InputStream; import java.util.List; public class RoleTest { private InputStream in; private SqlSession sqlSession; private IRoleDao iRoleDao; @Before public void init() throws IOException { in = Resources.getResourceAsStream("SqlMapConfig.xml"); SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in); sqlSession = factory.openSession(true); iRoleDao = sqlSession.getMapper(IRoleDao.class); } @After public void destroy() throws IOException { if(in != null) { in.close(); } if(sqlSession != null) { sqlSession.close(); } } @Test public void testFindAll() { List<Role> roles = iRoleDao.findAll(); for(Role role : roles) { System.out.println(role); } } }
2. 完成查询角色获取角色下所属用户信息
2.1 分析
为了完成对查询角色的同时还能获取角色下所属用户的信息,我们首先要考虑在mysql中,如何使用SQL语句来完成对两个表之间的查询。
在查询role表的时候,为了获取到user表下的数据,我们需要外键表的参与,通过外键表来获取关系,然后根据关系同时获取到user表中与role表有关系的数据。
select u.*, r.id as rid, r.ROLE_NAME, r.ROLE_DESC from role r
left outer join user_role ur on r.id = ur.rid
left outer join user u on u.id = ur.uid
查询出来的结果:
id username birthday sex address rid ROLE_NAME ROLE_DESC
41 老王 2018-02-27 17:47:08 男 北京 1 院长 管理整个学院
45 传智播客 2018-03-04 12:04:06 男 北京金燕龙 1 院长 管理整个学院
41 老王 2018-02-27 17:47:08 男 北京 2 总裁 管理整个公司
2.2 操作步骤
经过测试上述的 SQL 语句能过获得我们需要的信息,所以接下来就可以按以下步骤进行使用 Mybatis 进行数据的查询。
-
首先,我们需要在 role 实体类中,创建一个对 user 表的容器的引用,用来存放查询出来的 user 信息。
package com.itheima.domain; import java.util.List; public class Role { private Integer roleId; private String roleName; private String roleDesc; // 多对多的关系映射,一个角色可以赋予多个用户 private List<User> users; public Integer getRoleId() { return roleId; } public void setRoleId(Integer roleId) { this.roleId = roleId; } 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; } public List<User> getUsers() { return users; } public void setUsers(List<User> users) { this.users = users; } @Override public String toString() { return "Role{" + "roleId=" + roleId + ", roleName='" + roleName + '\'' + ", roleDesc='" + roleDesc + '\'' + '}'; } }
-
在映射文件中,修改配置,在结果集映射中添加 collection 字标签用来创建使封装的实体类的属性和数据库中的列名一一对应。
<resultMap id="roleMap" type="role"> <id property="roleId" column="rid"></id> <result property="roleName" column="ROLE_NAME"></result> <result property="roleDesc" column="ROLE_DESC"></result> <collection property="users" ofType="user"> <id property="id" column="id"></id> <result property="sex" column="sex"></result> <result property="address" column="address"></result> <result property="birthday" column="birthday"></result> <result property="username" column="username"></result> </collection> </resultMap>
-
然后修改 select 标签下的 SQL 配置:
<!-- 查询所有 --> <select id="findAll" resultMap="roleMap"> select u.*, r.id as rid, r.ROLE_NAME, r.ROLE_DESC from role r left outer join user_role ur on r.id = ur.rid left outer join user u on u.id = ur.uid; </select>
这里需要注意,因为 SQL 语句在 Mybatis 中是拼接的,所以即使你已经换行了,但是Mybatis 在执行的时候极有可能在拼接时将换行省略,拼接成
roler left
这种情况。为了防止这些情况的发生,在你编写 SQL 语句的时候,一定要保证空格,在换行的那一行前或者后都可以。 -
在测试类中进行测试:
package com.itheima.test; import com.itheima.dao.IRoleDao; import com.itheima.domain.Role; 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.IOException; import java.io.InputStream; import java.util.List; public class RoleTest { private InputStream in; private SqlSession sqlSession; private IRoleDao iRoleDao; @Before public void init() throws IOException { in = Resources.getResourceAsStream("SqlMapConfig.xml"); SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in); sqlSession = factory.openSession(true); iRoleDao = sqlSession.getMapper(IRoleDao.class); } @After public void destroy() throws IOException { if(in != null) { in.close(); } if(sqlSession != null) { sqlSession.close(); } } @Test public void testFindAll() { List<Role> roles = iRoleDao.findAll(); for(Role role : roles) { System.out.println("——————————每个角色的信息————————————"); System.out.println(role); System.out.println(role.getUsers()); } } }
-
结果:
——————————每个角色的信息———————————— Role{roleId=1, roleName='院长', roleDesc='管理整个学院'} [User{id=41, username='老王', address='北京', sex='男', birthday=Tue Feb 27 17:47:08 CST 2018}, User{id=45, username='传智播客', address='北京金燕龙', sex='男', birthday=Sun Mar 04 12:04:06 CST 2018}] ——————————每个角色的信息———————————— Role{roleId=2, roleName='总裁', roleDesc='管理整个公司'} [User{id=41, username='老王', address='北京', sex='男', birthday=Tue Feb 27 17:47:08 CST 2018}] ——————————每个角色的信息———————————— Role{roleId=3, roleName='校长', roleDesc='管理整个学校'} []
3. 完成查询用户获取用户所包含的角色信息
3.1 分析
根据之前的SQL语句,进行以下的修改,查询:
select u.*,r.id as rid, r.role_name, r.role_desc from user u
left OUTER join user_role ur on u.id = ur.uid
left outer join role r on r.id = ur.rid;
查询的结果:
41 老王 2018-02-27 17:47:08 男 北京 1 院长 管理整个学院
45 传智播客 2018-03-04 12:04:06 男 北京金燕龙 1 院长 管理整个学院
41 老王 2018-02-27 17:47:08 男 北京 2 总裁 管理整个公司
42 小二王 2018-03-02 15:09:37 女 北京金燕龙
43 小二王 2018-03-04 11:34:34 女 北京金燕龙
46 老王 2018-03-07 17:37:26 女 北京
48 小马宝莉 2018-03-08 11:44:00 女 北京修正
3.2 操作步骤
-
首先,在 User 实体类中添加对 Role 对象的容器的引用,以存放 Role 对象的数据
package com.itheima.domain; import java.util.List; public class Role { private Integer roleId; private String roleName; private String roleDesc; // 多对多的关系映射,一个角色可以赋予多个用户 private List<User> users; public Integer getRoleId() { return roleId; } public void setRoleId(Integer roleId) { this.roleId = roleId; } 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; } public List<User> getUsers() { return users; } public void setUsers(List<User> users) { this.users = users; } @Override public String toString() { return "Role{" + "roleId=" + roleId + ", roleName='" + roleName + '\'' + ", roleDesc='" + roleDesc + '\'' + '}'; } }
-
然后,在 UserDao 接口的映射文件中,添加结果集的映射:
<!-- 定义User的resultMap --> <resultMap id="userMap" type="user"> <id column="id" property="id"></id> <result property="username" column="username"></result> <result property="sex" column="sex"></result> <result property="address" column="address"></result> <result property="birthday" column="birthday"></result> <!-- 配置角色集合的映射 --> <collection property="roles" ofType="role"> <id property="roleId" column="rid"></id> <result property="roleName" column="role_name"></result> <result property="roleDesc" column="role_desc"></result> </collection> </resultMap>
-
然后,将 SQL 语句配置进 select 标签,同时,不要忘记将 resultType 属性改成 resultMap 属性,并将上面配置好的结果集设计为其属性值:
<!-- 查询所有 --> <select id="findAll" resultMap="userMap"> select u.*,r.id as rid, r.role_name, r.role_desc from user u left OUTER join user_role ur on u.id = ur.uid left outer join role r on r.id = ur.rid; </select>
注意换行之后的 SQL 语句的空格分隔。
-
在测试类中进行测试:
package com.itheima.test; import com.itheima.dao.IUserDao; import com.itheima.domain.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.ArrayList; import java.util.List; /** * @author 黑马程序员 * @Company http://www.ithiema.com * * 测试mybatis的crud操作 */ public class MybatisTest { private InputStream in; private SqlSession sqlSession; private IUserDao userDao; @Before//用于在测试方法执行之前执行 public void init()throws Exception{ //1.读取配置文件,生成字节输入流 in = Resources.getResourceAsStream("SqlMapConfig.xml"); //2.获取SqlSessionFactory SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in); //3.获取SqlSession对象 sqlSession = factory.openSession(true); //4.获取dao的代理对象 userDao = sqlSession.getMapper(IUserDao.class); } @After//用于在测试方法执行之后执行 public void destroy()throws Exception{ //6.释放资源 sqlSession.close(); in.close(); } /** * 测试查询所有 */ @Test public void testFindAll(){ //5.执行查询所有方法 List<User> users = userDao.findAll(); for(User user : users){ System.out.println("——————————————————————————————每个用户的信息————————————————————————————"); System.out.println(user); System.out.println(user.getRoles()); } } /** * 测试删除操作 */ @Test public void testFindOne(){ //5.执行查询一个方法 User user = userDao.findById(50); System.out.println(user); } }
-
结果:
——————————————————————————————每个用户的信息———————————————————————————— User{id=41, username='老王', address='北京', sex='男', birthday=Tue Feb 27 17:47:08 CST 2018} [Role{roleId=1, roleName='院长', roleDesc='管理整个学院'}, Role{roleId=2, roleName='总裁', roleDesc='管理整个公司'}] ——————————————————————————————每个用户的信息———————————————————————————— User{id=45, username='传智播客', address='北京金燕龙', sex='男', birthday=Sun Mar 04 12:04:06 CST 2018} [Role{roleId=1, roleName='院长', roleDesc='管理整个学院'}] ——————————————————————————————每个用户的信息———————————————————————————— User{id=42, username='小二王', address='北京金燕龙', sex='女', birthday=Fri Mar 02 15:09:37 CST 2018} [] ——————————————————————————————每个用户的信息———————————————————————————— User{id=43, username='小二王', address='北京金燕龙', sex='女', birthday=Sun Mar 04 11:34:34 CST 2018} [] ——————————————————————————————每个用户的信息———————————————————————————— User{id=46, username='老王', address='北京', sex='女', birthday=Wed Mar 07 17:37:26 CST 2018} [] ——————————————————————————————每个用户的信息———————————————————————————— User{id=48, username='小马宝莉', address='北京修正', sex='女', birthday=Thu Mar 08 11:44:00 CST 2018} []
与我们使用 SQL 语句在 mysql 数据库中查询出来的一致。