java+mybatis动态获取时间更新库表功能

概述

库表是根据时间创建的，查询人员出勤需要根据时间动态修改sql

技术栈

springboot + mybatis + ruoyi框架

Mapper.xml

        select YCQ,SJCQ from (
        select (select count(1) from ckyw${year}.dbo.kqb${erpYear}${month} where dw in ('金型一线','金型二线')) YCQ,
        (select count(1) from ckyw${year}.dbo.kqb${erpYear}${month} where dw in ('金型一线','金型二线') and m${day}<>2) SJCQ
        ) A

Mapper

HashMap getKJYJXJGrygl(@Param("year") int year,@Param("erpYear") String erpYear,@Param("month") String month,@Param("day") String day);

Service

HashMap getKJYJXJGrygl(@Param("year") int year,@Param("erpYear") String erpYear,@Param("month") String month,@Param("day") String day);

ServiceImpl

    @Override
    public HashMap getKJYJXJGrygl(@Param("year") int year, @Param("erpYear") String erpYear, @Param("month") String month, @Param("day") String day) {
        return kjyjxjgMapper.getKJYJXJGrygl(year,erpYear,month,day);
    }

Controller

    @GetMapping("/getKJYJXJGrygl")
    public AjaxResult getKJYJXJGrygl() {
        LocalDate now = LocalDate.now();
        int year = now.getYear();
        String erpyear =String.format("%02d", year%100); ;
        int monthValue = now.getMonthValue();
        String month = String.format("%02d", monthValue);
        int dayOfMonth = now.getDayOfMonth()-1;
        String day = String.format("%02d", dayOfMonth);
        HashMap rygl = kjyjxjgService.getKJYJXJGrygl(year,erpyear,month,day);
        return AjaxResult.success(rygl);
    }