1.我们希望返回的实体对象结构如下：
@Data public class OrderPaymentInfoRetDTO { private Integer id;//id字段必须要写，他作为过层级1对多的重要滤条件存在的 RespOrderListDTO respOrderListDTO;//一个对象 List<QueryOrderPaymentDTO> respOrderAndSubListDtos;//list }
2.我们定义了两个 resultMap 分别上面的两个类属性
2.1 此resultMap对应 RespOrderListDTO 类
<resultMap id="forgeOrderBaseMap" type="com.zhangmen.fms.trn.forge.model.resp.RespOrderListDTO"> <id column="id" property="id" jdbcType="INTEGER"/> <result column="stu_id" property="stuId" jdbcType="INTEGER"/> <result column="sel_id" property="selId" jdbcType="INTEGER"/> <result column="paid_sel_id" property="paidSelId" jdbcType="INTEGER"/> <result column="owner_sel_id" property="ownerSelId" jdbcType="INTEGER"/> <result column="money" property="money" jdbcType="INTEGER"/> <result column="amount" property="amount" jdbcType="DECIMAL"/> <result column="mins" property="mins" jdbcType="INTEGER"/> <result column="gift_mins" property="giftMins" jdbcType="INTEGER"/> <result column="is_paid" property="isPaid" jdbcType="INTEGER"/> <result column="is_canceled" property="isCanceled" jdbcType="BIT"/> <result column="state" property="state" jdbcType="BIT"/> <result column="out_trade_no" property="outTradeNo" jdbcType="VARCHAR"/> <result column="hours" property="hours" jdbcType="INTEGER"/> <result column="meta" property="meta" jdbcType="INTEGER"/> <result column="is_split" property="isSplit" jdbcType="BIT"/> <result column="deal_time" property="dealTime" jdbcType="TIMESTAMP"/> <result column="expired_at" property="expiredAt" jdbcType="TIMESTAMP"/> <result column="pay_way" property="payWay" jdbcType="VARCHAR"/> <result column="hour_pkg_type" property="hourPkgType" jdbcType="INTEGER"/> <result column="created_at" property="createdAt" jdbcType="TIMESTAMP"/> <result column="updated_at" property="updatedAt" jdbcType="TIMESTAMP"/> <result column="hour_pkg_id" property="hourPkgId" jdbcType="INTEGER"/> <result column="total_money" property="totalMoney" jdbcType="DECIMAL"/> <result column="payment_type" property="paymentType" jdbcType="INTEGER"/> <result column="refer_id" property="referId" jdbcType="INTEGER"/> <result column="hour_pkg_catg" property="hourPkgCatg" jdbcType="BIT"/> <result column="stu_grade" property="stuGrade" jdbcType="VARCHAR"/> <result column="supply_payment_id" property="supplyPaymentId" jdbcType="INTEGER"/> <result column="biz_source" jdbcType="INTEGER" property="bizSource"/> <result column="bu" property="ghpBu" jdbcType="INTEGER"/> </resultMap>
2.2 此resultMap对应 QueryOrderPaymentDTO 类
<resultMap id="forgeOrderPaymentBaseMap" type="com.zhangmen.fms.trn.forge.model.QueryOrderPaymentDTO"> <id column="subId" property="orderPaymentId" jdbcType="INTEGER"/> <result column="orderId" property="orderId" jdbcType="INTEGER"/> <result column="orderNo" property="orderNo" jdbcType="VARCHAR"/> <result column="subOutTradeNo" property="orderPaymentNo" jdbcType="VARCHAR"/> <result column="subAmount" property="orderPaymentAmount" jdbcType="DECIMAL"/> <result column="subIsPaid" property="isPaid" jdbcType="INTEGER"/> <result column="subIsCanceled" property="isCanceled" jdbcType="INTEGER"/> <result column="subState" property="orderPaymentStatus" jdbcType="INTEGER"/> <result column="subDealTime" property="dealTime" jdbcType="TIMESTAMP"/> <result column="subPassTime" property="passTime" jdbcType="TIMESTAMP"/> <result column="subPayWay" property="payWay" jdbcType="VARCHAR"/> <result column="subAuditState" property="auditState" jdbcType="INTEGER"/> <result column="subPayType" property="payType" jdbcType="INTEGER"/> </resultMap>
3.然后定义 映射到 我们要返回的 OrderPaymentInfoRetDTO类上的实体resultMap
<resultMap id="orderPaymentInfoRetMap" type="com.zhangmen.fms.trn.forge.model.resp.OrderPaymentInfoRetDTO"> <id column="id" property="id" jdbcType="INTEGER"/> //作为collection属性的分组过滤条件 <association property="respOrderListDTO" resultMap="forgeOrderBaseMap" /> <collection property="respOrderAndSubListDtos" resultMap="forgeOrderPaymentBaseMap"/> </resultMap>
4.定义我们的查询SQL
<select id="selectSubPaymentAttachmentPaymentsByCond" parameterType="com.zhangmen.fms.trn.forge.model.req.QueryOrderPaymentReq" resultMap="orderPaymentInfoRetMap" > SELECT p.id, p.stu_id, p.sel_id, p.paid_sel_id, p.owner_sel_id, p.money, p.amount, p.mins, p.gift_mins, p.is_paid, p.is_canceled, p.state AS state, p.out_trade_no, p.hours, p.meta, p.is_split, p.deal_time, p.expired_at, p.pay_way, p.hour_pkg_type, p.created_at, p.updated_at, p.hour_pkg_id, p.total_money, p.payment_type, p.refer_id, p.hour_pkg_catg, p.stu_grade, p.supply_payment_id, p.biz_source, ghp.bu, p.id AS orderId, p.out_trade_no AS orderNo, s.id AS subId, s.out_trade_no AS subOutTradeNo, s.amount AS subAmount, s.is_paid AS subIsPaid, s.is_canceled AS subIsCanceled, s.state AS subState, s.deal_time AS subDealTime, s.pass_time AS subPassTime, s.pay_way AS subPayWay, s.audit_state AS subAuditState, s.pay_type AS subPayType from payments p INNER JOIN sub_payments s on s.parent_pay_id = p.id INNER JOIN grades_hours_prices ghp on ghp.id = p.hour_pkg_id WHERE p.is_paid = 1 AND p.is_canceled = 0 AND s.is_paid = 1 AND s.is_canceled = 0 <if test="orderNoList != null and orderNoList.size() > 0"> AND p.out_trade_no IN <foreach item="item" index="index" collection="orderNoList" open="(" separator="," close=")"> #{item} </foreach> </if> <if test="stuId != null"> AND p.stu_id > #{stuId} </if> <if test="orderPaymentAmount != null"> AND s.amount > #{orderPaymentAmount} </if> ORDER BY p.id DESC, s.deal_time DESC </select>
注意：我们查看SQL可知，这样的语句查询出的结果是笛卡尔积并非是层级结构，而我们想要获取到层级结构就需要MyBatis对返回的结果进行封装，将相同的父属性进行合并，然后把子对象附属在其下。
实现此过滤的关键因素就是上面加粗的大字号字体， 注意resultMap中放大的字体，父对象中的 <id column 属性对应的ID列一定要是过滤的主键，同时作为层级结构中的子列表的ID属性也要是其主键，否则无法进行层级关系的转换