JavaEE简单示例——MyBatis多对多查询的其他几种查询方式

简单介绍：

在上一章我们简单介绍了一下在多对多的时候查询的配置方法，但是我们知识讲解了其中一种情况，还有几种情况我们没有讲解，下面我们对于剩下的三种情况直接展示代码

代码实现：

使用嵌套查询的方式配置根据商品号查询包含该商品的订单编号：

使用嵌套结果集的方式配置根据订单号查询订单内包含的商品的详细信息：

使用嵌套结果集的方式配置根据商品编号查询包含该商品的订单的详细信息：

SQL映射文件：

<?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="Mappers.more_to_more_select">
<!--    配置多对多查询中的使用订单号查询订单所包含的商品信息-->
<!--    首先是子查询-->
  <select id="selectGoodsById" resultType="Goods" parameterType="int">
    select * from goods where good_id in (select good_id from order_list where order_id = #{id})
  </select>
<!--  然后是配置父查询-->
  <select id="selectOrdersById" parameterType="int" resultMap="OrdersMapper">
    select * from orders where order_id = #{id}
  </select>
  <resultMap id="OrdersMapper" type="Orders">
    <collection property="goodsList"
                column="order_id"
                ofType="Goods"
                select="Mappers.more_to_more_select.selectGoodsById"
                javaType="java.util.List"/>
  </resultMap>

<!--  配置根据商品查询包含该商品的订单的详细信息-->
  <select id="selectGoodsById2" resultType="Orders" parameterType="int">
    select * from orders where order_id in (select order_id from order_list where good_id = #{id})
  </select>
  <select id="selectOrdersById2" resultMap="GoodsMapper" parameterType="int">
    select * from goods where good_id = #{id}
  </select>
  <resultMap id="GoodsMapper" type="Goods">
    <collection property="ordersList" column="good_id"
                javaType="java.util.List"
                ofType="Orders"
                select="Mappers.more_to_more_select.selectGoodsById2"/>
  </resultMap>

<!--  配置根据订单号查询订单内商品详细信息的嵌套结果集查询-->
  <select id="selectOrdersByGoodsId" resultMap="OrderByGoodsMapper" parameterType="int">
    select o.order_id oid,order_information,g.good_id gid,good_information
        from orders o,goods g,order_list ol
    where o.order_id = ol.order_id
    and g.good_id = ol.good_id
    and o.order_id = 1;
  </select>
  <resultMap id="OrderByGoodsMapper" type="Orders">
    <result property="order_id" column="oid"/>
    <result property="order_information" column="order_information"/>
    <collection property="goodsList" javaType="java.util.List" ofType="Goods">
      <result property="good_id" column="gid"/>
      <result property="good_information" column="good_information"/>
    </collection>
  </resultMap>

<!--  根据商品编号查询包含该商品的订单的详细信息的嵌套结果集信息-->
  <select id="selectGoodsByOrdersId" resultMap="GoodsByOrderId" parameterType="int">
    select o.order_id oid,order_information,g.good_id gid,good_information
    from orders o,goods g,order_list ol
    where o.order_id = ol.order_id
      and g.good_id = ol.good_id
      and g.good_id = 1;
  </select>
  <resultMap id="GoodsByOrderId" type="Goods">
    <result property="good_id" column="gid"/>
    <result property="good_information" column="good_information"/>
    <collection property="ordersList" javaType="java.util.List" ofType="Orders">
      <result property="order_id" column="oid"/>
      <result property="order_information" column="order_information"/>
    </collection>
  </resultMap>
</mapper>

接口文件：

package Mappers;

import com.mybatis.POJO.Goods;
import com.mybatis.POJO.Orders;

import java.util.List;

public interface more_to_more_select {
    public List<Goods> selectGoodsById(int i);
    public List<Orders> selectOrdersById(int i);
    public List<Orders> selectGoodsById2(int i);
    public List<Goods> selectOrdersById2(int i);
    public List<Orders> selectOrdersByGoodsId(int i);
    public List<Goods> selectGoodsByOrdersId(int i);
}

测试类：

package Mappers;

import com.mybatis.POJO.Goods;
import com.mybatis.POJO.Orders;
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.Before;
import org.junit.Test;

import java.io.InputStream;

public class more_to_more_selectTest {
    SqlSession session = null;
    more_to_more_select mapper = null;

    @Before
    public void setUp() throws Exception {
        InputStream stream = Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(stream);
        session = build.openSession(true);
        mapper = session.getMapper(more_to_more_select.class);
    }
    @Test
    public void testSelectGoodsById() {
        for (Goods goods : mapper.selectGoodsById(1)) {
            System.out.println(goods.toString());
        }
    }

    @Test
    public void testSelectOrdersById() {
        for (Orders orders : mapper.selectOrdersById(1)) {
            System.out.println(orders.toString());
        }
    }
    @Test
    public void testSelectOrderById2(){
        for (Goods goods : mapper.selectOrdersById2(1)) {
            System.out.println(goods.toString());
        }
    }
    @Test
    public void testSelectGoodsById2(){
        for (Orders orders : mapper.selectGoodsById2(1)) {
            System.out.println(orders.toString());
        }
    }
    @Test
    public void selectOrdersByGoodsId(){
        for (Orders orders : mapper.selectOrdersByGoodsId(1)) {
            System.out.println(orders.toString());
        }
    }
    @Test
    public void selectGoodsByOrdersId(){
        for (Goods goods : mapper.selectGoodsByOrdersId(1)) {
            System.out.println(goods.toString());
        }
    }
}

注意点：

唯一的注意点就是我们一定要注意类和表的查询嵌套关系，如果发现写着写着就乱了说明还是记得不牢，回去再看看基本的逻辑结构。

嵌套查询和嵌套结果集的对比：

其实在选择嵌套查询还是选择嵌套结果集这个就是看个人喜欢就好，嵌套查询是使用简单的SQL语句，但是配置映射文件的时候会比较复杂一点。嵌套结果集是使用比较复杂的SQL语句，但是SQL映射文件的配置比较简单。