lr393993507
java_天照
2019-04-28 22:10

MyBatis-plus分页条件自定义查询的问题

5
  • java
package io.renren.modules.soft.service.impl;

import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;

import io.renren.common.utils.PageUtils;
import io.renren.common.utils.Query;
import io.renren.modules.soft.dao.OrderDao;
import io.renren.modules.soft.dto.OrderVO;
import io.renren.modules.soft.entity.OrderEntity;
import io.renren.modules.soft.service.OrderService;


@Service("orderService")
public class OrderServiceImpl extends ServiceImpl<OrderDao, OrderEntity> implements OrderService {

    @Autowired
    private OrderDao orderDao;

    @Override
    public PageUtils getlistData(Map<String, Object> params) {
        Page<OrderVO> page = (Page<OrderVO>) new Query<OrderVO>().getPage(params);
        page.setRecords(this.baseMapper.selectListData(page,params));
        return new PageUtils(page);
    }

}

OrderDao.java方法

package io.renren.modules.soft.dao;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;

import io.renren.modules.soft.dto.OrderVO;
import io.renren.modules.soft.entity.OrderEntity;

/**
 * 订单基础表
 * 
 * @author Mark
 * @email sunlightcs@gmail.com
 * @date 2019-03-22 08:54:02
 */
@Mapper
public interface OrderDao extends BaseMapper<OrderEntity> {

    List<OrderVO> selectListData(Page<OrderVO> page, @Param("map")Map<String, Object> params);

}

自定的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="io.renren.modules.soft.dao.OrderDao">

    <!-- 可根据自己的需求,是否要使用 -->
    <resultMap type="io.renren.modules.soft.entity.OrderEntity" id="orderMap">
        <result property="id" column="ID"/>
        <result property="orderNo" column="order_no"/>
        <result property="orderType" column="order_type"/>
        <result property="orderTitle" column="order_title"/>
        <result property="totalFee" column="total_fee"/>
        <result property="settlementTotalFee" column="settlement_total_fee"/>
        <result property="payWay" column="pay_way"/>
        <result property="orderStatus" column="order_status"/>
        <result property="payStatus" column="pay_status"/>
        <result property="payId" column="pay_id"/>
        <result property="userId" column="user_id"/>
        <result property="payEndTime" column="pay_end_time"/>
        <result property="createTime" column="create_time"/>
        <result property="createBy" column="create_by"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="status" column="status"/>
    </resultMap>
    <!-- 可根据自己的需求,是否要使用 -->
    <resultMap type="io.renren.modules.soft.dto.OrderVO" id="orderVOMap">
        <result property="id" column="ID"/>
        <result property="orderNo" column="order_no"/>
        <result property="orderType" column="order_type"/>
        <result property="orderTitle" column="order_title"/>
        <result property="totalFee" column="total_fee"/>
        <result property="settlementTotalFee" column="settlement_total_fee"/>
        <result property="payWay" column="pay_way"/>
        <result property="orderStatus" column="order_status"/>
        <result property="payStatus" column="pay_status"/>
        <result property="payId" column="pay_id"/>
        <result property="username" column="username"/>
        <result property="payEndTime" column="pay_end_time"/>
    </resultMap>

<select id="selectListData" parameterType="String" resultMap="orderVOMap">
    SELECT
        a.ID,
        a.order_no,
        a.order_type,
        a.order_title,
        a.total_fee,
        a.settlement_total_fee,
        a.pay_way,
        a.order_status,
        a.pay_status,
        a.pay_id,
        au.wechat_nick_name as username,
        a.pay_end_time


    FROM
        tb_order a,
        tb_user au
    WHERE
        a.status = 0
        AND a.user_id = au.id
    <if test="map.username != null">
        AND au.wechat_nick_name like concat(concat("%",#{map.username}),"%")
    </if>
  </select>
</mapper>

出现的问题:当map.username不是空的时候,在第一页能够查询出所有的结果,但是跳转到第二页的时候,再输入条件查询,直接连带着分页参数和条件查询参数直接查询SQL语句。
这样,如果第二页中不存在这个条件的结果,那么就完全查询不到着一条数据。请问有什么解决方法

当在第一页查询的日志如下:

i.r.m.soft.dao.OrderDao.selectListData   : ==>  Preparing: SELECT a.ID, a.order_no, a.order_type, a.order_title, a.total_fee, a.settlement_total_fee, a.pay_way, a.order_status, a.pay_status, a.pay_id, au.wechat_nick_name as username, a.pay_end_time FROM tb_order a, tb_user au WHERE a.status = 0 AND a.user_id = au.id AND au.wechat_nick_name like concat(concat("%",?),"%") LIMIT ?,? 
i.r.m.soft.dao.OrderDao.selectListData   : ==> Parameters: 刘仁(String), 0(Long), 10(Long)
i.r.m.soft.dao.OrderDao.selectListData   : <==      Total: 3

当在第二页的时候查询日志如下:

i.r.m.soft.dao.OrderDao.selectListData   : ==>  Preparing: SELECT a.ID, a.order_no, a.order_type, a.order_title, a.total_fee, a.settlement_total_fee, a.pay_way, a.order_status, a.pay_status, a.pay_id, au.wechat_nick_name as username, a.pay_end_time FROM tb_order a, tb_user au WHERE a.status = 0 AND a.user_id = au.id AND au.wechat_nick_name like concat(concat("%",?),"%") LIMIT ?,? 
i.r.m.soft.dao.OrderDao.selectListData   : ==> Parameters: 刘仁(String), 10(Long), 10(Long)
i.r.m.soft.dao.OrderDao.selectListData   : <==      Total: 0

因为分页了,所有查询不到结果,可是我想分不分也都能查询到全部结果,各位大神有什么好的解决方案,请帮一下忙。

  • 点赞
  • 回答
  • 收藏
  • 复制链接分享

2条回答