文章目录
前言
这是一种读取excel文件数据以集合的事项来实现数据三级联动的一种实例，使用到的技术稳定，算法简单，但是可靠。期间穿插了一种以parentId来实现三级数据联动的方法，在这里也做了说明。做了这些，前端需要什么样的数据，就返回什么数据，分分钟的事情
提示：以下是本篇文章正文内容，下面案例可供参考
一、级联的示例数据
示例数据我们选取职业数据，比如：金融-投融资-投资顾问，这种三级的数据结构，当然，最简单的三级数据示例大家也可以选择省市区之类的，更直观。这里仅作示例说明，不做其他只用，各路大神定有更好的办法。
引入的依赖包：
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import javax.annotation.Resource;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
大概就是这些依赖包，其他的也没啥了，根据环境的不同，大家调整
二、步骤
1.读取excel文件
excel文件里面有表，行，列数据，要分别读取。
frontDo方法先读取文件：
private Workbook frontDo(String excelPath) throws IOException, InvalidFormatException {
File excel = new File(excelPath);//excelPath是文件所在的路径
Workbook wb = null;
if (excel.isFile() && excel.exists()) {
String[] split = excel.getName().split("\\."); //.是特殊字符，需要转义！！！！！
//根据文件后缀（xls/xlsx）进行判断，但是自己的office一定是要激活的，打开的时候不能自动弹出窗口
if ("xls".equals(split[1])) {
FileInputStream fis = new FileInputStream(excel); //文件流对象
wb = new HSSFWorkbook(fis);
} else if ("xlsx".equals(split[1])) {
wb = new XSSFWorkbook(excel);
} else {
System.out.println("文件类型错误!");
}
}
return wb;
}
2.读取文件内数据表
readSheet方法代码如下：
private List<Sheet> readSheet(String url) throws IOException, InvalidFormatException {
Workbook wb = frontDo(url);
int sheetSize = wb.getNumberOfSheets();
List<Sheet> sheets = new ArrayList<Sheet>();
for (int i = 0; i < sheetSize; i++) {
Sheet sheet = wb.getSheetAt(i);
sheets.add(sheet);
}
return sheets;
}
3.读取数据表内的行并把行数据序列化到一个关于行的entity：RowEntity
RowEntity的代码如下：
package data.dictionary.plateform.core.occupation;
import lombok.Data;
@Data
public class RowEntity {
private String first; //每行的第一列数据
private String second;//每行的第二列数据
private String third;//每行的第三列数据
}
buildEntity读取每行方法：
private List<RowEntity> buildEntity(String path) throws IOException, InvalidFormatException {
//"C:\\Users\\yongc\\Desktop\\data\\19.xlsx";
List<RowEntity> rowEpntitys = new ArrayList<>();
List<Sheet> sheets = readSheet(path);
int firstRowIndex = sheets.get(0).getFirstRowNum();
int lastRowIndex = sheets.get(0).getLastRowNum();
System.out.println(firstRowIndex);
System.out.println(lastRowIndex);
System.out.println();
for (int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) { //遍历行
Row row = sheets.get(0).getRow(rIndex);
RowEntity rowEntity = new RowEntity();
rowEntity.setFirst(row.getCell(0).toString());
rowEntity.setSecond(row.getCell(1).toString());
rowEntity.setThird(row.getCell(2).toString());
rowEpntitys.add(rowEntity);
}
return rowEpntitys;
}
到此，excel表的所有数据都序列化到一个list中了。
4.一级数据序列化：DataEntity
DataEntity类：
这个类也是建表的结构，数据库中这些数据是要入库的。
package data.dictionary.plateform.core.occupation;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
@Data
@TableName(value = "t_data_occupation",resultMap = "BaseResultMap")
public class DataEntity {
@TableId(value = "id",type = IdType.AUTO)
private int id;
private String dataName;
private int dataLeft;
private int dataRight;
private int dataDepth;
private int parentId;
}
一级数据序列化：
private List<DataEntity> buildFirst(List<RowEntity> rowEpntitys) {
List<DataEntity> dataEntities = new ArrayList<>();
//赋值
for (int i = 0; i < rowEpntitys.size(); i++) {
DataEntity dataEntity = new DataEntity();
dataEntity.setDataName(rowEpntitys.get(i).getFirst());
dataEntity.setDataDepth(1);
//dataEntity.setId(s);
dataEntities.add(dataEntity);
}
//去重
HashSet b = new HashSet(dataEntities);
dataEntities.clear();
dataEntities.addAll(b);
return dataEntities;
}
5.一级数据保存
这一步的作用也是生成默认的id，保存上一步稳定生成的一级数据结果。
private List<DataEntity> firstDataPersistence(List<DataEntity> dataEntities){
occupationDataDAO.insertForEach(dataEntities);
QueryWrapper<DataEntity> queryWrapper=new QueryWrapper<>();
queryWrapper.lambda();
List<DataEntity> dataEntityList=occupationDataDAO.selectList(queryWrapper);
return dataEntityList;
}
6.二级数据序列化并持久保存
这一步的作用也是生成默认的id，保存上一步稳定生成的一级数据结果。
private List<DataEntity> secondDataPersistence(List<RowEntity> rowEpntitys){
//取出上一步持久到数据库的一级数据，因为只有一级数据在库里，这个时候可以是全查，不过最好加上depth=1的关键字
QueryWrapper<DataEntity> queryWrapper=new QueryWrapper<>();
queryWrapper.lambda();
List<DataEntity> dataEntityList=occupationDataDAO.selectList(queryWrapper);
//赋值
for (int i = 0; i < dataEntityList.size(); i++) {
List<DataEntity> dataEntityListSecond=new ArrayList<>();
for (int j = 0; j < rowEpntitys.size(); j++) {
if (dataEntityList.get(i).getDataName().equals(rowEpntitys.get(j).getFirst())) {
DataEntity dataEntity=new DataEntity();
dataEntity.setDataDepth(2);
dataEntity.setDataName(rowEpntitys.get(j).getSecond());
dataEntity.setParentId(dataEntityList.get(i).getId());
dataEntityListSecond.add(dataEntity);
}
}
//去重
HashSet c = new HashSet(dataEntityListSecond);
dataEntityListSecond.clear();
dataEntityListSecond.addAll(c);
//入库
occupationDataDAO.insertForEach(dataEntityListSecond);
}
return null;
}
7.三级数据序列化并持久保存
这一步的作用也是生成默认的id，保存上一步稳定生成的一级数据结果。
private List<DataEntity> thirdDataPersistence(List<RowEntity> rowEpntitys){
//查出保存到数据库的所有一二级数据结果，并包含一二级数据的关系
List<DataEntityVO> dataEntityVOS=readService.selectDataEntityVO(null);
//赋值
Integer num=0;//计数，随时和对数据条数是否正确
for (int i = 0; i <dataEntityVOS.size() ; i++) {
List<DataEntity> dataEntities=new ArrayList<>();
for (int j = 0; j < rowEpntitys.size(); j++) {
if (dataEntityVOS.get(i).getParentName().equals(rowEpntitys.get(j).getFirst())&&dataEntityVOS.get(i).getDataName().equals(rowEpntitys.get(j).getSecond())) {
DataEntity dataEntity=new DataEntity();
dataEntity.setParentId(dataEntityVOS.get(i).getId());
dataEntity.setDataName(rowEpntitys.get(j).getThird());
dataEntity.setDataDepth(3);
dataEntities.add(dataEntity);
}
}
//去重
HashSet c = new HashSet(dataEntities);
dataEntities.clear();
dataEntities.addAll(c);
num=num+dataEntities.size();
//入库
occupationDataDAO.insertForEach(dataEntities);
}
System.out.println(num);//打印出数据条数
return null;
}
到此已经完成了所有的数据入库，这个时候已经配置了二级三级数据的父级id，这个时候已经可以完成三级联动的操作了
关于集合的思想到此还未有体现，下面开始配置数据的左右边界，这样就可以按照集合的思想来处理三级联动选择数据了。
这里调用了一个接口 readService.selectDataEntityVO(null)
接口：List selectDataEntityVO(DataEntity dataEntity);
接口实现：
@Override
public List<DataEntityVO> selectDataEntityVO(DataEntity dataEntity) {
QueryWrapper<DataEntity> queryWrapper=new QueryWrapper<>();
queryWrapper.lambda().eq(DataEntity::getDataDepth,2);
List<DataEntity> dataEntityList=occupationDataDAO.selectList(queryWrapper);
List<DataEntityVO> dataEntityVOS=new ArrayList<>();
for (int i = 0; i < dataEntityList.size(); i++) {
DataEntityVO dataEntityVO=new DataEntityVO();
BeanUtils.copyProperties(dataEntityList.get(i),dataEntityVO);
QueryWrapper<DataEntity> queryWrapper1=new QueryWrapper<>();
queryWrapper1.lambda().eq(DataEntity::getId,dataEntityVO.getParentId());
DataEntity dataEntitiy=occupationDataDAO.selectOne(queryWrapper1);
dataEntityVO.setParentName(dataEntitiy.getDataName());
dataEntityVOS.add(dataEntityVO);
}
return dataEntityVOS;
}
8.输出数据的树状结构（全部数据）
这个单独写一个接口来操作：返回的数组里，对象是：DataEntityVO
DataEntityVO类：
package data.dictionary.plateform.core.vo;
import lombok.Data;
import java.util.List;
@Data
public class DataEntityVO {
private int id;
private String dataName;
private int dataLeft;
private int dataRight;
private int dataDepth;
private int parentId;
private String parentName;
private List<DataEntityVO> dataEntityVOList;
}
接口：List<DataEntityVO> selectDataEntityVOTrees();
方法比较笨，但是逻辑便于理解。
接口实现：
@Override
public List<DataEntityVO> selectDataEntityVOTrees() {
List<DataEntityVO> dataEntityVOsF=new ArrayList<>();
QueryWrapper<DataEntity> queryWrapper1=new QueryWrapper<>();
queryWrapper1.lambda().eq(DataEntity::getDataDepth,1);
List<DataEntity> dataEntitiesF=occupationDataDAO.selectList(queryWrapper1);
for (int i = 0; i < dataEntitiesF.size(); i++) {
DataEntityVO dataEntityVOF=new DataEntityVO();
dataEntityVOF.setId(dataEntitiesF.get(i).getId());
dataEntityVOF.setDataName(dataEntitiesF.get(i).getDataName());
dataEntityVOF.setDataDepth(1);
QueryWrapper<DataEntity> queryWrapper2=new QueryWrapper<>();
queryWrapper2.lambda().eq(DataEntity::getParentId,dataEntitiesF.get(i).getId());
List<DataEntityVO> dataEntityVOsS=new ArrayList<>();
List<DataEntity> dataEntitiesS=occupationDataDAO.selectList(queryWrapper2);
for (int j = 0; j < dataEntitiesS.size(); j++) {
DataEntityVO dataEntityVOS=new DataEntityVO();
dataEntityVOS.setId(dataEntitiesS.get(j).getId());
dataEntityVOS.setDataDepth(2);
dataEntityVOS.setDataName(dataEntitiesS.get(j).getDataName());
dataEntityVOS.setParentId(dataEntitiesS.get(j).getParentId());
QueryWrapper<DataEntity> queryWrapper3=new QueryWrapper<>();
queryWrapper3.lambda().eq(DataEntity::getParentId,dataEntitiesS.get(j).getId());
List<DataEntityVO> dataEntityVOsT=new ArrayList<>();
List<DataEntity> dataEntitiesT=occupationDataDAO.selectList(queryWrapper3);
for (int k = 0; k < dataEntitiesT.size(); k++) {
DataEntityVO dataEntityVOT=new DataEntityVO();
dataEntityVOT.setId(dataEntitiesT.get(k).getId());
dataEntityVOT.setParentId(dataEntitiesT.get(k).getParentId());
dataEntityVOT.setDataName(dataEntitiesT.get(k).getDataName());
dataEntityVOT.setDataDepth(3);
dataEntityVOsT.add(dataEntityVOT);
}
dataEntityVOS.setDataEntityVOList(dataEntityVOsT);
dataEntityVOsS.add(dataEntityVOS);
}
dataEntityVOF.setDataEntityVOList(dataEntityVOsS);
dataEntityVOsF.add(dataEntityVOF);
}
return dataEntityVOsF;
}
9.配置集合边界
这里配置的集合边界并没有留出余量，如果数据有新增和修改就要重新执行全部的程序，不过无所谓了，这种数据也就是执行一次，资源啊，内存啊啥的，可以忽略。
private List<DataEntity> buildLeftRight(List<DataEntityVO> dataEntityVOS){
List<DataEntity> dataEntitieAll=new ArrayList<>();
int numLR=0;
for (int i = 0; i < dataEntityVOS.size(); i++) {
//一级
DataEntity dataEntityF=new DataEntity();
dataEntityVOS.get(i).setDataLeft(numLR);
numLR=numLR+1;
for (int j = 0; j <dataEntityVOS.get(i).getDataEntityVOList().size() ; j++) {
//二级
DataEntity dataEntityS=new DataEntity();
dataEntityVOS.get(i).getDataEntityVOList().get(j).setDataLeft(numLR);
numLR=numLR+1;
for (int k = 0; k < dataEntityVOS.get(i).getDataEntityVOList().get(j).getDataEntityVOList().size(); k++) {
//三级
DataEntity dataEntityT=new DataEntity();
dataEntityVOS.get(i).getDataEntityVOList().get(j).getDataEntityVOList().get(k).setDataLeft(numLR);
numLR=numLR+1;
dataEntityVOS.get(i).getDataEntityVOList().get(j).getDataEntityVOList().get(k).setDataRight(numLR);
numLR=numLR+1;
BeanUtils.copyProperties(dataEntityVOS.get(i).getDataEntityVOList().get(j).getDataEntityVOList().get(k),dataEntityT);
dataEntitieAll.add(dataEntityT);
}
dataEntityVOS.get(i).getDataEntityVOList().get(j).setDataRight(numLR);
numLR=numLR+1;
BeanUtils.copyProperties(dataEntityVOS.get(i).getDataEntityVOList().get(j),dataEntityS);
dataEntitieAll.add(dataEntityS);
}
dataEntityVOS.get(i).setDataRight(numLR);
BeanUtils.copyProperties(dataEntityVOS.get(i),dataEntityF);
dataEntitieAll.add(dataEntityF);
numLR=numLR+1;
}
return dataEntitieAll;
}
10.按照上一步配置好的数据入库
private void updateLereDataPersistence(List<DataEntity> dataEntitieAll){
//循环更新，有必要的大神可以做数据库循环，也可一次插入，在这里偷懒了，本类就是一次操作终身受用
//不要计较太多，先解决有无的问题
for (int i = 0; i <dataEntitieAll.size() ; i++) {
occupationDataDAO.updateById(dataEntitieAll.get(i));
}
}
11.从数据excel表到数据库完整数据的调用过程，以上方法写完之后，调用这个方法就完成了以上方法的调用。
protected void allDataPersistence(String path) throws IOException, InvalidFormatException {
List<RowEntity> rowEpntitys=this.buildEntity(path);//1
List<DataEntity> dataEntities= this.buildFirst(rowEpntitys);//2
this.firstDataPersistence(dataEntities);//3
this.secondDataPersistence(rowEpntitys);//4
this.thirdDataPersistence(rowEpntitys);//5
List<DataEntityVO> dataEntityVOS=readService.selectDataEntityVOTrees();//6
this.updateLereDataPersistence(this.buildLeftRight(dataEntityVOS));//7
}
12.excel内的数据
13.其他代码
mybits的mapper代码
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="data.dictionary.plateform.server.dao.OccupationDataDAO">
<resultMap id="BaseResultMap" type="data.dictionary.plateform.core.occupation.DataEntity">
<id column="id" property="id" jdbcType="INTEGER"/>
<result column="data_name" jdbcType="VARCHAR" property="dataName"/>
<result column="data_left" jdbcType="INTEGER" property="dataLeft"/>
<result column="data_right" jdbcType="INTEGER" property="dataRight"/>
<result column="data_depth" jdbcType="INTEGER" property="dataDepth"/>
<result column="parent_id" jdbcType="INTEGER" property="parentId"/>
</resultMap>
<insert id="insert" parameterType="data.dictionary.plateform.core.occupation.DataEntity" >
insert into t_data_occupation
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="dataName != null and dataName != ''">
data_name,
</if>
<if test="dataLeft != null ">
data_left,
</if>
<if test="dataRight != null ">
data_right,
</if>
<if test="dataDepth != null ">
data_depth,
</if>
<if test="parentId != null ">
parent_id,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="dataName != null and dataName != ''">
#{dataName,jdbcType=VARCHAR},
</if>
<if test="dataLeft != null">
#{dataLeft,jdbcType=INTEGER},
</if>
<if test="dataRight != null">
#{dataRight,jdbcType=INTEGER},
</if>
<if test="dataDepth != null">
#{dataDepth,jdbcType=INTEGER},
</if>
<if test="parentId != null">
#{parentId,jdbcType=INTEGER},
</if>
</trim>
</insert>
<update id="updateById" parameterType="data.dictionary.plateform.core.occupation.DataEntity">
update t_data_occupation
set
<if test="dataName != null and dataName != ''">
data_name = #{dataName,jdbcType=VARCHAR},
</if>
<if test="dataLeft != null and dataLeft !=0">
data_left = #{dataLeft,jdbcType=INTEGER},
</if>
<if test="dataRight != null and dataRight !=0">
data_right = #{dataRight,jdbcType=INTEGER},
</if>
<if test="dataDepth != null and dataDepth !=0">
data_depth = #{dataDepth,jdbcType=INTEGER}
</if>
where id = #{id,jdbcType=INTEGER};
</update>
<select id="selectLinked" parameterType="java.lang.Integer" resultType="data.dictionary.plateform.core.occupation.DataEntity">
SELECT
pare.*
FROM
t_data_occupation AS node,
t_data_occupation AS pare
WHERE
node.data_left BETWEEN pare.data_left AND pare.data_right
AND node.id=#{id,jdbcType=INTEGER}
ORDER BY pare.data_left
</select>
<insert id="insertForEach" parameterType="data.dictionary.plateform.core.occupation.DataEntity">
insert into t_data_occupation
(data_name,data_left,data_right,data_depth,parent_id)
values
<foreach collection="dataEntities" item="item" index="index" separator="," >
(#{item.dataName,jdbcType=VARCHAR},
#{item.dataLeft,jdbcType=INTEGER},
#{item.dataRight,jdbcType=INTEGER},
#{item.dataDepth,jdbcType=INTEGER},
#{item.parentId,jdbcType=INTEGER})
</foreach>
</insert>
</mapper>
mySQL数据库结构
-- ----------------------------
-- Table structure for t_data_occupation
-- ----------------------------
DROP TABLE IF EXISTS `t_data_occupation`;
CREATE TABLE `t_data_occupation` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data_name` varchar(64) DEFAULT NULL,
`data_left` int(64) DEFAULT NULL,
`data_right` int(64) DEFAULT NULL,
`data_depth` int(64) DEFAULT NULL,
`parent_id` int(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1061 DEFAULT CHARSET=utf8;
总结
这个接口输入是一个path，本地的excel文件地址：C:\Users\yongc\Desktop\data\1.xlsx
输出就是一个集合。
方法又简单又笨，但是这是一种比较可靠的解决方案，解决了有无的问题，虽然实施起来麻烦，但是一次操作就可以了，工程问题，能解决问题的就是好方法，暂且到这里了，以后有更好的方案会持续的放出来和大家一起交流。
3477
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
