MybatisPlus通用增强BaseMapper，新增通过ID更新实体全部字段或只更新非空字段方法
通用扩展BaseMapper
作用：继承MybatisPlus原有的BaseMapper，功能增强。比如：批量查询、批量更新、查询单个或多个VO对象…有了这些方法Service层就不需要去继承MybatisPlus的IService了，减少代码侵入。
代码自取：
package xxx.common.mybatis.core.mapper;
import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ObjectUtil;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.core.toolkit.ReflectionKit;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.toolkit.Db;
import com.zxsl.core.exception.NotFoundException;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.logging.Log;
import org.apache.ibatis.logging.LogFactory;
import java.io.Serializable;
import java.util.Collection;
import java.util.List;
import java.util.Map;
/**
* 自定义Mapper接口, 实现自定义扩展
* @param <T> table实体 泛型
* @author Boven
* @since 2024-01-13
*/
@SuppressWarnings("unchecked")
public interface BaseMapperExt<T> extends BaseMapper<T> {
Log log = LogFactory.getLog(BaseMapperExt.class);
default Class<T> currentModelClass() {
return (Class<T>) ReflectionKit.getSuperClassGenericType(this.getClass(), BaseMapperExt.class, 0);
}
// -------------------------- 插入 --------------------------
/**
* 批量插入
*/
default boolean insertBatch(Collection<T> entityList) {
return Db.saveBatch(entityList);
}
/**
* 批量插入(包含限制条数)
*/
default boolean insertBatch(Collection<T> entityList, int batchSize) {
return Db.saveBatch(entityList, batchSize);
}
/**
* 插入或更新(包含限制条数)
*/
default boolean insertOrUpdate(T entity) {
return Db.saveOrUpdate(entity);
}
/**
* 批量插入或更新
*/
default boolean insertOrUpdateBatch(Collection<T> entityList) {
return Db.saveOrUpdateBatch(entityList);
}
/**
* 批量插入或更新(包含限制条数)
*/
default boolean insertOrUpdateBatch(Collection<T> entityList, int batchSize) {
return Db.saveOrUpdateBatch(entityList, batchSize);
}
// -------------------------- 更新 --------------------------
/**
* 更新所有字段，包括空值字段
* @see xxx.common.mybatis.methods.UpdateAlwaysById
*/
int updateAlwaysById(@Param(Constants.ENTITY) T entity);
/**
* 更新非空字段
* @see xxx.common.mybatis.methods.UpdateNotNullById
*/
int updateNotNullById(@Param(Constants.ENTITY) T entity);
/**
* 更新非空且非空白字符串字段
* @see xxx.common.mybatis.methods.UpdateNotEmptyById
*/
int updateNotEmptyById(@Param(Constants.ENTITY) T entity);
/**
* 批量更新
*/
default boolean updateBatchById(Collection<T> entityList) {
return Db.updateBatchById(entityList);
}
/**
* 批量更新(包含限制条数)
*/
default boolean updateBatchById(Collection<T> entityList, int batchSize) {
return Db.updateBatchById(entityList, batchSize);
}
// -------------------------- 查询 --------------------------
/**
* 根据 ID 查询
*/
default T selectByIdValid(Serializable id) {
T obj = this.selectById(id);
if (ObjectUtil.isNull(obj))
throw new NotFoundException();
return obj;
}
default <C> C selectVoById(Serializable id, Class<C> voClass) {
T obj = this.selectById(id);
if (ObjectUtil.isNull(obj)) {
return null;
}
// return MapstructUtils.convert(obj, voClass);
return BeanUtil.copyProperties(obj, voClass);
// return BeanUtils.copyProperties(obj, voClass);
// return BeanCopyUtils.copy(obj, voClass);
}
default <C> List<C> selectVoBatchIds(Collection<? extends Serializable> idList, Class<C> voClass) {
List<T> list = this.selectBatchIds(idList);
if (CollUtil.isEmpty(list)) {
return CollUtil.newArrayList();
}
// return BeanCopyUtils.copyList(list, voClass);
return BeanUtil.copyToList(list, voClass);
}
/**
* 查询（根据 columnMap 条件）
*/
default <C> List<C> selectVoByMap(Map<String, Object> map, Class<C> voClass) {
List<T> list = this.selectByMap(map);
if (CollUtil.isEmpty(list)) {
return CollUtil.newArrayList();
}
return BeanUtil.copyToList(list, voClass);
// return BeanCopyUtils.copyList(list, voClass);
}
/**
* 根据 entity 条件，查询一条记录
*/
default <C> C selectVoOne(Wrapper<T> wrapper, Class<C> voClass) {
T obj = this.selectOne(wrapper);
if (ObjectUtil.isNull(obj)) {
return null;
}
// return BeanCopyUtils.copy(obj, voClass);
return BeanUtil.copyProperties(obj, voClass);
}
/**
* 查询列表，不用传参
*/
default List<T> selectList() {
return this.selectList(new QueryWrapper<>());
}
/**
* 根据 entity 条件，查询全部记录
*/
default <C> List<C> selectVoList(Wrapper<T> wrapper, Class<C> voClass) {
List<T> list = this.selectList(wrapper);
if (CollUtil.isEmpty(list)) {
return CollUtil.newArrayList();
}
return BeanUtil.copyToList(list, voClass);
// return BeanCopyUtils.copyList(list, voClass);
}
/**
* 分页查询VO
*/
default <C, P extends IPage<C>> P selectVoPage(IPage<T> page, Wrapper<T> wrapper, Class<C> voClass) {
List<T> list = this.selectList(page, wrapper);
IPage<C> voPage = new Page<>(page.getCurrent(), page.getSize(), page.getTotal());
if (CollUtil.isEmpty(list)) {
return (P) voPage;
}
voPage.setRecords(BeanUtil.copyToList(list, voClass));
// voPage.setRecords(BeanCopyUtils.copyList(list, voClass));
return (P) voPage;
}
}
updateAlwaysById updateNotNullById updateNotEmptyById三个方法的实现类
updateAlwaysById：更新实体所有字段，包括空值字段
updateNotNullById：更新实体非空字段
updateNotEmptyById：更新实体非空且非空白字符串字段
说明：
这3个方法不受MybatisPlus全局配置文件mybatis-plus.global-config.dbConfig.updateStrategy的影响，该配置系统默认是NOT_NULL，当我们在使用demoMapper.updateById(demo)的时候，如果实体的字段是null值，不会去更新数据库，但有的时候我们就是需要把数据库字段覆盖为null，就只能写Update方法一个个设置，比较繁琐。用这3个方法就能非常容易且灵活的实现，不受配置文件和实体字段注解@TableField(updateStrategy = FieldStrategy.ALWAYS)的影响，更加自由灵活。
UpdateAlwaysById
package xxx.common.mybatis.methods;
import com.baomidou.mybatisplus.core.enums.SqlMethod;
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.core.toolkit.sql.SqlScriptUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;
import java.util.Objects;
import static java.util.stream.Collectors.joining;
/**
* 根据 ID 修改对象所有字段，包含空值字段。效果等同于FieldStrategy.ALWAYS。<br>
* 此方法不受全局配置 mybatis-plus.global-config.dbConfig.updateStrategy 影响
*
* @author Boven
* @date 2024-11-15
*/
@Slf4j
public class UpdateAlwaysById extends AbstractMethod {
public UpdateAlwaysById() {
super("updateAlwaysById");
}
@Override
public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
final String additional = optlockVersion(tableInfo) + tableInfo.getLogicDeleteSql(true, true);
final String sqlSet = sqlSet(tableInfo.isWithLogicDelete(), false, tableInfo, false, ENTITY, ENTITY_DOT);
String sql = String.format(SqlMethod.UPDATE_BY_ID.getSql(), tableInfo.getTableName(), sqlSet,
tableInfo.getKeyColumn(), ENTITY_DOT + tableInfo.getKeyProperty(), additional);
SqlSource sqlSource = super.createSqlSource(configuration, sql, modelClass);
return addUpdateMappedStatement(mapperClass, modelClass, methodName, sqlSource);
}
protected String sqlSet(boolean logic, boolean ew, TableInfo table, boolean judgeAliasNull, final String alias,
final String prefix) {
String sqlScript = getAllSqlSet(logic, prefix, table);
if (judgeAliasNull) {
sqlScript = SqlScriptUtils.convertIf(sqlScript, String.format("%s != null", alias), true);
}
if (ew) {
sqlScript += NEWLINE;
sqlScript += convertIfEwParam(U_WRAPPER_SQL_SET, false);
}
sqlScript = SqlScriptUtils.convertSet(sqlScript);
return sqlScript;
}
/**
* 获取所有的 sql set 片段
*
* @param ignoreLogicDelFiled 是否过滤掉逻辑删除字段
* @param prefix 前缀
* @return sql 脚本片段
*/
public String getAllSqlSet(boolean ignoreLogicDelFiled, final String prefix, TableInfo table) {
final String newPrefix = prefix == null ? EMPTY : prefix;
// i.getSqlSet(true, newPrefix) 是不同点
return table.getFieldList().stream()
.filter(i -> {
if (ignoreLogicDelFiled) {
return !(table.isWithLogicDelete() && i.isLogicDelete());
}
return true;
}).map(i -> i.getSqlSet(true, newPrefix)).filter(Objects::nonNull).collect(joining(NEWLINE));
}
}
UpdateNotNullById
package xxx.common.mybatis.methods;
import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.FieldStrategy;
import com.baomidou.mybatisplus.core.enums.SqlMethod;
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.metadata.TableFieldInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.core.toolkit.sql.SqlScriptUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;
import java.util.Objects;
import static java.util.stream.Collectors.joining;
/**
* 根据 ID 修改对象不为Null字段。效果等同于FieldStrategy.NOT_NULL。<br>
* 不受全局配置 mybatis-plus.global-config.dbConfig.updateStrategy 影响
*
* @author Boven
* @date 2024-11-15
*/
@Slf4j
public class UpdateNotNullById extends AbstractMethod {
public UpdateNotNullById() {
super("updateNotNullById");
}
@Override
public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
final String additional = optlockVersion(tableInfo) + tableInfo.getLogicDeleteSql(true, true);
final String sqlSet = sqlSet(tableInfo.isWithLogicDelete(), false, tableInfo, false, ENTITY, ENTITY_DOT);
String sql = String.format(SqlMethod.UPDATE_BY_ID.getSql(), tableInfo.getTableName(), sqlSet,
tableInfo.getKeyColumn(), ENTITY_DOT + tableInfo.getKeyProperty(), additional);
SqlSource sqlSource = super.createSqlSource(configuration, sql, modelClass);
return addUpdateMappedStatement(mapperClass, modelClass, methodName, sqlSource);
}
protected String sqlSet(boolean logic, boolean ew, TableInfo table, boolean judgeAliasNull, final String alias,
final String prefix) {
String sqlScript = getAllSqlSet(logic, prefix, table);
if (judgeAliasNull) {
sqlScript = SqlScriptUtils.convertIf(sqlScript, String.format("%s != null", alias), true);
}
if (ew) {
sqlScript += NEWLINE;
sqlScript += convertIfEwParam(U_WRAPPER_SQL_SET, false);
}
sqlScript = SqlScriptUtils.convertSet(sqlScript);
return sqlScript;
}
/**
* 获取所有的 sql set 片段
*
* @param ignoreLogicDelFiled 是否过滤掉逻辑删除字段
* @param prefix 前缀
* @return sql 脚本片段
*/
public String getAllSqlSet(boolean ignoreLogicDelFiled, final String prefix, TableInfo table) {
final String newPrefix = prefix == null ? EMPTY : prefix;
return table.getFieldList().stream()
.filter(i -> {
if (ignoreLogicDelFiled) {
return !(table.isWithLogicDelete() && i.isLogicDelete());
}
return true;
}).map(i -> getSqlSet(false, newPrefix, i)).filter(Objects::nonNull).collect(joining(NEWLINE));
}
/**
* 获取 set sql 片段
*
* @param ignoreIf 忽略 IF 包裹
* @param prefix 前缀
* @return sql 脚本片段
*/
public String getSqlSet(final boolean ignoreIf, final String prefix, final TableFieldInfo fieldInfo) {
final String newPrefix = prefix == null ? EMPTY : prefix;
boolean withUpdateFill = fieldInfo.getFieldFill() == FieldFill.UPDATE || fieldInfo.getFieldFill() == FieldFill.INSERT_UPDATE;
// 默认: column=
String sqlSet = fieldInfo.getColumn() + EQUALS;
if (StringUtils.isNotBlank(fieldInfo.getUpdate())) {
sqlSet += String.format(fieldInfo.getUpdate(), fieldInfo.getColumn());
} else {
sqlSet += SqlScriptUtils.safeParam(newPrefix + fieldInfo.getEl());
}
sqlSet += COMMA;
if (ignoreIf) {
return sqlSet;
}
if (withUpdateFill) {
// 不进行 if 包裹
return sqlSet;
}
// FieldStrategy.NOT_NULL 是区别
return convertIf(sqlSet, convertIfProperty(newPrefix, fieldInfo.getProperty()), FieldStrategy.NOT_NULL, fieldInfo);
}
/**
* 转换成 if 标签的脚本片段
*
* @param sqlScript sql 脚本片段
* @param property 字段名
* @param fieldStrategy 验证策略
* @return if 脚本片段
*/
private String convertIf(final String sqlScript, final String property, final FieldStrategy fieldStrategy, final TableFieldInfo fieldInfo) {
if (fieldStrategy == FieldStrategy.NEVER) {
return null;
}
if (fieldInfo.isPrimitive() || fieldStrategy == FieldStrategy.IGNORED || fieldStrategy == FieldStrategy.ALWAYS) {
return sqlScript;
}
if (fieldStrategy == FieldStrategy.NOT_EMPTY && fieldInfo.isCharSequence()) {
return SqlScriptUtils.convertIf(sqlScript, String.format("%s != null and %s != ''", property, property),
false);
}
return SqlScriptUtils.convertIf(sqlScript, String.format("%s != null", property), false);
}
private String convertIfProperty(String prefix, String property) {
return StringUtils.isNotBlank(prefix) ? prefix.substring(0, prefix.length() - 1) + "['" + property + "']" : property;
}
}
UpdateNotEmptyById
package xxx.common.mybatis.methods;
import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.FieldStrategy;
import com.baomidou.mybatisplus.core.enums.SqlMethod;
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.metadata.TableFieldInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.core.toolkit.sql.SqlScriptUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;
import java.util.Objects;
import static java.util.stream.Collectors.joining;
/**
* 根据 ID 修改对象不为Empty字段。效果等同于FieldStrategy.NOT_EMPTY。<br>
* 不受全局配置 mybatis-plus.global-config.dbConfig.updateStrategy 影响
*
* @author Boven
* @date 2024-11-15
*/
@Slf4j
public class UpdateNotEmptyById extends AbstractMethod {
public UpdateNotEmptyById() {
super("updateNotEmptyById");
}
@Override
public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
final String additional = optlockVersion(tableInfo) + tableInfo.getLogicDeleteSql(true, true);
final String sqlSet = sqlSet(tableInfo.isWithLogicDelete(), false, tableInfo, false, ENTITY, ENTITY_DOT);
String sql = String.format(SqlMethod.UPDATE_BY_ID.getSql(), tableInfo.getTableName(), sqlSet,
tableInfo.getKeyColumn(), ENTITY_DOT + tableInfo.getKeyProperty(), additional);
SqlSource sqlSource = super.createSqlSource(configuration, sql, modelClass);
return addUpdateMappedStatement(mapperClass, modelClass, methodName, sqlSource);
}
protected String sqlSet(boolean logic, boolean ew, TableInfo table, boolean judgeAliasNull, final String alias,
final String prefix) {
String sqlScript = getAllSqlSet(logic, prefix, table);
if (judgeAliasNull) {
sqlScript = SqlScriptUtils.convertIf(sqlScript, String.format("%s != null", alias), true);
}
if (ew) {
sqlScript += NEWLINE;
sqlScript += convertIfEwParam(U_WRAPPER_SQL_SET, false);
}
sqlScript = SqlScriptUtils.convertSet(sqlScript);
return sqlScript;
}
/**
* 获取所有的 sql set 片段
*
* @param ignoreLogicDelFiled 是否过滤掉逻辑删除字段
* @param prefix 前缀
* @return sql 脚本片段
*/
public String getAllSqlSet(boolean ignoreLogicDelFiled, final String prefix, TableInfo table) {
final String newPrefix = prefix == null ? EMPTY : prefix;
return table.getFieldList().stream()
.filter(i -> {
if (ignoreLogicDelFiled) {
return !(table.isWithLogicDelete() && i.isLogicDelete());
}
return true;
}).map(i -> getSqlSet(false, newPrefix, i)).filter(Objects::nonNull).collect(joining(NEWLINE));
}
/**
* 获取 set sql 片段
*
* @param ignoreIf 忽略 IF 包裹
* @param prefix 前缀
* @return sql 脚本片段
*/
public String getSqlSet(final boolean ignoreIf, final String prefix, final TableFieldInfo fieldInfo) {
final String newPrefix = prefix == null ? EMPTY : prefix;
boolean withUpdateFill = fieldInfo.getFieldFill() == FieldFill.UPDATE || fieldInfo.getFieldFill() == FieldFill.INSERT_UPDATE;
// 默认: column=
String sqlSet = fieldInfo.getColumn() + EQUALS;
if (StringUtils.isNotBlank(fieldInfo.getUpdate())) {
sqlSet += String.format(fieldInfo.getUpdate(), fieldInfo.getColumn());
} else {
sqlSet += SqlScriptUtils.safeParam(newPrefix + fieldInfo.getEl());
}
sqlSet += COMMA;
if (ignoreIf) {
return sqlSet;
}
if (withUpdateFill) {
// 不进行 if 包裹
return sqlSet;
}
// FieldStrategy.NOT_EMPTY 是区别
return convertIf(sqlSet, convertIfProperty(newPrefix, fieldInfo.getProperty()), FieldStrategy.NOT_EMPTY, fieldInfo);
}
/**
* 转换成 if 标签的脚本片段
*
* @param sqlScript sql 脚本片段
* @param property 字段名
* @param fieldStrategy 验证策略
* @return if 脚本片段
*/
private String convertIf(final String sqlScript, final String property, final FieldStrategy fieldStrategy, final TableFieldInfo fieldInfo) {
if (fieldStrategy == FieldStrategy.NEVER) {
return null;
}
if (fieldInfo.isPrimitive() || fieldStrategy == FieldStrategy.IGNORED || fieldStrategy == FieldStrategy.ALWAYS) {
return sqlScript;
}
if (fieldStrategy == FieldStrategy.NOT_EMPTY && fieldInfo.isCharSequence()) {
return SqlScriptUtils.convertIf(sqlScript, String.format("%s != null and %s != ''", property, property),
false);
}
return SqlScriptUtils.convertIf(sqlScript, String.format("%s != null", property), false);
}
private String convertIfProperty(String prefix, String property) {
return StringUtils.isNotBlank(prefix) ? prefix.substring(0, prefix.length() - 1) + "['" + property + "']" : property;
}
}
sql注入器配置
@MapperScan("${mybatis-plus.mapperPackage}")
public class MybatisPlusConfig {
/**
* sql注入器配置
*/
@Bean
public ISqlInjector sqlInjector() {
return new DefaultSqlInjector() {
@Override
public List<AbstractMethod> getMethodList(Configuration configuration, Class<?> mapperClass, TableInfo tableInfo) {
final List<AbstractMethod> methodList = super.getMethodList(configuration, mapperClass, tableInfo);
methodList.add(new UpdateAlwaysById());
methodList.add(new UpdateNotNullById());
methodList.add(new UpdateNotEmptyById());
return methodList;
}
};
}
}
其他自定义配置
- 查询结果为map时，数据库字段，下划线字段 转 小写驼峰形式。
- JSONObject别名。
一些小型/敏捷型项目，没必须搞那么多实体类或VO对象，增加维护成本。查询返回结果可以都用fastjson2的JSONObject来接收，查询完成后也方便在Service里面stream操作。
import com.alibaba.fastjson2.JSONObject;
import com.baomidou.mybatisplus.extension.MybatisMapWrapperFactory;
import com.baomidou.mybatisplus.autoconfigure.ConfigurationCustomizer;
@MapperScan("${mybatis-plus.mapperPackage}")
public class MybatisPlusConfig {
/**
* Mybatis自定义配置
*/
@Bean
public ConfigurationCustomizer configurationCustomizer() {
return configuration -> {
// Mybatis resultType为map时下划线字段 转 小写驼峰形式
configuration.setObjectWrapperFactory(new MybatisMapWrapperFactory());
// 注册JSONObject类型，xml里面返回类型就可以不用写全包名。示例：<select id="queryXXX" resultType="JSONObject"></select>
configuration.getTypeAliasRegistry().registerAlias("JSONObject", JSONObject.class);
};
}
}
mybatis-plus全局配置
# https://baomidou.com/config/
mybatis-plus:
configuration:
# 自动驼峰命名规则（camel case）映射
mapUnderscoreToCamelCase: true
# 解决返回map类型数据空值字段不显示
callSettersOnNulls: true
global-config:
# 是否打印 Logo banner
banner: true
dbConfig:
# 主键类型 AUTO 自增 NONE 空 INPUT 用户输入 ASSIGN_ID 雪花 ASSIGN_UUID 唯一 UUID
idType: AUTO
# 逻辑已删除值
logicDeleteValue: 1
# 逻辑未删除值
logicNotDeleteValue: 0
insertStrategy: NOT_NULL
updateStrategy: NOT_NULL
whereStrategy: NOT_NULL
示例代码
// ---------------------------------- Controller ----------------------------------
/**
* xxxx
*/
@GetMapping("/transferContrast")
public ApiResult transferContrast(@RequestParam(defaultValue = "false") boolean apiParamsInfo, String type, Integer year, Integer month) {
if (apiParamsInfo) {
return ApiInfoUtil.getInstance()
.addRequestInfo("type", "类型", "String", "month.环比 year.同比，默认month")
.addRequestInfo("year", "年份，默认为当前年份", "Number")
.addRequestInfo("month", "月份，默认为当前月份", "Number")
.addResponseInfo("garbageType", "xx类型")
.addResponseInfo("total", "本月实际量，吨")
.addResponseInfo("lastTotal", "上月环比量 / 去年同期量，吨")
.buildApiResult();
}
LocalDate now = LocalDate.now();
if (year == null)
year = now.getYear();
if (month == null)
month = now.getMonthValue();
if (StrUtil.isBlank(type))
type = "month";
return ApiResult.ok(xxxService.transferContrast(type, year, month));
}
// ---------------------------------- service ----------------------------------
@Slf4j
@RequiredArgsConstructor
@Service
public class OverviewServiceImpl implements OverviewService {
private final TwoOverviewMapper overviewMapper;
@Override
public List<JSONObject> transferContrast(String type, Integer year, Integer month) {
final LocalDate startDate = LocalDate.of(year, month, 1);
final LocalDate endDate = YearMonth.from(startDate).atEndOfMonth();
LocalDate startDate2;
LocalDate endDate2;
if (type.equals("year")) {
startDate2 = startDate.minusYears(1);
endDate2 = YearMonth.from(startDate2).atEndOfMonth();
} else {
startDate2 = startDate.minusMonths(1);
endDate2 = YearMonth.from(startDate2).atEndOfMonth();
}
final List<JSONObject> objectList = overviewMapper.transferContrast(startDate, endDate, startDate2, endDate2);
final List<JSONObject> resList = objectList.stream().map(obj -> {
obj.put("totalW", IndexUtil.toTenThousand(obj.getBigDecimal("total")));
return obj;
}).collect(Collectors.toList());
return resList;
}
}
// ---------------------------------- Mapper ----------------------------------
public interface TwoOverviewMapper {
List<JSONObject> transferContrast(@Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate,
@Param("startDate2") LocalDate startDate2, @Param("endDate2") LocalDate endDate2);
}
// ---------------------------------- Mapper xml----------------------------------
<select id="transferContrast" resultType="JSONObject">
select a.garbage_type, a.total, b.total last_total from (
SELECT garbage_type, SUM(actual_value) total
FROM table_name
where del_flag = 0
and date >= #{startDate}
and date <= #{endDate}
and type = 'transfer'
GROUP BY garbage_type) a
LEFT JOIN (
SELECT garbage_type, SUM(actual_value) total
FROM table_name
where del_flag = 0
and date >= #{startDate2}
and date <= #{endDate2}
and type = 'transfer'
GROUP BY garbage_type
) b on b.garbage_type = a.garbage_type
ORDER BY total DESC
</select>
扫一扫
6753
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
