分页查询sql

  <resultMap id="modelBOMap" type="tech.yummy.iot.base.domain.bo.ModelBO">
    <id column="id" property="id"></id>
    <result column="model_qualifier" property="modelQualifier"/>
    <result column="model_name" property="modelName"/>
    <result column="model_enable" property="modelEnable"/>
    <result column="material_md_code" property="materialCode"/>
    <result column="material_name" property="materialName"/>
    <result column="access_way" property="accessWay"/>
    <result column="access_interval" property="accessInterval"/>
    <result column="access_count" property="accessCount"/>
    <result column="join_guide" property="joinGuide"/>
    <result column="is_qualifier" property="isQualifier"/>
    <result column="create_emp" property="createEmp"/>
    <result column="create_name" property="createName"/>
    <result column="create_time" property="createTime"/>
    <result column="modify_emp" property="modifyEmp"/>
    <result column="modify_name" property="modifyName"/>
    <result column="modify_time" property="modifyTime"/>
    <result column="remark" property="remark"/>
    <result column="name" property="materialMdName"/>
    <result column="md_code" property="materialMdCode"/>
    <!-- 型号信息-->
    <collection property="materialSpecBOList" ofType="tech.yummy.iot.base.domain.bo.ModelMaterialSpecBO"
                javaType="java.util.List" select="selectMaterialSpecByModelId" column="id">
    </collection>

    </resultMap>
    <sql id="QueryParam">
        <where>
            <if test="id != null">
                and m.id = #{id}
            </if>

      <if test="modelQualifier != null and modelQualifier !=''">
        and m.model_qualifier = #{modelQualifier}
      </if>

      <if test="modelName != null and modelName !=''">
        <if test="vague == true">
          and m.model_name like CONCAT('%',#{modelName},'%')
        </if>
        <if test="vague == false">
          and m.model_name = #{modelName}
        </if>
      </if>
      <if test="modelEnable != null">
        and m.model_enable = #{modelEnable}
      </if>
      <if test="materialCode != null and materialCode != ''">
        and m.material_md_code = #{materialCode}
      </if>

    </where>
  </sql>


  <sql id="Order">
    <if test="orderColumns != null">
      order by
      <foreach item="item" collection="orderColumns" separator="," index="">
        ${item.orderColumn} ${item.orderType}
      </foreach>
    </if>
  </sql>

  <sql id="Page">
    <if test="offset != null  and pageSize != null  and currentId == null ">
      limit #{offset} , #{pageSize}
    </if>
    <if test="pageSize != null   and   currentId != null">
      limit #{pageSize}
    </if>
  </sql>

  <!-- 分页 -->
  <select id="listModelByParam" resultMap="modelBOMap"
          parameterType="tech.yummy.iot.admin.model.domain.param.ModelQuery">
    SELECT
    m.id,
    m.model_name,
    m.model_qualifier,
    m.model_enable,
    m.material_md_code,
    m.material_name,
    m.access_way,
    m.access_interval,
    m.access_count,
    m.join_guide,
    m.is_qualifier,
    m.create_emp,
    m.create_name,
    m.create_time,
    m.modify_emp,
    m.modify_name,
    m.modify_time,
    m.remark
    FROM t_model m
    <include refid="QueryParam"></include>
    <include refid="Order"></include>
    <include refid="Page"></include>
  </select>
  
  <!-- 总记录数 -->
  <select id="countModelByParam" parameterType="tech.yummy.iot.admin.model.domain.param.ModelQuery"
          resultType="java.lang.Long">
    select
    count(1)
    FROM t_model m
    <include refid="QueryParam"></include>
  </select>

/**
     * 分页
     * @param modelQuery 查询对象
     * @return 分页结果
     */
    @Override
    public PaginationResult<ModelVO> pageModel(ModelQuery modelQuery) {
        modelQuery.setVague(Boolean.TRUE);
        modelQuery.setOrderColumns(Arrays.asList(new OrderColumn("m.create_time", OrderColumn.ORDER_TYPE_DESC)));

        List<ModelBO> modelBOList = modelMapper.listModelByParam(modelQuery);
        if (CollectionUtils.isEmpty(modelBOList)) {
            return new PaginationResult<>();
        }
        Long total = modelMapper.countModelByParam(modelQuery);
        List<ModelVO> modelVOList = modelBOList.stream().map(modelBO -> {
            ModelVO modelVO = new ModelVO(modelBO);

            return modelVO;
        }).collect(Collectors.toList());

        return new PaginationResult(total, modelVOList);
    }

番外：

PageHelper在对mybatis一对多分页不正确的问题