工作中Mabatis问题记录-两个实体具有一对多关系，根据一那个实体进行分页

 

<sql id="limit">
  <if test='offset != 0 and limit != 0'>
    LIMIT #{offset}, #{limit}
  </if>

  <if test='offset == 0 and limit != 0'>
    LIMIT #{limit}
  </if>
</sql>

 

  <resultMap id="queryPointInfosMap" type="com..gs.stat.service.bean.QueryPointInfo">
    <result column="project_id" jdbcType="INTEGER" property="projectId" />
    <result column="city_name" jdbcType="VARCHAR" property="cityName" />
    <result column="region_name" jdbcType="VARCHAR" property="regionName" />
    <result column="project_name" jdbcType="VARCHAR" property="projectName" />
    <result column="point_name" jdbcType="VARCHAR" property="statPointName" />
    <result column="point_status" jdbcType="TINYINT" property="statPointstatus" />
    <result column="photo_front" jdbcType="VARCHAR" property="photoFront" />
    <result column="photo_left" jdbcType="VARCHAR" property="photoLeft" />
    <result column="photo_right" jdbcType="VARCHAR" property="photoRight" />
    <result column="notes" jdbcType="VARCHAR" property="notes" />
  </resultMap>

 

<sql id="where">
    <where>
      AND x.is_deleted=0 and y.is_deleted=0
      <if test="cityName != null and cityName != ''"> AND m.name=#{cityName}</if>
      <if test="regionName != null and regionName != ''"> AND z.name=#{regionName}</if>
      <if test="projectName != null and projectName != ''"> AND y.name=#{projectName}</if>
      <if test="updatedTimeBegin != null and updatedTimeBegin != ''">
        AND  date_format(x.updated_time, '%Y-%m-%d') <![CDATA[>=]]> #{updatedTimeBegin}
      </if>
      <if test="updatedTimeEnd != null and updatedTimeEnd != ''">
        AND date_format(x.updated_time, '%Y-%m-%d') <![CDATA[<=]]> #{updatedTimeEnd}
      </if>
      /*status=2,则全部查询，0：可用，1：不可用*/
      <if test="status !=null and status !=2 ">AND x.status = #{status}</if>
    </where>
  </sql>

 统计对象和统计点是一对多的关系，在页面上要实现根据统计对象进行分页、分组，所以要先找到分页中所有的满足条件的统计对象的id，（如果不对统计对象进行限制，

可能在分页中找到固定的数目的统计对象不满足其他的限制，每页的数据条数就不是固定的了，）然后再查找满足条件的

所需数据。这条sql是查找原始数据，为了提高查询效率对数据分组的部分放到程序中处理了。

<select id="queryPointInfoList" parameterType="com..gs.stat.service.param.QueryPointInfoParam" resultMap="queryPointInfosMap">
  select y.id project_id,m.name city_name,z.name region_name,y.name project_name,x.name point_name,
  x.status point_status,photo_front,photo_left,photo_right,notes from project_info y
  inner join stat_point_info x on x.project_id = y.id
  inner join region_info z on y.region_id = z.id
  inner join city_info m on z.city_id = m.id
  <where>
  y.id in (//先找到分页中所有的满足条件的统计对象的id
  select t.projectId from
  (/*IN里不支持LIMIT,所以需要再嵌套一层，详见参考文献【1】*/
  SELECT distinct y.id projectId from project_info y
  INNER join stat_point_info x on x.project_id = y.id
  inner join region_info z on y.region_id = z.id
  inner join city_info m on z.city_id = m.id
  <include refid="where"/>
  <include refid="limit"/>
  ) as t
  )
    AND x.is_deleted=0 and y.is_deleted=0
    <if test="cityName != null and cityName != ''"> AND m.name=#{cityName}</if>
    <if test="regionName != null and regionName != ''"> AND z.name=#{regionName}</if>
    <if test="projectName != null and projectName != ''"> AND y.name=#{projectName}</if>
    <if test="updatedTimeBegin != null and updatedTimeBegin != ''">
      AND  date_format(x.updated_time, '%Y-%m-%d') <![CDATA[>=]]> #{updatedTimeBegin}
    </if>
    <if test="updatedTimeEnd != null and updatedTimeEnd != ''">
      AND date_format(x.updated_time, '%Y-%m-%d') <![CDATA[<=]]> #{updatedTimeEnd}
    </if>
    /*status=2,则全部查询，0：可用，1：已关闭*/
    <if test="status !=null and status !=2 ">AND x.status = #{status}</if>
  </where>
</select>

 

【1】：http://www.jb51.net/article/46209.htm