MyBatis中连表查询，按主表分页的方法

问题描述：

查询一张订单表，但是一张订单中含有多个订单明细（多个商品），按一张订单为单位查询，需查询此订单下的所有商品。

订单表：

订单明细表：

解决方法：

订单实体类：

package com.kgc.ymw.entity;

import java.util.Date;
import java.util.List;

public class Order {
    private Integer id;

    private Integer userid;

    private String loginname;

    private String useraddress;

    private Date createtime;

    private Float cost;

    private String serialnumber;

    private Integer isdelete;

    private Integer ispay;

    private List<OrderDetail> list;//在实体类中增加一个订单详情List集合的属性list

    //省略setter,getter方法
}

订单order.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.kgc.ymw.dao.OrderMapper">
  <resultMap id="BaseResultMap" type="com.kgc.ymw.entity.Order">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="userId" jdbcType="INTEGER" property="userid" />
    <result column="loginName" jdbcType="VARCHAR" property="loginname" />
    <result column="userAddress" jdbcType="VARCHAR" property="useraddress" />
    <result column="createTime" jdbcType="TIMESTAMP" property="createtime" />
    <result column="cost" jdbcType="REAL" property="cost" />
    <result column="serialNumber" jdbcType="VARCHAR" property="serialnumber" />
    <result column="isDelete" jdbcType="INTEGER" property="isdelete" />
    <result column="ispay" jdbcType="INTEGER" property="ispay" />
    <!--   使用collection标签，属性名为list，ofType为集合的泛型，select为订单子查询的id   -->
    <collection property="list" ofType="OrderDetail" select="selectList" column="id"></collection>
  </resultMap>
  <resultMap id="detailList" type="OrderDetail">
    <id column="id" property="id"/>
    <result column="orderId" property="orderid"/>
    <result column="productId" property="productid"/>
    <result column="quantity" property="quantity"/>
    <result column="cost" property="cost"/>
    <result column="fileName" property="filename"/>
    <result column="name" property="name"/>
    <result column="price" property="price"/>
  </resultMap>
  <sql id="Base_Column_List">
    id, userId, loginName, userAddress, createTime, cost, serialNumber, isDelete, ispay
  </sql>

  <!--   订单主查询   -->
  <select id="selectByUserId" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select eo.*,eu.`type`,eu.userName from easybuy_order eo
    inner join easybuy_user eu on eu.id =eo.userId  and eu.status = 1
    where  eo.isDelete=0
    <if test="identify!=null and identify==0">
      and eo.userId = #{userId}
    </if>
    <if test="identify!=null and identify==1 ">
      and eo.userId != #{userId}
    </if>
    <if test="ispay!=null and ispay!='' and ispay!=-3">
      and eo.ispay = #{ispay}
    </if>
    <if test="userName!=null and userName!=''">
      and eu.userName = #{userName}
    </if>
    <if test="serialNumber!=null and serialNumber!=''">
      and eo.serialNumber like "%"#{serialNumber}"%"
    </if>
    order by eo.createTime desc
  </select>

  <!--  订单明细子查询  -->
  <select id="selectList" resultMap="detailList">
    select * from easybuy_order_detail eod
    where eod.orderId=#{id}
  </select>

</mapper>

查询的时候直接调订单主查询就ok了。