记录一次使用 表达式引擎 自定义注解 还有 sql union all 实现对数据库数据提取、重组、计算的业务 mybatis-plus + SpringBoot

这样编写减少了前后端很多没必要的遍历，以及if判断并最大限度提高了代码的可变通性
额外需要学习的是ORM框架下，如何接收多表（各表结构不同）操作后，sql返回的新结构的临时表问题

表达式引擎用到的依赖

        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-jexl3</artifactId>
            <version>3.1</version>
        </dependency>

超长sql语句

  @Select("\tSELECT address school, classesid class_name, count(id) count, #{e} project from (\n" +
            "\t\tSELECT * FROM `t_wisdom_course` where valuemorning1 like CONCAT('%', #{e}, '%') union all\n" +
            "\t\tSELECT * FROM `t_wisdom_course` where valuemorning2 like CONCAT('%', #{e}, '%') union all\n" +
            "\t\tSELECT * FROM `t_wisdom_course` where valuemorning3 like CONCAT('%', #{e}, '%') union all\n" +
            "\t\tSELECT * FROM `t_wisdom_course` where valuemorning4 like CONCAT('%', #{e}, '%') union all\n" +
            "\t\tSELECT * FROM `t_wisdom_course` where valuemorning5 like CONCAT('%', #{e}, '%') union all\n" +
            "\t\tSELECT * FROM `t_wisdom_course` where valueafternoon1 like CONCAT('%', #{e}, '%') union all\n" +
            "\t\tSELECT * FROM `t_wisdom_course` where valueafternoon2 like CONCAT('%', #{e}, '%') union all\n" +
            "\t\tSELECT * FROM `t_wisdom_course` where valueafternoon3 like CONCAT('%', #{e}, '%') union all\n" +
            "\t\tSELECT * FROM `t_wisdom_course` where valueafternoon4 like CONCAT('%', #{e}, '%') union all\n" +
            "\t\tSELECT * FROM `t_wisdom_course` where valueafternoon5 like CONCAT('%', #{e}, '%') union all\n" +
            "\t\tSELECT * FROM `t_wisdom_course` where valueweek1 like CONCAT('%', #{e}, '%') union all\n" +
            "\t\tSELECT * FROM `t_wisdom_course` where valueweek2 like CONCAT('%', #{e}, '%') union all\n" +
            "\t\tSELECT * FROM `t_wisdom_course` where valueweek3 like CONCAT('%', #{e}, '%') union all\n" +
            "\t\tSELECT * FROM `t_wisdom_course` where valueweek4 like CONCAT('%', #{e}, '%') union all\n" +
            "\t\tSELECT * FROM `t_wisdom_course` where valueweek5 like CONCAT('%', #{e}, '%')\n" +
            "\t) a GROUP BY classesid;")
    List<WisdomCourseTypeDto> findAllProject(String e);

自定义注解

/**
 * @Author Lambert
 * @Date 2023/4/20 10:01
 ***/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface CourseCalc {
    int count() default 80;
}

DTO类

@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class WisdomCourseDto {
    @CourseCalc(count = 80)
    @ApiModelProperty(value = "走平衡木")
    private List<WisdomCourseTypeDto> balanceBeam;

    @CourseCalc(count = 80)
    @ApiModelProperty(value = "单脚站")
    private List<WisdomCourseTypeDto> standOnOneLeg;

    @CourseCalc(count = 80)
    @ApiModelProperty(value = "脚尖对脚跟倒着走")
    private List<WisdomCourseTypeDto> walkToetoheelBackwards;

    @CourseCalc(count = 80)
    @ApiModelProperty(value = "单脚原地跳")
    private List<WisdomCourseTypeDto> jumpOnOneFoot;

    @CourseCalc(count = 80)
    @ApiModelProperty(value = "双脚原地左右跳")
    private List<WisdomCourseTypeDto> jumpLeftAndRightWithYourFeetOnTheSpot;

    @CourseCalc(count = 80)
    @ApiModelProperty(value = "双脚左右交替跳")
    private List<WisdomCourseTypeDto> hopFromSideToSide;

    @CourseCalc(count = 80)
    @ApiModelProperty(value = "立定跳远")
    private List<WisdomCourseTypeDto> standingBroadJump;

    @CourseCalc(count = 80)
    @ApiModelProperty(value = "一次性连续蹲起")
    private List<WisdomCourseTypeDto> squatContinuouslyInOneGo;

    @CourseCalc(count = 80)
    @ApiModelProperty(value = "蹲起")
    private List<WisdomCourseTypeDto> squat;

    @CourseCalc(count = 80)
    @ApiModelProperty(value = "原地侧面投")
    private List<WisdomCourseTypeDto> sideThrowInPlace;

    @CourseCalc(count = 80)
    @ApiModelProperty(value = "脚尖对脚跟走")
    private List<WisdomCourseTypeDto> goToeToHeel;

    @CourseCalc(count = 80)
    @ApiModelProperty(value = "双脚向前左右跳")
    private List<WisdomCourseTypeDto> jumpLeftAndRightWithYourFeetForward;

    @CourseCalc(count = 80)
    @ApiModelProperty(value = "蛙跳")
    private List<WisdomCourseTypeDto> leapfrog;

    @CourseCalc(count = 80)
    @ApiModelProperty(value = "脚尖对脚跟走（一）")
    private List<WisdomCourseTypeDto> toeToHeelOne;

    @CourseCalc(count = 80)
    @ApiModelProperty(value = "脚尖对脚跟走（二）")
    private List<WisdomCourseTypeDto> toeToHeelTwo;

    @CourseCalc(count = 80)
    @ApiModelProperty(value = "原地踏步走")
    private List<WisdomCourseTypeDto> walkinplace;

    @CourseCalc(count = 80)
    @ApiModelProperty(value = "走直线")
    private List<WisdomCourseTypeDto> takeTheAirLine;

    @CourseCalc(count = 80)
    @ApiModelProperty(value = "走曲线")
    private List<WisdomCourseTypeDto> curve;

    @CourseCalc(count = 80)
    @ApiModelProperty(value = "手膝爬")
    private List<WisdomCourseTypeDto> handAndKneeCrawling;

    @CourseCalc(count = 80)
    @ApiModelProperty(value = "手足爬")
    private List<WisdomCourseTypeDto> footAndFootCrawling;

    @CourseCalc(count = 80)
    @ApiModelProperty(value = "钻爬")
    private List<WisdomCourseTypeDto> method;

    @CourseCalc(count = 80)
    @ApiModelProperty(value = "原地正面投")
    private List<WisdomCourseTypeDto> headInPlace;

    @CourseCalc(count = 32)
    @ApiModelProperty(value = "连续三次跳")
    private List<WisdomCourseTypeDto> threeJumpsInARow;

    @CourseCalc(count = 32)
    @ApiModelProperty(value = "单脚连续跳")
    private List<WisdomCourseTypeDto> hopContinuouslyOnOneFoot;

    @CourseCalc(count = 32)
    @ApiModelProperty(value = "单脚连续向前跳0.5米")
    private List<WisdomCourseTypeDto> jumpForwardOnOneFootInSuccessionMeter;

    @CourseCalc(count = 32)
    @ApiModelProperty(value = "单脚连续向前跳")
    private List<WisdomCourseTypeDto> jumpForwardOnOneFootInSuccession;
}

核心实现

@SpringBootTest
class EducationErverApplicationTests {

    @Autowired
    WisdomCourseTypeMapper wisdomCourseTypeMapper;
    @Autowired
    WisdomCourseMapper wisdomCourseMapper;

    @Test
    void contextLoads() {
        WisdomCourseDto wisdomCourseDto = new WisdomCourseDto();
        //创建表达式引擎
        JexlEngine engine = new Engine();
        JexlContext context = new MapContext();
        Field[] declaredFields = wisdomCourseDto.getClass().getDeclaredFields();
        for (int i = 0; i < declaredFields.length; i++) {
            char[] chars = declaredFields[i].getName().toCharArray();
            chars[0] = toUpperCase(chars[0]);
            int count = declaredFields[0].getAnnotation(CourseCalc.class).count();
            String project = declaredFields[0].getAnnotation(ApiModelProperty.class).value();
            List<WisdomCourseTypeDto> t = wisdomCourseMapper.findAllProject(project);
            for (WisdomCourseTypeDto wisdomCourseTypeDto : t) {
                DecimalFormat df = new DecimalFormat("0.00");
                String format = df.format(Float.valueOf(wisdomCourseTypeDto.getCount()) / count * 100) + "%";
                wisdomCourseTypeDto.setCount(format);
            }
            String expressionStr = "wisdomCourseDto.set" + String.valueOf(chars) + "(t)";
            context.set("t", t);
            context.set("wisdomCourseDto", wisdomCourseDto);
            JexlExpression expression = engine.createExpression(expressionStr);
            Object o = expression.evaluate(context);
        }
    }

    /**
     * 字符转成大写
     *
     * @param c 需要转化的字符
     */
    public static char toUpperCase(char c) {
        if (97 <= c && c <= 122) {
            c ^= 32;
        }
        return c;
    }
}