mybatis plus 多表分页查询使用QueryWrapper构造器

wcdunf

已于 2022-12-16 01:54:11 修改

阅读量933

点赞数

分类专栏： mybatisPlus 文章标签： mybatis mysql sql

于 2022-12-16 01:41:32 首次发布

本文链接：https://blog.csdn.net/wcdunf/article/details/128337615

版权

mybatisPlus 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

mysql语句

SELECT
	t1.number merchantNumber,
	t1.district_number districtNumber,
	t1.fraction,
	t1.latitude,
	t1.longitude,
	t1.merchant_name merchantName,
	t1.user_id userId,
	t3.district_name districtName,
	t4.avatar_url avatarUrl,
	t4.nickname 
FROM
	ec_merchant t1
	LEFT JOIN sys_user t2 ON t1.user_id = t2.user_id
	LEFT JOIN ec_district t3 ON t1.district_number = t3.number
	LEFT JOIN ec_we_chat_user t4 ON t1.user_id = t4.personnel_id 
WHERE
	t1.del_flag = 0 
	AND t4.personnel_id IS NOT NULL 
	AND t3.number = 'aWj2gj5NfpFkxx57ZFElp' 
	AND t1.user_id = '29' 
	AND t1.fraction BETWEEN 0 AND 40;
	GROUP BY t1.fraction DESC

service.java

IPage<Map<String, Object>> findWarningMerchantList(
@Param("page") Page<Object> page,
@Param("ew") QueryWrapper<Object> wrapper);

serviceImpl.java

import com.alibaba.fastjson.JSONObject; 
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import org.apache.commons.lang3.StringUtils;

@Override
    public AjaxResult findWarningMerchantList(JSONObject data) {
        String userId = data.getString("userId");
        String districtNumber = data.getString("districtNumber");
        ArrayList<Integer> fraction = data.getObject("fraction", ArrayList.class);
        Integer startIndex = data.getInteger("startIndex");
        Integer pageSize = data.getInteger("pageSize");
        Page<Object> page = new Page<>(startIndex, pageSize);
        QueryWrapper<Object> wrapper = new QueryWrapper<>();
        wrapper.eq( "t1.del_flag", 0);
        wrapper.isNotNull( "t4.personnel_id");
        wrapper.eq(StringUtils.isNotBlank(districtNumber), "t3.number", districtNumber);
        wrapper.eq(StringUtils.isNotBlank(userId), "t1.user_id", userId);
        if (fraction.size() > 0) {
            wrapper.between("t1.fraction", fraction.get(0), fraction.get(1));
        }
        wrapper.orderByDesc("t1.fraction");
        IPage<Map<String, Object>> result = baseMapper.findWarningMerchantList(page, wrapper);
        return AjaxResult.success(result);
    }

mapper.xml

 <select id="findWarningMerchantList" resultType="java.util.Map">
            SELECT
                t1.number merchantNumber,
                t1.district_number districtNumber,
                t1.fraction,
                t1.latitude,
                t1.longitude,
                t1.merchant_name merchantName,
                t1.user_id userId,
                t3.district_name districtName,
                t4.avatar_url avatarUrl,
                t4.nickname
            FROM
                ec_merchant t1
                LEFT JOIN sys_user t2 ON t1.user_id = t2.user_id
                LEFT JOIN ec_district t3 ON t1.district_number = t3.number
                LEFT JOIN ec_we_chat_user t4 ON t1.user_id = t4.personnel_id
            ${ew.customSqlSegment}
    </select>