我要评论springboot接收excel数据文件去重
1.创建测试表
set names utf8mb4; set foreign_key_checks = 0; -- ---------------------------- -- table structure for student -- ---------------------------- drop table if exists `student`; create table `student` ( `id` int(11) not null comment '学号', `name` varchar(64) character set utf8 collate utf8_general_ci not null comment '姓名', `year` int(11) not null comment '年份', `level` tinytext character set utf8 collate utf8_general_ci not null comment '等级', primary key (`id`, `name`, `year`) using btree ) engine = innodb character set = utf8 collate = utf8_general_ci row_format = dynamic; set foreign_key_checks = 1;
2.生产测试文档
3.springboot接收文件并入库
接收到上传的模板文件后，首先读取首行进行字段名称判断，然后一行行的读取excel表格中的数据，生成entity对象。
如果入库的数组大小超过1000，那就入库一次，然后入库使用的类似oracle的merge into语句的on duplicate key update
注意事项：
- 因为测试样例数据量小，上传文件大小限制在springboot中没有设置
- 生成的excel字段学号和年份单元格格式应该设置为数值
@autowired
private operatedao operatedao;
@postmapping(value = "/add/tax")
public string inserttaxdata(@requestparam("file") multipartfile file) {
try {
string filename = file.getoriginalfilename();
system.out.println(filename);
inputstream tps = (file.getinputstream());
final xssfworkbook tpworkbook = new xssfworkbook(tps);
sheet sheet = tpworkbook.getsheetat(0);
row headerrow = sheet.getrow(0);
if (headerrow.getcell(0).getstringcellvalue().equals("学号")
&& headerrow.getcell(1).getstringcellvalue().equals("姓名")
&& headerrow.getcell(2).getstringcellvalue().equals("年份")
&& headerrow.getcell(3).getstringcellvalue().equals("等级")
) {
int i = 1;
boolean flag = true;
list<studententity> entitylist = new arraylist<>();
studententity studententity;
while (flag) {
row row = sheet.getrow(i++);
if (row != null) {
studententity = new studententity();
cell id = row.getcell(0);
cell name = row.getcell(1);
cell year = row.getcell(2);
cell level = row.getcell(3);
studententity.setid((int) id.getnumericcellvalue());
studententity.setname(name.getstringcellvalue());
studententity.setyear((int) year.getnumericcellvalue());
studententity.setlevel( level.getstringcellvalue());
entitylist.add(studententity);
} else {
flag = false;
}
if (entitylist.size() > 1000) {
int rs = operatedao.addorupdatebatchtax(entitylist);
system.out.println(rs);
entitylist.clear();
}
}
if (entitylist.size() > 0) {
operatedao.addorupdatebatchtax(entitylist);
}
tps.close();
} else {
return "检查模板是否正确";
}
} catch (ioexception e) {
e.printstacktrace();
return e.getmessage();
}
return "success";
}@mapper
public interface operatedao {
integer addorupdatebatchtax(list<studententity> list);
}<?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="com.test.springboot.dao.operatedao">
<insert id="addorupdatebatchtax" parametertype="java.util.list">
insert into `student`(`id`, `name`, `year`, `level`)
values
<foreach collection="list" item="tax" index="index" separator=",">
(#{tax.id},#{tax.name},#{tax.year},#{tax.level})
</foreach>
on duplicate key update
level =values ( `level` )
</insert>
</mapper>总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论