1. 数据库表关系介绍
关系型数据库表关系分为
* 一对一
* 一对多
* 多对多
举例
*
人和身份证号就是一对一
一个人只能有一个身份证号
一个身份证号只能属于一个人
* 用户和订单就是一对多，订单和用户就是多对一
一个用户可以下多个订单
多个订单属于同一个用户
* 学生和课程就是多对多
一个学生可以选修多门课程
一个课程可以被多个学生选修
* 特例
一个订单只从属于一个用户，所以mybatis
将多对一看成了一对一
案例环境准备
CREATE DATABASE `mybatis_db`;
USE `mybatis_db`;
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....
INSERT INTO `user`(`id`,`username`,`birthday`,`sex`,`address`) VALUES (1,'子 慕','2020-11-11 00:00:00','男','北京海淀'),(2,'应颠','2020-12-12 00:00:00','男','北 京海淀');
DROP TABLE IF EXISTS `orders`; CREATE TABLE `orders` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`ordertime` VARCHAR(255) DEFAULT NULL,
`total` DOUBLE DEFAULT NULL,
`uid` INT(11) DEFAULT NULL, PRIMARY KEY (`id`),
KEY `uid` (`uid`),`orders`
CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES ('1', '2020-12-12', '3000', '1');
INSERT INTO `orders` VALUES ('2', '2020-12-12', '4000', '1');
INSERT INTO `orders` VALUES ('3', '2020-12-12', '5000', '2');
-- ----------------------------
-- Table structure for sys_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_role`; CREATE TABLE `sys_role` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`rolename` VARCHAR(255) DEFAULT NULL,
`roleDesc` VARCHAR(255) DEFAULT NULL, PRIMARY KEY (`id`)
) ENGINE`sys_user_role``sys_role`=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of sys_role
-- ----------------------------
INSERT INTO `sys_role` VALUES ('1', 'CTO', 'CTO'); INSERT INTO `sys_role` VALUES ('2', 'CEO', 'CEO');
-- ----------------------------
-- Table structure for sys_user_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_user_role`; CREATE TABLE `sys_user_role` (
`userid` INT(11) NOT NULL,
`roleid` INT(11) NOT NULL, PRIMARY KEY (`userid`,`roleid`), KEY `roleid` (`roleid`),
CONSTRAINT `sys_user_role_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `sys_role` (`id`),
CONSTRAINT `sys_user_role_ibfk_2` FOREIGN KEY (`roleid`) REFERENCES `user` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of sys_user_role
-- ----------------------------
INSERT INTO `sys_user_role` VALUES ('1', '1');
INSERT INTO `sys_user_role` VALUES ('2', '1');
INSERT INTO `sys_user_role` VALUES ('1', '2');
INSERT INTO `sys_user_role` VALUES ('2', '2');
SELECT u.*,o.id oid,o.ordertime,o.total,o.uid FROM orders o RIGHT JOIN USER u ON o.uid = u.id
2. 一对一（多对一）
2.1 介绍
一
对
一
查询模型
用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
一对一查询的需求：查询所有订单，与此同时查询出每个订单所属的用户
一对一查询语句
SELECT * FROM orders o LEFT JOIN USER u ON o.`uid`=u.`id`;2.2 代码实现
1 ）Order实体
package com.lagou.domain;
public class Orders {
private Integer id;
private String ordertime;
private Double total;
private Integer uid;
// 表示当前订单属于那个用户 association
private User user;
@Override
public String toString() {
return "Orders{" +
"id=" + id +
", ordertime='" + ordertime + '\'' +
", total=" + total +
", uid=" + uid +
", user=" + user +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getOrdertime() {
return ordertime;
}
public void setOrdertime(String ordertime) {
this.ordertime = ordertime;
}
public Double getTotal() {
return total;
}
public void setTotal(Double total) {
this.total = total;
}
public Integer getUid() {
return uid;
}
public void setUid(Integer uid) {
this.uid = uid;
}
}
2）User实体
package com.lagou.domain;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
public class User implements Serializable {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
// 表示多方关系:集合 : 代表了当前用户所具有的订单列表 collection
private List<Orders> ordersList;
// 表示多方关系:集合 : 代表了当前用户所具有的角色列表 collection
private List<Role> roleList;
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", birthday=" + birthday +
", sex='" + sex + '\'' +
", address='" + address + '\'' +
", ordersList=" + ordersList +
", roleList=" + roleList +
'}';
}
public List<Role> getRoleList() {
return roleList;
}
public void setRoleList(List<Role> roleList) {
this.roleList = roleList;
}
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 Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public List<Orders> getOrdersList() {
return ordersList;
}
public void setOrdersList(List<Orders> ordersList) {
this.ordersList = ordersList;
}
}
3 ）OrderMapper接口
package com.lagou.mapper;
import com.lagou.domain.Orders;
import java.util.List;
public interface OrderMapper {
/*
一对一关联查询：查询所有订单，与此同时还要查询出每个订单所属的用户信息
*/
public List<Orders> findAllWithUser();
}
4 ）OrderMapper.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.lagou.mapper.OrderMapper">
<!--一对一关联查询：查询所有订单，与此同时还要查询出每个订单所属的用户信息-->
<resultMap id="orderMap" type="com.lagou.domain.Orders">
<id property="id" column="id"/>
<result property="ordertime" column="ordertime"/>
<result property="total" column="total"/>
<result property="uid" column="uid"/>
<!--
association : 在进行一对一关联查询配置时，使用association标签进行关联
property="user" ：要封装实体的属性名
javaType="com.lagou.domain.User" 要封装的实体的属性类型
-->
<association property="user" javaType="com.lagou.domain.User">
<id property="id" column="uid"></id>
<result property="username" column="username"></result>
<result property="birthday" column="birthday"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
</association>
</resultMap>
<select id="findAllWithUser" resultMap="orderMap">
SELECT * FROM orders o LEFT JOIN USER u ON o.uid = u.id
</select>
</mapper>5）测试代码
package com.lagou.test;
import com.lagou.domain.Orders;
import com.lagou.domain.User;
import com.lagou.mapper.OrderMapper;
import com.lagou.mapper.UserMapper;
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.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class mybatisTest {
/*
一对一关联查询：查询所有订单，与此同时还要查询出每个订单所属的用户信息
*/
@Test
public void test1() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
List<Orders> orders = mapper.findAllWithUser();
for (Orders order : orders) {
System.out.println(order);
}
sqlSession.close();
}
}
3. 一对多
3.1 介绍
一
对多查询模型
用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
一对多查询的需求：查询所有用户，与此同时查询出该用户具有的订单
一对多查询语句
SELECT *,o.id oid FROM USER u LEFT JOIN orders o ON u.`id` = o.`uid`;3.2 代码实现
1 ）User实体（上文已描述）
2 ）Order实体（上文已描述）
3）UserMapper接口
package com.lagou.mapper;
import com.lagou.domain.User;
import java.util.List;
public interface UserMapper {
/*
一对多关联查询:查询所有的用户，同时还要查询出每个用户所关联的订单信息
*/
public List<User> findAllWithOrder();
}
4 ）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.lagou.mapper.UserMapper">
<!--当前映射开启二级缓存-->
<cache></cache>
<!--一对多关联查询:查询所有的用户，同时还要查询出每个用户所关联的订单信息-->
<resultMap id="userMap" type="com.lagou.domain.User">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="birthday" column="birthday"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
<!--
collection : 一对多使用collection标签进行关联
-->
<collection property="ordersList" ofType="com.lagou.domain.Orders">
<id property="id" column="oid"></id>
<result property="ordertime" column="ordertime"/>
<result property="total" column="total"/>
<result property="uid" column="uid"/>
</collection>
</resultMap>
<select id="findAllWithOrder" resultMap="userMap">
SELECT u.*,o.id oid,o.ordertime,o.total,o.uid FROM orders o RIGHT JOIN USER u ON o.uid = u.id
</select>
</mapper>5 ）测试代码
package com.lagou.test;
import com.lagou.domain.Orders;
import com.lagou.domain.User;
import com.lagou.mapper.OrderMapper;
import com.lagou.mapper.UserMapper;
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.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class mybatisTest {
/*
一对多关联查询：查询所有用户及关联的订单信息
*/
@Test
public void test2() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> allWithOrder = mapper.findAllWithOrder();
for (User user : allWithOrder) {
System.out.println(user);
}
sqlSession.close();
}
}
4. 多对多
4.1 介绍
多对多查询的模型
用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用多对多查询的需求：查询所有用户同时查询出该用户的所有角色
多对多查询语句
SELECT
* FROM
USER u -- 用户表
LEFT JOIN user_role ur -- 左外连接中间表
ON u.`id` = ur.`uid`
LEFT JOIN role r -- 左外连接中间表
ON ur.`rid` = r.`id` ;4.2 代码实现
1）User实体（上文已描述）
2）Role实体
package com.lagou.domain;
public class Role {
private Integer id;
private String rolename;
private String roleDesc;
@Override
public String toString() {
return "Role{" +
"id=" + id +
", rolename='" + rolename + '\'' +
", roleDesc='" + roleDesc + '\'' +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
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 ）UserMapper接口
package com.lagou.mapper;
import com.lagou.domain.User;
import java.util.List;
public interface UserMapper {
/*
多对多关联查询:查询所有的用户，同时还要查询出每个用户所关联的角色信息
*/
public List<User> findAllWithRole();
}
4 ）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.lagou.mapper.UserMapper">
<!--当前映射开启二级缓存-->
<cache></cache>
<!--多对多关联查询:查询所有的用户，同时还要查询出每个用户所关联的角色信息-->
<resultMap id="userRoleMap" type="user">
<id property="id" column="id"/>
<result property="username" column="username"></result>
<result property="birthday" column="birthday"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
<collection property="roleList" ofType="role">
<id column="rid" property="id"></id>
<result column="rolename" property="rolename"></result>
<result column="roleDesc" property="roleDesc"></result>
</collection>
</resultMap>
<select id="findAllWithRole" resultMap="userRoleMap">
SELECT u.*,r.id rid,r.rolename,r.roleDesc FROM USER u LEFT JOIN sys_user_role ur ON ur.userid = u.id
LEFT JOIN sys_role r ON ur.roleid = r.id
</select>
</mapper>4 ）测试代码
package com.lagou.test;
import com.lagou.domain.Orders;
import com.lagou.domain.User;
import com.lagou.mapper.OrderMapper;
import com.lagou.mapper.UserMapper;
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.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class mybatisTest {
/*
多对多关联查询：查询所有用户及关联的角色信息
*/
@Test
public void test3() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> allWithRole = mapper.findAllWithRole();
for (User user : allWithRole) {
System.out.println(user);
}
sqlSession.close();
}
}
多表查询小结
5. 什么是嵌套查询
嵌套查询就是将原来多表查询中的联合查询语句拆成单个表的查询，再使用mybatis的语法嵌套在一 起。
举例：
* 需求：查询一个订单，与此同时查询出该订单所属的用户
- 联合查询
SELECT * FROM orders o LEFT JOIN USER u ON o.`uid`=u.`id`;
- 嵌套查询
- 先查询订单
SELECT * FROM orders
- 再根据订单uid外键，查询用户
SELECT * FROM `user` WHERE id = #{根据订单查询的uid}
- 最后使用mybatis，将以上二步嵌套起来
6. 一对一嵌套查询
6.1 介绍
需求：查询一个订单，与此同时查询出该订单所属的用户
一对一查询语句
-- 先查询订单
SELECT * FROM orders;
-- 再根据订单uid外键，查询用户
SELECT * FROM `user` WHERE id = #{订单的uid};6.2 代码实现
1）OrderMapper接口
package com.lagou.mapper;
import com.lagou.domain.Orders;
import java.util.List;
public interface OrderMapper {
/*
一对一嵌套查询：查询所有订单，与此同时还要查询出每个订单所属的用户信息
*/
public List<Orders> findAllWithUser2();
}
2）OrderMapper.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.lagou.mapper.OrderMapper">
<resultMap id="orderMap2" type="com.lagou.domain.Orders">
<id property="id" column="id"/>
<result property="ordertime" column="ordertime"/>
<result property="total" column="total"/>
<result property="uid" column="uid"/>
<!--问题：1.怎么去执行第二条sql , 2.如何执行第二条sql的时候，把uid作为参数进行传递-->
<association property="user" javaType="com.lagou.domain.User"
select="com.lagou.mapper.UserMapper.findById" column="uid"
fetchType="eager"/>
</resultMap>
<!--一对一嵌套查询-->
<select id="findAllWithUser2" resultMap="orderMap2">
SELECT * FROM orders
</select>
</mapper>3）UserMapper接口
package com.lagou.mapper;
import com.lagou.domain.User;
import java.util.List;
public interface UserMapper {
/*
根据id查询用户
*/
public User findById(Integer id);
}
4）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.lagou.mapper.UserMapper">
<!--当前映射开启二级缓存-->
<cache></cache>
<!--根据id查询用户
useCache="true" 代表当前这个statement是使用二级缓存
-->
<select id="findById" resultType="com.lagou.domain.User" parameterType="int" useCache="true">
SELECT * FROM user WHERE id = #{id}
</select>
</mapper>5）测试代码
package com.lagou.test;
import com.lagou.domain.Orders;
import com.lagou.domain.User;
import com.lagou.mapper.OrderMapper;
import com.lagou.mapper.UserMapper;
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.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class mybatisTest {
/*
一对一嵌套查询：查询所有订单及关联的用户信息
*/
@Test
public void test4() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
List<Orders> allWithUser2 = mapper.findAllWithUser2();
for (Orders orders : allWithUser2) {
System.out.println(orders);
}
sqlSession.close();
}
}
7. 一对多嵌套查询
7.1 介绍
需求：查询所有用户，与此同时查询出该用户具有的订单
一对多查询语句
-- 先查询用户
SELECT * FROM `user`;
-- 再根据用户id主键，查询订单列表
SELECT * FROM orders where uid = #{用户id};7.2 代码实现
1）UserMapper接口
package com.lagou.mapper;
import com.lagou.domain.User;
import java.util.List;
public interface UserMapper {
/*
一对多嵌套查询：查询所有的用户，同时还要查询出每个用户所关联的订单信息
*/
public List<User> findAllWithOrder2();
}
2）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.lagou.mapper.UserMapper">
<!--当前映射开启二级缓存-->
<cache></cache>
<!--一对多嵌套查询：查询所有的用户，同时还要查询出每个用户所关联的订单信息-->
<resultMap id="userOrderMap" type="com.lagou.domain.User">
<id property="id" column="id"/>
<result property="username" column="username"></result>
<result property="birthday" column="birthday"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
<!--fetchType="lazy" : 延迟加载策略
fetchType="eager": 立即加载策略
-->
<collection property="ordersList" ofType="com.lagou.domain.Orders" select="com.lagou.mapper.OrderMapper.findOByUid" column="id"/>
</resultMap>
<select id="findAllWithOrder2" resultMap="userOrderMap">
SELECT * FROM USER
</select>
</mapper>3）OrderMapper接口
package com.lagou.mapper;
import com.lagou.domain.Orders;
import java.util.List;
public interface OrderMapper {
/*
根据uid查询对应订单
*/
public List<Orders> findOByUid(Integer uid);
}
4）OrderMapper.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.lagou.mapper.OrderMapper">
<!--一对多嵌套查询-->
<select id="findOByUid" parameterType="int" resultType="com.lagou.domain.Orders">
SELECT * FROM orders WHERE uid = #{uid}
</select>
</mapper>5）测试代码
package com.lagou.test;
import com.lagou.domain.Orders;
import com.lagou.domain.User;
import com.lagou.mapper.OrderMapper;
import com.lagou.mapper.UserMapper;
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.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class mybatisTest {
/*
一对多嵌套查询：查询所有用户及关联的订单信息
*/
@Test
public void test5() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> allWithOrder2 = mapper.findAllWithOrder2();
for (User user : allWithOrder2) {
System.out.println(user);
// 要用到该用户的订单信息
//sSystem.out.println(user.getOrdersList());
}
sqlSession.close();
}
}
8. 多对多嵌套查询
8.1 介绍
需求：查询用户 同时查询出该用户的所有角色
多对多查询语句
-- 先查询用户
SELECT * FROM `user`;
-- 再根据用户id主键，查询角色列表
SELECT * FROM role r INNER JOIN user_role ur ON r.`id` = ur.`rid` WHERE ur.`uid` = #{用户id};8.2 代码实现
1）UserMapper接口
package com.lagou.mapper;
import com.lagou.domain.User;
import java.util.List;
public interface UserMapper {
/*
多对多嵌套查询：查询所有的用户，同时还要查询出每个用户所关联的角色信息
*/
public List<User> findAllWithRole2();
}
2）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.lagou.mapper.UserMapper">
<!--当前映射开启二级缓存-->
<cache></cache>
<resultMap id="userRoleMap2" type="com.lagou.domain.User">
<id property="id" column="id"/>
<result property="username" column="username"></result>
<result property="birthday" column="birthday"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
<collection property="roleList" ofType="com.lagou.domain.Role" column="id" select="com.lagou.mapper.RoleMapper.findByUid"></collection>
</resultMap>
<!--多对多嵌套查询：查询所有的用户，同时还要查询出每个用户所关联的角色信息-->
<select id="findAllWithRole2" resultMap="userRoleMap2">
SELECT * FROM USER
</select>
</mapper>3）RoleMapper接口
package com.lagou.mapper;
import com.lagou.domain.Role;
import java.util.List;
public interface RoleMapper {
/*
根据用户id查询对应角色
*/
public List<Role> findByUid(Integer uid);
}
4）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.lagou.mapper.RoleMapper">
<select id="findByUid" resultType="com.lagou.domain.Role" parameterType="int">
SELECT * FROM sys_role r INNER JOIN sys_user_role ur ON ur.roleid = r.id
WHERE ur.userid = #{uid}
</select>
</mapper>5）测试代码
package com.lagou.test;
import com.lagou.domain.Orders;
import com.lagou.domain.User;
import com.lagou.mapper.OrderMapper;
import com.lagou.mapper.UserMapper;
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.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class mybatisTest {
/*
多对多嵌套查询：查询所有用户及关联的角色信息
*/
@Test
public void test6() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> allWithRole2 = mapper.findAllWithRole2();
for (User user : allWithRole2) {
System.out.println(user);
}
sqlSession.close();
}
}
9. 嵌套查询小结
节选自拉钩教育JAVA系列课程
2451
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
