mybatis学习教程中级（七）一对多查询-优快云博客

本文介绍如何在MyBatis中实现一对多关联查询，包括配置mapper.xml文件中的resultMap来关联多个表的数据，以及对应的Java实体类设计。通过示例展示了如何使用collection和association标签来处理一对多的关系。

1 引言

本章实现一对多的查询，还是继续一对一的上一章基础上加上一对多。

2、一对多实现

mapper.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">
<!--命名空间：分类管理sql隔离，方便管理-->
<mapper namespace="com.ycy.mybatis.dao.OrdersCustomMapper">
    <resultMap id="orderResultMap" type="orders">
        <id column="id" property="id"/>
        <result column="user_id" property="userId"/>
        <result column="number" property="number"/>
        <result column="createtime" property="createtime"/>
        <result column="note" property="note"/>
        <association property="user" javaType="user">
            <id  column="user_id" property="id"/>
            <result column="username" property="username"/>
            <result column="address" property="address"/>
        </association>
    </resultMap>
  <resultMap id="oderAndDetailMap" type="orders" extends="orderResultMap">
        <!--继承订单信息与用户信息-->
        <!--订单明细
        ofType:集合中po类型
        -->
        <collection  property="orderdetails" ofType="Orderdetail">
            <id column="orderdetail_id" property="id"/>
            <result column="items_id" property="itemsId"/>
            <result column="items_num" property="itemsNum"/>
        </collection>
    </resultMap>
    <!--resultType进行查询-->
   <select id="findOrderCustomer" resultType="OrdersCustom">
               SELECT
          o.*,
          u.username,
          u.address
        FROM
          orders o,
          USER u
        WHERE o.user_id = u.id
   </select>
    <!--使用resultmap进行查询-->
    <select id="findOrderResultMap" resultMap="orderResultMap">
                       SELECT
          o.*,
          u.username,
          u.address
        FROM
          orders o,
          USER u
        WHERE o.user_id = u.id
   </select>
  <!--根据订单联合查询用户与订单详情  一对多关联（订单与订单详情）-->
   <select id="findOrderAndDetail" resultMap="oderAndDetailMap" >
    SELECT
      o.*,
      u.username,
      u.address ,
      d.id orderdetail_id,
      d.items_id,
      d.items_num
    FROM
      orders o,
      USER u ,
      orderdetail d
    WHERE o.user_id = u.id
    AND d.orders_id=o.id
   </select>

</mapper>

</pre><pre name="code" class="html">

Orders.java实体类

public class Orders {
    private Integer id;

    private Integer userId;

    private String number;

    private Date createtime;

    private String note;

    //用户信息
    private User user;
    //getset 省略 篇幅问题

    //订单明细
    private List<Orderdetail> orderdetails;
}

mapper.java

package com.ycy.mybatis.dao;

import com.ycy.mybatis.module.Orders;
import com.ycy.mybatis.module.OrdersCustom;

import java.util.List;

/**
 * Created by Administrator on 2015/9/9 0009.
 */
public interface OrdersCustomMapper {
   //一对一ResultType
   public List<OrdersCustom> findOrderCustomer() throws  Exception;
   //一对一ResultMap
   public  List<Orders> findOrderResultMap() throws  Exception;
   //一对多
   public  List<Orders> findOrderAndDetail() throws  Exception;
}

一对多测试

package com.ycy.mybatis.test;

import com.ycy.mybatis.dao.OrdersCustomMapper;
import com.ycy.mybatis.module.Orderdetail;
import com.ycy.mybatis.module.Orders;
import com.ycy.mybatis.module.OrdersCustom;
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.IOException;
import java.io.InputStream;
import java.util.List;

/**
 * Created by Administrator on 2015/8/31 0031.
 */
public class MybatisTest7 {
    private SqlSessionFactory sqlSessionFactory = null;
    @Before
    public void  before() throws IOException {
        String resource="SqlMapConfig.xml";
        InputStream in = Resources.getResourceAsStream(resource);
        sqlSessionFactory= new SqlSessionFactoryBuilder().build(in);
    }
    //一对多 订单-订单详情
    @Test
    public  void findOrderAndDetail() throws Exception {
        SqlSession sqlSession=sqlSessionFactory.openSession();
        OrdersCustomMapper ordersCustomMapper=     sqlSession.getMapper(OrdersCustomMapper.class);
        List<Orders>  ordersList=  ordersCustomMapper.findOrderAndDetail();
        sqlSession.close();
        for (Orders ordersCustom : ordersList) {
            System.out.println(ordersCustom.getUser().getUsername());
            List<Orderdetail>  orderdetailList=     ordersCustom.getOrderdetails();
            System.out.println(ordersCustom.getOrderdetails());
            for (Orderdetail orderdetail : orderdetailList) {
                System.err.println(orderdetail.getItemsNum());
            }

        }

    }


}

2、一对多实现（稍微复杂）

之前我们看看我们查询结果，是根据order表来的查询结果现在我们根据User表

在user.java中创建映射的属性：集合 List<Orders> orderlist

在Orders中创建映射的属性：集合List<Orderdetail> orderdetails

在Orderdetail中创建商品属性：pojo Items items

具体的java实体类我就不写出了了，我只写一个mapper.xml 文件你看懂就ok了。。。主要是requestMmap的编写

    <!--userAndDetailMap 根据用户查询订单，订单详细，商品-->
    <resultMap id="userAndDetailMap" type="com.ycy.mybatis.module.User">
        <!--用户信息-->
        <id column="user_id" property="id"/>
        <result column="username" property="username"/>
        <result column="sex" property="sex"/>
        <!--订单信息-->
        <collection property="orderlist" javaType="com.ycy.mybatis.module.Orders">
            <id column="id" property="id"/>
            <result column="user_id" property="userId"/>
            <result column="number" property="number"/>
            <result column="createtime" property="createtime"/>
            <result column="note" property="note"/>
            <!--订单明细-->
            <collection property="orderdetails" javaType="com.ycy.mybatis.module.Orderdetail">
                <id column="orderdetail_id" property="id"/>
                <result column="items_id" property="itemsId"/>
                <result column="items_num" property="itemsNum"/>
                <!--商品信息-->
                <association property="items" javaType="com.ycy.mybatis.module.Items">
                    <id column="item_id" property="id"/>
                    <result column="item_name" property="name"/>
                    <result column="item_detail" property="detail"/>
                </association>
            </collection>
        </collection>
    </resultMap>

sql语句依然可以使用这样

    <!--根据用户单联合查询用户与订单详情  一对多关联（订单与订单详情）-->
    <select id="findUserAndDetail" resultMap="userAndDetailMap" >
        SELECT
        o.*,
        u.username,
        u.address ,
        d.id orderdetail_id,
        d.items_id,
        d.items_num
        FROM
        orders o,
        USER u ,
        orderdetail d
        WHERE o.user_id = u.id
        AND d.orders_id=o.id
    </select>