场景是这样的:
在递归或一对多关联关系的查询中,可能会出现方法传递的参数需要在子查询中使用如:
调用queryCatGoods是给定入参
{“catId”:"-1",“orderEffDate”:“DESC”}
<resultMap id="prodGoods" type="com.iwhalecloud.retail.goods.entity.CatGoods">
<id column="CAT_ID" jdbcType="VARCHAR" property="catId"></id>
<collection property="goodAttr" select="queryGoodsAttr" column="{goodsCatId=cat_id}"></collection>
<collection property="children" ofType="com.iwhalecloud.retail.goods.entity.CatGoods" select="queryCatGoods" column="{catId=cat_id}"></collection>
</resultMap>
<select id="queryCatGoods" resultMap="prodGoods" parameterType="Map">
select CAT_ID,CAT_NAME,PARENT_CAT_ID,CAT_PATH from PROD_GOODS_CAT where IS_DELETED = '0'
<if test="catId==null">
and parent_cat_id = '-1'
</if>
<if test="catId!=null">
and parent_cat_id = #{catId}
</if>
</select>
<select id="queryGoodsAttr" resultType="com.iwhalecloud.retail.goods.entity.GoodAndAttr" parameterType="Map">
SELECT t.GOODS_ID,t.GOODS_NAME,t.GOODS_SN,t.BRAND_ID,t.BUY_COUNT,t.SUPPLIER_ID,t.SELLING_POINT
,t.MKT_PRICE,t.DISCOUNT_PRICE,'中国移动' supplier_name,'100Gmbps5ms' prod_spec_value
FROM prod_goods t
where MARKET_ENABLE = '3'
and IS_DELETED = '0'
<if test="goodsCatId!=null">
and t.GOODS_CAT_ID = #{goodsCatId}
</if>
<if test="orderEffDate!=null">
order by EFF_DATE 2
<if test="orderEffDesc!=null and orderEffDesc =='true'">
DESC
</if>
<if test="orderEffDesc!=null and orderEffDesc !='true'">
asc
</if>
</if>
<if test="orderMktPrice!=null">
order by MKT_PRICE
<if test="orderMktPriceDesc!=null and orderMktPriceDesc =='true'">
DESC
</if>
<if test="orderMktPriceDesc!=null and orderMktPriceDesc !='true'">
asc
</if>
</if>
<if test="orderMultiple!=null">
order by BUY_COUNT,MKT_PRICE,EFF_DATE
<if test="orderMultipleDesc!=null and orderMultipleDesc =='true'">
DESC
</if>
<if test="orderMultipleDesc!=null and orderMultipleDesc !='true'">
asc
</if>
</if>
</select>
上述代码中
1.父查询中可以正常获取到方法传递进来的catId
2.父查询查到的cat_id别名goodsCatId可以正常传递到子查询中
3.子查询中获取不到方法传递的参数orderEffDate
大佬带着跟了一下源码发现mybatis处理参数的时候就没有把方法的参数往下传~所以这个想法是无法实现了
处理方案:很丑 可以不要看就直接想别的办法
<resultMap id="prodGoods" type="com.iwhalecloud.retail.goods.entity.CatGoods">
<id column="CAT_ID" jdbcType="VARCHAR" property="catId"></id>
<collection property="goodAttr" select="queryGoodsAttr" column="{goodsCatId=cat_id,orderEffDate=orderEffDate,orderMktPrice=orderMktPrice,orderMultiple=orderMultiple}"></collection>
<collection property="children" ofType="com.iwhalecloud.retail.goods.entity.CatGoods" select="queryCatGoods" column="{catId=cat_id}"></collection>
</resultMap>
<select id="queryCatGoods" resultMap="prodGoods" parameterType="Map">
-- 这里是mybatis的问题,mybatis不支持往子查询中传递方法调用时传递的参数,只能传递父查询查询到的列值
select CAT_ID,CAT_NAME,PARENT_CAT_ID,CAT_PATH,#{orderEffDate} orderEffDate,#{orderMktPrice} orderMktPrice,#{orderMultiple} orderMultiple from PROD_GOODS_CAT where IS_DELETED = '0'
<if test="catId==null">
and parent_cat_id = '-1'
</if>
<if test="catId!=null">
and parent_cat_id = #{catId}
</if>
</select>
<select id="queryGoodsAttr" resultType="com.iwhalecloud.retail.goods.entity.GoodAndAttr" parameterType="Map">
SELECT t.GOODS_ID,t.GOODS_NAME,t.GOODS_SN,t.BRAND_ID,t.BUY_COUNT,t.SUPPLIER_ID,t.SELLING_POINT
,t.MKT_PRICE,t.DISCOUNT_PRICE,'中国移动' supplier_name,'100Gmbps5ms' prod_spec_value
FROM prod_goods t
where MARKET_ENABLE = '3'
and IS_DELETED = '0'
<if test="goodsCatId!=null">
and t.GOODS_CAT_ID = #{goodsCatId}
</if>
<if test="orderEffDate!=null">
order by EFF_DATE 2
<if test="orderEffDesc!=null and orderEffDesc =='true'">
DESC
</if>
<if test="orderEffDesc!=null and orderEffDesc !='true'">
asc
</if>
</if>
<if test="orderMktPrice!=null">
order by MKT_PRICE
<if test="orderMktPriceDesc!=null and orderMktPriceDesc =='true'">
DESC
</if>
<if test="orderMktPriceDesc!=null and orderMktPriceDesc !='true'">
asc
</if>
</if>
<if test="orderMultiple!=null">
order by BUY_COUNT,MKT_PRICE,EFF_DATE
<if test="orderMultipleDesc!=null and orderMultipleDesc =='true'">
DESC
</if>
<if test="orderMultipleDesc!=null and orderMultipleDesc !='true'">
asc
</if>
</if>
</select>
注:还有一个坑
父属性CatGoods对应的实体类还得补上那几个冗余的字段(丑的不行)
结束