Thought sorting process
- Taking the vehicle configuration information as an example, the imported template information is as follows. My requirement is fixed to 7 levels, and it doesn’t matter if it is not fixed
Just import the last level, that is,
/Guangqi Honda,/Guangqi Honda/2018,/Guangqi Honda/2018/car series 01, you only need to import the/GAC Honda/2018/car series 01level
- Split the imported hierarchical data according to the columns, put the split data into Set and save them, and combine them into all hierarchical levels
Split
/Guangqi Honda/2018/car series 01into/Guangqi Honda,/Guangqi Honda/2018,/ Guangqi Honda/2018 model/car series 01, stored in Set respectively
- Save data in batches to the database according to the Set collection (the parent node has not been set yet)
- As shown in the picture of serial number 1, the hierarchy is fixed at 7 levels, and the order is strictly from left to right; starting from the brand, search for the data of the next level in order, search for child nodes according to the hierarchy, and update in batches (set parent node data)
Code Implementation
The data layer tool is MyBatis Plus
Related entities
@Data
public class VehicleDictListImportDto {<!-- -->
@ExcelProperty(value = "Brand", order = 0)
private String brand;
@ExcelProperty(value = "year model", order = 1)
private String year;
@ExcelProperty(value = "Car Series", order = 2)
private String series;
@ExcelProperty(value = "model", order = 3)
private String configuration;
@ExcelProperty(value = "Displacement", order = 4)
private String displacement;
@ExcelProperty(value = "appearance color", order = 5)
private String appearanceColor;
@ExcelProperty(value = "Interior color", order = 6)
private String interiorColor;
}
public enum NodeTypeEnum {<!-- -->
BRAND("BRAND", "Brand"),
YEAR("YEAR", "year model"),
SERIES("SERIES", "Car Series"),
CONFIGURATION("CONFIGURATION", "Vehicle Type"),
DISPLACEMENT("DISPLACEMENT", "Displacement"),
APPEARANCE_COLOR("APPEARANCE_COLOR", "appearance color"),
INTERIOR_COLOR("INTERIOR_COLOR", "Interior color");
}
/**
* Tree node data
*/
@Data
@TableName(value = "tree_node")
public class TreeNodeEntity implements Serializable {<!-- -->
private static final long serialVersionUID = 1L;
@TableId(type = IdType. ASSIGN_UUID, value = "id")
private String id;
/**
* business type
*/
@TableField
private String attrCode;
/**
* node name
*/
@TableField
private String name;
/**
* Node code
*/
@TableField
private String code;
/**
* node type
*/
@TableField
private String type;
/**
* Node sorting
*/
@TableField
private Integer orderNum;
/**
* parent node id
*/
@TableField
private String pId;
@TableField("is_deleted")
private Boolean deleted = false;
/**
* node name hierarchy
*/
@TableField
private String level;
}
Controller
@RequestExcel uses the starter of EasyExcel to convert the imported data into entities
@RequestMapping(value = "/importExcel", method = {<!-- -->RequestMethod.POST})
public Result<Integer> importExcel(@RequestExcel(ignoreEmptyRow = true) List<VehicleDictListImportDto> importDtoList) {<!-- -->
return Result.ok(treeNodeService.importExcel(importDtoList));
}
Service
@Slf4j
@Service
public class TreeNodeService extends ServiceImpl<TreeNodeMapper, TreeNodeEntity> {<!-- -->
@Resource
private TreeNodeMapper treeNodeMapper;
@Resource
private ThreadPoolTaskExecutor threadPoolTaskExecutor;
public static final String FORWARD_SLASH = "/";
@Transactional(rollbackFor = Exception. class)
public Integer importExcel(List<VehicleDictListImportDto> importDtoList) {<!-- -->
if (importDtoList. size() == 0) {<!-- -->
return 0;
}
// all levels
Set<String> levelSet = new HashSet<>();
for (VehicleDictListImportDto importDto : importDtoList) {<!-- -->
String brand = FORWARD_SLASH + importDto.getBrand();
String year = FORWARD_SLASH + importDto.getYear();
String series = FORWARD_SLASH + importDto.getSeries();
String configuration = FORWARD_SLASH + importDto.getConfiguration();
String displacement = FORWARD_SLASH + importDto.getDisplacement();
String appearanceColor = FORWARD_SLASH + importDto.getAppearanceColor();
String interiorColor = FORWARD_SLASH + importDto.getInteriorColor();
levelSet. add(brand);
levelSet. add(brand + year);
levelSet. add(brand + year + series);
levelSet.add(brand + year + series + configuration);
levelSet.add(brand + year + series + configuration + displacement);
levelSet.add(brand + year + series + configuration + displacement + appearanceColor);
levelSet.add(brand + year + series + configuration + displacement + appearanceColor + interiorColor);
}
// Get the current login
String userId = UserUtils. getUserId();
// get the executor
ThreadPoolExecutor threadPoolExecutor = threadPoolTaskExecutor.getThreadPoolExecutor();
// total level
List<String> levelList = new ArrayList<>(levelSet);
List<TreeNodeEntity> createList = new ArrayList<>();
for (String level : levelList) {<!-- -->
String name = StringUtils.substringAfterLast(level, FORWARD_SLASH);
TreeNodeEntity treeNodeEntity = new TreeNodeEntity();
treeNodeEntity.setAttrCode("NEW_VEHICLE");
treeNodeEntity.setName(name);
treeNodeEntity. setCode(name);
treeNodeEntity.setType(this.getTypeByLevel(level));
treeNodeEntity.setOrderNum(1000);
treeNodeEntity.setLevel(level);
treeNodeEntity.setCreateUser(userId);
treeNodeEntity.setCreateTime(new Date());
treeNodeEntity.setUpdateUser(userId);
treeNodeEntity.setUpdateTime(new Date());
createList.add(treeNodeEntity);
}
int number = 500;
int createListSize = createList. size();
int createThreadSize = (int) Math.ceil(1.0 * createListSize / number);
// Asynchronously add data in batches
List<CompletableFuture<Void>> createFutureList = new ArrayList<>();
for (int i = 0; i < createThreadSize; i ++ ) {<!-- -->
List<TreeNodeEntity> createSubList = createList.subList(i * number, Math.min(i * number + number, createListSize));
CompletableFuture<Void> future = CompletableFuture. runAsync(() -> {<!-- -->
log.info("Sub-thread - add in batches - start: {}", Thread.currentThread().getName());
super. saveBatch(createSubList);
log.info("Sub-thread-batch new-end: {}", Thread.currentThread().getName());
}, threadPoolExecutor);
createFutureList.add(future);
}
CompletableFuture<Void> createAllOf = CompletableFuture.allOf(createFutureList.toArray(new CompletableFuture[0]));
createAllOf. join();
log.info("Main thread - the new operation has ended");
// collection of node types
Map<String, List<TreeNodeEntity>> typeMap = createList. stream()
.collect(Collectors.groupingBy(TreeNodeEntity::getType));
List<TreeNodeEntity> updateList = new ArrayList<>();
NodeTypeEnum[] nodeTypeEnumArray = NodeTypeEnum. values();
for (int i = 0; i < nodeTypeEnumArray. length - 1; i ++ ) {<!-- -->
NodeTypeEnum nodeTypeEnum = nodeTypeEnumArray[i];
List<TreeNodeEntity> pidEntityList = typeMap.get(nodeTypeEnum.getValue());
for (TreeNodeEntity pidEntity : pidEntityList) {<!-- -->
String id = pidEntity. getId();
String level = pidEntity. getLevel();
// prevent finding only the same data on the left
List<TreeNodeEntity> entityList = typeMap.get(nodeTypeEnumArray[i + 1].getValue()).stream()
.filter(x -> StringUtils.contains(x.getLevel(), level + FORWARD_SLASH))
.collect(Collectors.toList());
for (TreeNodeEntity entity : entityList) {<!-- -->
entity.setPId(id);
updateList. add(entity);
}
}
}
// Asynchronously modify data in batches
int updateListSize = updateList. size();
int updateThreadSize = (int) Math. ceil(1.0 * updateListSize / number);
List<CompletableFuture<Void>> updateFutureList = new ArrayList<>();
for (int i = 0; i < updateThreadSize; i ++ ) {<!-- -->
List<TreeNodeEntity> updateSubList = updateList.subList(i * number, Math.min(i * number + number, updateListSize));
CompletableFuture<Void> future = CompletableFuture. runAsync(() -> {<!-- -->
log.info("Sub thread - batch update - start: {}", Thread.currentThread().getName());
super. updateBatchById(updateSubList);
log.info("Sub thread - batch update - end: {}", Thread.currentThread().getName());
}, threadPoolExecutor);
updateFutureList.add(future);
}
CompletableFuture<Void> updateAllOf = CompletableFuture.allOf(updateFutureList.toArray(new CompletableFuture[0]));
updateAllOf. join();
log.info("Main thread - update operation has ended");
return createListSize;
}
\t
/**
* Get the node type according to the level
*
* @param level level
* @return node type
*/
private String getTypeByLevel(String level) {<!-- -->
String type;
int times = StringUtils.countMatches(level, FORWARD_SLASH);
switch (times) {<!-- -->
case 1:
type = NodeTypeEnum.BRAND.getValue();
break;
case 2:
type = NodeTypeEnum.YEAR.getValue();
break;
case 3:
type = NodeTypeEnum.SERIES.getValue();
break;
case 4:
type = NodeTypeEnum.CONFIGURATION.getValue();
break;
case 5:
type = NodeTypeEnum. DISPLACEMENT. getValue();
break;
case 6:
type = NodeTypeEnum. APPEARANCE_COLOR. getValue();
break;
case 7:
type = NodeTypeEnum.INTERIOR_COLOR.getValue();
break;
default:
throw new BizException("The level passed in is incorrect, the wrong data is: {}", level);
}
return type;
}
}