springboot+mybatisplus动态表名、多表分页

最新推荐文章于 2024-08-08 09:35:09 发布

小明的学习圈子

最新推荐文章于 2024-08-08 09:35:09 发布

阅读量549

点赞数 8

文章标签： spring boot mybatis

本文链接：https://blog.csdn.net/china_coding/article/details/136959631

版权

springboot+mybatisplus动态表名、多表分页

一、动态表名

1、service接口

IPage<TallyListHeade> queryTallyListHeadePageList(Page<TallyListHeade> page, TallyListHeade tallyListHeade);

2、serviceImpl实现类

@Override
    public IPage<TallyListHeade> queryTallyListHeadePageList(Page<TallyListHeade> page, TallyListHeade tallyListHeade) {
        QueryWrapper<TallyListHeade> queryWrapper = new QueryWrapper<>();
        String tableName = null;
        String trans = tallyListHeade.getTrans();
        if ("11".equals(trans)) {
            tableName = "v_before_tally_in";
        } else if ("12".equals(trans)) {
            tableName = "v_before_tally_out";
        }
        String startDate = tallyListHeade.getStartDate();
        String endDate = tallyListHeade.getEndDate();
        String shipNameEn = tallyListHeade.getShipNameEn();

        String gs = tallyListHeade.getGs();
        queryWrapper.eq("t.GS", gs);

        //to_char(t.ARRIVALDATE,'yyyy-mm')
        if (StringUtils.isNotBlank(startDate) && StringUtils.isNotBlank(endDate)) {
            queryWrapper.apply("TO_CHAR(t.Shipdate, 'YYYY-MM-DD') between {0} and {1}", startDate, endDate);
        } else if (StringUtils.isNotBlank(startDate)) {
            queryWrapper.apply("TO_CHAR(t.Shipdate, 'YYYY-MM-DD') >= {0}", startDate);
        } else if (StringUtils.isNotBlank(endDate)) {
            queryWrapper.apply("TO_CHAR(t.Shipdate, 'YYYY-MM-DD') <= {0}", endDate);
        }

        //UPPER(TRANSPORT_NAME) like UPPER('%" + shipname + "%')"
        if (StringUtils.isNotEmpty(shipNameEn)) {
            queryWrapper.apply("UPPER(t.SHIP_NAME_EN) LIKE UPPER('%" + shipNameEn + "%')");
        }
        TallyListHeade info = new TallyListHeade();
        info.setTableName(tableName);
        return page.setRecords(tallyListMapper.queryTallyListHeadePageList(page, queryWrapper, info));
    }

4、mapper接口

分页插件：Page

分页条件搜索的关键部分："@Param(Constants.WRAPPER)QueryWrapper "

动态表名设置部分：“@Param(“tallyListHeade”) TallyListHeade tallyListHeade)”

List<TallyListHeade> queryTallyListHeadePageList(Page<TallyListHeade> page, @Param(Constants.WRAPPER)QueryWrapper<TallyListHeade> queryWrapper,@Param("tallyListHeade") TallyListHeade tallyListHeade);

5、mapper的xmlsql语句

接受表名参数：“${tallyListHeade.tableName}”

分页固定写法：" ${ew.customSqlSegment}"

<select id="queryTallyListHeadePageList" resultType="org.jeecg.modules.customs.tally.entity.TallyListHeade"
            parameterType="org.jeecg.modules.customs.tally.entity.TallyListHeade">
        select t.*,decode(listguid,'','未生成','已生成') istally from  ${tallyListHeade.tableName}  t
        ${ew.customSqlSegment}
</select>

二、多表分页

和上边的区别是不用传递动态表名称这个参数，sql写成多表查询方式，然后通过apply方法可以动态的拼接各种条件。

1、service接口

IPage<ArrivalList> queryArrivalListPageList(Page<ArrivalList> page, ArrivalList arrivalList);

2、serviceImpl实现类

    @Override
    public IPage<ArrivalList> queryArrivalListPageList(Page<ArrivalList> page, ArrivalList arrivalList) {

        QueryWrapper<ArrivalList> queryWrapper = new QueryWrapper<>();

        String guid = arrivalList.getGuid();
        String parentBillNo= arrivalList.getParentBillNo();
        queryWrapper.eq("T.GUID",guid);
        //UPPER(PARENT_BILL_NO) like UPPER('%" + PARENT_BILL_NO + "%')";
        if(StringUtils.isNotEmpty(parentBillNo)){
            queryWrapper.apply("UPPER(t.PARENT_BILL_NO) LIKE UPPER('%" + parentBillNo + "%')");
        }

        return page.setRecords(arrivalListMapper.queryArrivalListPageList(page, queryWrapper));
    }

4、mapper接口

List<ArrivalList> queryArrivalListPageList(Page<ArrivalList> page,  @Param(Constants.WRAPPER)QueryWrapper<ArrivalList> queryWrapper);

5、mapper的xmlsql语句

    <select id="queryArrivalListPageList" resultType="org.jeecg.modules.customs.arrival.entity.ArrivalList"
            parameterType="org.jeecg.modules.customs.arrival.entity.ArrivalList" >
         select
           t.GUID,
           t.ID,
           a.WRAP_CNAME,
           a.WRAP_ENAME
        from T_ARRIVAL_LIST t
   left join
             T_BASE_WRAP a
          on t.WRAP_TYPE = a.WRAP_ENAME
        ${ew.customSqlSegment}
	</select>