原因：由于PageHelper分页总数是根据count（0）来计算出来的
(即自动生成一条SQL select count(0) from xx where 筛选条件)，而collection会把结果集合并(一对多的情况)，计算总数在合并结果集之前,所以计算的总数不对。
下面sql就是统计了合并结果集之前的总数。
<resultMap type="com.a.b.model.sku.SkuStock" id="ResultMap"> <id column="id" property="id" jdbcType="VARCHAR"/> <id column="store_code" property="storeCode" jdbcType="VARCHAR"/> <id column="sku_code" property="skuCode" jdbcType="VARCHAR"/> <id column="pro_status" property="proStatus" jdbcType="INTEGER"/> <id column="no_number" property="noNumber" jdbcType="DECIMAL"/> //property="batchList" ,要与第一步对象中定义的list属性名称一样 <collection property="batchList" resultMap="BatchListResultMap"/> </resultMap> <resultMap type="com.a.b.model.sku.SkuStockBatch" id="BatchListResultMap"> <id column="ssb_id" property="id" jdbcType="VARCHAR"/> <id column="store_code" property="storeCode" jdbcType="VARCHAR"/> <id column="provider_code" property="providerCode" jdbcType="VARCHAR"/> <id column="sku_code" property="skuCode" jdbcType="VARCHAR"/> <id column="batch_number" property="batchNumber" jdbcType="VARCHAR"/> <id column="price" property="price" jdbcType="DECIMAL"/> <!--由于SkuStockBatch表的cur_number和id字段与主表SkuStock重复 所以去了别名区分解析映射结果--> <id column="ssb_cur_number" property="curNumber" jdbcType="DECIMAL"/> </resultMap> <sql id="BathList_Column_List"> ssb.id as ssb_id, ssb.store_code, ssb.provider_code, ssb.sku_code,ssb.batch_number, ssb.price, ssb.cur_number as ssb_cur_number </sql> <select id="findAll" resultMap="ResultMap"> SELECT ss.*,<include refid="BathList_Column_List"/> FROM sku_stock ss left join sku_stock_batch ssb on ssb.store_code=ss.store_code and ssb.sku_code = ss.sku_code order by ss.create_time desc </select>2.处理方法
可以使用嵌套查询的方式;
由于我的子查询条件是多个参数，所以嵌套查询的改造还是有些波折。
刚开始改造 cloumn传给子查询的参数只有一个，所以出现子集查询两个参数值都一样，出现list结果为null 的情况。
这里先给出 mybatis 官方API的嵌套查询多个参数传值的说明文档截图
(嵌套查询改造后的代码如下)
<resultMap type="com.a.b.model.sku.SkuStock" id="ResultAllMap"> <id column="id" property="id" jdbcType="VARCHAR"/> <id column="store_code" property="storeCode" jdbcType="VARCHAR"/> <id column="sku_code" property="skuCode" jdbcType="VARCHAR"/> <id column="pro_status" property="proStatus" jdbcType="INTEGER"/> <id column="cur_number" property="curNumber" jdbcType="DECIMAL"/> <id column="create_time" property="createTime" jdbcType="DATE"/> <id column="update_time" property="updateTime" jdbcType="DATE"/> <id column="del_flag" property="delFlag" jdbcType="INTEGER"/> <!-- 这里的column 传参s是传给getBatchList 而getBatchList需要传递 两个参数 参考官方API--> <collection property="batchList" ofType="com.a.b.model.sku.SkuStockBatch" select="getBatchList" column="{storeCode=store_code,skuCode=sku_code}"> </collection> </resultMap> <sql id="BathList_Column_List"> ssb.id, ssb.store_code, ssb.provider_code, ssb.sku_code,ssb.batch_number, ssb.price, ssb.cur_number,ssb.tax_rate, ssb.price_cost, ssb.del_flag </sql> <sql id="Base_Column_List"> id, store_code, sku_code, pro_status,cur_number, create_time, update_time, del_flag </sql> <select id="getBatchList" resultMap="BatchListResultMap"> SELECT <include refid="BathList_Column_List"/> FROM sku_stock_batch ssb where 1=1 and ssb.del_flag = 0 AND ssb.store_code = #{storeCode} AND ssb.sku_code = #{skuCode} </select> <select id="findAll" resultMap="ResultAllMap"> SELECT <include refid="Base_Column_List"/> FROM sku_stock where 1=1 and del_flag = 0 <if test="storeCode != null and storeCode !=''"> AND store_code = #{storeCode} </if> <if test="skuCode != null and skuCode!=''"> AND sku_code = #{skuCode} </if> order by create_time desc </select>改造后的代码
PageHelper分页执行的统计就只有主表查询的sql了
SELECT count(0) FROM sku_stock WHERE 1 = 1 AND del_flag = 0;
