项目场景：
将mysql表中多个数字类型(decimal/int/bignt/…)的字段值格式化成携带千字符以及百分比的字段值，并保留x位小数
问题描述：
由于需要读取原始表中多个浮点型字段值到服务层，并将其格式化为千位符或者百分比后保留x为小数，其实可以通过读取数据库表数据再遍历的方法去进行逐个格式化，但多个报表的情况下，这样就显得代码很冗余，并且效率不高，此时就可以通过自定义注解的方法进行优化。
数据库层
CREATE TABLE `t_shop_live_info` (
`id` bigint(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`shop_name` varchar(100) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '店铺',
`bs_date` date NOT NULL DEFAULT '1900-01-01' COMMENT '统计日期',
`full_watch_uv` bigint(12) NOT NULL DEFAULT '0' COMMENT '观看人数',
`watch_avg_dur` decimal(14,2) NOT NULL DEFAULT '0.00' COMMENT '平均观看时长',
`item_click_count` bigint(12) NOT NULL DEFAULT '0' COMMENT '商品点击次数',
`item_click_uv` bigint(12) NOT NULL DEFAULT '0' COMMENT '商品点击人数',
`shop_car_click_uv` bigint(12) NOT NULL DEFAULT '0' COMMENT '购物车点击数',
`like_count` bigint(12) NOT NULL DEFAULT '0' COMMENT '点赞数',
`pay_buyer_count` bigint(12) NOT NULL DEFAULT '0' COMMENT '累计成交人数',
`buy_cvr` decimal(14,4) NOT NULL DEFAULT '0.0000' COMMENT '成交转化率',
`order_gmv` decimal(14,3) NOT NULL DEFAULT '0.000' COMMENT '累计成交金额',
PRIMARY KEY (`id`),
UNIQUE KEY `shops_date` (`bs_date`,`shop_name`) USING BTREE COMMENT '日期+店铺名 唯一'
) ENGINE=InnoDB AUTO_INCREMENT=182 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='店铺直播明细报表';
代码分布：
注解类
import java.lang.annotation.*;
/**
* 注解数字格式化类（只注解在实体类类名上）
* @Author: linyan
**/
@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface NumberFormatObject {
}import java.lang.annotation.*;
/**
* 自定义数字格式化注解（注解在实体类的字段上）
* @Author: linyan
**/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
@Documented
public @interface NumberFormatField {
// 类型
Style style() default Style.DEFAULT;
// 模板
String pattern() default "###";
/**
* Common number format styles.
**/
enum Style {
/**
* 默认原始值
**/
DEFAULT,
/**
* 数字格式
**/
NUMBER,
/**
* 百分比
**/
PERCENT,
/**
* 金额格式
**/
CURRENCY,
/**
* 百分符
**/
PERCENT_SIGN
}
}拦截器
这里科普一下。
@Intercepts 注解中的handleResultSets是对数据库返回结果进行拦截;
下面展示一些其他注解对应的拦截切入点。
//语句执行拦截
Executor (update, query, flushStatements, commit, rollback, getTransaction, close, isClosed)
// 参数获取、设置时进行拦截
ParameterHandler (getParameterObject, setParameters)
// 对返回结果进行拦截
ResultSetHandler (handleResultSets, handleOutputParameters)
//sql语句拦截
StatementHandler (prepare, parameterize, batch, update, query)import cn.hutool.core.util.NumberUtil;
import com.momo.entity.annotation.NumberFormatField;
import com.momo.entity.annotation.NumberFormatObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.ibatis.executor.resultset.ResultSetHandler;
import org.apache.ibatis.plugin.*;
import org.springframework.core.annotation.AnnotationUtils;
import org.springframework.stereotype.Component;
import java.lang.reflect.Field;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Objects;
import java.util.Properties;
/**
* @ClassName NumberFormatInterceptor
* @Description 数字格式化拦截器
* @Author linyan
**/
@Slf4j
@Component
@Intercepts({
@Signature(type = ResultSetHandler.class, method = "handleResultSets", args = Statement.class)
})
public class NumberFormatInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
// 取出查询结果
Object resultObject = invocation.proceed();
if (Objects.isNull(resultObject)) {
return null;
}
//基于selectList
if (resultObject instanceof ArrayList) {
ArrayList resultList = (ArrayList) resultObject;
if (!CollectionUtils.isEmpty(resultList) && needToFormat(resultList.get(0))) {
for (Object result : resultList) {
// 数字格式化
format(result);
}
}
//基于selectOne
} else {
if (needToFormat(resultObject)) {
// 数字格式化
format(resultObject);
}
}
return resultObject;
}
/**
* 切记配置，否则当前拦截器不会加入拦截器链
*/
@Override
public Object plugin(Object o) {
return Plugin.wrap(o, this);
}
/**
* 自定义配置写入，没有自定义配置的可以直接置空此方法
**/
@Override
public void setProperties(Properties properties) {
log.info("properties: {}", properties);
}
/**
* 校验该实例的类是否被@NumberFormatObject所注解
**/
private boolean needToFormat(Object object) {
Class<?> objectClass = object.getClass();
NumberFormatObject formatObject = AnnotationUtils.findAnnotation(objectClass, NumberFormatObject.class);
return Objects.nonNull(formatObject);
}
/**
* 数字内容格式化
**/
public <T> T format(T result) throws IllegalAccessException {
// 取出resultType的类
Class<?> resultClass = result.getClass();
Field[] declaredFields = resultClass.getDeclaredFields();
for (Field field : declaredFields) {
// 取出所有被CustomNumberFormat注解的字段
NumberFormatField numberFormat = field.getAnnotation(NumberFormatField.class);
if (!Objects.isNull(numberFormat)) {
field.setAccessible(true);
Object object = field.get(result);
// 暂时只支持String类型的格式化
if (object instanceof String) {
String numberValue = (String) object;
NumberFormatField.Style style = numberFormat.style();
switch (style) {
case NUMBER:
field.set(result, NumberUtil.decimalFormat(numberFormat.pattern(), Double.parseDouble(numberValue)));
break;
case PERCENT:
field.set(result, NumberUtil.formatPercent(Double.parseDouble(numberValue), Integer.parseInt(numberFormat.pattern())));
break;
case CURRENCY:
field.set(result, NumberUtil.decimalFormatMoney(Double.parseDouble(numberValue)));
break;
case PERCENT_SIGN:
field.set(result, numberValue + "%");
break;
default:
}
}
}
}
return result;
}
}监听器
import com.momo.mybatis.interceptor.NumberFormatInterceptor;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationListener;
import org.springframework.context.event.ContextRefreshedEvent;
import org.springframework.stereotype.Component;
import java.util.List;
/**
* @ClassName MybatisSqlListener
* @Description Mybatis SQL监听器
* @Author linyan
**/
@Slf4j
@Component
public class MybatisSqlListener implements ApplicationListener<ContextRefreshedEvent> {
@Autowired
private NumberFormatInterceptor numberFormatInterceptor;
@Autowired
private List<SqlSessionFactory> sqlSessionFactoryList;
@Override
public void onApplicationEvent(ContextRefreshedEvent event) {
this.addMyInterceptor();
}
private void addMyInterceptor() {
log.debug("添加自定义Mybatis SQL拦截器.");
for (SqlSessionFactory sqlSessionFactory : sqlSessionFactoryList) {
sqlSessionFactory.getConfiguration().addInterceptor(numberFormatInterceptor);
}
}
}实体类
注意： 所有需要格式化的字段都需要设置为String类型，因为格式化后的字段值都为字符串。
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.momo.entity.annotation.NumberFormatField;
import com.momo.entity.annotation.NumberFormatObject;
import lombok.Data;
import java.io.Serializable;
/**
* @ClassName TDwdShopLiveInfoEntity
* @Description 快手小店 -- 直播间明细报表
* @Author linyan
**/
@Data
@TableName("t_dwd_shop_live_info")
@NumberFormatObject
public class TDwdShopLiveInfoEntity implements Serializable {
private static final long serialVersionUID = 4457077769274462293L;
@TableId
private Integer id;
private String shopName;
@JsonFormat(pattern = "yyyy-MM-dd", locale = "GMT+8")
private String bsDate;
/**
* 千位符
**/
@NumberFormatField(style = CustomNumberFormat.Style.NUMBER, pattern = "#,###")
private String fullWatchUv;
/**
* 千位符，保留两位小数
**/
@NumberFormatField(style = CustomNumberFormat.Style.NUMBER, pattern = "#,###.##")
private String watchAvgDur;
@NumberFormatField(style = CustomNumberFormat.Style.NUMBER, pattern = "#,###")
private String itemClickCount;
@NumberFormatField(style = CustomNumberFormat.Style.NUMBER, pattern = "#,###")
private String itemClickUv;
@NumberFormatField(style = CustomNumberFormat.Style.NUMBER, pattern = "#,###")
private String shopCarClickUv;
@NumberFormatField(style = CustomNumberFormat.Style.NUMBER, pattern = "#,###")
private String likeCount;
@NumberFormatField(style = CustomNumberFormat.Style.NUMBER, pattern = "#,###")
private String payBuyerCount;
/**
* 百分比，保留2位小数
**/
@NumberFormatField(style = CustomNumberFormat.Style.PERCENT, pattern = "2")
private String buyCvr;
/**
* 千位符，保留三位小数
**/
@NumberFormatField(style = CustomNumberFormat.Style.NUMBER, pattern = "#,###.###")
private String orderGmv;
}业务层
/**
* @MethodName: getShopLiveList
* @Description: 分页查询
* @Param: [param]
* @Return: java.util.Map<java.lang.String,java.lang.Object>
* @Author: linyan
**/
@Override
public Map<String, Object> getShopLiveList(ServiceDTO param) {
Map<String, Object> map = new HashMap<>();
IPage<TDwdShopLiveInfoEntity> page = new Page<>(param.getCurPage(), param.getPageSize());
LambdaQueryWrapper<TDwdShopLiveInfoEntity> wrapper = new LambdaQueryWrapper<>();
int len = 2;
if (null != param.getDateTime() && param.getDateTime().length == len) {
String startTime = null;
String endTime = null;
startTime = param.getDateTime()[0];
endTime = param.getDateTime()[1];
wrapper.between(TDwdShopLiveInfoEntity::getBsDate, startTime, endTime);
}
if (null != param.getShops() && param.getShops().length > 0) {
wrapper.in(TDwdShopLiveInfoEntity::getShopName, Arrays.asList(param.getShops()));
}
wrapper.orderByDesc(TDwdShopLiveInfoEntity::getBsDate);
IPage<TDwdShopLiveInfoEntity> reportPage = tDwdShopLiveInfoMapper.selectPage(page, wrapper);
// 这里读取出来的数据已经是经过格式化的了
List<TDwdShopLiveInfoEntity> records = reportPage.getRecords();
records.forEach(entity -> {
// 打印
log.info("entity: {}", entity);
});
map.put("data", records);
map.put("total", reportPage.getTotal());
map.put("pageSize", reportPage.getSize());
map.put("curPage", reportPage.getCurrent());
return map;
}控制台输出结果：
entity: TDwdShopLiveInfoEntity(id=181, shopName=不点官方旗舰店, bsDate=2021-10-14, fullWatchUv=110,634, watchAvgDur=68, itemClickCount=1,815, itemClickUv=1,217, shopCarClickUv=2,259, likeCount=3,159, payBuyerCount=12, buyCvr=10.23%, orderGmv=951.6)
entity: TDwdShopLiveInfoEntity(id=179, shopName=三点几, bsDate=2021-10-14, fullWatchUv=104,044, watchAvgDur=59, itemClickCount=16,533, itemClickUv=8,008, shopCarClickUv=12,963, likeCount=4,680, payBuyerCount=908, buyCvr=18.92%, orderGmv=41,960.9)
entity: TDwdShopLiveInfoEntity(id=178, shopName=三点几, bsDate=2021-10-13, fullWatchUv=45,286, watchAvgDur=51, itemClickCount=907, itemClickUv=440, shopCarClickUv=878, likeCount=2,894, payBuyerCount=14, buyCvr=2%, orderGmv=686.4)
entity: TDwdShopLiveInfoEntity(id=176, shopName=不点官方旗舰店, bsDate=2021-10-13, fullWatchUv=25,247, watchAvgDur=51, itemClickCount=3,501, itemClickUv=1,141, shopCarClickUv=1,807, likeCount=903, payBuyerCount=71, buyCvr=3%, orderGmv=4,073.7)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
