记录一次使用 表达式引擎 自定义注解 还有 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();
        Field[] declaredFields = wisdomCourseDto.getClass().getDeclaredFields();
        for (int i = 0; i < declaredFields.length; i++) {
            //创建表达式引擎
            JexlEngine engine = new Engine();
            JexlContext context = new MapContext();
            char[] chars = declaredFields[i].getName().toCharArray();
            chars[0] = toUpperCase(chars[0]);
            int count = declaredFields[i].getAnnotation(CourseCalc.class).count();
            String project = declaredFields[i].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);
        }
    }