创建带Collection的Mybatis sql语句

最新推荐文章于 2025-05-08 15:23:43 发布

「已注销」

最新推荐文章于 2025-05-08 15:23:43 发布

阅读量2.2k

点赞数

分类专栏： Mysql 文章标签： mysql xml

本文链接：https://blog.youkuaiyun.com/maoliangfei2011/article/details/51850684

版权

Mysql 专栏收录该内容

3 篇文章

订阅专栏

这篇博客介绍了如何在Mybatis中通过XML配置文件创建带Collection的SQL语句，包括订单导出查询及获取输入列表的方法。内容涉及了resultMap的定义，以及如何在SQL查询中处理复杂的关联数据，如通过`<collection>`标签获取OrderProduct的OrderInput列表。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

<?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.kakasure.tongji.dao.mapper.OrderProductsMapperExt" >
    <resultMap id="OrderExprotRsltMap" type="com.kakasure.tongji.dao.dto.OrderExportDto" >
        <id column="id" property="id" jdbcType="BIGINT" />
        <result column="order_number" property="orderNumber" jdbcType="VARCHAR" />
        <result column="product_name" property="productName" jdbcType="VARCHAR" />
        <result column="product_nums" property="productNums" jdbcType="INTEGER" />
        <result column="cash_total" property="cashTotal" jdbcType="DECIMAL" />
        <result column="receiver_name" property="receiverName" jdbcType="VARCHAR" />
        <result column="receiver_mobile" property="receiverMobile" jdbcType="VARCHAR" />
        <result column="address_detail" property="addressDetail" jdbcType="VARCHAR" />
        <result column="attributes_json" property="attributesJson" jdbcType="VARCHAR" />
        <result column="options_json" property="optionsJson" jdbcType="VARCHAR" />
        <result column="product_type" property="productType" jdbcType="VARCHAR" />
        <result column="customer_name" property="customerName" jdbcType="VARCHAR" />
        <result column="affiliate_name" property="affiliateName" jdbcType="VARCHAR" />
        <result column="gmt_modified" property="gmtModified" jdbcType="TIMESTAMP" />
        <result column="status" property="status" jdbcType="INTEGER" />
        <result column="cash_postage" property="cashPostage" jdbcType="DECIMAL" />
        <result column="point_used" property="pointUsed" jdbcType="DECIMAL" />
        <result column="cash_coupon" property="cashCoupon" jdbcType="DECIMAL" />
        <result column="cash_rebate" property="cashRebate" jdbcType="DECIMAL" />
        <result column="vendor_name" property="vendorName" jdbcType="DECIMAL" />
        <result column="affiliate2_name" property="affiliate2Name" jdbcType="DECIMAL" />
        <collection property="inputLists" ofType="com.kakasure.tongji.dao.model.OrderInput"
          javaType="ArrayList" column="id" select="getInputLists"/>
    </resultMap>
    
    <resultMap id="OrderInputRsltMap" type="com.kakasure.tongji.dao.model.OrderInput" >
        <id column="id" property="id" jdbcType="BIGINT" />
        <result column="order_product_id" property="orderProductId" jdbcType="BIGINT" />
        <result column="order_id" property="orderId" jdbcType="BIGINT" />
        <result column="product_id" property="productId" jdbcType="VARCHAR" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="input_value" property="inputValue" jdbcType="VARCHAR" />
        <result column="selected_attribute_id" property="selectedAttributeId" jdbcType="BIGINT" />
    </resultMap>
    
    <select id="getExportVendorOrders" resultMap="OrderExprotRsltMap" parameterType="map">
        SELECT a.order_number, b.name product_name,
                         (CASE WHEN b.type = 0 THEN '实物' WHEN b.type=1 THEN '服务' ELSE b.type END) product_type, b.number product_nums,
                       a.cash_total,(CASE WHEN b.type = 1 THEN '' ELSE c.name END ) receiver_name, o.gmt_created gmt_modified,  a.status, a.cash_postage,
                         CASE WHEN b.type = 1 THEN s.input_value ELSE c.mobile END receiver_mobile,
                          (CASE WHEN b.type = 1 THEN '' ELSE c.address_detail END ) address_detail ,
                          b.attributes_json, b.options_json, b.id, b.customer_user_id, a.vendor_user_id,
                       o.customer_username customer_name,u1.username affiliate_name
                  FROM kks_order_cash_vendor a STRAIGHT_JOIN sso_user u1 ON u1.id = a.affiliate_user_id AND u1.privilege LIKE 'affiliate%'
                       STRAIGHT_JOIN kks_order_vendor o ON o.order_id = a.order_id AND o.vendor_user_id = a.vendor_user_id
                       STRAIGHT_JOIN kks_order_products b ON b.id = a.order_products_id  
                       LEFT JOIN kks_order_address c ON b.order_id = c.order_id
                       LEFT JOIN kks_order_input_server s  ON b.id = s.order_product_id AND s.type = 1
                  WHERE a.order_products_id = b.id
                  AND a.vendor_user_id = #{userId}
                <if test="status!=null and status!='' or status == 0">
                  AND a.status = #{status,jdbcType=INTEGER}
                </if>
                <if test="startTime!=null and startTime!=''">
                   AND o.gmt_created >= #{startTime,jdbcType=TIMESTAMP}
                 </if>
                 <if test="endTime!=null and endTime!=''">
                     AND o.gmt_created < DATE_ADD(#{endTime,jdbcType=TIMESTAMP},INTERVAL 1 DAY)
                 </if>
               <if test="keyWord!=null and keyWord!=''">
               AND (a.order_number LIKE '%${keyWord}%' OR b.name LIKE '%${keyWord}%' OR o.customer_username LIKE '%${keyWord}%')
               </if>    
           ORDER BY o.gmt_created DESC, a.order_number
           LIMIT 0, 1000
    </select>
    <select id="getExportAdminOrders" resultMap="OrderExprotRsltMap" parameterType="map">
     SELECT a.order_id,o.order_number, b.name product_name,(CASE WHEN b.type = 0 THEN '实物' WHEN b.type=1 THEN '服务' ELSE b.type END) product_type,
                   o.customer_username customer_name,b.number product_nums,a.cash_total,
                   a.point_used, a.cash_coupon, a.cash_rebate, a.cash_postage,(CASE WHEN b.type = 1 THEN '' ELSE c.name END )receiver_name,
                   CASE WHEN b.type = 1 THEN s.input_value ELSE c.mobile END receiver_mobile,
                   (CASE WHEN b.type = 1 THEN '' ELSE c.address_detail END ) address_detail , b.attributes_json, b.options_json,
                   o.vendor_username vendor_name,u1.username affiliate_name, u2.username affiliate2_name,
                   a.status, o.gmt_created gmt_modified,b.id      
            FROM core_order_cash a STRAIGHT_JOIN kks_order_vendor o ON  a.order_id = o.order_id AND a.vendor_user_Id = o.vendor_user_id
               STRAIGHT_JOIN kks_order_products b ON b.id = a.order_product_id
               STRAIGHT_JOIN sso_user u1 ON u1.id = a.affiliate_user_id AND u1.privilege = 'affiliate'
               LEFT JOIN sso_user u2 ON u2.id = a.affiliate2_user_id AND u2.privilege = 'affiliate_2'
               LEFT JOIN kks_order_address c ON b.order_id = c.order_id
               LEFT JOIN kks_order_input_server s  ON b.id = s.order_product_id AND s.type = 1
            <where>
                <if test="status!=null and status!='' or status == 0">
                  AND a.status = #{status,jdbcType=INTEGER}
                </if>
                <if test="startTime!=null and startTime!=''">
                   AND o.gmt_created >= #{startTime,jdbcType=TIMESTAMP}
                 </if>
                 <if test="endTime!=null and endTime!=''">
                     AND o.gmt_created < DATE_ADD(#{endTime,jdbcType=TIMESTAMP},INTERVAL 1 DAY)
                 </if>
                <if test="keyWord!=null and keyWord!=''">
                 AND (o.order_number LIKE '%${keyWord}%' OR b.name LIKE '%${keyWord}%'
                      OR o.customer_username LIKE '%${keyWord}%' OR o.vendor_username LIKE '%${keyWord}%')
                </if>
              </where>
           ORDER BY o.gmt_created desc, o.order_number
            LIMIT 0, 1000
    </select>
    
    <select id="getInputLists" resultMap="OrderInputRsltMap" parameterType="long">
        SELECT * FROM kks_order_input WHERE order_product_id = #{id}
    </select>
</mapper>