使用Mybatis分页插件PageHelper时的分页问题
使用Mybatis分页插件PageHelper时的分页问题
1对1查询,分页正常
1对多查询,如使用左右连接查询则会导致结果的总记录条数,子记录条数会叠加到主记录条数,导致数据不对称。
总结:使用mybatis时,在一对多的查询并且需要分页的时候需要使用子查询形式。
1) 主记录的resultMap <resultMap id="artWorkMap" type="Artwork"> <id column="id" jdbcType="VARCHAR" property="id"></id> <result column="commited" jdbcType="VARCHAR" property="commited"></result> <association property="user"> <id column="u_id" jdbcType="VARCHAR" property="id"></id> <result column="u_username" jdbcType="VARCHAR" property="username"></result> </association> <association property="template" resultMap="templateRigMap"> </association> <collection property="materialCompositions" ofType="MaterialComposition" select="selectMaterialCompositions" ### 关联的子查询 column="id"> ### 传主记录的列名的参数值 </collection> </resultMap> 2) 子记录的ResultMap,依次为 "1对1" 的与 "1对多" 的 <resultMap id="templateRigMap" type="Template"> <id column="bt_id" jdbcType="VARCHAR" property="id"></id> <result column="bt_template_name" jdbcType="VARCHAR" property="templateName"></result> <result column="bt_deleted" jdbcType="BIT" property="deleted"></result> <result column="bt_create_date" jdbcType="TIMESTAMP" property="createDate"></result> </resultMap> <resultMap id="materialCompositionsRigMap" type="MaterialComposition"> <id column="bmc_id" jdbcType="VARCHAR" property="id"></id> <result column="bmc_artwork_id" jdbcType="VARCHAR" property="artworkId"></result> </resultMap> 3)主记录查询 (把一对一的查询放在一起,一对多的数据另写一条查询) <select id="selectAllOrInCompany" resultMap="artWorkMap"> SELECT ba.* ,su.id "u_id" ,su.username "u_username" ,bc.id ,bc.company_no ,bc.update_date "c_update_date" ,bb.id "bb_id" ,bb.update_date "bb_update_date" ,bt.id "bt_id" ,bt.update_date "bt_update_date" from biz_artwork ba left join sys_user su on su.id = ba.user_id left join biz_company bc on bc.id = su.company_id left join base_brand bb on ba.brand_id = bb.id left join base_template bt on ba.template_id = bt.id where ba.deleted = 0 <choose> <when test="companyNo !=null and companyNo != ‘‘"> and ba.artwork_no like concat(‘‘, #{companyNo}, ‘%‘) </when> <otherwise> and ba.commited = 1 </otherwise> </choose> <if test="keywords !=null and keywords.length() > 0"> and ( ba.artwork_no like concat(‘%‘, #{keywords}, ‘%‘) or su.username like concat(‘%‘, #{keywords}, ‘%‘) or bc.company_name like concat(‘%‘, #{keywords}, ‘%‘) )</if> order by ba.update_date desc</select> 4)子记录查询 <select id="selectMaterialCompositions" resultMap="materialCompositionsRigMap"> select bmc.id "bmc_id" ,bmc.create_date "bmc_create_date" ,bmc.update_date "bmc_update_date" from biz_material_composition bmc where bmc.artwork_id = #{id} ### 接收主记录的列名的参数值 </select>
相关推荐
javamagicsun 2020-06-21
xiuyangsong 2020-11-16
Nishinoshou 2020-11-09
jimgreatly 2020-09-01
dongxurr 2020-08-18
Dullonjiang 2020-08-15
Dullonjiang 2020-08-11
Dullonjiang 2020-08-09
dongxurr 2020-08-08
yunzhonmghe 2020-08-07
jimgreatly 2020-08-03
Dullonjiang 2020-07-30
jimgreatly 2020-07-27
liqiancao 2020-07-26
xiuyangsong 2020-07-26
dongxurr 2020-07-26
mcvsyy 2020-07-26
helloxusir 2020-07-25