Java 自定义数字格式化注解格式化数据库字段值

项目场景：

将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.*;


@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface NumberFormatObject {
}

import java.lang.annotation.*;


@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
@documented
public @interface NumberFormatField {

    // 类型
    Style style() default Style.DEFAULT;

    // 模板
    String pattern() default "###";

    
    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;


@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);
    }

    
    private boolean needToFormat(Object object) {
        Class objectClass = object.getClass();
        NumberFormatObject formatObject = AnnotationUtils.findAnnotation(objectClass, NumberFormatObject.class);
        return Objects.nonNull(formatObject);
    }


    
    public  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;


@Slf4j
@Component
public class MybatisSqlListener implements ApplicationListener {

    @Autowired
    private NumberFormatInterceptor numberFormatInterceptor;

    @Autowired
    private List 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;


@Data
@TableName("t_dwd_shop_live_info")
@NumberFormatObject
public class TDwdShopLiveInfoEntity implements Serializable {
    private static final long serialVersionUID = 4457077769274418293L;

    @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;

    
    @NumberFormatField(style = CustomNumberFormat.Style.PERCENT, pattern = "2")
    private String buyCvr;

    
    @NumberFormatField(style = CustomNumberFormat.Style.NUMBER, pattern = "#,###.###")
    private String orderGmv;

}

业务层

    
    @Override
    public Map getShopLiveList(ServiceDTO param) {

        Map map = new HashMap<>();

        IPage page = new Page<>(param.getCurPage(), param.getPageSize());

        LambdaQueryWrapper 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 reportPage = tDwdShopLiveInfoMapper.selectPage(page, wrapper);
        // 这里读取出来的数据已经是经过格式化的了
        List 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)

Java 自定义数字格式化注解格式化数据库字段值

Java相关栏目本月热门文章