mybatis 多表查询一对一一对多查询

86 阅读 0 评论 57 点赞

我是靠谱客的博主开放机器猫，最近开发中收集的这篇文章主要介绍mybatis 多表查询一对一一对多查询，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

  本文举例： 

  1.订单信息表 

  2.订单详情表 

  3.发票表 

  三表关系： 

  订单信息表 订单信息详情表 1：n 

  订单信息表 发票表 1：1 

  需求： 

查询订单表订单详情表和发票表所有信息：

<!-- 订单信息-->
<resultMap id="BaseResultMapOrderInfo" type="com.ehaoyao.ordercenter.model.OrderInfo">
  <id column="id" property="id" jdbcType="BIGINT" />
  <result column="order_number" property="orderNumber" jdbcType="VARCHAR" />
  <result column="original_order_number" property="originalOrderNumber" jdbcType="VARCHAR" />
  <result column="payment_order_number" property="paymentOrderNumber" jdbcType="VARCHAR" />
  <result column="order_flag" property="orderFlag" jdbcType="VARCHAR" />
  <result column="order_type" property="orderType" jdbcType="VARCHAR" />
  <result column="cps_flag" property="cpsFlag" jdbcType="VARCHAR" />
  <result column="payment_price" property="paymentPrice" jdbcType="DECIMAL" />
  <result column="order_price" property="orderPrice" jdbcType="DECIMAL" />
  <result column="express_price" property="expressPrice" jdbcType="DECIMAL" />
  <result column="platform_price" property="platformPrice" jdbcType="DECIMAL" />
  <result column="buyer_service_price" property="buyerServicePrice" jdbcType="DECIMAL" />
  <result column="trade_service_price" property="tradeServicePrice" jdbcType="DECIMAL" />
  <result column="trade_free_price" property="tradeFreePrice" jdbcType="DECIMAL" />
  <result column="refund_price" property="refundPrice" jdbcType="DECIMAL" />
  <result column="start_time" property="startTime" jdbcType="TIMESTAMP" />
  <result column="payment_time" property="paymentTime" jdbcType="TIMESTAMP" />
  <result column="expire_time" property="expireTime" jdbcType="TIMESTAMP" />
  <result column="cancel_time" property="cancelTime" jdbcType="TIMESTAMP" />
  <result column="cancel_status" property="cancelStatus" jdbcType="VARCHAR" />
  <result column="order_status" property="orderStatus" jdbcType="VARCHAR" />
  <result column="priority" property="priority" jdbcType="INTEGER" />
  <result column="pay_type" property="payType" jdbcType="VARCHAR" />
  <result column="receiver" property="receiver" jdbcType="VARCHAR" />
  <result column="mobile" property="mobile" jdbcType="VARCHAR" />
  <result column="telephone" property="telephone" jdbcType="VARCHAR" />
  <result column="province" property="province" jdbcType="VARCHAR" />
  <result column="city" property="city" jdbcType="VARCHAR" />
  <result column="county" property="county" jdbcType="VARCHAR" />
  <result column="town" property="town" jdbcType="VARCHAR" />
  <result column="address_detail" property="addressDetail" jdbcType="VARCHAR" />
  <result column="app_signature" property="appSignature" jdbcType="VARCHAR" />
  <result column="split_amount" property="splitAmount" jdbcType="INTEGER" />
  <result column="remark" property="remark" jdbcType="VARCHAR" />
  <result column="warehouse_name" property="warehouseName" jdbcType="VARCHAR" />
  <result column="shopping_type" property="shoppingType" jdbcType="VARCHAR" />
  <result column="rq_code" property="rqCode" jdbcType="VARCHAR" />
  <result column="customer_service_no" property="customerServiceNo" jdbcType="VARCHAR" />
  <result column="to_channel" property="toChannel" jdbcType="VARCHAR" />
  <result column="is_change_express" property="isChangeExpress" jdbcType="CHAR" />
  <result column="cfy_download_url" property="cfyDownloadUrl" jdbcType="VARCHAR" />
  <result column="reserve1" property="reserve1" jdbcType="VARCHAR" />
  <result column="reserve2" property="reserve2" jdbcType="VARCHAR" />
  <result column="to_ordercenter_time" property="toOrdercenterTime" jdbcType="TIMESTAMP" />
  <result column="to_erp" property="toErp" jdbcType="VARCHAR" />
  <result column="to_erp_time" property="toErpTime" jdbcType="TIMESTAMP" />
  <result column="last_time" property="lastTime" jdbcType="TIMESTAMP" />
  <result column="user_id" property="userId" jdbcType="INTEGER" />
  <result column="balance_pay_price" property="balancePayPrice" jdbcType="DECIMAL" />
  <result column="order_class" property="orderClass" jdbcType="INTEGER" />
  <result column="is_inside" property="isInside" jdbcType="VARCHAR" />
  <result column="erp_status" property="erpStatus" jdbcType="VARCHAR" />
  <result column="pharm_code" property="pharmCode" jdbcType="VARCHAR" />
  <result column="ship_type" property="shipType" jdbcType="INTEGER" />
  <result column="pharm_relation" property="pharmRelation" jdbcType="VARCHAR" />
</resultMap>
<!--订单详情-->
<resultMap id="BaseResultMapOrderDetail" type="com.ehaoyao.ordercenter.model.OrderDetail">
  <id column="id" property="id" jdbcType="BIGINT" />
  <result column="order_number" property="orderNumber" jdbcType="VARCHAR" />
  <result column="order_flag" property="orderFlag" jdbcType="VARCHAR" />
  <result column="product_id" property="productId" jdbcType="VARCHAR" />
  <result column="product_outer_id" property="productOuterId" jdbcType="VARCHAR" />
  <result column="product_name" property="productName" jdbcType="VARCHAR" />
  <result column="count" property="count" jdbcType="DECIMAL" />
  <result column="unit" property="unit" jdbcType="VARCHAR" />
  <result column="unit_price" property="unitPrice" jdbcType="DECIMAL" />
  <result column="total_price" property="totalPrice" jdbcType="DECIMAL" />
  <result column="settlement_price" property="settlementPrice" jdbcType="DECIMAL" />
  <result column="original_price" property="originalPrice" jdbcType="DECIMAL" />
  <result column="md_price" property="mdPrice" jdbcType="DECIMAL" />
  <result column="product_tpye" property="productTpye" jdbcType="VARCHAR" />
  <result column="short_term_id" property="shortTermId" jdbcType="INTEGER" />
</resultMap>


<!--发票信息-->
<resultMap id="BaseResultMapInvoiceInfo" type="com.ehaoyao.ordercenter.model.InvoiceInfo" >
  <id column="id" property="id" jdbcType="BIGINT" />
  <result column="order_number" property="orderNumber" jdbcType="VARCHAR" />
  <result column="order_flag" property="orderFlag" jdbcType="VARCHAR" />
  <result column="invoice_title" property="invoiceTitle" jdbcType="VARCHAR" />
  <result column="invoice_content" property="invoiceContent" jdbcType="VARCHAR" />
  <result column="order_price" property="orderPrice" jdbcType="DECIMAL" />
  <result column="invoice_type" property="invoiceType" jdbcType="VARCHAR" />
  <result column="remark" property="remark" jdbcType="VARCHAR" />
  <result column="invoice_title_type" property="invoiceTitleType" jdbcType="BIT" />
  <result column="invoice_address" property="invoiceAddress" jdbcType="VARCHAR" />
  <result column="invoice_mobile" property="invoiceMobile" jdbcType="VARCHAR" />
  <result column="bank_address" property="bankAddress" jdbcType="VARCHAR" />
  <result column="bank_account" property="bankAccount" jdbcType="VARCHAR" />
  <result column="payer_register_no" property="payerRegisterNo" jdbcType="VARCHAR" />
</resultMap>

<resultMap id="AllResultMap" type="com.ehaoyao.ordercenter.model.OrderInfo" extends="BaseResultMapOrderInfo">
  <association property="invoiceInfo"  column="{orderFlag=order_flag,orderNumber=order_number}"  select="selectInvoiceInfo" >
  </association>

  <collection property="details"  ofType="com.ehaoyao.ordercenter.model.OrderDetail"
              column="{orderFlag=order_flag,orderNumber=order_number}"  select="selectOrderDetail" >
  </collection>

  <collection property="orderDetailExtends"  ofType="com.ehaoyao.ordercenter.model.OrderDetailExtend"
              column="{orderFlag=order_flag,orderNumber=order_number}" select="selectOrderDetailExtends" >
  </collection>


</resultMap>