<select id="getProductOfBrand" resultMap="ProductOfBrand">
SELECT PB.id pbid, PB.name pbname,PP.id ppid, PP.name ppname,PPC.name ppcname
FROM pms_brand PB
INNER JOIN pms_product PP
ON PB.id = PP.brand_id
INNER JOIN pms_product_category PPC
ON PPC.id=PP.product_category_id
WHERE PB.id = #{id}
</select>
<resultMap id="ProductOfBrand" type="com.web.hello.dto.ProductOfBrandDto">
<id column="pbid" jdbcType="BIGINT" property="brandId" />
<result column="pbname" jdbcType="VARCHAR" property="brandName" />
<collection property="productsList" ofType="com.web.hello.dto.ProductUnderBrandDto">
<result column="ppid" jdbcType="INTEGER" property="productId" />
<result column="ppname" jdbcType="VARCHAR" property="productName" />
<result column="ppcname" jdbcType="INTEGER" property="productCategory" />
</collection>
</resultMap>
在以上关联查询成功的基础上,想用嵌套查询实现同样的效果时,遇到了子查询结果返回的productsList始终是有结果集的,但每一个属性值都为空。
第二种配置如下:
<select id="getProductOfBrand2" resultMap="ProductOfBrand2">
SELECT PB.id pbid, PB.name pbname FROM pms_brand PB WHERE PB.id = #{id}
</select>
<resultMap id="ProductOfBrand2" type="com.web.hello.dto.ProductOfBrandDto">
<id column="pbid" jdbcType="BIGINT" property="brandId" />
<result column="pbname" jdbcType="VARCHAR" property="brandName" />
<collection property="productsList" ofType="com.web.hello.dto.ProductUnderBrandDto" column="{brand_id=pbid}" select="getProduct">
<id column="ppid" jdbcType="BIGINT" property="productId" />
<result column="ppname" jdbcType="VARCHAR" property="productName" />
<result column="ppcname" jdbcType="INTEGER" property="productCategory" />
</collection>
</resultMap>
<select id="getProduct" resultType="com.web.hello.dto.ProductUnderBrandDto">
SELECT PP.id ppid, PP.name ppname, PPC.name ppcname
FROM pms_product PP INNER JOIN pms_product_category PPC
ON PPC.id=PP.product_category_id
WHERE PP.brand_id = #{brand_id}
</select>
排查过程:
1.首先productsList的条数符合预期,只是每一条的属性都为空,所以判断子查询的参数传值没有问题,且取到了正确的结果,问题应该是出在值映射上。
2.网上查到信息,指出错原因可能是select里设置了resultType而不是resultMap导致的,试了一下把配置改成了适应resultMap的果然解决了。
修改后:
<resultMap id="ProductOfBrand2" type="com.web.hello.dto.ProductOfBrandDto">
<id column="pbid" jdbcType="BIGINT" property="brandId" />
<result column="pbname" jdbcType="VARCHAR" property="brandName" />
<collection property="productsList" column="{brand_id=pbid}" select="getProduct" />
</resultMap>
<select id="getProduct" resultMap="getProduct">
SELECT PP.id ppid, PP.name ppname, PPC.name ppcname
FROM pms_product PP INNER JOIN pms_product_category PPC
ON PPC.id=PP.product_category_id
WHERE PP.brand_id = #{brand_id}
</select>
<resultMap id="getProduct" type="com.web.hello.dto.ProductUnderBrandDto">
<id column="ppid" jdbcType="BIGINT" property="productId" />
<result column="ppname" jdbcType="VARCHAR" property="productName" />
<result column="ppcname" jdbcType="INTEGER" property="productCategory" />
</resultMap>
但是觉得这种写法太费了。。。弃。。。
网友评论