前言
天天做管理系统,重复的crud搞得人很烦......
某管理系统特点(缺点)
- 繁琐和重复:
CRUD 操作通常需要大量的重复代码,特别是在开发简单的数据管理应用时,可能会显得繁琐和冗长。 - 缺乏创新性:
CRUD 操作是基本的数据处理,缺乏创新性和挑战性。在处理大量相似的操作时,开发者可能感到缺乏动力。 - 枯燥和机械性:
CRUD 操作的实现通常是机械性的,开发者可能会觉得这些任务缺乏乐趣,因为它们是例行公事。 - 可维护性问题:
随着应用的复杂性增加,通过简单的 CRUD 操作来管理数据可能导致代码难以维护。例如,如果数据模型发生变化,可能需要大量修改,一个简单的字段展示调整也要改代码。
因为业务的不确定,可能有的时候,一个页面里面会有直接存储、数据字典、其他表字段等类型的数据,而且数据库存储的
表数据id(外键)、或者数据字典的key/id,扔到后端之后可能还要一个一个去校验........展示的时候通常会查询对应的字段值、
数据字典名字name
前端新增时,通常使用组件选择直接取id/key......直接存储
导入时通常使用excel文件导入大批量数据,对于引用类型数据,通常给字典里面的名字name,或者数据库的某个字段值
都是重复操作。。。真枯燥。。。是时候写几行垃圾代码了,先拿导入试试水。。。
怎么写?
1、定义一个注解类标注该字段的数据类型,引用类型里面配置好引用数据字典内容,或关联表的数据信息
2、读取文件后,通过Spring IOC容器获取配置好的数据访问对象,或数据字典查询对象
3、取值、校验、设值
一、注解类
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.fasterxml.jackson.annotation.JacksonAnnotation;
import com.mbrains.base.AutoFillModel;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface MetadataType {
String value() default "text";
String dictionary() default "";
Class clazz() default AutoFillModel.class;
String field() default "";
Class mapper() default BaseMapper.class;
}
二、ApplicationContextAware
使用ApplicationContextAware接口写一个工具类,用于获取一个数据库操作对象某Mapper实例
package com.mbrains.admin.ioc;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;
@Component
public class MbrainsAware implements ApplicationContextAware {
private ApplicationContext context;
@Override
public void setApplicationContext(ApplicationContext applicationContext) {
this.context = applicationContext;
}
public <T> T getBean(Class<T> clazz){
return this.context.getBean(clazz);
}
}
三、实体类使用 MetadataType
@TableField("product_series_sub_name")
@ApiModelProperty(value="产品子系列")
@MetadataType(value = "dictionary", dictionary = "cpzxl")
protected String productSeriesSubName;
@TableField("apply_type_name")
@ApiModelProperty(value="应用类型")
@MetadataType(value = "refers", field = "className", clazz = AppTypeClass.class, mapper = AppTypeClassDao.class)
protected String applyTypeName;
四、导入工具类
package com.mbrains.base.utils;
import java.io.InputStream;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.fasterxml.jackson.databind.node.ObjectNode;
import com.mbrains.anno.MetadataType;
import com.mbrains.base.MBrainsAutoFillModel;
import com.mbrains.dao.MetadataConfigDAO;
import com.mbrains.exception.BusinessException;
import com.mbrains.model.MetadataConfig;
import com.mbrains.model.MetadataTable;
import com.mbrains.model.TableModel;
import com.mbrains.service.DictionaryService;
import org.apache.commons.lang.BooleanUtils;
import org.apache.commons.lang.CharUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.ObjectUtils;
import org.springframework.web.multipart.MultipartFile;
/**
* Excel工具方法
*
* no code import tools
*
* MBrains 数据模板构建需求-导入模板工具
*
*/
public class AutoExcelUtils {
private final static Logger log = LoggerFactory.getLogger(ExcelUtils.class);
public final static String EXCEL2003 = "xls";
public final static String EXCEL2007 = "xlsx";
/**
*
* @param cls 导入映射实体类
* @param file 数据源文件
* @param headers 数据模板配置
* @param sheetIndex 读取的sheet
* @param startRow 开始读取的数据行,大于等于1,第0行是表头
* @param <T>
* @return
*/
public static <T> List<T> importData(DictionaryService dictionaryService, MBrainsAware aware, Class<T> cls, MultipartFile file, List<MetadataConfig> headers, int sheetIndex, int startRow) throws Exception {
MBrainsAssert.state(headers.size() > 0, "未配置可导入列!");
String fileName = file.getOriginalFilename();
List<T> dataList = new ArrayList<>();
Workbook workbook = null;
InputStream is = file.getInputStream();
if (fileName.endsWith(EXCEL2007)) {
workbook = new XSSFWorkbook(is);
}
if (fileName.endsWith(EXCEL2003)) {
workbook = new HSSFWorkbook(is);
}
if (workbook != null) {
Map<String, Field> classMap = new HashMap<>();
Map<Integer, MetadataConfig> filedMap = new HashMap<>();
List<Field> fields = Stream.of(cls.getDeclaredFields()).collect(Collectors.toList());
fields.forEach(field -> {
TableField tableFieldName = field.getAnnotation(TableField.class);
if (tableFieldName != null) {
String value = tableFieldName.value();
if (StringUtils.isNotBlank(value)) {
field.setAccessible(true);
classMap.put(field.getName(), field);
}
}
});
/**
* 取导入-排序表头
* 取第一行表头,保存顺序,
* 匹配导入配置,读取后面行的数据,设置导入字段的值
* 返回需要的数据
* 配置-导入文件-实体-数据库表 :
* 关系 --> config.columnComment = file.head && config.columnName = clazz.TableFiled.value(实体类注解——数据库字段名)
*/
Sheet sheet = workbook.getSheetAt(sheetIndex);
Row header = sheet.getRow(0);
for (MetadataConfig config : headers) {
boolean matchConfig = false;
for (int j = header.getFirstCellNum(); j <= header.getLastCellNum(); j++) {
if(!ObjectUtils.isEmpty(header.getCell(j))) {
Cell cell = header.getCell(j);
String head = cell.getStringCellValue();
if (config.getColumnComment().equals(head)) {
matchConfig = true;
filedMap.put(j, config);
}
}
}
if(!matchConfig){
throw new BusinessException("导入模板错误,请重新下载导入模板!");
}
}
//表头校验
if (headers.size() != filedMap.size()) {
throw new BusinessException("导入模板错误,请重新下载导入模板!");
}
if (sheet.getLastRowNum() - sheet.getFirstRowNum() <= 0){
throw new BusinessException("空文件!导入模板中没有要导入的数据!");
}
StringBuilder errorReading = new StringBuilder();
for (int i = startRow; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
// 忽略空白行
if (row == null) {
continue;
}
// 判断是否为空白行
T t = cls.newInstance();
boolean allBlank = true;
for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
if (j < 0){
break;
}
if(!ObjectUtils.isEmpty(row.getCell(j))) {
Cell cell = row.getCell(j);
String cellValue = getCellValue(cell);
if (StringUtils.isNotBlank(cellValue)) {
allBlank = false;
}
if(!ObjectUtils.isEmpty(filedMap.get(j))) {
String targetCell = filedMap.get(j).getBeanFiled();
if (!ObjectUtils.isEmpty(targetCell) && classMap.containsKey(targetCell)) {
Field field = classMap.get(targetCell);
field.setAccessible(true);
MetadataType type = field.getAnnotation(MetadataType.class);
if(type != null && StringUtils.isNotEmpty(type.value().trim())){
validateAndSetFieldValue(headers, dictionaryService, aware, t, cellValue.trim(), field, type, errorReading, i);
}else {
handleField(t, cellValue, field);
}
}
}
}
}
if (!allBlank) {
dataList.add(t);
}
}
if (errorReading.toString().length() > 0){
throw new BusinessException(errorReading.toString());
}
}
return dataList;
}
private static <T> void validateAndSetFieldValue(List<MetadataConfig> headers, DictionaryService dictionaryService, MBrainsAware aware,
T t, String cellValue, Field field, MetadataType type, StringBuilder errorReading, int rowNum) throws Exception {
switch (type.value()){
case "refers":
refersFieldValidate(aware, t, cellValue, field, type, errorReading);
break;
case "dictionary":
dictionaryFieldValidate(headers, dictionaryService, t, cellValue, field, type, errorReading, rowNum);
break;
case "boolean":
booleanFieldValidate(headers, t, cellValue, field, type, errorReading, rowNum);
break;
}
}
private static <T> void refersFieldValidate(MBrainsAware aware, T t, String cellValue, Field field, MetadataType type, StringBuilder errorReading) throws Exception {
Class<?> clazz = type.clazz();
Class<?> mapper = type.mapper();
MetadataConfigDAO configDAO = aware.inject(MetadataConfigDAO.class);
BaseMapper businessMapper = (BaseMapper) aware.inject(mapper);
TableName table = clazz.getAnnotation(TableName.class);
MBrainsAssert.state(errorReading, clazz == MBrainsAutoFillModel.class, "引用属性:[" + field.getName() + "] 未正确配置关联实体类!");
MBrainsAssert.state(errorReading, table != null && !StringUtils.isEmpty(table.value().trim()), "引用属性:[" + field.getName() + "] 未正确配置关联实体类!");
String tableName = table.value();
TableModel model = new TableModel();
model.setTableName(tableName);
List<MetadataTable> tableColumns = configDAO.queryTableInfos(model);
MBrainsAssert.notNull(errorReading, tableColumns, "引用属性:[" + field.getName() + "] 关联的实体类未正确配置数据库表信息,无法正确实现目标值注入!");
MBrainsAssert.notEmpty(errorReading, type.field().trim(), "引用属性:[" + field.getName() + "] 未正确配置关联实体类的属性名,无法正确实现目标值注入!");
Map<String, MetadataTable> columns = tableColumns.stream().collect(Collectors.toMap(column -> MBrainsCamelUtils.toCamelCase(column.getColumnName()), column -> {
column.setBeanFiled(MBrainsCamelUtils.toCamelCase(column.getColumnName()));
return column;
}, (v1, v2) -> v1));
MBrainsAssert.state(errorReading, columns.containsKey(type.field().trim()), "引用属性:[" + field.getName() + "] 未正确配置关联实体类的属性名,无法正确实现目标值注入!");
MBrainsAssert.state(errorReading, mapper != BaseMapper.class, "引用属性:[" + field.getName() + "] 未正确配置关联的Mapper类,无法正确实现目标值注入!");
List<MBrainsAutoFillModel> list = businessMapper.selectList(new QueryWrapper<MBrainsAutoFillModel>().eq(columns.get(type.field().trim()).getColumnName(), cellValue));
MBrainsAssert.state(errorReading, list.size() == 1, "引用属性:[" + field.getName() + (list.size() > 1 ? "匹配到多个相同值的数据,无法确定其唯一性!": "没有匹配的数据可以使用!"));
String id = list.get(0).getId();
handleField(t, id, field);
}
private static <T> void booleanFieldValidate(List<MetadataConfig> headers, T t, String cellValue, Field field, MetadataType type, StringBuilder errorReading, int rowNum) throws Exception {
MBrainsAssert.state(errorReading,cellValue.equals("是") || cellValue.equals("否"),
"文件读取校验:第" + rowNum + "行[" + getHeaderName(field.getName(), headers) + "] “布尔”类型数据填写数据有误,仅支持“是”/“否”;\n");
String booleanValue = cellValue.equals("是") ? "1" : "0";
handleField(t, booleanValue, field);
}
private static String getHeaderName(String name, List<MetadataConfig> headers) {
MetadataConfig config = headers.stream().filter(head -> head.getBeanFiled().equals(name)).findFirst().get();
return config.getColumnComment();
}
private static <T> void dictionaryFieldValidate(List<MetadataConfig> headers, DictionaryService dictionaryService, T t,
String cellValue, Field field, MetadataType type, StringBuilder errorReading, int rowNum) throws Exception {
MBrainsAssert.notEmpty(errorReading, type.dictionary(), "文件读取校验:第" + rowNum + "行[" + getHeaderName(field.getName(), headers) + "]数据字典配置错误;\n");
List<ObjectNode> dictionaries = dictionaryService.getDictionaries(type.dictionary());
String dictCode = dictionaryService.getDictCode(dictionaries, cellValue);
MBrainsAssert.notEmpty(errorReading, dictCode, "文件读取校验:第" + rowNum + "行[" + getHeaderName(field.getName(), headers) + "] 未匹配到可使用的数据字典值;\n");
handleField(t, dictCode, field);
}
private static String getCellValue(Cell cell) {
if (cell == null) {
return "";
}
if (cell.getCellType() == CellType.NUMERIC) {
if (DateUtil.isCellDateFormatted(cell)) {
return HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();
} else {
return new BigDecimal(cell.getNumericCellValue()).toString();
}
} else if (cell.getCellType() == CellType.STRING) {
return StringUtils.trimToEmpty(cell.getStringCellValue());
} else if (cell.getCellType() == CellType.FORMULA) {
return StringUtils.trimToEmpty(cell.getCellFormula());
} else if (cell.getCellType() == CellType.BLANK) {
return "";
} else if (cell.getCellType() == CellType.BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == CellType.ERROR) {
return "ERROR";
} else {
return cell.toString().trim();
}
}
private static <T> void handleField(T t, String value, Field field) throws Exception {
Class<?> type = field.getType();
if (type == null || type == void.class || StringUtils.isBlank(value)) {
return;
}
if (type == Object.class) {
field.set(t, value);
//数字类型
} else if (type.getSuperclass() == null || type.getSuperclass() == Number.class) {
if (type == int.class || type == Integer.class) {
field.set(t, NumberUtils.toInt(value));
} else if (type == long.class || type == Long.class) {
field.set(t, NumberUtils.toLong(value));
} else if (type == byte.class || type == Byte.class) {
field.set(t, NumberUtils.toByte(value));
} else if (type == short.class || type == Short.class) {
field.set(t, NumberUtils.toShort(value));
} else if (type == double.class || type == Double.class) {
field.set(t, NumberUtils.toDouble(value));
} else if (type == float.class || type == Float.class) {
field.set(t, NumberUtils.toFloat(value));
} else if (type == char.class || type == Character.class) {
field.set(t, CharUtils.toChar(value));
} else if (type == boolean.class) {
field.set(t, BooleanUtils.toBoolean(value));
} else if (type == BigDecimal.class) {
field.set(t, new BigDecimal(value));
}
} else if (type == Boolean.class) {
field.set(t, BooleanUtils.toBoolean(value));
} else if (type == Date.class) {
//
field.set(t, value);
} else if (type == String.class) {
field.set(t, value);
} else {
Constructor<?> constructor = type.getConstructor(String.class);
field.set(t, constructor.newInstance(value));
}
}
}
效果
导入的数据,两个引用类型字段
成功的表字段,拿到id
成功拿到的字典key
后面就可以直接批量插入,后续导入可以直接使用,不用写那些枯燥代码了
List<ProductModelMCU> sourceMCUS = sourceMCUS = AutoExcelUtils.importData(dictionaryService, aware, BeanMxY.class, file, headers, 0, 1);
int i = mxyDAO.insertBatchSomeColumn(sourceMCUS);
总结
MetadataConfig等数据是前期维护的元数据配置,配置某个模块的数据在前端展示的名字、一些样式、以及必填校验、是否可查看、是否可导入、可导出、排序等内容,可依据这些配置生成一些导入模板、实现一些样式的调整…
同时后期可以迭代一些新东西…