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_ROWScw.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.remarkFROM`c_warning` AS `cw`LEFT JOIN `c_charging_pile` AS `ccp` ON cw.device_id = ccp.IDWHERE 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};###用于获取totalSELECT FOUND_ROWS() as total;</select>

从List<Object>中取出数据：

 @Overridepublic 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并不会再次查询，而是获取到对应的分页查询到数据的结果.