Mybatis分页查询,同时返回total

在垃圾项目中一般都是使用mybatis plus自动返回Page,但是涉及到多表联合或者等等情况最终还是要使用mybatis进行手写sql查询,所以有此文章以便后续使用查询.

首先mysql需要支持多条查询语句,在mysql配置url后加上:

&allowMultiQueries=true&useAffectedRows=true

Mapper查询数据的返回值用Object接收

 List<Object> getWarningDataFromPage(@Nullable @Param("userId") Long userId,
                                         @Nullable @Param("orderNum") String orderNum,
                                         @Nullable @Param("deviceId") String deviceId,
                                         @Param("pageSize") long pageSize,
                                         @Param("pageNo") long pageNo
    );

mapper xml语句

<!--需查询数据map-->
<resultMap id="BaseResultMap" type="com.jingli.modules.sys.warning.domain.Warning">
        <id property="id" column="id" jdbcType="INTEGER"/>
        <result property="warningId" column="warning_id" jdbcType="VARCHAR"/>
        <result property="warningType" column="warning_type" jdbcType="INTEGER"/>
        <result property="warningDesc" column="warning_desc" jdbcType="VARCHAR"/>
        <result property="warningContent" column="warning_content" jdbcType="VARCHAR"/>
        <result property="warningTime" column="warning_time" jdbcType="TIMESTAMP"/>
        <result property="operatorId" column="operator_id" jdbcType="INTEGER"/>
        <result property="deviceId" column="device_id" jdbcType="VARCHAR"/>
        <result property="port" column="port" jdbcType="VARCHAR"/>
        <result property="orderNumber" column="order_number" jdbcType="VARCHAR"/>
        <result property="status" column="status" jdbcType="INTEGER"/>
        <result property="remark" column="remark" jdbcType="VARCHAR"/>
    </resultMap>

<!--total数据-->
  <resultMap type="java.lang.Integer" id="count">
        <result column="total"/>
   </resultMap>

<!--进行多次查询获取到对应数据-->
<select id="getWarningDataFromPage" resultMap="BaseResultMap,count">
        SELECT SQL_CALC_FOUND_ROWS
        cw.id, cw.warning_id, cw.warning_type, cw.warning_desc, cw.warning_content, cw.warning_time, cw.operator_id,
        cw.device_id, cw.port, cw.order_number, cw.status, cw.remark
        FROM
        `c_warning` AS `cw`
        LEFT JOIN `c_charging_pile` AS `ccp` ON cw.device_id = ccp.ID
        WHERE 1 = 1
        <if test="null != userId">
            AND ccp.userId = #{userId}
        </if>
        <if test="null != orderNum and orderNum !=''">
            AND cw.order_number = #{orderNum}
        </if>
        <if test="null != deviceId and deviceId !=''">
            AND cw.device_id = #{deviceId}
        </if>

        limit #{pageNo},#{pageSize};
        
        ###用于获取total
        SELECT FOUND_ROWS() as total;
    </select>

从List<Object>中取出数据：

 @Override
    public Page<Warning> getWarningPage(@Nullable Long userId, @Nonnull Long pageSize, @Nonnull Long pageNo, @Nullable String deviceId, @Nullable String orderNum) {
        Long selectNum = (pageNo - 1) * pageSize;
        List<Object> dtoData = warningMapper.getWarningDataFromPage(userId, orderNum, deviceId, pageSize, selectNum);
        List<Warning> warningList = (List<Warning>) dtoData.get(0);
        Integer total =((List<Integer>) dtoData.get(1)).get(0);
        Page<Warning> warningPage = new Page<>(pageNo, pageSize);

        warningPage.setRecords(warningList);
        warningPage.setTotal(total);
        warningPage.setSearchCount(true);
        warningPage.setCurrent(pageNo);
        warningPage.setSize(pageSize);

        return warningPage;
    }

至此可实现mybatis plus的Page查询，mysql中执行查询total并不会再次查询，而是获取到对应的分页查询到数据的结果.