mybatis+oracle+pageHelper实现一对多连表查询结果集正确分页（父表和子表都可带查询条件）

mybatis+oracle+pageHelper实现一对多连表查询结果集正确分页（父表和子表都可带查询条件）

直接上干货

public class CheckTotal implements Serializable{
	private static final long serialVersionUID = 8748981176708546780L;

	private String checktotalId;
//餐包商
    private String mealSeller;
//餐廳位置
    private String restaurantLocation;
//餐廳負責人
    private String leader;
//稽核時間
    private String checktime;
//包商簽字
    private String mealSure;
//總務確認
    private String rearServiceSure;
//有無問題
    private String hastrouble;
//表單狀態
    private String overend;
//憑證URL
    private String proof;
//稽核狀態
    private String jiheStatus;
//餐廳信息ID
    private String restaurantId;
//稽核性質
    private String checkNaturn;
//稽核要點
    private String checkPoint;

    private String filed1;

    private String filed2;

    private String filed3;

    private String filed4;
    
    private List<CheckPeople> peopleList;

get，set省略。。。这个Javabean是一，下面的Javabean是多

public class CheckPeople implements Serializable{
	private static final long serialVersionUID = -3723739981383642209L;

	private String checkpeopleId;

    private String checkTotalId;
//稽核人名字
    private String checkManname;
//稽核人工號
    private String checkManCard;
//稽核人類型
    private String manType;

    private String remark1;

下面是业务
这是service
@Override
public PageInfo getTotalDataByCondition(Integer start, Integer length, String startDate, String endDate,
String checkPeople, String overend) {
// TODO Auto-generated method stub
if (null != start && null != length) {
PageHelper.startPage(start, length, true, true);
}

	Page<CheckTotal> list = mapper.getTotalDataByPage(startDate, endDate,checkPeople, overend);
	List<CheckTotal> listData = mapper.getTotalData(list);
	list.clear();
	list.addAll(listData);
	PageInfo<CheckTotal> pageInfo = new PageInfo<CheckTotal>(list);
	return pageInfo;
}

这里是mappers
Page getTotalDataByPage(
@Param(“startDate”)String startDate,
@Param(“endDate”)String endDate,
@Param(“checkPeople”)String checkPeople,
@Param(“overend”)String overend);

List<CheckTotal> getTotalData(@Param("idList")List<CheckTotal> idList);

这里是xml,下面两个select就是精华

<?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.foxconn.mapper.CheckTotalMapper">
  <resultMap id="BaseResultMap" type="com.foxconn.entity.CheckTotal">
    <id column="CHECKTOTAL_ID" jdbcType="VARCHAR" property="checktotalId" />
    <result column="MEAL_SELLER" jdbcType="VARCHAR" property="mealSeller" />
    <result column="RESTAURANT_LOCATION" jdbcType="VARCHAR" property="restaurantLocation" />
    <result column="LEADER" jdbcType="VARCHAR" property="leader" />
    <result column="CHECKTIME" jdbcType="VARCHAR" property="checktime" />
    <result column="MEAL_SURE" jdbcType="VARCHAR" property="mealSure" />
    <result column="REAR_SERVICE_SURE" jdbcType="VARCHAR" property="rearServiceSure" />
    <result column="HASTROUBLE" jdbcType="VARCHAR" property="hastrouble" />
    <result column="OVEREND" jdbcType="VARCHAR" property="overend" />
    <result column="PROOF" jdbcType="VARCHAR" property="proof" />
    <result column="JIHE_STATUS" jdbcType="VARCHAR" property="jiheStatus" />
    <result column="RESTAURANT_ID" jdbcType="VARCHAR" property="restaurantId" />
    <result column="CHECK_NATURN" jdbcType="VARCHAR" property="checkNaturn" />
    <result column="CHECK_POINT" jdbcType="VARCHAR" property="checkPoint" />
    <result column="FILED1" jdbcType="VARCHAR" property="filed1" />
    <result column="FILED2" jdbcType="VARCHAR" property="filed2" />
    <result column="FILED3" jdbcType="VARCHAR" property="filed3" />
    <result column="FILED4" jdbcType="VARCHAR" property="filed4" />
    <collection property="peopleList" ofType="com.foxconn.entity.CheckPeople">
        <id column="CHECKPEOPLE_ID" jdbcType="VARCHAR" property="checkpeopleId" />
	    <result column="CHECK_TOTAL_ID" jdbcType="VARCHAR" property="checkTotalId" />
	    <result column="CHECK_MANNAME" jdbcType="VARCHAR" property="checkManname" />
	    <result column="CHECK_MAN_CARD" jdbcType="VARCHAR" property="checkManCard" />
	    <result column="MAN_TYPE" jdbcType="VARCHAR" property="manType" />
	    <result column="REMARK1" jdbcType="VARCHAR" property="remark1" />
    </collection>
    <collection property="problemList" ofType="com.foxconn.entity.CheckProblem">      
        <id column="CHECK_PROBLEM_ID" jdbcType="VARCHAR" property="checkProblemId" />
	    <result column="CHECK_MTOTAL_ID" jdbcType="VARCHAR" property="checkMtotalId" />
	    <result column="TYPE_BIG_NAME" jdbcType="VARCHAR" property="typeBigName" />
	    <result column="THIRD_CODE" jdbcType="VARCHAR" property="thirdCode" />
	    <result column="TYPE_SMALL_NAME" jdbcType="VARCHAR" property="typeSmallName" />
	    <result column="STAND_CONTENT" jdbcType="VARCHAR" property="standContent" />
	    <result column="BEFORE_IMG" jdbcType="VARCHAR" property="beforeImg" />
	    <result column="AFTER_IMG" jdbcType="VARCHAR" property="afterImg" />
	    <result column="PROBLEM_DETAIL" jdbcType="VARCHAR" property="problemDetail" />
	    <result column="BEFORE_SCORE" jdbcType="DECIMAL" property="beforeScore" />
	    <result column="AFTER_SCORE" jdbcType="DECIMAL" property="afterScore" />
	    <result column="BEFORE_MONEY" jdbcType="OTHER" property="beforeMoney" />
	    <result column="AFTER_MONEY" jdbcType="OTHER" property="afterMoney" />
	    <result column="WRITETIME" jdbcType="TIMESTAMP" property="writetime" />
	    <result column="GRADE" jdbcType="VARCHAR" property="grade" />
	    <result column="REVISE" jdbcType="VARCHAR" property="revise" />
	    <result column="COLUMN33" jdbcType="VARCHAR" property="column33" />
    </collection>
  </resultMap>
  <sql id="total_Column_List">
    CHECKTOTAL_ID, MEAL_SELLER, RESTAURANT_LOCATION, LEADER, CHECKTIME, MEAL_SURE, REAR_SERVICE_SURE, 
    HASTROUBLE, OVEREND, PROOF, JIHE_STATUS, RESTAURANT_ID, CHECK_NATURN, CHECK_POINT, 
    FILED1, FILED2, FILED3, FILED4
  </sql>
  <sql id="problem_Column_List">
    CHECK_PROBLEM_ID, CHECK_MTOTAL_ID, TYPE_BIG_NAME, THIRD_CODE, TYPE_SMALL_NAME, STAND_CONTENT, 
    BEFORE_IMG, AFTER_IMG, PROBLEM_DETAIL, BEFORE_SCORE, AFTER_SCORE, BEFORE_MONEY, AFTER_MONEY, 
    WRITETIME, GRADE, REVISE, COLUMN33
  </sql>
  <sql id="people_Column_List">
    CHECKPEOPLE_ID, CHECK_TOTAL_ID, CHECK_MANNAME, CHECK_MAN_CARD, MAN_TYPE, REMARK1
  </sql>
  <select id="getTotalDataByPage" parameterType="java.lang.String" resultMap="BaseResultMap">
  SELECT DISTINCT CHECKTOTAL_ID FROM (
	        select 
	        ct.CHECKTOTAL_ID
	        from t_checktotal ct left join t_checkpeople cpeo on ct.checktotal_id = cpeo.check_total_id
	        left join t_checkproblem cpro on ct.checktotal_id = cpro.check_mtotal_id
	        where 1 = 1
	        <if test="startDate != null">
	        and to_char(ct.CHECKTIME,'yyyy-MM-dd') &gt; #{startDate}
	        </if>
	        <if test="endDate !=null">
	        and to_char(ct.checktime,'yyyy-MM-dd') &lt; #{endDate}
	        </if>
	        <if test="checkPeople != null">
	        and cpeo.CHECK_MANNAME = #{checkPeople}
	        </if>
	        <if test="overend != null">
	        and ct.overend = #{overend}
	        </if>
	        order by ct.CHECKTIME
        )
  </select>
  
  
    <select id="getTotalData"  resultMap="BaseResultMap">
		    <if test="idList != null">
		        select 
		       *
		        from t_checktotal ct left join t_checkpeople cpeo on ct.checktotal_id = cpeo.check_total_id
		        left join t_checkproblem cpro on ct.checktotal_id = cpro.check_mtotal_id where ct.CHECKTOTAL_ID in 
		        <foreach item='item' index='index' collection='idList' open='(' separator=',' close=')'>
		            #{item.checktotalId}
		        </foreach>
		        
		    </if>
		    <if test="idList == null">
                SELECT '' FROM DUAL
		    </if>
  </select>
</mapper>

加上pagehelper执行的count SQL，查一次数据总共会执行3次SQL查询。一对多，一那张表的数据就能正确分页了。目前还没有发现什么问题，可以套用。但是上面的checktotal_id 还是有优化的可能，没去搞了。希望有大神能改进改进。。。