11.MyBatis的嵌套查询
1.什么事嵌套查询
嵌套查询就是将原来多表查询中的联合查询语句拆成单个表的查询,再使用mybatis的语法嵌套在一
起。
-
例子:
* 需求:查询一个订单,与此同时查询出该订单所属的用户 1. 联合查询 SELECT * FROM orders o LEFT JOIN USER u ON o.`uid`=u.`id`; 2. 嵌套查询 2.1 先查询订单 SELECT * FROM orders 2.2 再根据订单uid外键,查询用户 SELECT * FROM `user` WHERE id = #{根据订单查询的uid} 2.3 最后使用mybatis,将以上二步嵌套起来
2.一对一嵌套查询
-
需求:查询一个订单,与此同时查询出该订单所属的用户
-
sql语句
-- 先查询订单 SELECT * FROM orders; -- 再根据订单uid外键,查询用户 SELECT * FROM `user` WHERE id = #{订单的uid}
2.1代码实现
(1)OrderMapper接口
// 查询用户订单
List<Orders> findUserOrders();
(2)OrderMapper.xml映射
- 一对一查询
<resultMap id="userOrdersMapper" type="orders">
<id property="id" column="id"></id>
<result property="orderTime" column="orderTime"></result>
<result property="total" column="total"></result>
<result property="uid" column="uid"></result>
<!--根据订单中uid外键,查询用户表
select:参数为userMapper的namespace.id
column:为传递的参数
-->
<association property="user" javaType="com.lagou.domain.User" select="com.lagou.mapper.UserMapper.findUserOrderByNested" column="uid">
</association>
</resultMap>
<select id="findUserOrders" resultMap="userOrdersMapper">
select * from orders
</select>
(3)userMapper接口
// 嵌套查询:查询订单拥有者
List<User> findUserOrderByNested(@Param("uid") Integer uid);
(4)UserMapper.xml映射
<resultMap id="getUserInfo" type="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>
</resultMap>
<select id="findUserOrderByNested" resultMap="getUserInfo" parameterType="Integer">
select * from `user` where id = #{uid}
</select>
(5)测试方法
@Test
public void test5() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);
List<Orders> userOrders = ordersMapper.findUserOrders();
for (Orders orders : userOrders){
System.out.println(orders);
}
sqlSession.close();
}
(6)测试结果
2.2流程解析
3.一对多嵌套查询
-
需求:查询所有用户,与此同时查询出该用户具有的订单
-
一对多查询语句
-- 先查询用户 SELECT * FROM `user`; -- 再根据用户id主键,查询订单列表 SELECT * FROM orders where uid = #{用户id};
3.1代码实现
- 思想:
- 先找到所有用户信息(即先在用户操作类xml查询出用户信息)
- 得到用户id后去查询订单表信息(关联查询)
- 根据uid=id(用户id)查询出结果
(1)userMapper接口
//嵌套查询: 查询用户的订单信息(一对多)
List<User> findUserOrder2();
(2)UserMapper.xml映射
<!-- 嵌套查询-->
<resultMap id="getUserInfoWithNested" type="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 property="ordersList" ofType="com.lagou.domain.Orders" select="com.lagou.mapper.OrdersMapper.findUserAllOrder" column="id"></collection>
</resultMap>
<select id="findUserOrder2" resultMap="getUserInfoWithNested">
select * from user
</select>
(3)OrderMapper接口
//嵌套查询:查询用户的订单
List<Orders> findUserAllOrder(@Param("id") Integer id);
(4)OrderMapper.xml映射
<!-- 嵌套查询 -->
<resultMap id="OrdersBaseInfo" type="orders">
<id property="id" column="id"></id>
<result property="orderTime" column="orderTime"></result>
<result property="total" column="total"></result>
<result property="uid" column="uid"></result>
</resultMap>
<select id="findUserAllOrder" parameterType="int" resultMap="OrdersBaseInfo">
select * from orders where uid = #{id}
</select>
(5)测试方法
/**
* 嵌套查询:一对多
* 查询用户对应的订单信息
* @throws IOException
*/
@Test
public void test6() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userOrders = userMapper.findUserOrder2();
for (User userOrder : userOrders){
System.out.println(userOrder);
}
sqlSession.close();
}
(6)测试结果
4.多对多嵌套查询
需求:查询用户,同时查询出该用户的所有角色
-
查询语句
-- 先查询用户 SELECT * FROM `user`; -- 再根据用户id主键,查询角色列表 SELECT * FROM role r INNER JOIN user_role ur ON r.`id` = ur.`rid` WHERE ur.`uid` = #{用户id};
4.1代码实现
(1)userMapper接口
/**
* 嵌套查询:查询用户对应的角色信息
*/
List<User> findAllWithRole();
(2)UserMapper.xml映射
<!-- 嵌套查询:查询用户对应的角色信息-->
<resultMap id="getUserRoleWithNested" type="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 property="roleList" ofType="com.lagou.domain.Role" select="com.lagou.mapper.RoleMapper.getRolelist" column="id"></collection>
</resultMap>
<select id="findAllWithRole" resultMap="getUserRoleWithNested">
select * from `user`
</select>
(3)RoleMapper接口
public interface RoleMapper {
List<Role> getRolelist(Integer id);
}
(4)RoleMapper.xml映射文件
<mapper namespace="com.lagou.mapper.RoleMapper">
<select id="getRolelist" resultType="role" parameterType="Integer">
SELECT * FROM sys_role r INNER JOIN sys_user_role ur ON r.id = ur.roleid WHERE ur.userid= #{id}
</select>
</mapper>
(5)测试类
/**
* 嵌套查询:多对多
* 查询用户对应的角色
* @throws IOException
*/
@Test
public void test7() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userOrders = userMapper.findAllWithRole();
for (User userOrder : userOrders){
System.out.println(userOrder);
}
sqlSession.close();
}
(6)测试结果
5.小结
- 一对一配置:使用+做配置,通过column条件,执行select查询
- 一对多配置:使用+做配置,通过column条件,执行select查询
- 多对多配置:使用+做配置,通过column条件,执行select查询
优点:简化多表查询操作
缺点:执行多次sql语句,浪费数据库性能