5 Mybatis Plus自定义分页查询

自定义SQL分页查询

1、Mapper接口方法自定义

public interface RentSetMealMapper extends BaseMapper<RentSetMeal> {

    /**
     * 自定义连表查询,包含分页
     * @param page
     * @param rentSetMealDto
     * @param marketParkingId
     * @return
     */
    Page<RentSetMealData> getPageAll(
    	IPage<RentSetMealData> page,
    	@Param("rentSetMealDto") RentSetMealDto rentSetMealDto, 
    	@Param("marketParkingId") List<Long> marketParkingId);
    	}    

2、Mapper.xml中进行相应sql语句编写

<?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="cn.com.hopson.hopsonone.park.mapper.platform.RentSetMealMapper">

    <select id="getPageAll" resultType="cn.com.hopson.hopsonone.park.domain.platform.data.RentSetMealData">
        SELECT rent.rent_set_meal_id,rent.rent_set_meal_no,rent.rent_set_meal_name,
        rent.months,rent.selling_price,rent.crowd,rent.type,rent.examine_status,rent.`status`,
        rule.rule_rent_name,rule.parking_name,rule.parking_id,rule.partition_name,rule.fixed_status
        FROM pf_rent_set_meal rent, pf_rule_rent rule
        WHERE rent.rule_rent_id=rule.rule_rent_id
        <if test="rentSetMealDto.rentSetMealName != null and rentSetMealDto.rentSetMealName != ''">
            AND (rent.rent_set_meal_name LIKE #{rentSetMealDto.rentSetMealName} or rent.rent_set_meal_no LIke
            #{rentSetMealDto.rentSetMealName})
        </if>
        <if test="rentSetMealDto.ruleRentName != null and rentSetMealDto.ruleRentName != ''">
            AND (rule.rule_rent_name LIKE #{rentSetMealDto.ruleRentName} or rule.rule_rent_no LIKE
            #{rentSetMealDto.ruleRentName})
        </if>
        <if test="rentSetMealDto.crowd != null and rentSetMealDto.crowd != ''">
            AND rent.crowd LIKE #{rentSetMealDto.crowd}
        </if>
        <if test="rentSetMealDto.status != null">
            AND rent.status = #{rentSetMealDto.status}
        </if>
        <if test="rentSetMealDto.type != null">
            AND rent.type = #{rentSetMealDto.type}
        </if>
        <if test="rentSetMealDto.partitionId != null">
            AND rule.partition_id = #{rentSetMealDto.partitionId}
        </if>
        <if test="rentSetMealDto.type != null">
            <if test="rentSetMealDto.type == 1">
                AND rule.fixed_status = 1
            </if>
            <if test="rentSetMealDto.type == 2">
                AND rule.fixed_status = 0
            </if>
        </if>
        <choose>
            <when test="rentSetMealDto.parkingId != null and rentSetMealDto.parkingId > 0">
                AND rule.parking_id = #{rentSetMealDto.parkingId}
            </when>
            <otherwise>
                <if test="marketParkingId != null and marketParkingId.size > 0">
                    AND rule.parking_id in
                    <foreach collection="marketParkingId" index="index" item="item" open="("
                             separator="," close=")">
                        #{item}
                    </foreach>
                </if>
            </otherwise>
        </choose>
        ORDER BY rent.create_time
    </select>
</mapper>                    

3、Service进行接口调用

        Page<RentSetMealData> page = new Page<>();
        if (rentSetMealDto.getPageNum() != null && rentSetMealDto.getPageSize() != null) {
            page.setCurrent(rentSetMealDto.getPageNum()).setSize(rentSetMealDto.getPageSize());
        }
        if (StringUtils.isNotBlank(rentSetMealDto.getRuleRentName())) {
            rentSetMealDto.setRuleRentName("%" + rentSetMealDto.getRuleRentName() + "%");
        }
        if (StringUtils.isNotBlank(rentSetMealDto.getRentSetMealName())) {
            rentSetMealDto.setRentSetMealName("%" + rentSetMealDto.getRentSetMealName() + "%");
        }
        if (StringUtils.isNotBlank(rentSetMealDto.getCrowd())) {
            rentSetMealDto.setCrowd("%" + rentSetMealDto.getCrowd() + "%");
        }
IPage<RentSetMealData> pageAll = rentSetMealMapper.getPageAll(page, rentSetMealDto, marketParkingId);