mysql-plus自定义查询,带分页

xml

    <sql id="selectXgSampleRecord">
        SELECT  xsr.id ,xsr.jyjg_id as 'jyjgId', jyjg.jg_name as 'jyjgName', xsr.create_user_id as 'createUserId'  , xsr.jysb_id as 'jysbId' ,
        jysb.sbm as jysbName , sysUser.user_name as 'createUserName' , xsr.specimen_number as 'specimenNumber' , xsr.create_time as 'createTime',
        xsr.result_time as 'resultTime', xsr.result_user as 'resultUser',
            d.id as djxxId, d.xm, d.sfzh, d.jcrq, d.bgrq, d.ypmc, d.yyid, d.bgzt, d.sjh, d.djid, d.djsj, d.guid, d.csrq,
            d.nation, d.sex, d.ryfl, d.bz, d.age, d.xzz, d.dzzsj,
            d.native_detailed as nativeDetailed, d.xzz_code as xzzCode,
            d.former_code as formerCode, d.former_detailed as formerDetailed, d.xlh as sampleCode,
            cyd.cydname as cydName,czyUser.user_name as czyName,d.gzdw as gzdw,d.other_card as otherIdCard
        from  xg_sample_record xsr
        LEFT JOIN xg_jyjg jyjg on xsr.jyjg_id =jyjg.id
        LEFT JOIN xg_jysb jysb on xsr.jysb_id =jysb.id
        LEFT JOIN sys_user  sysUser on sysUser.user_id =xsr.create_user_id
        join xg_djxx d on xsr.djxx_id = d.id
        left join xg_cyd cyd on cyd.id = d.cyd_id
        left join sys_user czyUser  on  czyUser.user_id = d.czyid
    </sql>
	<!--  -->
    <select id="XgSampleRecordList" resultType="java.util.Map">
        <include refid="selectXgSampleRecord"/>

        ${ew.customSqlSegment}
        - 固定写法  官网有介绍
    </select>

mapper

    public List<Map<String, Object>> XgSampleRecordList(IPage<XgSampleRecord> page, 
    													@Param(Constants.WRAPPER) Wrapper queryWrapper);

serviceImpl

    @Override
    public List<Map<String, Object>> selectXgSampleRecordList(IPage<XgSampleRecord> page, Wrapper queryWrapper) {
        return xgSampleRecordMapper.XgSampleRecordList(page,queryWrapper);
    }

controller

    @PreAuthorize("@ss.hasPermi('accounting:XgSampleRecord:list')")
    @GetMapping("/list")
    public TableDataInfo list(XgSampleRecord xgSampleRecord, String createUser, String jysb, Long pageNum, Long pageSize) throws ParseException {
       // startPage();
        QueryWrapper<XgSampleRecord> lqw = new QueryWrapper<XgSampleRecord>();
        if (StringUtils.isNotBlank(xgSampleRecord.getJyjgId())){
            lqw.like("xsr.jg_name" ,xgSampleRecord.getJyjgId());
        }
        if (Strings.isNotBlank(createUser)){
            lqw.like("sysUser.user_name", createUser);
        }
        if (StringUtils.isNotBlank(jysb)){
            lqw.like("jysb.sbm", jysb);
        }
        if(StringUtils.isNotBlank(xgSampleRecord.getSampleCode()) ){
            lqw.eq("d.xlh",xgSampleRecord.getSampleCode());
        }
        if (xgSampleRecord.getSpecimenNumber() != null){
            lqw.eq("xsr.specimen_number" ,xgSampleRecord.getSpecimenNumber());
        }
        if (xgSampleRecord.getCreateTime() != null){
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
            String dateString = formatter.format(xgSampleRecord.getCreateTime());
           lqw.apply( "datediff (day, xsr.create_time, '"+dateString+"') = 0");
        }
        if (xgSampleRecord.getDjsj() != null){
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
            String dateString = formatter.format(xgSampleRecord.getDjsj());
            lqw.apply( "datediff (day, d.djsj, '"+dateString+"') = 0");
        }
        if (StringUtils.isNotBlank(xgSampleRecord.getResultUser())){
            lqw.eq("xsr.result_user" ,xgSampleRecord.getResultUser());
        }
        if(StringUtils.isNotBlank(xgSampleRecord.getCydId())){
            lqw.eq("cyd.id" ,xgSampleRecord.getCydId());
        }
        //System.err.println("检验机构id"+SecurityUtils.getLoginUser().getUser().getJgId());
        if(!ADMIN_jg.equals(SecurityUtils.getLoginUser().getUser().getJgId())){
            lqw.eq("xsr.jyjg_id", SecurityUtils.getLoginUser().getUser().getJgId());
        }
        lqw.inSql("d.bgzt", "1,2");
        //lqw.orderByDesc("xsr.id");
        IPage<XgSampleRecord> page = new Page<>(pageNum, pageSize);
        List<Map<String, Object>> list = iXgSampleRecordService.selectXgSampleRecordList(page,lqw);
        return getDataTable(list, page.getTotal());
    }