# SQL函数

# Wrapper内置的常用函数支持

  • selectSum(UserDO::getId)
  • selectCount(UserDO::getId)
  • selectMax(UserDO::getId)
  • selectMin(UserDO::getId)
  • selectAvg(UserDO::getId)
  • selectLen(UserDO::getId)

支持自定义别名

selectSum(UserDO::getId, UserDTO::getTotal)

对应sql

SUM(t.id) AS total

# 自定义聚合函数用法

public enum FuncEnum implements BaseFuncEnum {
    IF_SEX("IF(%s=1,'男','女')"),                         //if 性别转换
    CASE_SEX("CASE %s WHEN 1 THEN '男' ELSE '女' END"),   //case 性别转换
    LCASE("LCASE(%s)");
    
    private final String sql;
    
    FuncEnum(String sql) {
        this.sql = sql;
    }

    @Override
    public String getSql() {
        return this.sql;
    }
}

测试

void funcTest() {
    userMapper.selectJoinList(UserDTO.class, new MPJLambdaWrapper<>()
            // 自定义的函数枚举
            .selectFunc(FuncEnum.DATE_FORMAT, UserDO::getDel)
            // 也可以用lambda自定义
            .selectFunc(() -> "IF(%s=1,'男','女')", UserDO::getSex)
            // 支持多个通配符,参数顺序与arg参数顺序保持一致
            .selectFunc("concat(%s, %s)", arg -> arg.accept(UserDO::getName, UserDO::getId), UserDO::getSex)
            // 自定义字段别名
            .selectFunc("concat(%s, %s)", arg -> arg.accept(
                     Fun.f("t", UserDO::getName), //t.name
                     Fun.f("t", UserDO::getId)    //t.id
                ), UserDO::getSex)
            .leftJoin(UserAddressDO.class, on -> on
                    .eq(UserAddressDO::getUserId, UserDO::getId)
                    .eq(UserAddressDO::getId, UserDO::getId))
            .eq(UserDO::getId, 2));
}

对应sql

SELECT DATE_FORMAT(t.del, '%Y-%m-%d')            AS del,
       UCASE(t1.address)                         AS address,
       CASE t.sex WHEN 1 THEN '男' ELSE '女' END AS sex,
       IF(t.sex = 1, '男', '女')                 AS sex
FROM user t
         LEFT JOIN user_address t1 ON (t1.user_id = t.id AND t1.id = t.id)
WHERE (t.id = ?)